CODEWITHSUNDEEP
mysqlhaversine
hidden
hidden

Reputation: 41

Get distance between 2 coordinates on globe

I tried to get distance between 2 coordinates using formula from here

The coordinates is 1.5378236000, 110.3372347000 and 1.5395056000, 110.3373156000.

Somehow the result turn out very different. I believed "dist1" is in KM but not sure about "dist2".

select 6371 * acos( cos( radians(1.5378236000) ) * cos( radians( 1.5395056000 ) ) * 
cos( radians( 1.5378236000 ) - radians(110.3373156000) ) 
+ sin( radians(1.5378236000) ) * sin( radians( 1.5395056000 ) ) ) AS dis1,
GetDistance(1.5378236000, 110.3372347000, 1.5395056000, 110.3373156000) as dis2

Results dist1: 12091.536526805385
dist2: 0.11190

GetDistance function

CREATE DEFINER=`root`@`localhost` FUNCTION `GetDistance`(
 lat1  numeric (9,6),
 lon1  numeric (9,6),
 lat2  numeric (9,6),
 lon2  numeric (9,6)
) RETURNS decimal(10,5)
    READS SQL DATA
BEGIN
/* http://www.codecodex.com/wiki/Calculate_distance_between_two_points_on_a_globe#MySQL */
  DECLARE  x  decimal (20,10);
  DECLARE  pi  decimal (21,20);
  SET  pi = 3.14159265358979323846;
  SET  x = sin( lat1 * pi/180 ) * sin( lat2 * pi/180  ) + cos(
 lat1 *pi/180 ) * cos( lat2 * pi/180 ) * cos(  abs( (lon2 * pi/180) -
 (lon1 *pi/180) ) );
  SET  x = acos( x );
  RETURN  ( 1.852 * 60.0 * ((x/pi)*180) ) / 1.609344;
END

Upvotes: 0

Views: 313

Answers (3)

O. Jones
O. Jones

Reputation: 108651

Your first expression has a mistake in it. You're taking the cosine of the difference between a latitude and longitude. You should, in that term, take the difference between the starting and ending longitudes.

The cosine-law (or haversine) formula for computing distances between pairs of latitude and longitude points is this:

   DEGREES(ACOS(COS(RADIANS(lat1)) * COS(RADIANS(lat2)) *
                COS(RADIANS(long1) - RADIANS(long2)) +
                SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))))

This yields results in degrees.

Your first expression in your question takes this form. As you can see you have the correct formula but you are plugging in the parameters incorrectly.

 6371 * acos( cos( radians(lat1)) * cos( radians( long1 )) * /*should be lat1, lat 2*/
              cos( radians( lat1) - radians(long1 ))         /*should be long1,long2*/
              sin( radians(lat1) ) * sin( radians(long2 )))  /*should be lat1, lat2 */

The first of your points appears to be in Kuching, Malaysia, just south of the junction between Green and Ahmad Zaidi streets. The second point is a block north of there. (According to your second result, it's about 112m north). Notice that the distance formula I wrote works in degrees of arc. You give it lat/long points in degrees, and it returns a distance in degrees. In order to convert degrees to km (a more useful measurement), you need to know how many km per degree.

Notice that your version of the formula contains the magic number 6371. This converts the radians that result from the ACOS() function to degrees, and then to km, using a constant of 111.195 km per degree. That's an acceptable value; the earth bulges a little at the equator.

Also, your stored function has an unnecessary ABS in that same term. It's also grossly inefficient due to the decimal arithmetic. MySQL uses DOUBLE ( ieee 64 bit floating ) arithmetic to do all the computation, but the way it's coded requires lots of wasteful and potentially precision-losing conversions back and forth to decimal.

If you're using commercial grade GPS coordinates, 32-bit FLOAT arithmetic is plenty of precision.

Here is an extensive explanation of this material. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

Upvotes: 0

chmouel kalifa
chmouel kalifa

Reputation: 129

here is the accurate method

    public static double elongation(double longitude1, double latitude1, 
            double longitude2, double latitude2)
    {
        return Math.Acos(1 - 2 * (hav(latitude1 - latitude2)
            + Math.Cos(RAD * latitude1) * math.Cos(RAD * latitude2)
            * hav(longitude1 - longitude2))) / RAD;
    }

when the fuction "hav" is

    static public double hav(double x)
    {
        return 0.5 - 0.5 * Math.Cos(RAD * x);
    }

Upvotes: 1

Ikechi Michael
Ikechi Michael

Reputation: 489

The first function gets the long distance (the distance if you go the long way around the globe)

The second is the distance if you take a short cut.

Look at the two points, they are very close to another. It's like going around the world just to get across the road. :D

The second distance is still in KMs, it's just pretty short. Earth's circumference is just over 12,000KMs.

Upvotes: 0

Related Questions