· 9 years ago · Dec 15, 2016, 11:58 PM
1SELECT (ST_Dump(st_union)).geom
2FROM
3 (SELECT ST_Union(geom) FROM roads) sq
4
5drop table lines;
6create table lines ( id integer primary key, geom geometry(linestring) );
7insert into lines (id, geom) values ( 1, 'LINESTRING(0 0, 0 1)');
8insert into lines (id, geom) values ( 2, 'LINESTRING(0 1, 1 1)');
9insert into lines (id, geom) values ( 3, 'LINESTRING(1 1, 1 2)');
10insert into lines (id, geom) values ( 4, 'LINESTRING(1 2, 2 2)');
11insert into lines (id, geom) values ( 11, 'LINESTRING(10 10, 10 11)');
12insert into lines (id, geom) values ( 12, 'LINESTRING(10 11, 11 11)');
13insert into lines (id, geom) values ( 13, 'LINESTRING(11 11, 11 12)');
14insert into lines (id, geom) values ( 14, 'LINESTRING(11 12, 12 12)');
15create index lines_gix on lines using gist(geom);
16
17CREATE OR REPLACE FUNCTION find_connected(integer) returns integer[] AS
18$$
19WITH RECURSIVE lines_r AS (
20 SELECT ARRAY[id] AS idlist, geom, id
21 FROM lines
22 WHERE id = $1
23 UNION ALL
24 SELECT array_append(lines_r.idlist, lines.id) AS idlist,
25 lines.geom AS geom,
26 lines.id AS id
27 FROM lines, lines_r
28 WHERE ST_Touches(lines.geom, lines_r.geom)
29 AND NOT lines_r.idlist @> ARRAY[lines.id]
30)
31SELECT
32 array_agg(id) AS idlist
33 FROM lines_r
34$$
35LANGUAGE 'sql';
36
37WITH RECURSIVE groups_r AS (
38 (SELECT find_connected(id) AS idlist,
39 find_connected(id) AS grouplist,
40 id FROM lines WHERE id = 1)
41 UNION ALL
42 (SELECT array_cat(groups_r.idlist,find_connected(lines.id)) AS idlist,
43 find_connected(lines.id) AS grouplist,
44 lines.id
45 FROM lines, groups_r
46 WHERE NOT idlist @> ARRAY[lines.id]
47 LIMIT 1)
48)
49SELECT id, grouplist
50FROM groups_r;
51
52id | grouplist
53----+---------------
54 1 | {1,2,3,4}
55 11 | {11,12,13,14}
56(2 rows)
57
58DO
59$$
60DECLARE
61this_id bigint;
62this_geom geometry;
63cluster_id_match integer;
64
65id_a bigint;
66id_b bigint;
67
68BEGIN
69DROP TABLE IF EXISTS clusters;
70CREATE TABLE clusters (cluster_id serial, ids bigint[], geom geometry);
71CREATE INDEX ON clusters USING GIST(geom);
72
73-- Iterate through linestrings, assigning each to a cluster (if there is an intersection)
74-- or creating a new cluster (if there is not)
75FOR this_id, this_geom IN SELECT id, geom FROM lines LOOP
76 -- Look for an intersecting cluster. (There may be more than one.)
77 SELECT cluster_id FROM clusters WHERE ST_Intersects(this_geom, clusters.geom)
78 LIMIT 1 INTO cluster_id_match;
79
80 IF cluster_id_match IS NULL THEN
81 -- Create a new cluster
82 INSERT INTO clusters (ids, geom) VALUES (ARRAY[this_id], this_geom);
83 ELSE
84 -- Append line to existing cluster
85 UPDATE clusters SET geom = ST_Union(this_geom, geom),
86 ids = array_prepend(this_id, ids)
87 WHERE clusters.cluster_id = cluster_id_match;
88 END IF;
89END LOOP;
90
91-- Iterate through the clusters, combining clusters that intersect each other
92LOOP
93 SELECT a.cluster_id, b.cluster_id FROM clusters a, clusters b
94 WHERE ST_Intersects(a.geom, b.geom)
95 AND a.cluster_id < b.cluster_id
96 INTO id_a, id_b;
97
98 EXIT WHEN id_a IS NULL;
99 -- Merge cluster A into cluster B
100 UPDATE clusters a SET geom = ST_Union(a.geom, b.geom), ids = array_cat(a.ids, b.ids)
101 FROM clusters b
102 WHERE a.cluster_id = id_a AND b.cluster_id = id_b;
103
104 -- Remove cluster B
105 DELETE FROM clusters WHERE cluster_id = id_b;
106END LOOP;
107END;
108$$ language plpgsql;