How to break apart a complex line segment into simple line segments between all points?

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)
...


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.

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)

• I hate you... 20 minutes ago I was thinking I know SQL quite good... This 20 minutes took me just to understand what is happening in this query... – Jendrusk Feb 8 '17 at 7:31
• @Jendrusk what did you find hard to understand with this? I'll be happy to explain? – I Support The Boycott Feb 8 '17 at 16:04
• Thanx Evan - It's OK now. I had to read alittle about LATERAL and WITH ORDINALITY, also window functions deployment broke my vision of what you can and can't do in SQL, so I know they exists but can't learn to use them when they could be usefull... First resolution I had in mind was based on some FOR ... LOOP, function or anonymous block - this query is at least few levels better – Jendrusk Feb 9 '17 at 7:28

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.