Find connected linestrings

find_connected.sql

-- Copyright Paul Ramsey, 2014
-- http://gis.stackexchange.com/questions/94203/how-to-group-connected-linestrings-in-postgis
 
drop table lines;
create table lines ( id integer primary key, geom geometry(linestring) );
insert into lines (id, geom) values ( 1, 'LINESTRING(0 0, 0 1)');
insert into lines (id, geom) values ( 2, 'LINESTRING(0 1, 1 1)');
insert into lines (id, geom) values ( 3, 'LINESTRING(1 1, 1 2)');
insert into lines (id, geom) values ( 4, 'LINESTRING(1 2, 2 2)');
insert into lines (id, geom) values ( 11, 'LINESTRING(10 10, 10 11)');
insert into lines (id, geom) values ( 12, 'LINESTRING(10 11, 11 11)');
insert into lines (id, geom) values ( 13, 'LINESTRING(11 11, 11 12)');
insert into lines (id, geom) values ( 14, 'LINESTRING(11 12, 12 12)');
insert into lines (id, geom) values ( 15, 'LINESTRING(21 22, 22 22)');
create index lines_gix on lines using gist(geom);
 
CREATE OR REPLACE FUNCTION find_connected(integer) returns integer[] AS
$$
WITH RECURSIVE lines_r AS (
  SELECT ARRAY[id] AS idlist, geom, id
  FROM lines 
  WHERE id = $1
  UNION ALL
  SELECT array_append(lines_r.idlist, lines.id) AS idlist, 
         lines.geom AS geom, 
         lines.id AS id
  FROM lines, lines_r
  WHERE ST_Touches(lines.geom, lines_r.geom)
  AND NOT lines_r.idlist @> ARRAY[lines.id]
)
SELECT 
  array_agg(id ORDER BY ID asc) AS idlist
  FROM lines_r
$$ 
LANGUAGE 'sql';
 
SELECT ids, ST_Collect(geom) AS geom FROM (
SELECT DISTINCT find_connected(id) as ids FROM lines) SQ
INNER JOIN lines ON id = ANY(ids)
GROUP BY ids;