PostGIS – Exploding MULTILINESTRING into Individual Segments

Question

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

P₁, P₂, … P_N

forming the line segments

L₁, L₂, … L_N-1

where L_X is the line segment connecting P_X to P_X+1.

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

Answer 1

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) )
FROM
-- extract the endpoints for every 2-point line segment for each linestring
(SELECT
  ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) as sp,
  ST_PointN(geom, generate_series(2, ST_NPoints(geom)  )) as ep
FROM
   -- 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;