I have the fallowing mysql query
SELECT de.geoId,(6371 * ACOS( COS( RADIANS(zde.latitude) )*
COS(RADIANS( de.latitude ) ) * COS( RADIANS( de.longitude ) - RADIANS(zde.longitude) ) +
SIN( RADIANS(zde.latitude) ) * SIN( RADIANS( de.latitude ) ) ) ) AS distance
FROM tbl_zipde AS zde
LEFT JOIN tbl_country_de AS de
Where zde.id=8 and de.geoId=24 having distance<1
This question has been around for a while, but it hasn't been answered correctly yet.
DECIMAL is an inappropriate data type for geolocation data.
The formula in the question is known as the Spherical Cosine Law formula. If you take a careful look at this formula you'll notice that when you deal with points that are very close together it takes the inverse cosine (
ACOS()) of a number that's vanishingly close to 1. That's a numerically unstable operation. Unstable in this context means that the output of the function can vary greatly if the input has small errors in it.
There's a better formula for distance along the surface of a sphere called the Vincenty Formula. Its last operation is an
ATAN2(): a much more numerically stable operation for tiny angles. This is it:
111.045 * DEGREES(ATAN2(SQRT( POW(COS(RADIANS(lat2))*SIN(RADIANS(lon2-lon1)),2) + POW(COS(RADIANS(lat1))*SIN(RADIANS(lat2)) - (SIN(RADIANS(lat1))*COS(RADIANS(lat2)) * COS(RADIANS(lon2-lon1))) ,2)), SIN(RADIANS(lat1))*SIN(RADIANS(lat2)) + COS(RADIANS(lat1))*COS(RADIANS(lat2))*COS(RADIANS(lon2-lon1))))
A MySQL stored procedure for this is here: http://www.plumislandmedia.net/mysql/vicenty-great-circle-distance-formula/
You can look up the math here: http://en.wikipedia.org/wiki/Great-circle_distance
Also, please keep in mind that the trig functions (cosine, sine, arctangent, etc.) in MySQL are implemented using the server computer's floating point math subsystem. If you provide data to them in DECIMAL format, the data is converted to DOUBLE, computed, and then converted back. This will cause it to lose precision. The
FLOAT data type (IEEE-488 single precision floating point) provides plenty of accuracy for commercial GPS-style latitude and longitude data.
The NULL results mentioned in the question are probably due to the input to ACOS turning up infinitesimally greater than one due to the errors inherent in converting between DOUBLE and DECIMAL. The inverse cosine of 1.00001 fails and yields NULL. Switching to FLOAT and to the Vincenty formula will eliminate this source of failure.