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.
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.
Sign up to join this communityIn 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.
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)
ST_AddMeasure
and ST_LineLocatePoint
.
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.