ES 使用geo point 查询离目标地址最近的数据
需求描述:项目中需要通过经纬度坐标查询目标地所在的行政区。
解决思路有两种,使用mysql和es分别查询。
1、使用mysql进行查询
将带有经纬度坐标的省市区数据存入mysql中,使用mysql直接计算,表结构及查询sql如下。
表结构:
CREATE TABLE `sys_district` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
`parent_id` INT(10) UNSIGNED NOT NULL COMMENT '父栏目',
`name` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
`zipcode` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`pinyin` VARCHAR(100) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
`lng` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
`lat` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
`level` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`sort` TINYINT(3) UNSIGNED NOT NULL DEFAULT '50' COMMENT '排序',
`location` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
PRIMARY KEY (`id`) USING BTREE
)
COMMENT='(公共)区域数据'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
查询sql:
SELECT * FROM sys_district WHERE ABS(lat - 34.4328) + ABS(lng - 115.88) = (SELECT MIN(ABS(lng - 115.88) + ABS(lat - 34.4328)) FROM sys_district ) LIMIT 1;
使用mysql计算可优化的地方在于,新版本mysql提供了空间几何字段类型POINT,优化后新表结构如下。
CREATE TABLE `sys_district` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
`parent_id` INT(10) UNSIGNED NOT NULL COMMENT '父栏目',
`name` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8mb3_general_ci',
`zipcode` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`pinyin` VARCHAR(100) NOT NULL DEFAULT '' COLLATE 'utf8mb3_general_ci',
`lng` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8mb3_general_ci',
`lat` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8mb3_general_ci',
`geom` POINT NOT NULL COMMENT 'geo',
`level` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`sort` TINYINT(3) UNSIGNED NOT NULL DEFAULT '50' COMMENT '排序',
`location` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb3_general_ci',
PRIMARY KEY (`id`) USING BTREE,
SPATIAL INDEX `geom` (`geom`)
)
COMMENT='(公共)区域数据'
COLLATE='utf8mb3_general_ci'
ENGINE=InnoDB
;
字段设置:
ALTER TABLE `sys_district`
ADD COLUMN `geom` POINT NULL AFTER `lat`;
UPDATE sys_district SET geom = ST_PointFromText(CONCAT('POINT(', lng, ' ', lat, ')')) ;
ALTER TABLE sys_district ADD SPATIAL INDEX(geom);
查询sql如下:
ST_PointFromText(CONCAT('POINT(', lng, ' ', lat, ')'))
将表中的经度和纬度转换为几何点。
ST_Distance_Sphere(geom, ST_PointFromText(CONCAT('POINT(', 120.15, ' ', 30.28, ')')))
计算每个点与目标点之间的距离(单位为米)。
ORDER BY distance
按距离从小到大排序
SELECT id, name, lng, lat,
ST_Distance_Sphere(geom, ST_PointFromText(CONCAT('POINT(', 120.15, ' ', 30.28, ')'))) AS distance
FROM sys_district
ORDER BY distance
LIMIT 3;
2、使用es进行查询
将带有经纬度坐标的省市区数据存入es中,mappings字段使用geo point类型,索引及查询dsl如下。
geo point文档地址:
Geo-distance query | Elasticsearch Guide [8.6] | Elastic
Sort search results | Elasticsearch Guide [8.6] | Elastic
mappings结构:
PUT /sys_district
{
"settings": {
"index": {
"number_of_shards": 1,
"number_of_replicas": 1
}
},
"mappings": {
"properties": {
"id": {
"type": "long"
},
"parent_id": {
"type": "long"
},
"name": {
"type": "keyword"
},
"zipcode": {
"type": "integer"
},
"pinyin": {
"type": "keyword"
},
"location": {
"type": "geo_point" // 如果用于地理坐标,可以考虑使用 geo_point 类型
},
"level": {
"type": "byte"
},
"sort": {
"type": "byte"
}
}
}
}
dsl语句:
# 搜索坐标点附近的数据
GET sys_district/_search
{
"from": 0,
"size": 3,
"query": {
"bool": {
"must": {
"match_all": {}
},
"filter": [
{
"geo_distance": {
# 半径内距离限制
"distance": "100km",
"location": {
# 目的地坐标
"lat": 34.4328,
"lon": 115.88
}
}
},
{
"term": {
"level": "3"
}
}
]
}
},
# 排序
"sort" : [
{
"_geo_distance" : {
"location" : {
"lat" : 34.4328,
"lon" :115.88
},
"order" : "asc",
"unit" : "km"
}
}
]
}
获取举例最近的排序不能漏了