最近做了一个项目:根据当前位置查询出附近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
]
}
]
}
}