2

I have a table that, when ordered, describes a path by point descriptions in each row. A column tells whether a row is a special (stop) point within that path and is empty otherwise. The path starts and ends with such a row and has many in between. What I want to do is create line segments from the point rows that start at such a row and also end at such a row.

point  stop
  1     yes
  2      
  3     yes
  4
  5
  6     yes

I want to call ST_Makeline from PostGIS to turn the ordered points into these line segments. How can I enforce that ST_Makeline, being a "summary" function is only called for the inclusive point groups 1 to 3 and 3 to 6?

  • First point is always one stop point? – McNets Jan 23 at 11:44
  • Yes, the first and last point mark the end points of the overall line – telegott Jan 23 at 11:59
  • Then all intermediate stop points must be included in two segments. It is a bit complicated design. – McNets Jan 23 at 12:09
1

I personally think this is not a good design because all intermediate stop points must be included into two segments.

IMHO, you should be able to transform it into something similar to:

id | start | end | stop
---|-------|-----|-----
 1 |   1   |  2  |
 2 |   2   |  3  | yes
 3 |   3   |  4  | 
 4 |   4   |  5  | 
 5 |   5   |  6  | yes

But using your current schema you could try by using:

WITH x AS
(
  SELECT
      point,
      stop,
      LEAD(point) OVER (ORDER BY point) nxt,
      SUM(CASE WHEN stop = true THEN 1 ELSE 0 END) OVER (ORDER BY point) grp
  FROM
      tbl
  ORDER BY
      point
)
SELECT
    grp, ST_MakeLine(array_agg(ST_MakePoint(point, nxt::int))) lines
FROM
    x
WHERE
    nxt IS NOT NULL
GROUP BY
    grp
ORDER BY
    grp;

That basically returns an array of points like this:

grp | points                   
--: | :------------------------
  1 | {"(1,2)","(2,3)"}        
  2 | {"(3,4)","(4,5)","(5,6)"}

Then using ST_MakePoint and ST_MakeLine you can get:

grp | lines                                                                                                             
--: | :-----------------------------------------------------------------------------------------------------------------
  1 | 010200000002000000000000000000F03F000000000000004000000000000000400000000000000840                                
  2 | 010200000003000000000000000000084000000000000010400000000000001040000000000000144000000000000014400000000000001840

db<>fiddle here

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.