[postgis-users] identfying a nearest point

[Supunmali Ahangama]

Stephen Woodbridge wrote:
> 
> Stephen Woodbridge wrote:
>> Supunmali Ahangama wrote:
>>> Please suggest me with the POSTGIS/pgrouting function to identify the 
>>> nearest
>>> point from set of points (vertices in a graph) to a particular point
>>> (outside the graph)? 
>> 
>> You typically want to search within some radius so you do not have to do 
>> a full table scan.
>> 
>> select * from vertices_tmp where st_expand(setsrid(makepoint(x,y),4326)) 
>> && the_geom order by distance(setsrid(makepoint(x,y),4326),the_geom) asc 
>> limit 1;
>> 
>> Should do what you want.
>> 
>> -Steve
> 
> Oops, forgot to put the raduis in:
> 
> select * from vertices_tmp where 
> st_expand(setsrid(makepoint(x,y),4326),radius)
> && the_geom order by distance(setsrid(makepoint(x,y),4326),the_geom) asc
> limit 1;
> 
> -Steve
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 


I tried that function, but it gives an incorrect result. When I viewed them
over a map viewer (Quantum GIS), there are other vertices closer to that
point. 
But through this SQL it dont select the closest vertex which I can view
through QGIS, but selects another vertex (this is not the most far ever
point) though there are many vertices closer by to that point.
Even I tried to find the nearest vertex to a point on the edge of the road
network, but it dont select the nearest point (one end point of the edge).
 
This also gives the same result when I tried finding closest vertex to other
points in customer_location.

This is the SQL I tried with:

select v.*, c.gid 
from vertices_tmp v, customer_location c 
where c.gid=1 and 
st_expand(setsrid(((select the_geom from customer_location where
gid=1)),4326), 10000000) && setsrid(v.the_geom,4326)
order by distance(setsrid((select the_geom from customer_location where
gid=1),4326),setsrid(v.the_geom,4326))
asc limit 1; 


and I tried this just considering distance (full table scan):
select v.*, c.gid 
from vertices_tmp v, customer_location c 
where c.gid=1
order by distance(setsrid((select the_geom from customer_location where
gid=1),4326),setsrid(v.the_geom,4326)) asc 
limit 1;  

both SQLs give the same answer (incorrect).  Please suggest me, what I have
done wrong. 
-- 
View this message in context: http://www.nabble.com/identfying-a-nearest-point-tp23193816p23211634.html
Sent from the PostGIS - User mailing list archive at Nabble.com.