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 08:58:50
Message-ID: 20041129085850.GA46935@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-general
On Sun, Nov 28, 2004 at 11:06:38PM -0800, Uwe C. Schroeder wrote:

> The "unhandy" part is maybe a personal perception. I like stored procs, but in 
> this case the calculation is repeated over and over again (ok, it would be 
> the same with views).  Basically the part I don't like is that the proc 
> currently calculates way more values than needed.  Because something like
> .... where sqrt(pow((lat1 - lat2),2) + pow((long1 - long2),2)) >= 50
> certainly calculates the distance of all the records and then compares the 
> result to the 50 mile radius.

The formula you mention is for calculating distances on a plane, not on
the surface of a sphere.  Google for more appropriate formulae (e.g.,
the Haversine Formula) or use the functions in contrib/earthdistance.

> I'd rather have something that excludes most of the records that
> aren't in question anyways. How do you come to the lat/long values
> for the max difference? Is there a general formula for that?

For the longitude bounds, calculate how far away a point one degree
due east or west would be.  For example, the distance between (40, -90)
and (40, -91) is about 53mi, depending on what value you use for the
Earth's radius (the Earth isn't a perfect sphere).  If you want to
find points within 50 miles, limit your search to longitudes within
50/53 (0.94) degrees of -90, or -90.94 to -89.06.

Repeat for latitude.  The distance between (40, -90) and (41, -90)
is about 69mi, so limit your search to latitudes within 50/69 (0.72)
degrees of 40, or 39.28 to 40.72.

Note that one degree of longitude doesn't cover the same distance
as one degree of latitude: that's because longitude lines converge
as they approach the poles.  At the equator, one degree of longitude
covers about 69mi, while at 40N it's only 53mi.

> This looks like I could omit records too far away from the calculation
> in the first place.

That's the idea.  Using the above calculations, you'd make a query
like this:

SELECT ...
FROM ...
WHERE latitude BETWEEN 39.28 AND 40.72
  AND longitude BETWEEN -90.94 AND -89.06
  AND distance(latitude, longitude, 40, -90) <= 50;

Substitute an appropriate function for distance().

The latitude and longitude checks find the candidate points and the
distance check makes the final selection.  With indexes on latitude
and longitude (or a multicolumn index on both latitude and longitude),
this query should be reasonably fast.

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

In response to

pgsql-general by date

Next:From: Nageshwar RaoDate: 2004-11-29 09:20:20
Subject: tool for postgresql
Previous:From: Chris GreenDate: 2004-11-29 08:50:51
Subject: Re: Adding Reply-To: <listname> to Lists configuration ...

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