sql - 围绕已知聚类中心的聚类点

Question

我有一组点 (~1000) 和一组聚类中心 (~100)。我现在想在考虑已知聚类中心的情况下对一组点进行聚类。所有集群都应从已知的集群中心开始，向外扩展，收集所有距离集群内已存在的最近点小于 x 米的点。

我现在有以下非常标准的 PostGIS dbscan 查询:

WITH clusters AS (
  SELECT
    landmark_id, coordinate,
    ST_ClusterDBSCAN(coordinate, eps := (30 / 111111.0), minpoints := 10) OVER() AS cluster_id
  FROM landmarks 
  WHERE coordinate IS NOT NULL
)
SELECT
  cluster.id, cluster.landmark_ids,
  ST_Centroid(cluster.geometry) AS coordinate,
  ST_AsGeoJSON(cluster.geometry) AS geometry
FROM (
  SELECT
    cluster_id AS id,
    array_agg(landmark_id) AS landmark_ids,
    ST_ConvexHull(ST_Collect(coordinate)) AS geometry
  FROM clusters
  WHERE cluster_id IS NOT NULL
  GROUP BY cluster_id
) AS cluster;

任何指示我如何调整上述查询或编写另一个查询来做我想做的事情而不诉诸过程代码(如果是这样，我也希望得到一些关于这方面的指示)？

Answer 1

关于已经在集群中，我不确定你是指第一个集群拾取的那些还是包括你将递归拾取的那些。

此解决方案仅与原始集群进行比较，不会尝试基于递归集群匹配。这将需要递归查询，我怀疑它是否会产生更好的答案。

也不确定您为什么决定使用 convexhull 来计算质心，我假设您需要真正的质心，这可以针对 ST_Collect 输出完成。

WITH cluster1 AS (
  SELECT
    landmark_id, coordinate,
    ST_ClusterDBSCAN(coordinate, eps := (30 / 111111.0), minpoints := 10) OVER() AS cluster_id
  FROM landmarks 
  WHERE coordinate IS NOT NULL
),
clustered AS ( SELECT * FROM cluster1 WHERE cluster_id IS NOT NULL ) 
clusterall AS (
SELECT 
    l.landmark_id, l.coordinate, c.cluster_id
 FROM landmarks AS l
    CROSS JOIN 
    -- find closest cluster
        LATERAL (SELECT cluster_id 
                FROM clustered AS c 
            ORDER BY  c.coordinate <-> l.coordinate LIMIT 1 ) AS c
    -- only look for landmarks not matched to a cluster
    WHERE l.landmark_id NOT IN(SELECT c.landmark_id FROM clustered AS c)
UNION ALL
SELECT c.landmark_id, c.coordinate, c.cluster_id
    FROM cluster1 
)
SELECT
  cluster.id, cluster.landmark_ids,
  ST_Centroid(cluster.geometry) AS coordinate,
  ST_AsGeoJSON(cluster.geometry) AS geometry
FROM (
  SELECT
    cluster_id AS id,
    array_agg(landmark_id) AS landmark_ids,
    ST_ConvexHull(ST_Collect(coordinate)) AS geometry
  FROM clusterall
  GROUP BY cluster_id
) AS cluster;