发生无效的浮点运算。 SQL Server 2008

发布于 2024-12-26 07:14:25 字数 1266 浏览 3 评论 0原文

我对这段代码有一个奇怪的问题：如果我像下面所示运行它，我会收到错误：

发生无效的浮点运算。

但是如果我将参数@Longitude更改为-98.508730（注意只有最后一位数字发生了变化）代码工作得很好。

该代码应该列出某个 LatLng 点周围 @MilesRadius 中的属性。
@Latitude 和@Longitude 参数与表Address 中的经度和纬度字段具有相同的类型。

我在这里能做什么？谢谢。

DECLARE @Latitude decimal (10,6);
DECLARE @Longitude decimal (10,6);
DECLARE @MilesRadius int;
SET @Latitude = 29.607654
SET @Longitude = -98.508731
SET @MilesRadius  = 5

SELECT     ADR.LineOne as address, 
           ADR.City as city,           
           ADR.Latitude as latitude,
           ADR.Longitude as longitude, 
           ((3959 * acos(cos(radians(@Latitude)) * cos(radians(ADR.Latitude)) * cos(radians(ADR.Longitude) - radians(@Longitude)) + sin(radians(@Latitude)) * sin(radians(ADR.Latitude))))) as distance
FROM       Shared.Address ADR
WHERE      ADR.Latitude IS NOT NULL AND 
           ADR.Longitude IS NOT NULL AND 
           (3959 * acos(cos(radians(@Latitude)) * cos(radians(ADR.Latitude)) * cos(radians(ADR.Longitude) - radians(@Longitude)) + sin(radians(@Latitude)) * sin(radians(ADR.Latitude)))) < @MilesRadius                       
ORDER BY distance

原文

I have weird problem with this code: if I run it like shown below I get error:

An invalid floating point operation occurred.

But if I change parameter @Longitude to -98.508730 (notice only last digit changed) code works just fine.

The code is supposed to lists properties in @MilesRadius around some LatLng point.
@Latitude and @Longitude parameters are of the same type as longitude and latitude fields in table Address.

What can I do here? Thanks.

DECLARE @Latitude decimal (10,6);
DECLARE @Longitude decimal (10,6);
DECLARE @MilesRadius int;
SET @Latitude = 29.607654
SET @Longitude = -98.508731
SET @MilesRadius  = 5

SELECT     ADR.LineOne as address, 
           ADR.City as city,           
           ADR.Latitude as latitude,
           ADR.Longitude as longitude, 
           ((3959 * acos(cos(radians(@Latitude)) * cos(radians(ADR.Latitude)) * cos(radians(ADR.Longitude) - radians(@Longitude)) + sin(radians(@Latitude)) * sin(radians(ADR.Latitude))))) as distance
FROM       Shared.Address ADR
WHERE      ADR.Latitude IS NOT NULL AND 
           ADR.Longitude IS NOT NULL AND 
           (3959 * acos(cos(radians(@Latitude)) * cos(radians(ADR.Latitude)) * cos(radians(ADR.Longitude) - radians(@Longitude)) + sin(radians(@Latitude)) * sin(radians(ADR.Latitude)))) < @MilesRadius                       
ORDER BY distance

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

苍白女子 2025-01-02 07:14:25

您使用的唯一返回域错误的函数是 ACOS 当输入不在 -1 到 +1 范围内时就会发生这种情况，因此您可以随意处理这种情况（我假设中间表达式类似于1.000000000001 由于舍入错误）

SELECT     ADR.LineOne as address, 
           ADR.City as city,           
           ADR.Latitude as latitude,
           ADR.Longitude as longitude, 
           distance
FROM       Shared.Address ADR
CROSS APPLY (SELECT cos(radians(@Latitude)) * cos(radians(ADR.Latitude)) * cos(radians(ADR.Longitude) - radians(@Longitude)) + sin(radians(@Latitude)) * sin(radians(ADR.Latitude))) T(ACosInput)
CROSS APPLY (SELECT ((3959 * acos(CASE WHEN ABS(ACosInput) > 1 THEN SIGN(ACosInput)*1 ELSE ACosInput END)))) T2(distance)
WHERE      ADR.Latitude IS NOT NULL AND 
           ADR.Longitude IS NOT NULL AND 
           distance < @MilesRadius                       
ORDER BY distance

The only function you are using that returns a domain error is ACOS and that occurs when the input is not in the range -1 to +1 so you can just fiddle this case (I'm assuming the intermediate expression is something like 1.000000000001 due to rounding errors)

SELECT     ADR.LineOne as address, 
           ADR.City as city,           
           ADR.Latitude as latitude,
           ADR.Longitude as longitude, 
           distance
FROM       Shared.Address ADR
CROSS APPLY (SELECT cos(radians(@Latitude)) * cos(radians(ADR.Latitude)) * cos(radians(ADR.Longitude) - radians(@Longitude)) + sin(radians(@Latitude)) * sin(radians(ADR.Latitude))) T(ACosInput)
CROSS APPLY (SELECT ((3959 * acos(CASE WHEN ABS(ACosInput) > 1 THEN SIGN(ACosInput)*1 ELSE ACosInput END)))) T2(distance)
WHERE      ADR.Latitude IS NOT NULL AND 
           ADR.Longitude IS NOT NULL AND 
           distance < @MilesRadius                       
ORDER BY distance

回复收藏 0 原文

~没有更多了~