PostgreSQL 9.6+PostGIS：高效生成关联建筑ID分组集合的优化问询

我懂你现在的痛点：用自连接过滤冗余接触对的方式在数据量上来后慢得离谱——这太正常了，因为这种自连接本质是O(n²)的时间复杂度，数据一多就卡得不行。你的核心需求其实是找出所有空间连通的建筑组（连通分量），然后生成最小的关联边集合（比如每个连通组里只保留从根节点到其他节点的连接），下面给你两个性能拉满的实现方案：

方案一：用PostGIS内置聚类函数（最简便高效） #

如果你的PostGIS版本在2.2及以上（PostgreSQL 9.6搭配的PostGIS通常满足这个条件），直接用ST_ClusterIntersecting函数就搞定了——这个函数是PostGIS专门为空间连通聚类设计的，底层是C语言实现，性能碾压纯SQL逻辑。

适配你数据逻辑的代码 #

WITH isolatedPonctualBuildings AS (
    SELECT DISTINCT ON (bat.pkid) 
        bat.pkid, 
        bat.pkid_emprise, 
        bat.origine, 
        bat.origine_id, 
        bat.geom 
    FROM Temp_batiments_sites AS bat 
    LEFT JOIN Temp_recoupements_bâtiments AS recoup 
        ON bat.pkid = recoup.pkid_batiment2 
    WHERE bat.type_geometry = 'Point' 
        AND recoup.pkid IS NULL
),
building_clusters AS (
    SELECT 
        pkid,
        pkid_emprise,
        origine,
        origine_id,
        geom,
        -- 给同一连通组的建筑分配相同的聚类ID
        ST_ClusterIntersecting(geom) OVER () AS cluster_id
    FROM isolatedPonctualBuildings
)
-- 生成最小关联集合：每个组内用最小pkid作为根节点，关联其他成员
SELECT 
    MIN(pkid) OVER (PARTITION BY cluster_id) AS root_pkid,
    pkid AS member_pkid
FROM building_clusters
WHERE pkid != MIN(pkid) OVER (PARTITION BY cluster_id);

这个方案的优势：

• 代码极简，不用处理复杂的连接和过滤逻辑
• 性能拉满，内置函数的执行效率远高于纯SQL
• 直接得到分组ID，后续可以基于聚类ID做任何聚合操作

方案二：递归CTE实现并查集（兼容低版本PostGIS） #

如果你的PostGIS版本偏低，或者需要更精细的控制，可以用递归CTE实现**并查集（Union-Find）**逻辑，给每个连通组分配一个根节点，最终生成最小关联边。

适配你数据逻辑的代码 #

WITH isolatedPonctualBuildings AS (
    SELECT DISTINCT ON (bat.pkid) 
        bat.pkid, 
        bat.pkid_emprise, 
        bat.origine, 
        bat.origine_id, 
        bat.geom 
    FROM Temp_batiments_sites AS bat 
    LEFT JOIN Temp_recoupements_bâtiments AS recoup 
        ON bat.pkid = recoup.pkid_batiment2 
    WHERE bat.type_geometry = 'Point' 
        AND recoup.pkid IS NULL
),
building_connections AS (
    -- 先得到去重的单向接触对（和你原来的逻辑一致）
    SELECT 
        bat1.pkid AS pkid_bat1,
        bat2.pkid AS pkid_bat2
    FROM isolatedPonctualBuildings bat1
    JOIN isolatedPonctualBuildings bat2 
        ON bat1.pkid_emprise = bat2.pkid_emprise
        AND ST_Intersects(bat1.geom, bat2.geom)
        AND (bat1.origine != bat2.origine OR bat1.origine_id != bat2.origine_id)
    WHERE bat1.pkid < bat2.pkid
),
connected_components AS (
    -- 初始状态：每个建筑自己作为根节点
    SELECT pkid AS node, pkid AS root
    FROM isolatedPonctualBuildings
    UNION ALL
    -- 递归更新：将子节点的根指向父节点的根，实现连通合并
    SELECT cc.node, bc.pkid_bat1 AS root
    FROM connected_components cc
    JOIN building_connections bc 
        ON cc.root = bc.pkid_bat2
)
-- 去重后得到最小关联集合
SELECT DISTINCT root, node
FROM connected_components
WHERE root != node;

这个方案用并查集的思想合并连通组，最终只会保留根节点到其他成员的连接——比如A、B、C互相连通的话，只会生成A-B、A-C，不会出现冗余的B-C。

额外性能优化建议 #

不管用哪个方案，一定要给你的几何字段创建空间索引，这会大幅提升ST_Intersects的查询速度：

CREATE INDEX idx_temp_batiments_sites_geom ON Temp_batiments_sites USING GIST(geom);

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