最近做了一个项目：根据当前位置查询出附近0.5km的商家并按由近到远排序

表结构如下

image-20200629091932967

原生sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT  
  *,(  
    6371 * acos (  
      cos ( radians( 34.21702164242081 ) )  
      * cos( radians( latitude ) )  
      * cos( radians( longitude ) - radians( 113.76823781349185 ) )  
      + sin ( radians( 34.21702164242081 ) )  
      * sin( radians( latitude ) )  
    )  
  ) AS distance  //距离
FROM merchant
HAVING distance < 0.5  //筛选距离小于0.5km的商家(可不写)，如果没查到数据就是没有小于0.5km的商家
ORDER BY distance  //根据距离远近来排序 默认升序 （可不写）
LIMIT 0 , 10;  //显示前10条数据（可不写）

查询结果

image-20200629092457795

使用laravel框架

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
use Illuminate\Support\Facades\DB;

...
    
public function test(){
    $latitude = 34.21702164242081;
    $longitude = 113.76823781349185;
    return DB::table('merchant')
        ->select('*', DB::raw(
            '6371 * acos(
                    cos ( radians( '.$latitude.' ) )
                    * cos( radians ( latitude ) )
                    * cos( radians( longitude ) - radians( '.$longitude.' ) )
                    + sin( radians( '.$latitude.' ))
                    * sin( radians( latitude ) )
                )
                as distance')
                )
        ->having('distance','<',0.5)
        ->orderBy('distance')
        ->paginate(10);
}

结果

image-20200629114059813

注意： MySql5.7一下会报错,所以使用MySql版本大于5.7的吧!

使用elasticsearch搜索

首先,介绍一下 Geo-point ，从es官网获取翻译

Geo-point数据类型
geo_point类型的字段接受经纬度对，可以使用:

  • 在边界框内、在中心点的一定距离内、在多边形内或在geo_shape查询内查找地理点。
  • 从地理位置或从中心点的距离聚合文档。
  • 将距离集成到文档的相关性评分中。
  • 按距离对文件进行排序。
  1. 指定字段类型
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
PUT attractions
{
  "mappings": {
    "properties": {
      "name": {
        "type": "text"
      },
      "location": {
        "type": "geo_point"
      }
    }
  }
}

=> 返回
{
  "acknowledged" : true,
  "shards_acknowledged" : true,
  "index" : "attractions"
}
  1. 添加数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
PUT attractions/_doc/1
{
  "name":"中国建设银行",
  "location": {
    "lat": 34.22278812802373,
    "lon": 113.76726148941043
  }
}

PUT attractions/_doc/2
{
  "name":"好想你",
  "location": {
    "lat": 34.22041060947206,
    "lon": 113.76440761901858
  }
}

PUT attractions/_doc/3
{
  "name":"举个栗子",
  "location": {
    "lat": 34.22023318002376,
    "lon": 113.76751898147586
  }
}

PUT attractions/_doc/4
{
  "name":"长葛市毛主席纪念馆",
  "location": {
    "lat": 34.225502675366116,
    "lon": 113.76586674072269
  }
}

=> 返回
{
  "_index" : "attractions",
  "_type" : "_doc",
  "_id" : "1",
  "_version" : 1,
  "result" : "created",
  "_shards" : {
    "total" : 2,
    "successful" : 1,
    "failed" : 0
  },
  "_seq_no" : 0,
  "_primary_term" : 1
}
...
  1. 搜索1km
  • 无排列
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
GET attractions/_doc/_search
{
  "query": {
    "constant_score": {
      "filter": {
        "geo_distance": {
          "distance": "1km",
          "location": {
            "lat": 34.21677323109457,
            "lon": 113.76829145767215
          }
        }
      }
    }
  }
}

=> 返回
{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "attractions",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "name" : "中国建设银行",
          "location" : {
            "lat" : 34.22278812802373,
            "lon" : 113.76726148941043
          }
        }
      },
      {
        "_index" : "attractions",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : 1.0,
        "_source" : {
          "name" : "举个栗子",
          "location" : {
            "lat" : 34.22023318002376,
            "lon" : 113.76751898147586
          }
        }
      },
      {
        "_index" : "attractions",
        "_type" : "_doc",
        "_id" : "4",
        "_score" : 1.0,
        "_source" : {
          "name" : "长葛市毛主席纪念馆",
          "location" : {
            "lat" : 34.225502675366116,
            "lon" : 113.76586674072269
          }
        }
      }
    ]
  }
}
  • 按远近排序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
GET attractions/_doc/_search
{
  "query": {
    "constant_score": {
      "filter": {
        "geo_distance": {
          "distance": "1km",
          "location": {
            "lat": 34.21677323109457,
            "lon": 113.76829145767215
          }
        }
      }
    }
  },
  "sort": [
    {
      "_geo_distance": {
        "location": [
          {
            "lat": 34.21677323109457,
            "lon": 113.76829145767215
          }
        ],
        "unit": "km",
        "distance_type": "arc",
        "order": "asc",
        "validation_method": "STRICT"
      }
    }
  ]
}

=> 返回
{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [
      {
        "_index" : "attractions",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : null,
        "_source" : {
          "name" : "举个栗子",
          "location" : {
            "lat" : 34.22023318002376,
            "lon" : 113.76751898147586
          }
        },
        "sort" : [
          0.39122806957571005
        ]
      },
      {
        "_index" : "attractions",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : null,
        "_source" : {
          "name" : "中国建设银行",
          "location" : {
            "lat" : 34.22278812802373,
            "lon" : 113.76726148941043
          }
        },
        "sort" : [
          0.6754979876459357
        ]
      },
      {
        "_index" : "attractions",
        "_type" : "_doc",
        "_id" : "4",
        "_score" : null,
        "_source" : {
          "name" : "长葛市毛主席纪念馆",
          "location" : {
            "lat" : 34.225502675366116,
            "lon" : 113.76586674072269
          }
        },
        "sort" : [
          0.9959412471457036
        ]
      }
    ]
  }
}