The coolest sql query in town

[X-Gote]

SELECT dealers . * , ( 3963 * acos( sin( zipcodesearch.latitude / 57.2958 ) * sin( zipcode.latitude / 57.2958 ) + cos( zipcodesearch.latitude / 57.2958 ) * cos( zipcode.latitude / 57.2958 ) * cos( zipcode.longitude / 57.2958 - zipcodesearch.longitude / 57.2958 ) ) )
as Dealer_Distance
FROM dealers, zipcode, zipcode
as zipcodesearch
Where (
zipcodesearch.ZIPCode = '85220'
) AND Dealer_Publish = "Y" AND dealers.Dealer_Zip = zipcode.ZIPCode AND ( 3963 * acos( sin( zipcodesearch.latitude / 57.2958 ) * sin( zipcode.latitude / 57.2958 ) + cos( zipcodesearch.latitude / 57.2958 ) * cos( zipcode.latitude / 57.2958 ) * cos( zipcode.longitude / 57.2958 - zipcodesearch.longitude / 57.2958 ) ) ) < 250
ORDER BY Dealer_Name DESC

[Consumer]

Originally posted by X-Gote
SELECT dealers . * , ( 3963 * acos( sin( zipcodesearch.latitude / 57.2958 ) * sin( zipcode.latitude / 57.2958 ) + cos( zipcodesearch.latitude / 57.2958 ) * cos( zipcode.latitude / 57.2958 ) * cos( zipcode.longitude / 57.2958 - zipcodesearch.longitude / 57.2958 ) ) )
as Dealer_Distance
FROM dealers, zipcode, zipcode
as zipcodesearch
Where (
zipcodesearch.ZIPCode = '85220'
) AND Dealer_Publish = "Y" AND dealers.Dealer_Zip = zipcode.ZIPCode AND ( 3963 * acos( sin( zipcodesearch.latitude / 57.2958 ) * sin( zipcode.latitude / 57.2958 ) + cos( zipcodesearch.latitude / 57.2958 ) * cos( zipcode.latitude / 57.2958 ) * cos( zipcode.longitude / 57.2958 - zipcodesearch.longitude / 57.2958 ) ) ) < 250
ORDER BY Dealer_Name DESC

Oh jeah? Does it show "distance from" in the results?

[X-Gote]

it certianly does!

[Consumer]

When Ramenboy did a function like that a year ago, he ended up rewriting it in C++ to incorporate it as a UDF for MySql for speed gain.

[X-Gote]

Its quick especially after you index your database. And quick if you use a precompiled version of mysql.. Ive noticed, at least in a production environment that hosts 40-50 databases, the pre-compiled version of MySQL caches a little nicer.

[Consumer]

Most worthwhile conversation on this board in a month :D

[Dub-U-Eff]

Originally posted by deepfreq
Most worthwhile conversation on this board in a month :D Really? I thought this topic was very stimulating...Piqued my interests...I even got a whistle tip installed on my Exploder...http://www.drum-core.com/board/showthread.php?s=&threadid=3338

[cpud]

hi, i cant code my way out of a paper sack ;/

[X-Gote]

http://www.mysql.com/images/mysql.png > http://www.drum-core.com/board/avatar.php?userid=188&dateline=1043663899