Skip site navigation (1) Skip section navigation (2)

indexing lat lon

From: "Jonathan Raemdonck" <jonathan_raemdonck(at)hotmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: indexing lat lon
Date: 2004-06-27 22:40:32
Message-ID: BAY17-F18cc4zyjKILk0004498b@hotmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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/


Responses

pgsql-general by date

Next:From: Tom LaneDate: 2004-06-27 22:40:44
Subject: Re: pg_dump out of shared memory
Previous:From: Tom LaneDate: 2004-06-27 22:14:32
Subject: Re: Performance problem on RH7.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group