PostGIS – How to Split Lines into Basic Segments at Vertices

postgisst-split

I have a MultiLineString table in PostgreSQL and I am trying to obtain a table with all the segments obtained from those lines if we split them at their vertices.
I have found this simple example, which actually works:

SELECT ST_AsText(ST_Split(mline, pt)) As wktcut
        FROM (SELECT
    ST_GeomFromText('MULTILINESTRING((10 10, 190 190), (15 15, 30 30, 100 90))') As mline,
    ST_Point(30,30) As pt) As foo;

But I am struggling to adapt this example to my case.

I am actually trying with something like this:

SELECT ST_AsText(ST_Split(mline, pt)) As wktcut 
FROM (SELECT 
     geom As mline from public.flood_def,
     (SELECT (ST_DumpPoints(public.flood_def.geom)).geom As pt FROM public.flood_def)) As foo;

where 'public.flood_def' is the table that contains the multilines. This, unfortunately, doesn't work. It throws a syntax error:

ERROR:  subquery in FROM must have an alias
LINE 30:      (SELECT (ST_DumpPoints(public.flood_def.geom)).geom As ...

Also, I'm finding it hard to understand the documentation about any of the PostGIS ST_xxxx instructions.

Best Answer

This has been answered several times. The question you are asking is how to explode the line into it's constituent segments. I found this solution here: see tilt's answer.

This worked when I tested it on my line table: 

CREATE TABLE flood_def_segments as 
WITH segments AS (
SELECT gid, ST_AsText(ST_MakeLine(lag((pt).geom, 1, NULL) OVER (PARTITION BY gid ORDER by gid, (pt).path), (pt).geom)) AS geom
  FROM (SELECT gid, ST_DumpPoints(geom) AS pt FROM flood_def) as dumps
)
SELECT * FROM segments WHERE geom IS NOT NULL;
Related Question