Name
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.
Synopsis
integer ST_ClusterRelateWin(geometry winset geom, text relate_matrix);
Description
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
Examples
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.
Connected and overlapping linestrings
CREATE TABLE clusterrelate (
id serial,
geom geometry);
INSERT INTO clusterrelate (geom)
VALUES
('LINESTRING(0 0,1 1)'),
('LINESTRING(2 2,1 1)'),
('LINESTRING(0 1,1 0)'),
('LINESTRING(0 1,0 4)'),
('LINESTRING(2 2,2 4)'),
('LINESTRING(1.5 2.5,2.5 3.5)');
SELECT id,
ST_AsText(geom),
ST_ClusterRelateWin(geom, '****0****') OVER () AS cluster
FROM clusterrelate;
id | st_astext | cluster
----+-----------------------------+---------
1 | LINESTRING(0 0,1 1) | 0
2 | LINESTRING(2 2,1 1) | 0
3 | LINESTRING(0 1,1 0) | 1
4 | LINESTRING(0 1,0 4) | 1
5 | LINESTRING(2 2,2 4) | 0
6 | LINESTRING(1.5 2.5,2.5 3.5) | 2