6

In the picture below: The given point is C, I would like to select A and B, but not Z (which is closer to C than B but it's not immediately after in the LineString).

Note that C is always ON the line but not a vertex of the line.

enter image description here

0

2 Answers 2

6

You can find these points using ST_DumpPoints (docs) combined with the lag and lead window functions.

First, we can set up some test data:

CREATE TEMPORARY TABLE test (geom geometry);
INSERT INTO test VALUES ('LINESTRING (1 1, 2 2, 3 3, 4 4, 5 5)');

Then, we can use ST_DumpPoints to get the coordinates in sequence:

SELECT ST_DumpPoints(geom) AS dump FROM test;
                       dump                       
--------------------------------------------------
 ({1},0101000000000000000000F03F000000000000F03F)
 ({2},010100000000000000000000400000000000000040)
 ({3},010100000000000000000008400000000000000840)
 ({4},010100000000000000000010400000000000001040)
 ({5},010100000000000000000014400000000000001440)

(5 rows)

This returns a geometry_dump object, whose path field contains the index of the point, and whose geom field contains the point itself. Next, we can select from the above, using the lead and lag functions (I removed the ST_AsText calls for clarity):

    SELECT
      ((dump).geom,
      lag ((dump).geom, 1) OVER (ORDER BY (dump).path ASC) AS prev,
      lead((dump).geom, 1) OVER (ORDER BY (dump).path ASC) AS next
    FROM (SELECT ST_DumpPoints(geom) AS dump FROM test) sq;

    curr    |    prev    |    next    
------------+------------+------------
 POINT(1 1) |            | POINT(2 2)
 POINT(2 2) | POINT(1 1) | POINT(3 3)
 POINT(3 3) | POINT(2 2) | POINT(4 4)
 POINT(4 4) | POINT(3 3) | POINT(5 5)
 POINT(5 5) | POINT(4 4) | 
(5 rows)

Now, we have a recordset against which we can match a test point and find the next and previous points in the sequence:

SELECT prev, next
FROM <stuff above>
WHERE ST_Equals(geom, 'POINT (4 4)');

    prev    |    next    
------------+------------
 POINT(3 3) | POINT(5 5)
(1 row)
2
  • I'll try your solution in a moment. I have to say that point C is not a vertex of the line, so I should try to insert it in the line in some way. Dec 18, 2015 at 19:11
  • Yup, that changes things. You might be better off using a combination of ST_AddMeasure and ST_LineLocatePoint.
    – dbaston
    Dec 18, 2015 at 19:52
2

I finally resolved simply with these steps:

1) creating a table with the individual segments ("dumped" from the linestrings.. actually I didn't use st_dump or similars, but I created the segments from the table containing all the points)

2) find the segments (AB) that intersect C points

3) calculate boundary of AB and dump the result to get points A and B

However thank everyone for the precious help which made me understand many things.

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.