Zip Code Proximity Search WTF
I love this little gem. Had to debug a website search tool for a client. It was basically a zip-code locator for a certain profession - kinda like a store locator. Enter a zip code (postal code for those of you on the other side of the pond), select a range (<20 miles, 20-50 miles, etc), and find the closest locations. The problem - it was returning results that were many miles away at the top of the list, and closer things further down. Not in every location, nationwide, just certain zip codes.
After wading through the mountains of VB code (this isn't the WTF) and oddball technology abuse/mishmash - MSAccess, XML, Sessions, JScript (saving that WTF for a different post) - I finally found it:
A page of JScript converts miles (search radius) to degrees, to determine what lats and longitudes to check. Then, a query to the database for all Zip/Location matches that fall into the target latitude and longitude.
The Punchline? "ORDER BY ZipCode".
Wow. Never mind that converting miles to degrees doesn't work so well if the chain is nationwide (meridians are farther apart in Florida than in Maine, so southern folk end up driving further E/W then perhaps they needed to). Unless that JScript looked up the starting zip for a center and compensated, which I doubt.
And, by the virtue of having a limiting circle, aren't they already computing distance with each row-compare in the SQL query? Or was that a limiting 'box'...
Ahh. I get it now. They never even bothered to compute the distance (maybe they forgot the Euclidean formula?) The zipcode was an arbitrary enough metric to sort on. I know, you can sort on ($MyZip - ZipCode)!
Because zip codes that are close numerically are often close geographically.