1

I am trying to cluster points near each other and group them into one coordinate based on the centroid of that cluster based on a bounding box. I'm able to cluster them and create a group number for the clusters, but I'm having trouble grouping them as one coordinate per cluster.

This is what I have so far:

SELECT ST_ClusterDBSCAN(geom, eps := 0.5, minpoints := 2) over () AS cid
FROM locations
WHERE geom 
    @
    ST_MakeEnvelope (
        -114.496858, 33.149466,
        -112.006293, 39.082191, -- bounding box
        4326)
GROUP BY(geom)

However, this doesn't seem to group them based on the cluster id (cid). I also need to return the total of points within that cluster (I looked into ST_NumGeometries for this, but can't figure out how to integrate it into the query) and just the lat and lng.

1
  • Try GROUP BY (cid). You're currently grouping by the original geom, nothing to do with the clustering. Presumably the original geometries are all distinct, so there'd be as many groups as input features. Mar 3, 2021 at 0:22

1 Answer 1

3

If we wrap your command inside a separate select we can then group by cid, and also get the centroid and count of inputs for each 'cluster'.

Here, for my example and given the nature of my data, I have excluded where the points do not sit in a cluster (WHERE cid IS NOT NULL).
Note : I have changed the bounding box coordinates and eps to suit my sample data.

DROP TABLE IF EXISTS march_grouped_hullpts;
CREATE TABLE march_grouped_hullpts AS
SELECT x.cid, ST_Centroid(ST_COLLECT(x.geom)) geom, ST_NumGeometries(ST_COLLECT(x.geom)) num_points FROM
(
    SELECT ST_ClusterDBSCAN(geom, eps := 1, minpoints := 2) over () AS cid, geom
    FROM public.hullpts
    WHERE ST_TRANSFORM(geom,4326)
        @
        ST_MakeEnvelope (
        145.00945, -37.84890,
        145.011165, -37.84674, -- bounding box
        4326)     
    GROUP BY(geom)
) as x
WHERE cid IS NOT NULL
GROUP BY x.cid
ORDER BY cid;

The second and third images show what is happening in the intermediate steps. CLUSTER POINTS

4
  • Instead of ST_NumGeometries(ST_COLLECT(x.geom)) you can use COUNT(x.cid), to be a little more SQL-like (and it might be slightly more efficient).
    – dr_jts
    Mar 4, 2021 at 18:23
  • 1
    Also looks like ST_Centroid is missing?
    – dr_jts
    Mar 4, 2021 at 18:24
  • Thanks added in the misisng ST_Centroid
    – Cushen
    Mar 4, 2021 at 22:38
  • Note that in some cases it might be useful to use a data point as the representative point for each cluster. This can be done using ST_PointOnSurface.
    – dr_jts
    Mar 4, 2021 at 23:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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