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
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