# Get the 2 points immediately before and after a given point in PostGIS

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. 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)
``````
• 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`. Dec 18, 2015 at 19:52

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.