indexing lat lon

[Jonathan Raemdonck]

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

[Tom Lane]

"Jonathan Raemdonck" <jo****************@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Oleg Bartunov]

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: ol**@sai.msu.su, http://www.sai.msu.su/~megera/
phone: [phone]

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster