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.
29 GunMuse Lane
P.O. box 166
Lakewood NM 88254
469 228 2183
From: Richard Lynch [mailto:ceo@stripped]
Sent: Monday, April 25, 2005 12:05 AM
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
> 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.
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=1