среда, 3 сентября 2008 г.

GEO Distance function in MySQL

Returns distance in meters between two geo points defined by their latitude and longitude.


DELIMITER $$;
DROP FUNCTION IF EXISTS Dist$$

CREATE FUNCTION Dist (lat1 float, lon1 float, lat2 float, lon2 float)
RETURNS float
BEGIN
RETURN 6370.695763 * acos(sin(lat1/57.2958) * sin(lat2/57.2958) + cos(lat1/57.2958) *
cos(lat2/57.2958) * cos(lon2/57.2958 -lon1/57.2958))

END $$
DELIMITER ;$$


in miles:
the formula is
3958.5668 * acos(sin(lat1/57.2958) * sin(lat2/57.2958) + cos(lat1/57.2958) *
cos(lat2/57.2958) * cos(lon2/57.2958 -lon1/57.2958))



References:


- Mathematical Functions in MySQL
http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html
http://dev.mysql.com/doc/refman/4.1/en/mathematical-functions.html

- CREATE FUNCTION in MySQL - syntax
http://dev.mysql.com/doc/refman/5.0/en/create-function-udf.html

Комментариев нет: