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.