sorin - 1 year ago 68

SQL Question

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

Answer Source

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.