找到两个纬度和经度之间的距离在SQL查询

Question

我一个数据库表名为“承包商”和表是具有5场

现在我的另一个纬度和长期对其中我已经选择这些记录，他们是：

纬度：19.2494730，经度：72.8612790

我的SQL查询：

SELECT *, round((3959 * acos(cos(radians(19.2494730)) * cos(radians(tbl.latitude)) * cos(radians(tbl.longitude) - radians(72.8612790)) + sin(radians(19.2494730)) * sin(radians(tbl.latitude)))),2) AS distance 
FROM `contractors` AS tbl 

，并给出以下结果：

但距离（它是在万里，我认为）是不正确的，因为当我运行下面的JavaScript代码，它给了我一些准确的结果。

function distance(lat1, lon1, lat2, lon2, unit) { 
    var radlat1 = Math.PI * lat1/180 
    var radlat2 = Math.PI * lat2/180 
    var radlon1 = Math.PI * lon1/180 
    var radlon2 = Math.PI * lon2/180 
    var theta = lon1-lon2 
    var radtheta = Math.PI * theta/180 
    var dist = Math.sin(radlat1) * Math.sin(radlat2) + Math.cos(radlat1) * Math.cos(radlat2) * Math.cos(radtheta); 
    dist = Math.acos(dist) 
    dist = dist * 180/Math.PI 
    dist = dist * 60 * 1.1515 
    if (unit=="K") { dist = dist * 1.609344 } 
    if (unit=="N") { dist = dist * 0.8684 } 
    alert(dist) 
}    
distance(19.2494730, 72.8612790, 19.281085, 72.855994, 'K'); 

我也有一个PHP代码片段，给了我更准确的结果

function distance($lat1, $lon1, $lat2, $lon2, $unit) { 

    $theta = $lon1 - $lon2; 
    $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta)); 
    $dist = acos($dist); 
    $dist = rad2deg($dist); 
    $miles = $dist * 60 * 1.1515; 
    $unit = strtoupper($unit); 

    if ($unit == "K") { 
    return ($miles * 1.609344); 
    } else if ($unit == "N") { 
     return ($miles * 0.8684); 
    } else { 
     return $miles; 
     } 
} 

echo distance(19.2494730, 72.8612790,19.281085, 72.855994, "M") . " Miles<br>"; 

任何人可以帮助作出上述SQL查询正确的，这样我可以给我的所有区域之间的准确的直线距离和特定的经纬度。

Answer 1

Haversine公式？

CREATE function [dbo].[GetDistance](
    @lat1 decimal(18,10), 
    @lon1 decimal(18,10), 
    @lat2 decimal(18,10), 
    @lon2 decimal(18,10) 
    ) 
    returns decimal(18,4) 
    as 
    begin 
     declare @radius decimal(18,10) 

     declare @a decimal(18,10) 
     declare @distance decimal(18,4) 

     -- Sets average radius of Earth in Kilometers 
     set @radius = 6371.0E 

     -- Convert degrees to radians 
     set @lon1 = radians(@lon1) 
     set @lon2 = radians(@lon2) 
     set @lat1 = radians(@lat1) 
     set @lat2 = radians(@lat2) 

     set @a = sqrt(square(sin((@[email protected])/2.0E)) + 
      (cos(@lat1) * cos(@lat2) * square(sin((@[email protected])/2.0E)))) 

     set @distance = 
      @radius * (2.0E *asin(case when 1.0E < @a then 1.0E else @a end)) 

     return @distance 
    end