Обсуждение: indexing lat lon
I have the following situation: I'm developing an reverse geocoder, so input is lat/lon coordinate and output is the nearest textual location. I have a database of +- 2 000 000 records that contains lat/lon and full name of the location. These are the steps I take now: 1 'draw' a box around the lat/lon position we have as input 2 search the DB for all the points in this box 3 measure the distance to each point in the box This works fine, but step 2 takes too lang for obvious reasons... select * from table where lat between box_point1_lat and box_point2_lat and lon between box_point1_lon and box_point2_lon; This would require a seq scan for but indexing the table doesn't help much either (factor 2) ... Is there a good way of indexing the lat/lon column so lookup is faster? I have looked at PostGis but didn't think it would give a good solution in this context (correct me if I'm wrong) _________________________________________________________________ Plannen om een nieuwe computer te kopen? Kijk eens hier.. http://www.msn.be/shopping/dell_bis/
"Jonathan Raemdonck" <jonathan_raemdonck@hotmail.com> writes: > These are the steps I take now: > 1 'draw' a box around the lat/lon position we have as input > 2 search the DB for all the points in this box > 3 measure the distance to each point in the box Right. All you need is an index amenable to step 2. > Is there a good way of indexing the lat/lon column so lookup is faster? Yeah, you can store the lat/long as a point, build an rtree index on it, and use box-overlap as the indexable operator. I seem to recall that you actually have to store the lat/long as a box of zero size to get this to work with the present set of rtree-indexable operators. If you look in the archives you will find some previous similar cases. regards, tom lane
Jonathan, you may try contrib/pgsphere http://www.pgastro.org/cgi-bin/wiki.pl?pgSphere On Sun, 27 Jun 2004, Jonathan Raemdonck wrote: > I have the following situation: > > I'm developing an reverse geocoder, so input is lat/lon coordinate and > output is the nearest textual location. > I have a database of +- 2 000 000 records that contains lat/lon and full > name of the location. > > These are the steps I take now: > 1 'draw' a box around the lat/lon position we have as input > 2 search the DB for all the points in this box > 3 measure the distance to each point in the box > > This works fine, but step 2 takes too lang for obvious reasons... > select * from table > where lat between box_point1_lat and box_point2_lat > and lon between box_point1_lon and box_point2_lon; > > This would require a seq scan for but indexing the table doesn't help much > either (factor 2) ... > > Is there a good way of indexing the lat/lon column so lookup is faster? I > have looked at PostGis but didn't think it would give a good solution in > this context (correct me if I'm wrong) > > _________________________________________________________________ > Plannen om een nieuwe computer te kopen? Kijk eens hier.. > http://www.msn.be/shopping/dell_bis/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83