SQL中如何通过Join关联实现基于地理位置的距离计算

ST_Distance_Sphere是MySQL 5.7+中计算地理距离的推荐方法，基于WGS84球面模型、单位米、支持空间索引；需确保POINT字段SRID为4326并建SPATIAL索引，避免误用ST_Distance或勾股运算。

MySQL 5.7+ 中用 ST_Distance_Sphere 做地理距离 Join

MySQL 5.7 起原生支持地理空间函数，ST_Distance_Sphere 是最稳妥的选择——它基于球面模型（WGS84），单位是米，精度够、性能可接受，且能走空间索引。

常见错误是直接用 ST_Distance：它返回的是笛卡尔平面距离（单位取决于坐标系），对经纬度数据完全失真；或者把经纬度当普通数值做勾股运算，误差动辄几公里。

• 确保两个表的地理字段都是 POINT 类型，并用 SRID 4326（WGS84）定义：

  ALTER TABLE locations ADD COLUMN coord POINT SRID 4326;

• 为该字段添加空间索引：

  CREATE SPATIAL INDEX idx_coord ON locations(coord);

• Join 时用 ST_Distance_Sphere 过滤，例如查离某点 5km 内的所有门店：

  SELECT b.name FROM branches b JOIN stores s ON ST_Distance_Sphere(b.coord, s.coord) <= 5000;

PostgreSQL + PostGIS 里用 ST_DWithin 更高效

PostGIS 的 ST_DWithin 比手动算距离快得多——它能利用 GIST 空间索引跳过大量无效计算，尤其适合“找附近 N 个点”或“半径内所有记录”这类场景。

注意别写成 ST_Distance(…) ：这会强制全表扫描，哪怕有索引也白搭。

• 字段必须是 GEOMETRY 或 GEOGRAPHY 类型；推荐用 GEOGRAPHY，单位默认米，无需指定 SRID：

  ALTER TABLE venues ADD COLUMN loc GEOGRAPHY(POINT, 4326);

• 创建索引：

  CREATE INDEX idx_venues_loc ON venues USING GIST(loc);

• 正确写法（自动走索引）：

  SELECT v.name FROM venues v JOIN events e ON ST_DWithin(v.loc, e.loc, 5000);

SQLite / Spatialite 中 ST_Distance 不可靠，改用 Haversine 手动算

Spatialite 的 ST_Distance 在地理坐标上默认按平面算，结果毫无意义；而 ST_DistanceSphere 存在版本兼容问题（3.0+ 才稳定）。实际项目中，不如直接嵌入 Haversine 公式更可控。

公式本身不复杂，但要注意：SQLite 没有原生弧度函数，必须用 CAST 和 PI() 自行转换，且所有角度必须先转弧度。

• 示例（查离 (116.4, 39.9) 10km 内的点）：

  SELECT name FROM places WHERE 6371 * acos(cos(radians(39.9)) * cos(radians(lat)) * cos(radians(lng) - radians(116.4)) + sin(radians(39.9)) * sin(radians(lat))) <= 10;

• lat 和 lng 必须是列名，不能是字符串；单位是千米，6371 是地球平均半径
• 无法利用索引，大数据量时务必加前置过滤（如先用 BBOX 粗筛：lng BETWEEN … AND … AND lat BETWEEN … AND …）

跨表 Join 距离计算时最容易被忽略的三件事

不是函数选对就万事大吉。真实业务里，性能崩盘和结果错乱往往来自这几个隐性环节：

• 坐标系不一致：一个表用 WGS84（EPSG:4326），另一个用 GCJ-02（国测局偏移），直接算距离等于随机数；必须统一转成同一体系再计算
• NULL 坐标没处理：ST_Distance_Sphere(NULL, pt) 返回 NULL，会导致整行被 Join 排除——加 WHERE coord IS NOT NULL 显式过滤
• JOIN 条件里混用非空间字段：比如 ON a.id = b.store_id AND ST_DWithin(a.loc, b.loc, 1000)，若 store_id 匹配度高，可能触发笛卡尔积放大，先用主键关联再加距离过滤更安全

距离计算本身不难，难的是让每一步都落在地理语义和数据库执行引擎的交点上——坐标系、索引、NULL、单位，漏掉任意一个，结果就不可信。