How to get an array of simple lines from one multipoint linestring in PostGIS?

Question

How can I extract some LINESTRING consisting of 3 or more POINTs to several LINESTRINGs each of 2 POINTs in PostGIS?

Answer 1

That should do:

SELECT a.id, ST_MakeLine(ST_PointN(the_geom,x),ST_PointN(the_geom,x+1))
FROM original AS a
JOIN
(SELECT id, generate_series(1, ST_NumPoints(the_geom)-1) AS x 
 FROM original ) AS b
ON a.id = b.id;

Answer 2

One option would be to create you one function that iterates through the points and use the funtion MakeLine in order to create a set of geometry.But if your linestrings are not to big check this link out, it should help you : http://www.mail-archive.com/postgis-users@postgis.refractions.net/msg02883.html

Regards