ST_ClusterRelateWin — Window function that returns a cluster id for each input geometry, clustering input geometries into connected sets using the relate pattern to determine whether the geometries are connected.
integer ST_ClusterRelateWin(geometry winset geom, text relate_matrix);
A window function that builds connected clusters of geometries that intersect. Geometries are added to a cluster if they share a pairwise DE9IM relationship with another member of the cluster. With this function it is possible to build a cluster of all objects that touch at boundaries, but exclude those that merely overlap.
Availability: 3.7.0
This collection of line strings would form a single cluster using ST_ClusterIntersectingWin, but using ST_ClusterRelateWin can be clustered into three groups that connect only via their end points.
WITH data(id, geom) AS (
VALUES (1, 'LINESTRING(0 0,1 1)'::geometry),
(2, 'LINESTRING(2 2,1 1)'::geometry),
(3, 'LINESTRING(0 1,1 0)'::geometry),
(4, 'LINESTRING(0 1,0 4)'::geometry),
(5, 'LINESTRING(2 2,2 4)'::geometry),
(6, 'LINESTRING(1.5 2.5,2.5 3.5)'::geometry)
), clustered AS (
SELECT id, geom,
ST_ClusterRelateWin(geom, '****0****') OVER () AS cluster
FROM data
)
SELECT cluster,
string_agg(id::text, ',' ORDER BY id) AS members,
ST_Collect(geom ORDER BY id) AS cluster_geom
FROM clustered
GROUP BY cluster
ORDER BY cluster;
0 | 1,2,5 | MULTILINESTRING((0 0,1 1),(2 2,1 1),(2 2,2 4)) 1 | 3,4 | MULTILINESTRING((0 1,1 0),(0 1,0 4)) 2 | 6 | MULTILINESTRING((1.5 2.5,2.5 3.5))