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

Re: indexing lat lon

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: Jonathan Raemdonck <jonathan_raemdonck(at)hotmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: indexing lat lon
Date: 2004-06-28 07:29:13
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general

you may try contrib/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..
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su,
phone: +007(095)939-16-83, +007(095)939-23-83

In response to

pgsql-general by date

Next:From: Philippe LangDate: 2004-06-28 08:13:02
Subject: Plperlu and sending emails, is it safe?
Previous:From: Carlos Ojea CastroDate: 2004-06-28 07:25:57
Subject: Connect to a PostgreSQL table with kylix3

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