5

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.

Pitrako Junior
  • 1,216
  • 18
  • 30
  • 2
    Have you made a search from old answers? I found for example this https://gis.stackexchange.com/questions/21648/explode-multilinestring-into-individual-segments-in-postgis-1-5 – user30184 Sep 03 '19 at 20:07
  • I did look for old answers but I didn't come across this one though (didn't know that what I'm after is called 'explode'). Anyway, thanks for the link. However, I have just tried and although I don't get any error, I get an empty output. Basically, I have merely changed 'mypolygontable' for the name of my multiline dataset. It seems I'm missing something else? – Pitrako Junior Sep 03 '19 at 20:25
  • 1
    "This doesn't work" is not a sufficiently detailed problem description. Please [Edit] the question to contain a description of the actual behavior. – Vince Sep 03 '19 at 20:46
  • 2
    If you have lines then you do not need to use ST_Boundary(geom). – user30184 Sep 03 '19 at 20:52
  • The SQL syntax error message, as text, should be featured in your post, not alluded to. – Vince Sep 03 '19 at 21:26

2 Answers2

9

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;
jbalk
  • 7,505
  • 1
  • 17
  • 39
2

This worked for me and doesn't require the ST_MakeLine step:

CREATE TABLE line_segments as (
  SELECT ST_CollectionExtract(ST_Split(geom4326,
  (ST_Dumppoints(geom4326)).geom),2) as geom4326 
  FROM mylinetable)

geom4326 is the name of the geometry column with the original line(s) in it.

My solution does the following:
1. Dumps the points (vertices) of the line;
2. Splits the line at those points, which returns a GeometryCollection object;
3. Extracts the line segment out of each GeometryCollection object.

I haven't compared speed with other solutions or tried to do anything complicated with it, but some variation on the theme would probably work for similar problems.

John
  • 518
  • 4
  • 13
  • This does not seem to work as st_dumppoints will result in multiple rows – squanto773 Jan 19 '21 at 21:47
  • Have you actually tried running it? I haven't looked at this in a long time but it did work for me back then. ST_Dumppoints is expected to dump all of the vertices. – John Jan 20 '21 at 06:08
  • I can confirm that running it will result in rows with more than two vertices in the geom column instead of two. – Alexander Gogl Oct 13 '21 at 20:11