Mysq创建l计算两GPS坐标的距离的函数

PHP计算两个GPS点之间的距离
Mysql计算两GPS坐标的距离

javascript计算两个GPS点之间的距离

2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

drop function getDistance ; DELIMITER $ $    CREATE D EFINER = ` root ` @ ` localhost ` FUNCTION ` getDistance ` (      lon1 float ( 10 , 7 )      , lat1 float ( 10 , 7 )      , lon2 float ( 10 , 7 )      , lat2 float ( 10 , 7 ) ) RETURNS double begin      declare d double ;      declare radius int ;      set radius = 6378140 ; #假设地球为正球形，直径为6378140米      set d = ( 2 * ATAN2 ( SQRT ( SIN ( ( lat1 - lat2 ) * PI ( ) / 180 / 2 )             * SIN ( ( lat1 - lat2 ) * PI ( ) / 180 / 2 ) +             COS ( lat2* PI ( ) / 180 ) * COS ( lat1* PI ( ) / 180 )             * SIN ( ( lon1 - lon2 ) * PI ( ) / 180 / 2 )             * SIN ( ( lon1 - lon2 ) * PI ( ) / 180 / 2 ) ) ,             SQRT ( 1 - SIN ( ( lat1 - lat2 ) * PI ( ) / 180 / 2 )             * SIN ( ( lat1 - lat2 ) * PI ( ) / 180 / 2 )             + COS ( lat2* PI ( ) / 180 ) * COS ( lat1* PI ( ) / 180 )             * SIN ( ( lon1 - lon2 ) * PI ( ) / 180 / 2 )             * SIN ( ( lon1 - lon2 ) * PI ( ) / 180 / 2 ) ) ) ) * radius ;      return d ; end $ $ DELIMITER ; select getDistance ( 116.3899 , 39.91578 , 116.3904 , 39.91576 ) ; #调用函数

Mysql计算两GPS坐标的距离SQL语句：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

#lat为纬度, lng为经度, 一定不要弄错 declare @ lon1 float ; declare @ lat1 float ; declare @ lon2 float ; declare @ lat2 float ; set @ lon1 = 116.3899 ; set @ lat1 = 39.91578 ; set @ lon2 = 116.3904 ; set @ lat2 = 39.91576 ;    select ( 2 * ATAN2 ( SQRT ( SIN ( ( @ lat1 - @ lat2 ) * PI ( ) / 180 / 2 )             * SIN ( ( @ lat1 - @ lat2 ) * PI ( ) / 180 / 2 ) +             COS ( @ lat2* PI ( ) / 180 ) * COS ( @ lat1* PI ( ) / 180 )             * SIN ( ( @ lon1 - @ lon2 ) * PI ( ) / 180 / 2 )             * SIN ( ( @ lon1 - @ lon2 ) * PI ( ) / 180 / 2 ) ) ,             SQRT ( 1 - SIN ( ( @ lat1 - @ lat2 ) * PI ( ) / 180 / 2 )             * SIN ( ( @ lat1 - @ lat2 ) * PI ( ) / 180 / 2 )             + COS ( @ lat2* PI ( ) / 180 ) * COS ( @ lat1* PI ( ) / 180 )             * SIN ( ( @ lon1 - @ lon2 ) * PI ( ) / 180 / 2 )             * SIN ( ( @ lon1 - @ lon2 ) * PI ( ) / 180 / 2 ) ) ) ) * 6378140 ;

错误及解决方法：

在创建函数前报如下错误：

1	This function has none of DETERMINISTIC , NO SQL , or READS SQL DATA in its declaration and binary logging is enabled ( you * might* want to use the less safe log_bin_trust_function_creators variable )

解决方法：

在创建函数前执行下面SQL语句：

1	set global log_bin_trust_function_creators = 1 ;

或者修改my.cnf文件并重启mysqld服务：

1	log_bin_trust_function_creators = 1