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
WITH testdata AS ( SELECT id, geom::geometry FROM ( VALUES (1, 'LINESTRING (0 0, 1 1)'), (2, 'LINESTRING (5 5, 4 4)'), (3, 'LINESTRING (6 6, 7 7)'), (4, 'LINESTRING (0 0, -1 -1)'), (5, 'POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0))')) AS t(id, geom) ) SELECT id, ST_AsText(geom), ST_ClusterIntersectingWin(geom) OVER () AS cluster FROM testdata; id | st_astext | cluster ----+--------------------------------+--------- 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