I am trying to cluster points in PostGIS. I have the points and the corresponding Latitudes and Longitudes which I have converted into point geometries. I want to cluster points in such a way that all the points within a cluster are within 25 miles of each other.
My input looks like this:
My desired output is this:
Here the new column Cluster_id
denotes the cluster to which the point is assigned to. Please tell me the right query to get this done. I am new to SQL so I am finding hard to write the right query.
So far, I have tried this:
SELECT ST_AsText(unnest(ST_ClusterWithin(the_geom, 1))) FROM all_locations
Best Answer
I'd suggest to use the
ST_ClusterDBSCAN
Window function rather than the Aggregate functionST_ClusterWithin
:clst_id
will holdINT
values representing the cluster each rows geometry belongs to.As stated in the comments,
ST_ClusterWithin
will aggregate geometries that are separated by no more than thedistance
to each other; usingminpoints := 1
inST_ClusterDBSCAN
will force the same effect.Compare
to
In both cases the geometries are stretched over a total distance of 5 degrees, but count as one and the same cluster (
ST_ClusterDBSCAN
starts counting at 0, whereas theORDINALITY
stars at 1) since they are withindistance/eps
of 1 degree to each other!This behavior may change for
minpoints > 1
(and on other data than the above), as there need to be at leastminpoints
core geometries withineps
distance to get counted as cluster.Needless to say, the latter approach is way less convoluted, and offers some nice functionality built into the windowing behavior (e.g. easy clustering over attributes etc.)
Note:
Both functions assume
distance/eps
in units of the underlying CRS; for a geographic reference system, this is degrees! Since there is no signature acceptingGEOGRAPHY
for neither of them, you will need toST_Transform
your data into a suitable projection to be able to work with metric/imperial units.