CODEWITHSUNDEEP
phpmysqlfloating-pointlatitude-longitudetrigonometry
sorin
sorin

Reputation: 461

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

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??

Upvotes: 3

Views: 1405

Answers (4)

Andy
Andy

Reputation: 23

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.

Upvotes: 0

O. Jones
O. Jones

Reputation: 108676

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.

Upvotes: 4

sorin
sorin

Reputation: 461

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??

Upvotes: 0

FJT
FJT

Reputation: 2083

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?

Upvotes: 1

Related Questions