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.
GROUP BY (cid)
. You're currently grouping by the originalgeom
, nothing to do with the clustering. Presumably the original geometries are all distinct, so there'd be as many groups as input features.