Using ST_ClusterDBSCAN on results of ST_ClusterKMeans for nested clustering with PostGIS?

Question

I would like to pipe the results of ST_ClusterKMeans() into a ST_ClusterDBSCAN() query.

The ST_ClusterKMeans gives me a fairly good results as shown in this screenshot. I classified the union of the point geometries returned from the grouped cluster and I overlaid the ST_PointOnSurface() geometries from the multipoints as black circle (size is dependent on the number of clustered points).

This is the query which returns the multipoint geometries:

SELECT
      ST_Union(cluster.geomcntr) AS geom,
      count(cluster.geomcntr) AS c
    FROM (
      SELECT
        qid AS id,
        brand AS brand,
        store AS label,
        --ST_ClusterKMeans(geomcntr, 200) OVER () AS cid,
        ST_ClusterDBSCAN(geomcntr, 0.1, 1) OVER () AS cid,
        geomcntr
      FROM retailpoints
      WHERE
        ST_DWithin(
          ST_MakeEnvelope(
            -17.951660156250004,
            59.512029386502704,
            9.953613281250002,
            49.439556958940855,
            4326),
          geomcntr,
          0.00001
        )
    ) cluster
    GROUP BY cluster.cid;

I would like to nest this query inside a query that calculates the ST_ClusterDBSCAN cluster inside each of the multigeometries, effectively splitting KMEANS cluster which are very sparse into separate village cluster.

Edit: Dan Baston's suggestion works a charm. Does exactly what I was trying to do. Here is a screenshot with the PointOnSurface centroids from the DBSCAN cluster which use the KMEANS cluster as input.

Answer 1

Interesting idea.

I think you can most easily accomplish this by delaying creation of the MultiPoint geometries until after your data has made it through both clustering algorithms. First, assign a k-means cluster ID to each input, then run DBSCAN across each k-means ID independently. (In the window function lingo, that's "partitioning" by the k-means ID.) Something like this should work:

SELECT
  ST_Collect(geom) AS geom,
  count(1) AS c
FROM (
  SELECT
    qid,
    brand,
    label,
    kmeans_cid,
    ST_ClusterDBSCAN(geom, 0.1, 1) OVER (PARTITION BY kmeans_id) AS dbscan_cid
  FROM (
    SELECT
      qid,
      brand,
      label,
      ST_ClusterKMeans(geom, 200) OVER () AS kmeans_cid
    FROM retailpoints
  ) retail_kmeans
) retail_dbscan
GROUP BY kmeans_cid, dbscan_cid