如何用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,6), lat2 FLOAT (10,6), 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

已邀请:

5 个回复

担每棱绰

这是我使用的公式。请记住，地球不是一个完美的球体，因此结果永远不会是完美的。 CREATE DEFINER =`root` @`localhost`功能`GeoDistMiles`（lat1 FLOAT，lon1 FLOAT，lat2 FLOAT，lon2 FLOAT）RETURNS浮动开始 DECLARE pi，q1，q2，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））; 返回3963.346 * rads; 结束

死狸蔽垂藐

我假设您正在尝试使用http://en.wikipedia.org/wiki/Haversine_formula。这是经过轻微测试的，但我认为您的公式应该是：

(ROUND((3956 * 2 * ASIN(SQRT(POWER(SIN(({$lat} - {$defaultLatitudeColumn}) * pi() / 180 / 2), 2) + COS({$lat} * pi()/180 ) * COS({$defaultLatitudeColumn} * pi()/180) *POWER(SIN(({$lng} - {$defaultLongitudeColumn}) * pi()/180 / 2), 2) )) )*{$magicNumber}) )/{$magicNumber}

（我删除了abs调用。）

舜辉

嗨，我有一个简单的程序，你可以用它来工作。程序非常简单，并计算两个城市之间的距离。您可以按自己的方式修改它。

drop procedure if exists select_lattitude_longitude;


delimiter //

create procedure select_lattitude_longitude(In CityName1 varchar(20) , In CityName2 varchar(20))

begin

declare origin_lat float(10,2);

declare origin_long float(10,2);

declare dest_lat float(10,2);

declare dest_long float(10,2);

if CityName1  Not In (select Name from City_lat_lon) OR CityName2  Not In (select Name from City_lat_lon) then

select 'The Name Not Exist or Not Valid Please Check the Names given by you' as Message;


else

select lattitude into  origin_lat from City_lat_lon where Name=CityName1;

select longitude into  origin_long  from City_lat_lon where Name=CityName1;

select lattitude into  dest_lat from City_lat_lon where Name=CityName2;

select longitude into  dest_long  from City_lat_lon where Name=CityName2;


select  origin_lat as CityName1_lattitude,
origin_long as CityName1_longitude,
dest_lat as CityName2_lattitude,
dest_long as CityName2_longitude;


SELECT 3956 * 2 * ASIN(SQRT( POWER(SIN((origin_lat - dest_lat) * pi()/180 / 2), 2) + COS(origin_lat * pi()/180) * COS(dest_lat * pi()/180) * POWER(SIN((origin_long-dest_long) * pi()/180 / 2), 2) )) * 1.609344 as Distance_In_Kms ;


end if;

end ;

//

delimiter ;

纤洞需匪

SELECT ACOS(COS(RADIANS(lat)) *
COS(RADIANS(lon)) * COS(RADIANS(34.7405350)) * COS(RADIANS(-92.3245120)) +
COS(RADIANS(lat)) * SIN(RADIANS(lon)) * COS(RADIANS(34.7405350)) * 
SIN(RADIANS(-92.3245120)) + SIN(RADIANS(lat)) * SIN(RADIANS(34.7405350))) * 
3963.1 AS Distance
FROM Stores 
WHERE 1
HAVING Distance <= 50

这是我在PHP中使用它的方式：

// Find rows in Stores within 50 miles of $lat,$lon
$lat = '34.7405350';
$lon = '-92.3245120';

$sql = "SELECT Stores.*, ACOS(COS(RADIANS(lat)) *
COS(RADIANS(lon)) * COS(RADIANS($lat)) * COS(RADIANS($lon)) +
COS(RADIANS(lat)) * SIN(RADIANS(lon)) * COS(RADIANS($lat)) * 
SIN(RADIANS($lon)) + SIN(RADIANS(lat)) * SIN(RADIANS($lat))) * 
3963.1 AS Distance
FROM Stores 
WHERE 1
HAVING Distance <= 50";

讹巳漓把备

3956 * 2 * ASIN(SQRT( POWER(SIN(($latitude -( cp.latitude)) * pi()/180 / 2),2) + COS($latitude * pi()/180 ) * COS( abs( cp.latitude) *  pi()/180) * POWER(SIN(($longitude - cp.longitude) *  pi()/180 / 2), 2) )) as distance

返回里程或KM？

要回复问题请先登录或注册

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

5 个回复

发起人

haversine

问题状态

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

与内容相关的链接

5 个回复

发起人

haversine

问题状态