3

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).

enter image description here

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.

enter image description here

4

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
  • Amazing... That does exactly what I was trying to do. See new screenshot. That answer being posted during Superbowl LII, even more impressive. Your blog post was actually my starting point to work with cluster in the first place. – Dennis Bauszus Feb 5 '18 at 11:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.