PostGIS – How to Break Apart Complex Line Segments into Simple Line Segments Between All Points

Question

Take for instance a very complex line string.. which has WKT like this LINESTRING(1 1,2 2,3 3,4 4,5 5 ...) I want to create two-point linestrings from many-point linestrings.

SELECT ST_MakeLine(ST_MakePoint(x,x)) AS l
FROM generate_series(1,10) AS x

To break down into simple linestrings between all points.

      linesegment      
-----------------------
 LINESTRING(1 1,2 2)
 LINESTRING(2 2,3 3)
 LINESTRING(3 3,4 4)
 LINESTRING(4 4,5 5)
 LINESTRING(5 5,6 6)
 ...

Answer 1

Here we use generate_series and ST_NPoints() to generate the index into the line. And, ST_PointN() to get the specific points.

WITH t AS (
  SELECT ST_MakeLine(ST_MakePoint(x,x)) AS l
  FROM generate_series(1,10) AS x
)
SELECT ST_AsText(
  ST_MakeLine(ST_PointN(t.l,gs), ST_PointN(t.l,gs+1))
) AS linesegment
FROM t
CROSS JOIN LATERAL generate_series(1,ST_NPoints(t.l)-1)
  WITH ORDINALITY AS gs
ORDER BY ordinality;

      linesegment      
-----------------------
 LINESTRING(1 1,2 2)
 LINESTRING(2 2,3 3)
 LINESTRING(3 3,4 4)
 LINESTRING(4 4,5 5)
 LINESTRING(5 5,6 6)
 LINESTRING(6 6,7 7)
 LINESTRING(7 7,8 8)
 LINESTRING(8 8,9 9)
 LINESTRING(9 9,10 10)
(9 rows)

This method taken from PostGIS in Action, 2nd Edition by Regina O. Obe and Leo S. Hsu.