[postgis-users] Closest point problem AND How to find the nearest road (geom) for the point which is not exactly located on the road.
woodbri at swoodbridge.com
Tue Aug 4 07:35:27 PDT 2009
Thurber, Fred wrote:
> I have a database full of point data. When our user stops at a certain
> lat/lon, how do I find the closest point in our database?
> We have at least 10,000 points, maybe a lot more so the search has to be
> efficient, but I have not found a way. It seems a little surprising
> considering how common this query must be.
> The obvious way is to scan the entire table and find the closest point.
> Let’s say our user stops at (40,70), I could do this:
> SELECT * FROM foo
> ORDER BY distance(point_geom, GeomFromText('POINT(70.0 40.1)', 4326))
> LIMIT 1
> When I run this through EXPLAIN ANALYZE, it shows a full-table scan. Is
> there a way I can use a GIST or is there some other way to speed it up?
Thilani also wrote:
> I’m developing a shortest path navigation application on GIS. I came across
> a problem when we need to find a shortest path from the location which is
> not exactly located on the road. In that case I need to find what is the
> nearest road (geom.) to that particular point 1st. Is there any available
> post GIS function to do that? If so can you kindly explain that using an
> Thanks and Best Regards,
Hi Fred and Thilani,
This question is asked often on the list You can check the archives for
more comments. Search for "Nearest Neighbor"
Basically, you can not currently do this efficiently with a single
query. Most people solve this problem if a plpgsql function. The key to
using an index requires a radius. Typical you should start with a small
radius and if you fail to get a result, double the radius and try again.
So something like this:
mac_search_radius := 1.0;
raduis := .0013;
select into rec * from mytable
where expand(setsrid(makepoint(70.0, 40.1),4326), radius) && the_geom
order by distance(setsrid(makepoint(70.0, 40.1),4326),the_geom)
if found then
radius := radius * 2.0;
if radius > max_search_radius then
-- do something with rec
More information about the postgis-users