ST_ClusterIntersectingWin — Window function that returns a cluster id for each input geometry, clustering input geometries into connected sets.
integer ST_ClusterIntersectingWin(geometry winset geom);
A window function that builds connected clusters of geometries that intersect. It is possible to traverse all geometries in a cluster without leaving the cluster. The return value is the cluster number that the geometry argument participates in, or null for null inputs.
Availability: 3.4.0
Return an explicit cluster id for each input geometry. Using the conventional cid alias keeps the geometry column ready for color-by-cluster rendering.
WITH testdata(id, geom) AS (
VALUES (1, 'LINESTRING (0 0,1 1)'::geometry),
(2, 'LINESTRING (5 5,4 4)'::geometry),
(3, 'LINESTRING (6 6,7 7)'::geometry),
(4, 'LINESTRING (0 0,-1 -1)'::geometry),
(5, 'POLYGON ((0 0,4 0,4 4,0 4,0 0))'::geometry)
), clustered AS (
SELECT id,
geom,
ST_ClusterIntersectingWin(geom) OVER () AS cid
FROM testdata
)
SELECT id,
geom AS geom_wkt,
cid
FROM clustered
ORDER BY id;
id | geom_wkt | cid ----+--------------------------------+----- 1 | LINESTRING(0 0,1 1) | 0 2 | LINESTRING(5 5,4 4) | 0 3 | LINESTRING(6 6,7 7) | 1 4 | LINESTRING(0 0,-1 -1) | 0 5 | POLYGON((0 0,4 0,4 4,0 4,0 0)) | 0