如何用MySQL的Haversine公式测量距离？

我从谷歌地图反向地理编码API获得纬度和经度,然后我需要这样的东西：

MysqL_query("SELECT users.*,".MysqL_distance_column($lat,$lng)." FROM users ORDER BY DISTANCE";

function MysqL_distance_column($lat=40,$lng=-73) {

   $defaultLatitudeColumn = 'user_lat'; 
   $defaultLongitudeColumn='user_lng';
   $defaultColumnName='user_distance';
    return  "(( 
(3956 * 2 * ASIN(SQRT( POWER(SIN(({$lat} - abs({$defaultLatitudeColumn})) 
* pi()/180 / 2),2) + COS({$lat} * pi()/180 ) 
* COS(abs({$defaultLatitudeColumn}) * pi()/180) 
* POWER(SIN(({$lng} - {$defaultLongitudeColumn}) * pi()/180 / 2),2) ))
 )) ) as {$defaultColumnName} ";

}

UPDATE
我不能让这个工作

delimiter //
CREATE FUNCTION `GeoDistMiles`( lat1 FLOAT (10,6),lon1 FLOAT (10,lat2 FLOAT (10,lon2 FLOAT (10,6) ) 
RETURNS FLOAT 
DETERMINISTIC 
NO sql
BEGIN
  DECLARE pi,q1,q2,q3 FLOAT (10,6);
  DECLARE rads FLOAT (10,6) DEFAULT 0;
  SET pi = PI();
  SET lat1 = lat1 * pi / 180;
  SET lon1 = lon1 * pi / 180;
  SET lat2 = lat2 * pi / 180;
  SET lon2 = lon2 * pi / 180;
  SET q1 = COS(lon1-lon2);
  SET q2 = COS(lat1-lat2);
  SET q3 = COS(lat1+lat2);
  SET rads = ACOS( 0.5*((1.0+q1)*q2 - (1.0-q1)*q3) );
  RETURN 3963.346 * rads;
END

最佳答案

这是我使用的公式.请记住,地球不是一个完美的球体,因此结果永远不会是完美的.

CREATE DEFINER=`root`@`localhost` FUNCTION `GeoDistMiles`( lat1 FLOAT,lon1 FLOAT,lat2 FLOAT,lon2 FLOAT ) RETURNS float
BEGIN
  DECLARE pi,q3 FLOAT;
  DECLARE rads FLOAT DEFAULT 0;
  SET pi = PI();
  SET lat1 = lat1 * pi / 180;
  SET lon1 = lon1 * pi / 180;
  SET lat2 = lat2 * pi / 180;
  SET lon2 = lon2 * pi / 180;
  SET q1 = COS(lon1-lon2);
  SET q2 = COS(lat1-lat2);
  SET q3 = COS(lat1+lat2);
  SET rads = ACOS( 0.5*((1.0+q1)*q2 - (1.0-q1)*q3) );
  RETURN 3963.346 * rads;
END

如何用MySQL的Haversine公式测量距离？

猜你在找的MySQL相关文章