PostGIS递归聚类超大小限制问题排查及替代方案咨询

原代码的核心问题 #

你的递归逻辑完全没有实现“拆分超大型集群”的功能，这是结果远超20条的根本原因：

• 递归CTE的recursive_clusters只是重复引用初始聚类的结果，没有对超标的集群执行重新聚类操作，nta_cluster_id全程没有更新
• 最后dataset中的重新聚类是按areaID全局分区，不是针对单个超标的子集群，相当于把整个区域的点重新做KMeans，自然会出现大集群
• cluster_sizes只计算了初始聚类的大小，递归过程中没有重新计算新拆分后的集群大小

正确的递归聚类实现思路 #

要实现“每个集群最多20条”的目标，需要在递归过程中对每个超过20条的集群单独拆分，直到所有集群都满足大小限制。以下是修正后的代码：

WITH RECURSIVE cluster_steps AS (
    -- 基础情况：初始聚类（先按areaID分组，每个area先分成1个集群）
    SELECT
        pma.*,
        ST_Transform(pma."centerPoint", 24313) AS geom_24313,
        CONCAT(pma."areaID", '_', 1) AS cluster_id,
        1 AS recursion_depth
    FROM "potential_missed_areas" pma
    WHERE pma."campaignid" = -1
      AND pma."status" = 'approved'

    UNION ALL

    -- 递归情况：拆分超过20条的集群
    SELECT
        cs.*,
        -- 对当前超标的集群，用KMeans拆分成ceil(集群大小/20)个子集群
        CONCAT(cs.cluster_id, '_', ST_ClusterKMeans(cs.geom_24313, GREATEST(2, CEIL(cluster_size / 20.0)::int)) OVER (PARTITION BY cs.cluster_id)) AS cluster_id,
        cs.recursion_depth + 1 AS recursion_depth
    FROM cluster_steps cs
    -- 先计算当前每个集群的大小
    JOIN (
        SELECT cluster_id, COUNT(*) AS cluster_size
        FROM cluster_steps
        GROUP BY cluster_id
    ) cs_size ON cs.cluster_id = cs_size.cluster_id
    WHERE cs_size.cluster_size > 20
      AND cs.recursion_depth < 100 -- 防止无限递归
),
-- 过滤出最终的非超标集群
final_clusters AS (
    SELECT
        cs.*
    FROM cluster_steps cs
    JOIN (
        SELECT cluster_id, COUNT(*) AS cluster_size
        FROM cluster_steps
        GROUP BY cluster_id
    ) cs_size ON cs.cluster_id = cs_size.cluster_id
    WHERE cs_size.cluster_size <= 20
)
-- 验证结果
SELECT
    "areaID",
    cluster_id,
    COUNT(*) AS cluster_count
FROM final_clusters
GROUP BY "areaID", cluster_id
ORDER BY "areaID", cluster_count DESC;

关键改进点 #

• 每次递归只针对单个超标集群进行拆分，新的cluster_id基于原集群ID生成，保证拆分的层级关系
• 递归过程中动态计算当前集群的大小，只处理超过20条的集群
• 最终只保留大小≤20的集群，确保结果符合要求

替代策略：基于空间密度的拆分（可选） #

如果希望聚类同时兼顾空间邻近性和大小限制，可以结合ST_ClusterDBSCAN：

1. 先用ST_ClusterDBSCAN按空间距离聚类，得到自然的空间集群
2. 对其中超过20条的集群，再用KMeans拆分到≤20条
3. 这种方法既保证空间相关性，又满足大小限制

示例代码片段：

-- 第一步：空间聚类
WITH spatial_clusters AS (
    SELECT
        pma.*,
        ST_Transform(pma."centerPoint", 24313) AS geom_24313,
        ST_ClusterDBSCAN(ST_Transform(pma."centerPoint", 24313), eps := 500, minpoints := 1) OVER (PARTITION BY pma."areaID") AS spatial_cluster_id
    FROM "potential_missed_areas" pma
    WHERE pma."campaignid" = -1
      AND pma."status" = 'approved'
),
-- 第二步：拆分超标的空间集群
split_clusters AS (
    SELECT
        sc.*,
        CASE WHEN cluster_size > 20 THEN
            CONCAT(sc.spatial_cluster_id, '_', ST_ClusterKMeans(sc.geom_24313, CEIL(cluster_size / 20.0)::int) OVER (PARTITION BY sc.spatial_cluster_id))
        ELSE sc.spatial_cluster_id::text END AS final_cluster_id
    FROM spatial_clusters sc
    JOIN (
        SELECT spatial_cluster_id, COUNT(*) AS cluster_size
        FROM spatial_clusters
        GROUP BY spatial_cluster_id
    ) sc_size ON sc.spatial_cluster_id = sc_size.spatial_cluster_id
)
-- 验证结果
SELECT
    "areaID",
    final_cluster_id,
    COUNT(*) AS cluster_count
FROM split_clusters
GROUP BY "areaID", final_cluster_id
ORDER BY "areaID", cluster_count DESC;

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