Select the lines that form a ring in postgis

Question

I need to select all the ids of the linestrings that forms a ring, grouped by ring.

For example, the 6 linestrings below

CREATE TABLE lines(id INTEGER);
SELECT AddGeometryColumn('lines', 'geom', 0, 'LINESTRING', 2);

INSERT INTO lines(id, geom) VALUES
    (1, ST_GeomFromText('LINESTRING(0 0, 1 0, 1 1)')),
    (2, ST_GeomFromText('LINESTRING(1 1, 0 1, 0 0)')),
    (3, ST_GeomFromText('LINESTRING(0 2, 1 2, 1 3)')),
    (4, ST_GeomFromText('LINESTRING(1 3, 0 3, 0 2)')),
    (5, ST_GeomFromText('LINESTRING(2 0, 3 0, 3 1)')),
    (6, ST_GeomFromText('LINESTRING(0 0, 1 0)'));

forms 2 ring: linestrings 1 and 2 form a ring (let's call it "ring 1"), 3 and 4 form "ring2".

I need something like this:

 line_id |  ring_id
 --------+--------------
     1   |     1  
     2   |     1
     3   |     2
     4   |     2

Answer 1

More of a big hammer approach, and still perhaps not what you want:

CREATE SEQUENCE polyseq;

WITH polys AS (
  SELECT nextval('polyseq') AS id, 
         (ST_Dump(ST_Polygonize(geom))).geom AS geom 
  FROM lines
) 
SELECT lines.id, polys.id 
FROM lines, polys 
WHERE ST_Touches(lines.geom, polys.geom);

If your data is strictly end-noded (which your entry 6 leads me to believe) a recursive approach that gathers up start/end points would be better, assuming that you can guarantee no rings ever share start/end points. If you can't, then the above approach is better, IMO.

Answer 2

I managed to do it with a recursive query

CREATE OR REPLACE FUNCTION array_sort(ANYARRAY) RETURNS ANYARRAY AS $$
SELECT  array_agg(x)
FROM    (SELECT unnest($1) AS x ORDER BY x) AS tmp;
$$ LANGUAGE sql IMMUTABLE;

WITH RECURSIVE merge_lines(id, geom, path, merged) AS
(
    SELECT  l.id, l.geom, array[l.id], ST_LineMerge(l.geom)
    FROM    lines l
    UNION ALL
    SELECT  l.id, l.geom, path || l.id,
            (ST_Dump(ST_LineMerge(ST_Collect(l.geom, merged)))).geom
    FROM    merge_lines, lines l
    WHERE   l.id != ALL(path) AND ST_Touches(l.geom, merged)
)
SELECT  DISTINCT array_sort(path)
FROM    merge_lines
WHERE   ST_IsRing(merged);

I'm sure there are better ways to do this, but for my relatively small dataset it works good.