2011/10/8 René Fournier <renefournier@xxxxxxxxx>
Yes exactly. That's how people do it now, in pre-PostGIS-2.0 era :-)
Make a search by bounding boxes, starting with some arbitraly selected radius. Increase the radius until you have at least N=1 result found, than sort these results by ST_Distance and select nearest neighbour.
PostGIS 2.0 solution: see http://blog.opengeo.org/2011/09/28/indexed-nearest-neighbour-search-in-postgis/
Wow, have to say, I love Postgresql and PostGIS. Just awesome.So I have a table with ~400,000 rows, each representing a road or street (multi line segment). I want to select the row whose line segment is closest the a given point. The following query...gc3=# SELECT r_stname_c, r_placenam, ST_Distance(ST_GeomFromText('POINT(-114.053205 51.069644)',4269),the_geom) AS distance FROM nrn_ab_8_0_roadseg ORDER BY distance ASC LIMIT 1;...works and produces...r_stname_c | r_placenam | distance----------------------+------------+----------------------19 Avenue North-east | Calgary | 5.74515867479735e-05…but seems a little slow (yes, there is a GIST index on the_geom). Explain shows:gc3=# explain SELECT r_stname_c, r_placenam, ST_Distance(ST_GeomFromText('POINT(-114.053205 51.069644)',4269),the_geom) AS distance FROM nrn_ab_8_0_roadseg ORDER BY distance asc limit 1;QUERY PLAN-----------------------------------------------------------------------------------------------------------Limit (cost=128520.06..128520.06 rows=1 width=464)-> Sort (cost=128520.06..129493.57 rows=389404 width=464)Sort Key: (st_distance('0101000020AD100000F5BEF1B567835CC06A2E3718EA884940'::geometry, the_geom))-> Seq Scan on nrn_ab_8_0_roadseg (cost=0.00..126573.04 rows=389404 width=464)(4 rows)Any suggests how to speed it up? Coming from MySQL, I'm brand-new to PostGIS (and Postgresql FWIW) and all the awesome spatial functions it has. I would think that maybe selecting a bounding box of rows, and then finding the one with the closest distance?
Yes exactly. That's how people do it now, in pre-PostGIS-2.0 era :-)
Make a search by bounding boxes, starting with some arbitraly selected radius. Increase the radius until you have at least N=1 result found, than sort these results by ST_Distance and select nearest neighbour.
PostGIS 2.0 solution: see http://blog.opengeo.org/2011/09/28/indexed-nearest-neighbour-search-in-postgis/
