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/ ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
