基于PostGIS geometry字段为指定居民列表查询每个用户的2个最近邻居

问题原因 #

你之前的查询没有返回结果，核心是两处笔误：

1. 多处错误引用了不存在的字段p.position，你的home表存储坐标的字段实际是map_position
2. 第一次查询的关联条件写错为tmp_tab.id=inhabitants.it，正确应为tmp_tab.id=inhabitants.id
   另外手动设置±0.0005的坐标过滤阈值逻辑不合理，重复子查询也会拉低查询效率。

优化解决方案 #

用PostGIS原生的KNN最近邻查询搭配LATERAL横向关联，不需要手动设置过滤范围，天然支持批量ID查询，且能利用空间索引提升查询效率：

WITH target_inhabitants AS (
    -- 提取待查询的目标居民列表及对应的住宅坐标
    SELECT 
        i.id AS inhabitant_id,
        h.map_position AS home_geo
    FROM inhabitants i
    JOIN home h ON i.home_id = h.id
    WHERE i.id = ANY(ARRAY[1398, 1399, 1400]) -- 替换成你要查询的批量居民ID列表
),
all_neighbor_candidates AS (
    -- 提取所有可作为邻居的居民信息及坐标
    SELECT
        i.id AS neighbor_id,
        i.first_name,
        i.last_name,
        h.humanreadiable_name AS neighbor_home_name,
        h.map_position AS neighbor_geo,
        ST_Y(ST_Centroid(ST_Transform(h.map_position, 4326))) AS neighbor_lat,
        ST_X(ST_Centroid(ST_Transform(h.map_position, 4326))) AS neighbor_lon
    FROM inhabitants i
    JOIN home h ON i.home_id = h.id
    -- 排除待查询的居民自身，不需要可以删除该行
    WHERE i.id NOT IN (SELECT inhabitant_id FROM target_inhabitants)
)
SELECT 
    t.inhabitant_id AS source_resident_id,
    a.neighbor_id,
    a.first_name AS neighbor_first_name,
    a.last_name AS neighbor_last_name,
    a.neighbor_home_name,
    a.neighbor_lat,
    a.neighbor_lon,
    -- 可选输出距离，单位为米，用球面距离计算更准确
    ST_DistanceSphere(
        ST_Centroid(ST_Transform(t.home_geo, 4326)), 
        ST_Centroid(ST_Transform(a.neighbor_geo, 4326))
    ) AS distance_meter
FROM target_inhabitants t
-- 为每个目标居民匹配最近的2个邻居
CROSS JOIN LATERAL (
    SELECT *
    FROM all_neighbor_candidates aoi
    ORDER BY aoi.neighbor_geo <-> t.home_geo
    LIMIT 2
) a
ORDER BY t.inhabitant_id, distance_meter ASC;

说明 #

• <->是PostGIS的空间距离运算符，做排序时会自动调用空间索引，数据量大的情况下也能保持很高的查询效率
• 如果你的map_position字段本身的SRID就是4326，可以去掉所有ST_Transform转换步骤，进一步提升性能
• 如果需要排除同住宅的居民，可以在LATERAL子查询中额外加过滤条件aoi.neighbor_geo != t.home_geo

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