如何在PostgreSQL中找到与指定位置最近的空间点

你当前的写法是手动指定表A（place）的地址生成多列距离，既不灵活也无法批量处理。要实现将所有距离放在同一列并筛选出最近的记录，可以通过关联表的方式来实现：

基础写法（仅取最近的一条） #

直接计算表A中所有位置到表B（task）指定位置的距离，按距离升序排序后取第一行：

SELECT 
    p.id,
    p.address,
    ST_Distance(t.the_geom, p.location) AS distance
FROM place p
-- 关联表B中id=15的空间位置
CROSS JOIN (SELECT the_geom FROM task WHERE id = 15) t
-- 按距离从小到大排序
ORDER BY distance ASC
-- 取距离最小的第一条记录
LIMIT 1;

进阶写法（返回所有距离最小的记录） #

如果存在多个位置与表B的距离相同且都是最小值，用窗口函数可以返回所有符合条件的记录：

SELECT id, address, distance
FROM (
    SELECT 
        p.id,
        p.address,
        ST_Distance(t.the_geom, p.location) AS distance,
        -- 按距离排名，相同距离排名一致
        RANK() OVER (ORDER BY ST_Distance(t.the_geom, p.location) ASC) AS rank_num
    FROM place p
    CROSS JOIN (SELECT the_geom FROM task WHERE id = 15) t
) ranked_places
-- 筛选排名第一的记录
WHERE rank_num = 1;

核心逻辑说明 #

• 用CROSS JOIN将表B中指定的单个空间位置与表A的所有记录关联，每条表A的记录都会自动计算到目标点的距离。
• 无需手动指定表A的地址，自动遍历所有表A的空间位置。
• 通过排序或窗口函数，轻松筛选出距离最小的记录。

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