Thursday, 29 November 2007

Latitude / longitude distance in mysql

This is the best function I've found for calculating the distance from two different locations using their latitude / longitude coordinates expressed in degrees. The number of km is returned.

Note that the IF-ENDIF part is not optional, since round-off errors could make tmp bigger than 1 or smaller than -1.

For those values ACOS is not defined and will return a NULL value.

DELIMITER //
DROP FUNCTION IF EXISTS km_from_deg //
CREATE FUNCTION km_from_deg (latA DOUBLE, longA DOUBLE, latB DOUBLE, longB DOUBLE)
RETURNS DOUBLE
BEGIN
DECLARE tmp DOUBLE;
SET tmp =
COS(RADIANS(longA - longB)) * COS(RADIANS(latB))*COS(RADIANS(latA)) +
SIN(RADIANS(latB))*SIN(RADIANS(latA));
IF tmp > 1 THEN set tmp = 1;
ELSEIF tmp < -1 THEN set tmp = -1;
END IF;
return 6372 * ACOS(tmp);
END;
//
DELIMITER ;

1 comment:

marta said...

really useful, great!
thankyou