如何使用PostGIS从单表中筛选所有相交多边形组

现有一张存储多组相交多边形的表，需要筛选出所有相交的多边形组。但使用以下递归查询实现时，存在分组重复（如root_id为1和2的分组包含相同多边形）、组内多边形重复（如root_id为7的分组内出现重复的8、9）的问题：

原递归查询代码 #

WITH RECURSIVE cte AS (

     SELECT id AS root_id,
            id, ARRAY[id] AS path,
            geom
       FROM polygons

      UNION ALL
     SELECT cte.root_id,
            t.id, path || t.id,
            t.geom
       FROM polygons AS t,
            cte
      WHERE t.id <> ALL(cte.path)
        AND ST_Intersects(t.geom, cte.geom) 
)

SELECT root_id, ARRAY_AG(id)
  FROM cte
 GROUP BY root_id
 ORDER BY root_id

原查询结果 #

优化后的解决方案 #

针对上述问题，通过固定组标识、严格递归路径、聚合去重三个方向优化查询：

WITH RECURSIVE cte AS (
    -- 初始节点：以每个多边形为起点，记录组的最小ID作为唯一标识
    SELECT 
        id AS current_id,
        id AS group_root,
        ARRAY[id] AS members
    FROM polygons

    UNION ALL

    -- 递归扩展：只加入与当前组相交、未在组内且ID大于组根的多边形
    SELECT 
        t.id AS current_id,
        cte.group_root,
        array_append(cte.members, t.id) AS members
    FROM polygons t
    JOIN cte ON 
        ST_Intersects(t.geom, cte.geom)
        AND t.id <> ALL(cte.members)
        AND t.id > cte.group_root -- 避免反向递归生成重复组
),
-- 拆解成员并去重
grouped_members AS (
    SELECT 
        group_root,
        unnest(members) AS polygon_id
    FROM cte
)
-- 聚合得到唯一的相交组，成员有序排列
SELECT 
    group_root AS group_id,
    array_agg(DISTINCT polygon_id ORDER BY polygon_id) AS polygon_group
FROM grouped_members
GROUP BY group_root
ORDER BY group_root;

优化说明 #

1. 消除重复分组：用group_root（组内最小ID）作为唯一组标识，递归时只允许加入ID大于group_root的多边形，确保每个相交组只会被生成一次
2. 解决组内重复：最后聚合时使用DISTINCT去重，同时ORDER BY让成员ID有序，结果更直观
3. 递归逻辑优化：通过t.id > cte.group_root限制递归方向，减少无效的递归分支，提升查询效率

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