MySQL基于Geometry实现全库地址距离排序及最近邻提取方法问询

提取指定坐标的X个最近邻地址 #

你现有SQL只需追加LIMIT子句即可实现该需求，示例取最近20个地址的写法如下：

SELECT 
  address.*,
  st_distance_sphere(
    address.coordinates,
    POINT(-1.1337599754333496, 53.52299880981445 )
  ) AS distance
FROM address
ORDER BY distance ASC
LIMIT 20;

将LIMIT后的数字替换为你需要的X即可，SQL中额外增加了address.*字段，可直接返回地址的完整信息。

全库地址距离排序/全量最近邻查询 #

小数据量（万级以内）方案 #

通过自连接实现全库地址两两距离计算，可根据需求返回排序后的全量地址对，或每个地址的X个最近邻：

• 全量地址对按距离排序（去重）

SELECT 
  a.id AS address_a_id,
  b.id AS address_b_id,
  st_distance_sphere(a.coordinates, b.coordinates) AS distance
FROM address a
INNER JOIN address b 
  ON a.id < b.id -- 避免重复计算相同地址对、排除自身匹配
ORDER BY distance ASC;

• 每个地址取前X个最近邻（示例取前5个）

WITH address_distance_rank AS (
  SELECT 
    a.id AS source_address_id,
    b.id AS target_address_id,
    st_distance_sphere(a.coordinates, b.coordinates) AS distance,
    ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY st_distance_sphere(a.coordinates, b.coordinates) ASC) AS rank_num
  FROM address a
  INNER JOIN address b ON a.id != b.id
)
SELECT * FROM address_distance_rank WHERE rank_num <= 5;

大数据量优化建议 #

如果表数据量超过10万，全表自连接会产生大量笛卡尔积导致性能极差，可做以下优化：

• 给coordinates字段建立空间索引
• 数据库支持空间KNN查询的情况下（如PostGIS、MySQL 8.0+），用对应空间运算符走索引查询，性能可提升数十倍

内容的提问来源于stack exchange，提问作者BoxRec