0

I have a query which tries to get objects around a radius of 5 km. There is one object A, which gets into the results, but if i calculate the distance of the center and the object coordinates i get around 10.772 km. Why does it get into the results?

Center (Berlin)

loc_lon: 13.406290000000013

loc_lat: 52.524268

Object A Coordinates (data inside table "objects")

Long: 13.260820

Lat: 52.485661

Convert center coordinates to radians:

$loc_lon = $loc_lon / 180 * M_PI;
$loc_lat = $loc_lat / 180 * M_PI;

Query:

SELECT id, title, loc_lat, loc_lon
FROM objects
WHERE ( 6368 * SQRT(2*(1-cos(RADIANS(loc_lat)) * cos(0.9167214137999) * (sin(RADIANS(`loc_lon`)) * sin(0.23398390097719) + cos(RADIANS(`loc_lon`)) * cos(0.23398390097719)) - sin(RADIANS(loc_lat)) * sin(0.9167214137999)))) 
BETWEEN 0 AND 5
Mike
  • 5,416
  • 4
  • 40
  • 73
  • What are the magic numbers 0.9167... and 0.23398...? I don't see how they relate to the center coordinates, and I don't recognize that formula (shouldn't there be some inverse sine or inverse cosine operations?) – Jim Lewis Mar 28 '15 at 19:41
  • These numbers are the center coordinates as radians. – Mike Mar 28 '15 at 19:45
  • D'oh, of course! OK, but I still don't recognize the formula...it doesn't seem to match either the Haversine formula or the spherical law of cosines, both of which involve inverse sine or inverse cosine operations. – Jim Lewis Mar 28 '15 at 20:31
  • Well, it works most of the time, but obviously not precise enough. If you have an alternative query i would be very interested. – Mike Mar 28 '15 at 20:40
  • This answer should be close to what you need: http://stackoverflow.com/a/574736/153430 – Jim Lewis Mar 28 '15 at 20:47
  • Interesting, I will try it. Thanks! – Mike Mar 28 '15 at 20:52
  • I switched the query successfully but guess what...i get exactly the same (wrong) results. – Mike Mar 28 '15 at 22:09
  • Solved it! It was an thinking error i did in an extended query. – Mike Mar 29 '15 at 12:37

0 Answers0