Clustering and combining points in PostGIS

Question

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.

Answer 1

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.