Http://www.gunmuse.com Ok I use a storelocator.
First if you have 8000 + records it becomes an issue. BUT Lat and long is in minutes and minutes can be used to estimate miles. By Breaking down the lat and long, Breaking down the Zip to a two digit prefix 88254 becomes 88 for indexing (Because the post offices goes in order folks with some exceptions) Then with a wide lasso you can rope your results to do your math check with. Break your lat and long fields up in hours minutes and seconds and filtering down becomes very easy to do. Learning to read a map before determining the key and distance calculation would help better understand this problem. Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183 -----Original Message----- From: Richard Lynch [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 12:05 AM To: Hank Cc: MySql Subject: Re: zip code search within x miles On Tue, April 19, 2005 8:55 am, Hank said: > Talk about over complicating things... here's the above query simplifed. > > I can not figure out why they were self joining the table three times: > > SELECT b.zip_code, b.state, > (3956 * (2 * ASIN(SQRT( > POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) + > COS(a.lat*0.017453293) * > COS(b.lat*0.017453293) * > POWER(SIN(((a.lng-b.lng)*0.017453293)/2),2))))) AS distance > FROM zips a, zips b > WHERE > a.zip_code = '90210' > GROUP BY distance > having distance <= 5; You'd have to time it, and *MAYBE* with enough indices this will all work out, but you'd probably be better off doing two queries. One to look up the long/lat for 90210, and another on just zips to calculate the distance. Benchmark on your own hardware and see for yourself. I could be 100% wrong. -- Like Music? http://l-i-e.com/artists.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
