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

Re: How many views...

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: "Uwe C(dot) Schroeder" <uwe(at)oss4u(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: How many views...
Date: 2004-11-29 06:43:28
Message-ID: 20041129064328.GA46514@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-general
On Sun, Nov 28, 2004 at 06:41:50PM -0800, Uwe C. Schroeder wrote:

> I need to search a lot of locations based on distance (simple zipcode match 
> based on longitude and latitude). However I need to calculate the distance 
> between each of the nodes, so if you are in xxx I need to get the distance to 
> all others in the database. I'm currently doing this with a stored procedure 
> that gets the originating zipcode and a maximum distance in miles which then 
> selects all other nodes within that search radius. This is pretty unhandy, 
> but it works.

What's unhandy about this approach?  I've written stored procedures
that do exactly what you're talking about; they work fine and are
easy to use.

Are you using a bounding box to limit the number of nodes that
you need to check?  For example, if the originating zipcode is
at 40.0N 90.0W and you want to find all other zipcodes within
50 miles, then you'd only need to check the distance to those
zipcodes with a latitude between about 39.27N - 40.73N and a
longitude between about 89.05W and 90.95W.  No zipcode outside
that box could possibly be within 50 miles of the origin, so
there's no need to calculate and check the distances to them.
If you have indexes on latitude and longitude then the search
should be fast.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

In response to

Responses

pgsql-general by date

Next:From: Greg StarkDate: 2004-11-29 06:46:50
Subject: Re: sequencing two tables
Previous:From: Greg StarkDate: 2004-11-29 06:18:05
Subject: Re: Boolean error

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