2

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.

5
  • 2
    Have you made a search from old answers? I found for example this gis.stackexchange.com/questions/21648/…
    – user30184
    Sep 3 '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? Sep 3 '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 3 '19 at 20:46
  • 2
    If you have lines then you do not need to use ST_Boundary(geom).
    – user30184
    Sep 3 '19 at 20:52
  • The SQL syntax error message, as text, should be featured in your post, not alluded to.
    – Vince
    Sep 3 '19 at 21:26
7

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;
1

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.

3
  • This does not seem to work as st_dumppoints will result in multiple rows
    – squanto773
    Jan 19 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 at 6:08
  • I can confirm that running it will result in rows with more than two vertices in the geom column instead of two. Oct 13 at 20:11

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.