MYSQL cannot select records with distance between points (lat,lng) =0

3
votes

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 
             ON (de.admin1_code=zde.admin_code1)  
                 Where zde.id=8 and  de.geoId=24 having distance<1

This query should return a record from table tbl_zountry_de which has the same latitude and longitude values as record with id=8 from tbl_zipde but because the calculated distance between this two points is 0, mysql recognizes 0 as NULL for distance and so it dosen't return any record but if I delete the "having distance<1" then it returns the corect record with id=24 from tbl_country_de but with a NULL value for table column distance.

How should I write the mysql query in order for mysql to return records with distance equal to 0 between (latitude and longitude values) for two points

Why does mysql return NULL for distance instead of '0' ?

I have change the latitude and longitude data type from decimal(10,7) to float(10,7) and I works. Don't know if that was the real problem??

4
phpmysqlfloating-pointlatitude-longitudetrigonometry
null would indicate a math error (e.g. division by zero), nulls in one of the lat/long fields, or a join that simply doesn't return any records, forcing the db to fill in nulls for missing fields, which propagate up the math food chain - Marc B
@MARC B OK I understand but simply by changing the data type from decimal to float solved the problem. What is the explanation for this ? - sorin
no idea. try re-running the original query with the parts broken out, e.g. radians(de.latitude) as radlat, and see if a null pops up in any of those. remember that null is "contagious". if you include a null value in any sort of math operation, the result of the entire operation becomes null. - Marc B

4 Answers

4
votes

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.

1
votes

It shouldn't return a null. You've got a left join rather than an inner join, so you possibly have some records in your table without a matching admin code in tbl_country_de

Or do you have nulls in your data?

0
votes

I have change the latitude and longitude data type from decimal(10,7) to float(10,7) and it works. Don't know if that was the real problem??

0
votes

I know this is a really old thread, but I just ran into this in some legacy code. I solved the problem by using the coalesce function around the distance calculation.

select coalesce(*distance caclulation*, 0) as distance from ...

Therefore I get 0 back from the calculation rather than null. I'm sure someone will tell me this is a hackish, terrible approach, but it's also not a critical operation to us or someone would have found the error before now.