I have a MULTILINESTRING in PostGIS consisting of several LINESTRING's. Consider each LINESTRING to have a set of POINT's

P1, P2, ... PN

forming the line segments

L1, L2, ... LN-1

where LX is the line segment connecting PX to PX+1.

Using PostGIS 1.5 how can I explode the MULTILINESTRING into individual line segments of each LINESTRING?


Two options come to mind. If you want a specific LINESTRING then you can use ST_NumGeometries() and ST_GeometryN(). Alternatively, if you want all the sub-geometries, ST_Dump() is the way to go.

After actually reading the question, you will need to do something similar to this post from the postgis-users list:

SELECT ST_AsText( ST_MakeLine(sp,ep) )
-- extract the endpoints for every 2-point line segment for each linestring
  ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) as sp,
  ST_PointN(geom, generate_series(2, ST_NPoints(geom)  )) as ep
   -- extract the individual linestrings
  (SELECT (ST_Dump(ST_Boundary(geom))).geom
   FROM mypolygontable
   -- eliminate 0 length linestring 
    where st_x(st_startpoint(geom))<> st_x(st_endpoint(geom))
   ) AS linestrings
) AS segments;
  • I read your answer as how to get LINESTRING's from a MULTILINESTRING. But I want to go a level deeper, I want the individual line segments, which when merged form the LINESTRING's which again, when merged, form the original MULTILINESTRING. Does that make sense?
    – Chau
    Mar 13 '12 at 10:09
  • 1
    Ah! Yes, now I've had a coffee I can see that's what you wrote... Mar 13 '12 at 10:17
  • 1
    I don't understand since I don't drink coffee ;)
    – Chau
    Mar 13 '12 at 11:49
  • Your addition works just fine though, so thanks a lot!
    – Chau
    Mar 13 '12 at 11:51

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.