Mysql计算两点间的距离算法

1,查看Mysql是否开启创建函数的开关

show variables like '%func%'; 

如果是OFF,那么

set global log_bin_trust_function_creators=1;

2,创建函数

delimiter $$
create function getDistance(lon1 float,lat1 float,lon2 float,lat2 float) returns double
begin
    declare d double;
    declare radius int;
    set radius = 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 ;

 

转载于:https://my.oschina.net/pengdake/blog/730392