I'd suggest to use the ST_ClusterDBSCAN
Window function rather than the Aggregate function ST_ClusterWithin
:
SELECT *,
ST_ClusterDBSCAN(the_geom, eps := <distance>, minpoints := 1) OVER() AS clst_id
FROM all_locations
;
clst_id
will hold INT
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 the distance
to each other; using minpoints := 1
in ST_ClusterDBSCAN
will force the same effect.
Compare
WITH
pts AS (
SELECT ST_MakePoint(n, 0) As geom
FROM Generate_Series(0, 5) AS n
)
SELECT dmp.clst_id
FROM (
SELECT ST_ClusterWithin(geom, 1) AS cw
FROM pts
) AS clst,
UNNEST(clst.cw) WITH ORDINALITY AS dmp (clst, clst_id),
LATERAL ST_Dump(ST_CollectionExtract(dmp.clst, 1)) AS extr
;
clst_id | geom
---------+------------
1 | POINT(0 0)
1 | POINT(1 0)
1 | POINT(2 0)
1 | POINT(3 0)
1 | POINT(4 0)
1 | POINT(5 0)
(6 rows)
to
WITH
pts AS (
SELECT ST_MakePoint(n, 0) As geom
FROM Generate_Series(0, 5) AS n
)
SELECT ST_ClusterDBSCAN(geom, 1, 1) OVER() AS clst_id,
ST_AsText(geom) AS geom
FROM pts
;
clst_id | geom
--------+------------
0 | POINT(0 0)
0 | POINT(1 0)
0 | POINT(2 0)
0 | POINT(3 0)
0 | POINT(4 0)
0 | POINT(5 0)
(6 rows)
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 the ORDINALITY
stars at 1) since they are within distance/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 least minpoints
core geometries within eps
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 accepting GEOGRAPHY
for neither of them, you will need to ST_Transform
your data into a suitable projection to be able to work with metric/imperial units.
ST_ClusterDBSCAN
; this is a window function and does not aggregate rows! Morning @JohnPowell, long time no chat in comments ;-) – geozelot Jan 24 '20 at 8:00ST_ClusterWithin
will collect all geometries that are not separated more than the threshold from each other!ST_ClsuterDBSCAN
will produce different results ifminpoints > 1
, but forminpoints = 1
it produces the same result. For global distance (where the cluster radius is no more than a threshold) one would need a kernel/moving window based approach. However, the same issues concerning CRS units apply! – geozelot Jan 24 '20 at 9:16