2

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)
 ...
Evan Carroll
  • 7,071
  • 2
  • 32
  • 58

3 Answers3

3

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)
Community
  • 1
Evan Carroll
  • 7,071
  • 2
  • 32
  • 58
  • 2
    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 08 '17 at 07:31
  • @Jendrusk what did you find hard to understand with this? I'll be happy to explain? – Evan Carroll Feb 08 '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 09 '17 at 07:28
3

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.

Evan Carroll
  • 7,071
  • 2
  • 32
  • 58
1

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.

Erwin Brandstetter
  • 236
  • 3
  • 9