SQL Haversine公式

Question

DECLARE @Lat1 AS DECIMAL(9,5)
DECLARE @Lon1 AS DECIMAL(9,5)
DECLARE @Radius AS DECIMAL(9,5)

SET @Lat1 = '32.74'   
SET @Lon1 = '-117.20'
SET @Radius = 3960

declare @CC decimal(9,5),@CS decimal(9,5),@rlat decimal(9,5),@slat decimal(9,5)

Select 
    @CC = cos(RADIANS(@Lat1))*cos(RADIANS(@Lon1))
    , @CS = cos(RADIANS(@Lat1))*sin(RADIANS(@Lon1))
    , @slat = Sin(radians(@lat1))

SELECT 
    call_id, dtservertime, sdisconnectreason,
    icompleted, Geolat, Geolong, sZipCode
FROM 
    (SELECT
        call_id, dtServerTime, sDisconnectReason,
        iCompleted, Geolat, Geolong, sZipCode
        ,convert(decimal(9,5),radians(geoLat)) as Rlat
        ,convert(decimal(9,5),radians(geoLong)) as Rlong
    FROM
        dbo.vMarketing) as x
WHERE 
    x.dtservertime >= '1/1/2016' and 
    (acos(
        @cc*(cos(rlat)*cos(RLONG) )
   +   @cs*(cos(RLAT)*sin(RLONG) )
   +   @SLat*sin(RLAT)
  ) * @Radius
) < 8

我一直在这个查询中得到一个错误，说发生了一个无效的浮点操作。

怎么了？

Answer 1

您需要向acos函数传递一个有效值，即传递的值必须介于-1和+1之间。因此，以下查询将导致SQL Server中的floating point error。

将导致浮点错误的查询

select acos(-1.1);
select acos(2.5);

不会导致浮点错误的查询

select acos(-1.0);
select acos(0.7);

从数学我们知道一个角度的余弦总是在-1和+1之间。

在您的情况下，您传递给acos函数的以下表达式的计算结果超出了-1到+1的范围。

@cc * ( COS(rlat) * COS(RLONG) ) + @cs * ( COS(RLAT) * SIN(RLONG) ) + @SLat * SIN(RLAT)

您可以在下面的屏幕截图中看到此无效函数调用在SQL Server Management Studio中的行为。

SSMS中超出范围的acos呼叫的屏幕截图

​

​