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

2 Answers 2


That should do:

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

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


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.