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 > [email protected] > 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. _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
