? ? ? ? 需求描述：項目中需要通過經緯度坐標查詢目標地所在的行政區。
? ? ? ? 解決思路大致有種，使用es和mysql分別查詢。? ?
? ? 1、使用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"}}]
}? ? ? ? 獲取舉例最近的排序不能漏了
?2、使用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;? ? ? ? 3、其他方式
? ? ? ? 如果帶查詢的數據項不變化，類似于行政區劃的坐標，還可以把這些數據加載到內存中進行計算。
? ? ? ? 3.1 Java-使用 Haversine 公式來計算（不依賴三方庫）
????????創建表示位置的類
public class Location {private double lon;private double lat;public Location(double lon, double double lat) {this.lon = lon;this.lat = lat;}// Getter 和 Setter 方法}????????使用 Haversine 公式計算兩點間的距離
public class DistanceCalculator {private static final int EARTH_RADIUS = 6371; // 地球半徑，單位為公里/*** 計算兩個經緯度點之間的距離*/public static double calculateDistance(Location loc1, Location loc2) {double lat1 = Math.toRadians(loc1.getLat());double lon1 = Math.toRadians(loc1.getLon());double lat2 = Math.toRadians(loc2.getLat());double lon2 = Math.toRadians(loc2.getLon());double dlat = lat2 - lat1;double dlon = lon2 - lon1;double a = Math.sin(dlat / 2) * Math.sin(dlat / 2) +Math.cos(lat1) * Math.cos(lat2) *Math.sin(dlon / 2) * Math.sin(dlon / 2);double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));return EARTH_RADIUS * c; // 返回單位為公里}
}????????查找最近的數據點
public class NearestLocationFinder {public static LocationData findNearestLocation(List<LocationData> locations, Location targetLocation) {LocationData nearest = null;double minDistance = Double.MAX_VALUE;for (LocationData location : locations) {Location currentLocation = new Location(location.getLocation().getLon(), location.getLocation().getLat());double distance = DistanceCalculator.calculateDistance(currentLocation, targetLocation);if (distance < minDistance) {minDistance = distance;nearest = location;}}return nearest;}
}? ? ? ? 調用方法
public class Main {public static void main(String[] args) {// 已加載所有的位置數據List<LocationData> locations = loadData();// 輸入的經緯度Location targetLocation = new Location(115.65, 34.43);// 查找最近的位置LocationData nearest = NearestLocationFinder.findNearestLocation(locations, targetLocation);System.out.println("最近的位置是: " + nearest.getName());}// 加載數據private static List<LocationData> loadData() {return new ArrayList<>();}
}? ? ? ? 4、Java-使用JTS STRtree（依賴三方庫）
? ? ? ? maven依賴
<dependency><groupId>org.locationtech.jts</groupId><artifactId>jts-core</artifactId><version>1.18.2</version>
</dependency>?????????調用方法
public class NearestPointFinder {public static void main(String[] args) {// 創建一個包含所有位置信息的列表List<LocationData> locations = loadData();// 輸入的經緯度double lon = 115.65, lat = 34.43;// 使用JTS的STRtree加速查詢STRtree tree = new STRtree();GeometryFactory geometryFactory = new GeometryFactory();for (LocationData location : locations) {Point point = geometryFactory.createPoint(new Coordinate(location.getLocation().getLon(), location.getLocation().getLat()));tree.insert(point.getEnvelopeInternal(), location);}Point targetPoint = geometryFactory.createPoint(new Coordinate(lon, lat));LocationData nearest = (LocationData) tree.nearestNeighbour(targetPoint.getEnvelopeInternal(), null);System.out.println("最近的位置是: " + nearest.getName());}private static List<LocationData> loadData() {// 加載位置數據return new ArrayList<>();}
}? ? ? ? 還有其他的一些三方庫：H3 by Uber、GeoTools、Spatial4j等。
總結：沒有最好的，只有最適合的，按需設計。
