基于PostGIS Geometry在同表编写SQL查询，找出包含的地理区域

嘿，作为SQL新手刚接触空间查询确实会有点懵，咱们一步步来搞定这个需求！

首先得确认你的PostgreSQL数据库已经启用了PostGIS扩展——这是处理地理几何数据的核心依赖，如果还没启用，先执行这条语句：

CREATE EXTENSION IF NOT EXISTS postgis;

核心思路 #

我们需要完成两个关键操作：

• 用空间函数判断一个区域的几何范围是否包含另一个区域
• 将被包含的多个区域名称聚合为列表格式

完整SQL查询代码 #

SELECT
    a.Name AS Name,
    a.Type AS Type,
    -- 聚合被包含的其他区域名称为有序数组，无匹配时返回空数组
    COALESCE(array_agg(b.Name ORDER BY b.Name), '{}'::text[]) AS Contains
FROM
    geo_areas a
-- 左连接确保没有包含任何区域的记录也能被保留
LEFT JOIN
    geo_areas b 
    ON ST_Contains(a.Geometry, b.Geometry) 
    -- 排除自身，只统计"其他区域"
    AND a.Name != b.Name
GROUP BY
    a.Name, a.Type
-- 按区域类型排序（州→城市→城镇），和示例结果格式一致
ORDER BY
    CASE a.Type 
        WHEN 'State' THEN 1 
        WHEN 'City' THEN 2 
        WHEN 'Town' THEN 3 
    END, 
    a.Name;

关键知识点解释 #

• ST_Contains(a.Geometry, b.Geometry)：PostGIS提供的核心空间函数，用于判断a的几何范围是否完全包含b的几何。
• LEFT JOIN：如果某个区域没有包含任何其他区域（比如示例里的Philadelphia和Pittsburgh），这条记录依然会被保留，不会被过滤掉。
• array_agg(b.Name ORDER BY b.Name)：把多个被包含的区域名称聚合成一个有序数组，ORDER BY让列表看起来更整齐。
• COALESCE(..., '{}'::text[])：当没有匹配的被包含区域时，返回空数组[]而不是NULL，完全符合你给出的示例结果。
• 排序规则：用CASE语句把州、城市、城镇按优先级排序，让输出结果和示例结构一致。

额外注意事项 #

1. 空间参考系一致性：确保Geometry字段的SRID（空间参考标识符）是统一的，否则ST_Contains可能返回错误结果。可以用SELECT ST_SRID(Geometry) FROM geo_areas;检查，如果不一致，用ST_Transform转换为相同SRID。
2. 性能优化：如果表中数据量很大，建议给Geometry字段创建空间索引，大幅提升查询速度：

CREATE INDEX idx_geo_areas_geometry ON geo_areas USING gist(Geometry);

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