[GIS] equivalent Explode lines in Postgis

explodepostgis

I want to divide linestring into separate segments via Postgis, like it "Explode lines" tool in QGIS does, e.g. from linestring
I want to get separate segments
enter image description here

I have some thoughts about take it by using (st_dumppoints(Linestring)).geom then split linestring by their nodes with ST_split and dump again received geometry collection, but I got an unexpected result, because it returns me segments like
enter image description here
(not pair of coordinates as I expected!)

Maybe anybody have some ideas?

Best Answer

This has been solved in a number of posts. Here's the one from Paul Ramsey that I like the best: http://blog.cleverelephant.ca/2015/02/breaking-linestring-into-segments.html

It boils down to: 

 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 lines) as dumps
)
SELECT * FROM segments WHERE geom IS NOT NULL;
Related Question