How to break apart a complex line segment 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.

Answer 2

Here we dump the line as points using ST_DumpPoints and then use a window function to compute the line segments using lead(). This method tended to be slower than this method here

WITH t AS (
  SELECT ST_MakeLine(ST_MakePoint(x,x)) AS l
  FROM generate_series(1,10) AS x
)
SELECT linesegment
FROM (
  SELECT ST_AsText(ST_MakeLine(geom, lead(geom) OVER ())) AS linesegment
  FROM t
  CROSS JOIN LATERAL ST_DumpPoints(t.l)
    WITH ORDINALITY
  ORDER BY ordinality
) AS g
WHERE linesegment IS NOT NULL;

      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)

Answer 3

I suggest this form of the query (short syntax):

SELECT ST_AsText(ST_MakeLine(geom, next_geom)) AS linesegment
FROM  (
   SELECT ST_MakeLine(ST_MakePoint(x,x)) AS l
   FROM   generate_series(1,10) x
   ) t
JOIN   LATERAL (
   SELECT geom, lead(geom) OVER () AS next_geom
   FROM   ST_DumpPoints(l)
   ) p ON next_geom IS NOT NULL;

Also demonstrating a window function in a LATERAL subquery.
(Compare Evan's comment on SO).

Or, more verbosely, to not depend on implementation details like order of rows or default column names:

SELECT path, ST_AsText(ST_MakeLine(geom, next_geom)) AS linesegment
FROM  (
   SELECT ST_MakeLine(ST_MakePoint(x,x)) AS l
   FROM   generate_series(1,10) x
   ) t
JOIN   LATERAL (
   SELECT path, geom, lead(geom) OVER (ORDER BY path) AS next_geom
   FROM   ST_DumpPoints(t.l) dp(path, geom)
   ) p ON next_geom IS NOT NULL
ORDER  BY path;

Since ST_DumpPoints() returns a path column anyway, we don't need to add WITH ORDINALITY.