enter image description here

Is there an efficient way to split a table of Multi/Linestrings that overlap() and touch() into unique Linestrings (note: creating edges using topology is going very...very...very slow).

Here is some sample data that I've been playing with...

CREATE TABLE multilines (
  "name" char(1) NOT NULL,
  "the_geom" geometry NOT NULL
-- add data, 3 multilines, ONE of the 'B' geoms has an interior 'donut hole' multilinestring
INSERT INTO multilines(name, the_geom) VALUES (
    'A', 'MULTILINESTRING((2 1,0 3),(0 3,2 3),(2 3,2 1))'::geometry
    ), (
    'MULTILINESTRING((0 0,4 0),(4 0,4 4),(4 4,0 4),(0 4,0 0),(1 1,2 1),(2 1,2 2),(2 2,1 2),(1 2,1 1))'::geometry
    ), (
    'B', 'MULTILINESTRING((-1 -1,-1 -2),(-1 -2,-2 -2),(-2 -2,-2 -1),(-2 -1,-1 -1))'::geometry

Since I don't have a node table of where the intersections are (and it seems intuitively inefficient to generate one) I was hoping to just do this without generating another table (which most people doing this type of query seem to be doing).

Notably...when I do something like 'ST_Linemerge(st_collect(the_geom))' ...I get 1 row showing ALL the merged lines (which isn't what I'd want - I definitely want to break the lines into separate Multi and/or Linestrings, and run a DISTINCT query just to return one of the_geoms).


You can use the following query to union the geometry, find all nodes and split the lines:

SELECT row_number() OVER() new_id, geom FROM
    (ST_Dump(ST_Node(ST_Union(the_geom)))).geom geom 
  FROM multilines) a

Then you must play a little bit to retrieve the original attributes by joining the previous table on "contains" or "within".

Splitted lines

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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