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.