[GIS] How to break multilinestring into constituent linestrings in PostGIS

postgis

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?

Best Answer

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)"

Related Solutions

[GIS] How to explode a MultiLineString using ST_Dump() in PostGIS

SELECT a.id, (ST_Dump(a.the_geom)).path path , (ST_Dump(a.the_geom)).geom geom ,ST_GeometryType((ST_Dump(a.the_geom)).geom ) geomtype INTO b FROM a

Create table with id, path , geom and geomtype columns. id point to original geom , path points to where geom was in original geom and geom is geometry.

Now you can add geom2 column to table (type geometry) and create new line with line-offset. After that you can create MULTILINE again using id
(not tested)

ALTER TABLE b add geom2 geometry
UPDATE b SET geom2 = line-offset(geom, 2) -- something like that 


SELECT id, ST_Linemerge(st_collect(geom)) from b group by id -- creates linestring or multiline

SELECT id, ST_MULTI(st_collect(geom)) from b group by id -- creates Multiline

if order is has to same

SELECT b.id, ST_Multi(ST_Collect(b.geom)) FROM SELECT (id, path, geom from b order by id, path ) b GROUP BY b.id

EDIT: ST_Collect should be replaced with st_union or other. St_Multi and st_linemerge do not like GEOMETRYCOLLECTIONS

EDIT : about Additional Details

INSERT INTO complex (name, the_geom) VALUES ( 'Bob', ST_GeomFromEWKT('SRID=4236;MULTILINESTRING((498376.89 651569.6,498372.28 651571.89),(498372.28 651571.89,498371.77 651576.05))') )
LINESTRING can be MULTILINESTRING , it is just a MULTI collection with one LINE

ANSWERS: Question A: im not sure, but here is tested example.

SELECT * INTO complex FROM (
 SELECT  'Harry' as name  , ST_GeomFromEWKT('SRID=4236;MULTILINESTRING((498376.89
    651569.6,498372.28 651571.89))')as the_geom 
    UNION 
 SELECT 'Bob' as name , ST_GeomFromEWKT('SRID=4236;MULTILINESTRING((498376.89
        651569.6,498372.28 651571.89),(498372.28 651571.89,498371.77 651576.05))') as the_geom 
        ) a

now this return 3 rows, Bob has 2 and Harry 1

SELECT *,  ST_AsEWKT(simple_geom)
-- into test
 FROM (
  SELECT
    dumped.*,
    (dumped.geom_dump).geom as simple_geom,
    (dumped.geom_dump).path as path
  FROM (
    SELECT *, ST_Dump(the_geom) AS geom_dump FROM complex
  ) as dumped
) AS simple   

Now we have test table with 3 rows .

SELECT st_asEWKT((st_union(the_geom))) as geom  from test group by name 
OR
SELECT st_asEWKT(st_linemerge(st_union(the_geom))) as geom  from test group by name 

First one returns multi type,  second tries to merge lines. In this case it will return 2 linestrings if it does not succeed it will return multilinestrings

Question B:

SELECT *, ST_Transform(geom,3857) as the_geom_webmercator from xxx

You need to set correct SRID to geoms first. Create them using EWKT with srid (like that example) or using ST_SetSRID(geom, srid) , example

UPDATE xxx set geom = ST_SetSRID(geom)

.. i hope this helps

[GIS] Removing unwanted Linestrings from Multilinestring in Postgis

You can use ST_Dump to pull out the individual Linestrings, test those for length > 0, and then recreate the Multilinestring using ST_Collect (rather than ST_Union or ST_LineMerge, which involve actual spatial processing calculations, rather than simply combining the underlying geometries into their multi version, as pointed out by @dbaston).

SELECT 
     ST_AsText(ST_Collect(geom)) 
  FROM 
    (SELECT    
        (ST_Dump(
           ST_GeomFromText('MULTILINESTRING(
             (-3.00712325415935 43.3437538570123,-3.00711888681593 43.3437506773148),
             (-3.00693096810649 43.34361446115,-3.00692660078307 43.3436112814451),
             (-3.00692660078307 43.3436112814451,-3.00692660078307 43.3436112814451),
             (-3.00692660078307 43.3436112814451,-3.0068846515836 43.3436419720614),
             (-3.00692660078307 43.3436112814451,-3.0069222334601 43.34360810174),
             (-3.00691127288165 43.3436001834041,-3.00690690556026 43.3435970036984))'))
       ).geom) g
WHERE ST_Length(g.geom) > 0;

which produces:

MULTILINESTRING(
    (-3.00691127288165 43.3436001834041,-3.00690690556026 43.3435970036984),
    (-3.00712325415935 43.3437538570123,-3.00711888681593 43.3437506773148),
    (-3.00692660078307 43.3436112814451,-3.0069222334601 43.34360810174),
    (-3.00693096810649 43.34361446115,-3.00692660078307 43.3436112814451),
    (-3.00692660078307 43.3436112814451,-3.0068846515836 43.3436419720614))

ie, with the line with the repeated coordinates gone. Obviously, you will want to remove the ST_AsText which is just for illustration. Note, ST_Dump is a set returning function, so you have to wrap it in parentheses followed by .geom, to get the geometry back, ie,

(ST_Dump(geom)).geom

which feels a bit strange at first.

You could do something similar to the above using a count of distinct points, but I think, testing on the length as you suggested, is the cleanest.

EDIT. It actually turns out that in this case, you don't even need to test for the length of the linestring, as ST_Union ignores the point when building the MultiLinestring back up. There are other ways to ignore certain geometry types when doing a union, such as testing with ST_GeometryType in the where clause.

Best Answer

Related Solutions

[GIS] How to explode a MultiLineString using ST_Dump() in PostGIS

[GIS] Removing unwanted Linestrings from Multilinestring in Postgis

Related Question