PostGIS如何查询空间表中shape_area大于1000m²的城市间最小距离

实现逻辑 #

• 先筛选所有shape_area > 1000㎡的城市记录
• 通过自连接配对城市，使用a.city_id < b.city_id避免重复计算同一对城市，同时排除自身配对场景
• 调用PostGIS提供的ST_Distance函数计算几何距离，由于你使用的26986投影单位为米，计算结果单位直接为米，无需额外转换

具体SQL代码 #

方案1：获取最小距离对应的城市对明细 #

SELECT 
    a.city_id AS 城市AID,
    a.city AS 城市A名称,
    b.city_id AS 城市BID,
    b.city AS 城市B名称,
    ST_Distance(a.geom, b.geom) AS 距离_米
FROM cities a
INNER JOIN cities b 
    ON a.city_id < b.city_id
WHERE 
    a.shape_area > 1000
    AND b.shape_area > 1000
ORDER BY 距离_米 ASC
LIMIT 1;

方案2：仅查询全局最小距离数值 #

SELECT MIN(ST_Distance(a.geom, b.geom)) AS 最小距离_米
FROM cities a
INNER JOIN cities b 
    ON a.city_id < b.city_id
WHERE 
    a.shape_area > 1000
    AND b.shape_area > 1000;

性能优化建议 #

如果城市数据量较大，可执行以下语句创建索引提升查询效率：

-- 创建空间索引加速几何距离计算
CREATE INDEX idx_cities_geom ON cities USING GIST(geom);
-- 创建普通索引加速条件筛选
CREATE INDEX idx_cities_shape_area ON cities(shape_area);
CREATE INDEX idx_cities_city_id ON cities(city_id);

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