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

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 (view raw or flat)
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

pgsql-general by date

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

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