CODEWITHSUNDEEP
mysqldistancelatitude-longitude
CarlosCarucce
CarlosCarucce

Reputation: 3569

Calulate distance using 2 global points using SQL

I am trying to get the distance in meters from 2 global points(lat/long)

I've found some references to haversine formula here and here. Based on the given formula, I made a sql script (mysql)

DELIMITER $$
CREATE FUNCTION get_geo_distance (lat1 DECIMAL, lon1 DECIMAL, lat2 DECIMAL, lon2 DECIMAL)
RETURNS DECIMAL
BEGIN

    DECLARE RR LONG;
    DECLARE o1 DECIMAL;
    DECLARE o2 DECIMAL;
    DECLARE Ao DECIMAL;
    DECLARE AA DECIMAL;
    DECLARE a DECIMAL;
    DECLARE c DECIMAL;

    SET RR = 6371000;
    SET o1 = RADIANS(lat1);
    SET o2 = RADIANS(lat2);
    SET Ao = RADIANS(lat2 - lat1);
    SET AA = RADIANS(lon2 - lon1);

    SET a = SIN(Ao/2) * SIN(Ao/2) +
            COS(o1) * COS(o2) * 
            SIN(AA/2) * SIN(AA/2);
    SET c = 2 * ATAN2(SQRT(a), SQRT(1-a));
    RETURN RR * c;

END $$
DELIMITER ;

But, when I call get_geo_distance, it will return 0 (zero) Could someone please help me out?

Examples of use:

SELECT get_geo_distance(1,1,0,0);
SELECT get_geo_distance(10, 10, 0, 0);
SELECT get_geo_distance(-22.7514855, -47.3987318, -22.7599327, -47.3914984);

Upvotes: 0

Views: 79

Answers (2)

Sasha Pachev
Sasha Pachev

Reputation: 5336

Replacing DECIMAL with REAL solves the problem for me:

drop function if exists get_geo_distance;
DELIMITER $$
CREATE FUNCTION get_geo_distance (lat1 REAL, lon1 REAL, lat2 REAL, lon2 REAL)
RETURNS real deterministic
BEGIN

    DECLARE RR REAL;
    DECLARE o1 REAL;
    DECLARE o2 REAL;
    DECLARE Ao REAL;
    DECLARE AA REAL;
    DECLARE a REAL;
    DECLARE c REAL;

    SET RR = 6371000;
    SET o1 = RADIANS(lat1);
    SET o2 = RADIANS(lat2);
    SET Ao = RADIANS(lat2 - lat1);
    SET AA = RADIANS(lon2 - lon1);

    SET a = SIN(Ao/2) * SIN(Ao/2) +
            COS(o1) * COS(o2) *
            SIN(AA/2) * SIN(AA/2);

    SET c = 2 * ATAN2(SQRT(a), SQRT(1-a));
    RETURN RR * c;

END $$
DELIMITER ;

select get_geo_distance(-22.7514855, -47.3987318, -22.7599327, -47.3914984);

returns

1196.826386258915

I have not delved in depth, but I am going to guess somethings gets lost in the floating point operations when the type is DECIMAL rather than REAL probably due to the default precision of DECIMAL.

Upvotes: 1

O. Jones
O. Jones

Reputation: 108841

Don't use decimal data types for this. Use FLOAT instead. Decimal roundoff is notorious for wrecking these computations.

This stored procedure does the job nicely.

DELIMITER $$
DROP FUNCTION IF EXISTS haversine$$

CREATE FUNCTION haversine(
        lat1 FLOAT, lon1 FLOAT,
        lat2 FLOAT, lon2 FLOAT
     ) RETURNS FLOAT
    NO SQL DETERMINISTIC
    COMMENT 'Returns the distance in degrees on the Earth
             between two known points of latitude and longitude'
BEGIN
    RETURN DEGREES(ACOS(
              COS(RADIANS(lat1)) *
              COS(RADIANS(lat2)) *
              COS(RADIANS(lon2) - RADIANS(lon1)) +
              SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))
            ));
END$$

DELIMITER ;

This function returns degrees. You need to multiply it by a constant to get more conventional distance measurements. In temperate latitudes the value 111.045 is a good value to use to get km, and 69.0 will get you statute miles (useful if you happen to live in a former British colony like the USA).

There's a more extensive writeup of this problem here. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

Upvotes: 2

Related Questions