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

Question

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.

Answer 1

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)

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