[postgis-users] Closest point problem AND How to find the nearest road (geom) for the point which is not exactly located on the road.

[Stephen Woodbridge]

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:
> Hi,
> 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
> example.
> Thanks and Best Regards,
> Thilani

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;
loop
   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)
    limit 1;
   if found then
     exit;
   end if;
   radius := radius * 2.0;
   if radius > max_search_radius then
     return null;
   end if;
end loop;
-- do something with rec
return some_result;

-Steve W