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?


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

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)
  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
  • Did not work for me, the singlepart geometries had more than two nodes. I leave this comment as a warning to others. – Pocketsand Dec 11 '19 at 11:22

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.