0

distance (straight)

from: (lat) 48.73233 (long) 2.36618

to: lat() 48.84647 (long) 2.41026

equals some: 13096.16 meters

If I use PHP formula, I get proper result.

But when I translate same PHP formula directly into MySQL query - I get 5904.2757 etc.

Here is the code:

php:

 $distance = atan2(sqrt(pow(sin((($to_lat - $from_lat) * M_PI / 180) / 2), 2) +
         cos(($from_lat * M_PI / 180)) * cos(($to_lat * M_PI / 180)) *
         pow(sin((($to_long - $from_long) * M_PI / 180) / 2), 2)), sqrt(1 - (pow(sin((($to_lat - $from_lat) * M_PI / 180) / 2), 2) +
         cos(($from_lat * M_PI / 180)) * cos(($to_lat * M_PI / 180)) *
         pow(sin((($to_long - $from_long) * M_PI / 180) / 2), 2)))) * 2 * $radiusOfEarth;

mysql:

atan2(sqrt(pow(sin(((ap.Latitude - $from_lat) * pi() / 180) / 2), 2) +
             cos(($from_lat * pi() / 180)) * cos((ap.Latitude * pi() / 180)) *
             pow(sin(((ap.Longitude - $from_long) * pi() / 180) / 2), 2)), sqrt(1 - (pow(sin(((ap.Latitude - $from_lat) * pi() / 180) / 2), 2) +
             cos(($from_lat * pi() / 180)) * cos((ap.Latitude * pi() / 180)) *
             pow(sin(((ap.Longitude - $from_long) * pi() / 180) / 2), 2)))) * 2 * 6371000 as Distance
Jeffz
  • 2,075
  • 5
  • 36
  • 51

1 Answers1

1

The exact thing you want.

SELECT ((ACOS(SIN(48.73233 * PI() / 180) * SIN(48.84647 * PI() / 180) + COS(48.73233 * PI() / 180) *
COS(48.84647 * PI() / 180) * COS((2.36618 - 2.41026) * PI() / 180)) * 180 / PI()) * 60 *1.1515 * 1.609344 *1000)
AS distance FROM dual;
av1987
  • 493
  • 4
  • 12
  • For the further ref. http://stackoverflow.com/questions/1006654/fastest-distance-lookup-given-latitude-longitude – av1987 Oct 02 '12 at 15:03
  • 1
    still gives me same result for metres: 5904.27 – Jeffz Oct 02 '12 at 16:27
  • 1
    my bad, I forgot to km times in the formula. multiply final result with `* 1.609344 ` and you get KMs then multiply it with 1000 you will get exact meters you want. – av1987 Oct 02 '12 at 16:57
  • the conversion does not qork.. something is still off. – Randy Oct 02 '12 at 17:05