Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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
share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.