6

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?

Jane
  • 1,066
  • 7
  • 20

2 Answers2

9

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;
tilt
  • 3,269
  • 13
  • 24
1

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
qwlice
  • 176
  • 4
  • 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