如何查询表中经纬度与当前行差值小于指定容差的所有记录

需要为上表新增一列，返回每条数据对应的所有满足 abs(lon_i - lon_j) < tol 且 abs(lat_i - lat_j) < tol 且 i != j 的其他ID值。

你最初的自连接思路逻辑上是通顺的，但存在两个问题：

1. 语法层面：你写的SQL没有显式声明和副本表的关联逻辑，无法直接取到lon_2、lat_2字段，会执行报错
2. 性能层面：无索引的自连接本质是全表笛卡尔积计算，时间复杂度为O(n²)，数据量超过1000条后性能会出现明显下降。

方案1：添加联合索引（改动最小，性价比最高） #

先给经纬度字段加联合索引，查询时先做范围裁剪过滤掉绝大多数无效数据，再做精确差值判断，性能比无索引自连接提升10~100倍：

-- 1. 新增联合索引
CREATE INDEX idx_lon_lat ON your_table (lon, lat);
-- 2. 新增存储近邻ID的列，可根据数据库类型选择JSON/ARRAY类型更适配
ALTER TABLE your_table ADD COLUMN nearby_ids TEXT;
-- 3. 批量更新近邻ID
UPDATE your_table t1
SET nearby_ids = (
    -- 聚合函数根据数据库适配：MySQL用GROUP_CONCAT，PostgreSQL用STRING_AGG，SQL Server用STRING_AGG
    SELECT GROUP_CONCAT(t2.id)
    FROM your_table t2
    WHERE t2.id != t1.id
    -- 先走索引做范围过滤，避免全表扫描
    AND t2.lon BETWEEN t1.lon - tol AND t1.lon + tol
    AND t2.lat BETWEEN t1.lat - tol AND t1.lat + tol
    -- 再做精确差值校验
    AND ABS(t1.lon - t2.lon) < tol
    AND ABS(t1.lat - t2.lat) < tol
);

方案2：用空间扩展优化（适合10万条以上地理数据场景） #

如果你的数据量较大，直接用数据库内置的地理空间扩展处理，性能比普通联合索引还要高一个数量级：

• MySQL可以存储POINT类型的经纬度，加空间索引后用MBRContains做范围匹配
• PostgreSQL可以搭配PostGIS扩展，用GIST空间索引配合ST_DWithin查询
  示例PostGIS版本代码：

-- 新增空间字段并构建索引
ALTER TABLE your_table ADD COLUMN geom GEOMETRY(Point, 4326);
UPDATE your_table SET geom = ST_SetSRID(ST_MakePoint(lon, lat), 4326);
CREATE INDEX idx_geom ON your_table USING GIST(geom);
-- 批量更新近邻ID
UPDATE your_table t1
SET nearby_ids = (
    SELECT STRING_AGG(t2.id::TEXT, ',')
    FROM your_table t2
    WHERE t2.id != t1.id
    -- tol如果是度单位可以乘以111319转为米单位匹配
    AND ST_DWithin(t1.geom, t2.geom, tol * 111319)
);

方案3：预分桶计算（适合离线批量处理场景） #

如果是离线批处理场景，可以先按tol的大小给经纬度划分网格桶，仅对同一个桶和相邻桶的点做差值计算，直接过滤掉90%以上的无效匹配，性能比普通自连接高几十倍。

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