Find the nearest points along the linestring in specified distance limit and order

Question

I have such problem and I would be nice If somebody can help me. I have points table with GIST index. Those points don't change in time.

I would like to fetch points that are near some given linestring. Example: Imagine that linestring is the road and points are poi along the road. I would like to fetch poi's that are in 5 km distance from the given road. I would like to fetch those pois in correct order (driving order along the road). Look at the image:

For given road from point 1 to 5 i would like to fetch POIs that is in 5 km max from the road and in order from point 1 to 5 along the road. So the result should be:

POI_ID
1
5
6
8
9
10
12
13

This should tell me what POI I can visit during traveling along the road with minimum cost.

Does anybody have some ideas how to do it with postgres and postgis?

Answer 1

Assuming you have geometry columns geom that use a projected SRID of meters in tables road (LINESTRING) and poi (POINT), your query to find all POIs within 5 km of road (where id = 123) should be something like:

SELECT poi.*, ST_Distance(road.geom, poi.geom)/1000.0 AS distance_km
FROM road, poi
WHERE road.id = 123 AND ST_DWithin(road.geom, poi.geom, 5000.0)
ORDER BY ST_LineLocatePoint(road.geom, poi.geom),
         ST_Distance(road.geom, poi.geom);

The first ORDER part with ST_LineLocatePoint uses a fraction between 0.0 and 1.0, depending where the point is along the LINESTRING. If the direction of the road is going "the wrong way", then append DESC to reverse the order. The second ORDER part is based on distance, which could be used if the point is slightly past the start/end of the LINESTRING (where ST_LineLocatePoint would return 0.0 or 1.0, respectively).

This query might also work if you are using the geography type with Long/Latitude values, since it automagically calculates metres -- not degrees. Check out docs for more:

• ST_Distance
• ST_DWithin
• ST_LineLocatePoint (or ST_Line_Locate_Point for older versions of PostGIS)