Re: indexing lat lon

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jonathan Raemdonck" <jonathan_raemdonck(at)hotmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: indexing lat lon
Date: 2004-06-27 23:04:20
Message-ID: 14720.1088377460@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Jonathan Raemdonck" <jonathan_raemdonck(at)hotmail(dot)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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Jay 2004-06-28 01:05:13 postgresql perl connectivity
Previous Message Tom Lane 2004-06-27 22:48:44 Re: ERROR: tables can have at most 1600 columns