mysql haversine distance

[welldressedmatt]

We're implementing a new client's existing site from another host onto our existing host, but I think their existing database must be built on ms sql rather than mysql -- because it is using the unrecognized function, haversine_distance, to figure the distance between stored sets of latitudes and longitudes in the database. I need an alternative to the following query that will work in mysql:

"select physicians.*, round(haversine_distance(latitude, longitude, $latitude, $longitude),2) as dist from physicians, zipcode where substring(physicians.zip,1,5) = zipcode.zip order by dist, lastname"

Basically, we have two tables. One with physicians and their addresses, and one with zipcodes and their corresponding lat/long. The user enters a zipcode in a form, then a query determines the lat/long for that zipcode from the zipcode table, and then uses the resulting lat/long pair with the query above to display all the physicians in that zipcode in order of distance. Actually, what we'd like it to do is show all the nearest physicians say in a 10 mile radius, rather than just those for the zipcode entered.

Any suggestions would be appreciated. I've done some hunting for alternative haversine queries, but none seem to be returning the right results. Thanks alot!

-Matt

[eeek]

There's a function defined at [sourceforge.net...] that does the calculation you need. It's in Postgresql but the math should translate.

[welldressedmatt]

It looks like the function you provided calculates the distance between two KNOWN zipcodes. I need to be able to find all the nearest zipcodes to one known zipcode. My mysql skills are not that high, so if anyone could help me rewrite the query above it would be a great help!

-Matt

[welldressedmatt]

The following query seems to be doing the trick:

"select physicians.*, round(3958*3.1415926*sqrt((latitude-'$latitude')*(latitude-'$latitude') + cos(latitude/57.29578)*cos('$latitude'/57.29578)*(longitude-'$longitude')*(longitude-'$longitude'))/180) as dist from physicians, zipcode where substring(physicians.zip,1,5) = zipcode.zip order by dist, lastname"

Thanks!
-Matt