equivalent Explode lines in Postgis

Question

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

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 (not pair of coordinates as I expected!)

Maybe anybody have some ideas?

Answer 1

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;

Answer 2

The code in the other answer doesn't work for me, it gives some duplicate segments, but I might have done some mistakes. The following code is taken from the same page and works for me:

SELECT st_makeLine(p0,p1)
FROM (
  SELECT st_pointN(way, generate_series(1, st_nPoints(way)-1)) p0,
  st_pointN(way, generate_series(2, st_nPoints(way)  )) p1
  FROM osm_line
) AS pts