5

I have a multilinestring in PostGIS which I need to divide into segments that break anywhere there is an intersection. So in the below example, the lines need to be split at the each point, since that's where more than two parts converge.

Example:

o--       o
   \      |
    o-----o---o
   /     /
  o      |
         o

This a simplification of my data. I've tried ST_LineMerge() but that just returned most of my multilinestrings back to me as is. One complicating factor is that there are many parts in the multilinestring that begin or end at the intersection of only two parts. So in the example below, ST_Dump() breaks the geometry into two parts, but I want them to be stitched together as a single linestring (since only two parts are converging).

     o
    /
   o------o

What is a good strategy for approaching this problem?

6

A combination of both ST_LineMerge and ST_Dump should give you the result that you want.

First apply ST_LineMerge to combine single line intersections, then ST_Dump to break apart the multi-linestrings.

If you have linestrings that go through an intersection without breaking you will also need to use ST_Node to segment the lines at the intersections.

An example query is

SELECT id, (dump_set).path AS line_part, ST_AsEWKT((dump_set).geom) AS line_string
FROM (
    SELECT id, ST_Dump(ST_LineMerge(ST_Node(geom))) AS dump_set
    FROM (VALUES
        (1, 'MULTILINESTRING((0 10, 10 10),(10 0, 10 10),(10 0, 20 0))'::Geometry),
        (2, 'MULTILINESTRING((0 10, 10 10),(10 10, 10 20),(10 0, 10 10))'::Geometry),
        (3, 'MULTILINESTRING((0 10, 10 10),(10 10, 10 20),(10 0, 10 10),(10 0, 20 0))'::Geometry),
        (4, 'MULTILINESTRING((0 10, 10 10, 10 20),(10 0, 10 10),(10 0, 20 0))'::Geometry)
        )Lines(id,geom)
    ) AS dump_results;

Stepping through each part of the operation using the 4th row from above.

-- Multi line string composed of three parts.  Intersection between first part and second at 10 10
SELECT ST_AsEWKT(
    ST_Node(geom) -- Splits part one at 10 10
    )
FROM ( VALUES(4, 'MULTILINESTRING((0 10, 10 10, 10 20),(10 0, 10 10),(10 0, 20 0))'::Geometry) )Lines(id,geom);
-- RESULT: "MULTILINESTRING( (0 10,10 10) , (10 10,10 20) , (10 10,10 0) , (10 0,20 0) )"

SELECT ST_AsEWKT(
    ST_LineMerge( -- merges parts 3 and 4 at 10 0
        ST_Node(geom)
    )
    )
FROM ( VALUES(4, 'MULTILINESTRING((0 10, 10 10, 10 20),(10 0, 10 10),(10 0, 20 0))'::Geometry) )Lines(id,geom);
-- RESULT: "MULTILINESTRING( (0 10,10 10) , (10 10,10 20) , (10 10,10 0,20 0) )"

SELECT ST_AsEWKT(
    (
        ST_Dump ( -- Returns the components of the multi linestring in an geometry_dump type
            ST_LineMerge( 
                ST_Node(geom)
            )
        )
    ).geom -- Extracts the geometry component of the geometry_dump type
    )
FROM ( VALUES(4, 'MULTILINESTRING((0 10, 10 10, 10 20),(10 0, 10 10),(10 0, 20 0))'::Geometry) )Lines(id,geom);
-- RESULT:
--    "LINESTRING(0 10,10 10)"
--    "LINESTRING(10 10,10 20)"
--    "LINESTRING(10 10,10 0,20 0)"
  • what does (d).path mean? – ziggy Apr 26 '17 at 21:10
  • i guess in general what does the (). do. I have seen it especially for (st_dumppoints).geom but where the docs on it – ziggy Apr 26 '17 at 21:12
  • @ziggy row expressions must be parenthesized like this; see the manual for a detailed explanation. – Mike T Apr 26 '17 at 21:21
  • 1
    @ziggy I've altered the query slightly to try and make it a little clearer. ST_Dump is a set returning function and as @MikeT has commented the row expressions require the parenthesis. The path value is essentially a way to identify the components of the dumped geometry. I included it in the query to show quickly the number of lines returned from each input geometry – MickyT Apr 26 '17 at 21:48
  • 1
    I would suggest trying a few different queries to see the way results are returned. For example try select * from ST_Dump('MULTILINESTRING(( ... ))'::Geometry) dump_set; and select ST_Dump('MULTILINESTRING(( ... ))'::Geometry) dump_set;. You will notice that the first will return the path and geometry in columns, whereas the second returns the row as a column. Also note that the path is an integer array. Mike T's answer here. Here's the link to the geometry_dump docs. – MickyT Apr 26 '17 at 23:14

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.