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

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)
  • 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 8 '17 at 7:31
  • @Jendrusk what did you find hard to understand with this? I'll be happy to explain? – Evan Carroll 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
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.