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.