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.

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

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

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.

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.