Re: How many views...

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/