Geek Answers Handbook

Find the closest point along the linear line in the specified distance limit and order

I have such a problem and it would be nice if someone can help me. I have a point table with a GIST index. These points do not change over time.

I would like to get the points that are next to some line. Example. Imagine that linestring is a road and the dots are along a road along a road. I would like to take poi that are 5 km away from this road. I would like to get these pois in the correct order (driving along the road). Look at the image:

image

For this road, from point 1 - 5, I would like to get a POI, which is 5 km from the road and in order from point 1 to 5 along the road. Thus, the result should be:

POI_ID 1 5 6 8 9 10 12 13

This should tell me which POI I can visit while traveling on the road at minimal cost.

Does anyone have any ideas how to do this using postgres and postgis?

+8
postgresql postgis
source share
2 answers

Assuming you have geom geometry columns that use the projected SRID of counters in the road (LINESTRING) and poi (POINT) tables, your query to find all the POIs within a 5 km radius (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 part of an ORDER with ST_LineLocatePoint uses a fraction between 0.0 and 1.0, depending on where the point is along LINESTRING. If the direction of the road is “wrong,” add DESC to reverse the order. The second part of ORDER is based on the distance, which can be used if the point slightly went beyond the start / end of LINESTRING (where ST_LineLocatePoint will return 0.0 or 1.0, respectively).

This query may also work if you use the geography type with long / latitude values, as it automatically calculates counters, not degrees. Check out the docs for more:

+8
source share

It looks like you could use the KNN-GIST function last year with PostgreSQL 9.1 and supported by PostGIS version 2.0.

http://blog.opengeo.org/2011/09/28/indexed-nearest-neighbour-search-in-postgis/

0
source share

More articles:


All Articles