CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2008-r2
Chris Townsend
Chris Townsend

Reputation: 3162

Receiving the following error with my query when calculating distance

I'm receiving the following error "An invalid floating point operation occurred." when I run this query:

SELECT PolID, LocID, Address, City, StateCode, OrigGeoLat, OrigGeoLong, NewGeoLat, NewGeoLong, 
acos(sin(radians(OrigGeoLat)) * sin(radians(NewGeoLat)) +
        cos(radians(OrigGeoLat)) * cos(radians(NewGeoLat)) *
        cos(radians(OrigGeoLong - NewGeoLong))) * 6372.8  as Distance
FROM zzGeoDataTMP

All of the *geoLat and *geoLong data is defined as numeric(18,10). When I run the query I start getting data back and then I get errors on specific rows of data. For example the following row throws the above mentioned exception only once the ACOS function is called:

OrigGeoLat|OrigGeoLong|NewGeoLat|NewGeoLong
---------------------------------------------
32.9364620|-80.0411000|32.9364620|-80.0411000

Thanks so much in advance for any insight you may be able to help with!

Upvotes: 3

Views: 622

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

You're calculation is accumulating some small errors, which mean that the calculated value (passed to ACOS) is slightly higher than 1. Try wrapping it with a ROUND call:

acos(ROUND(sin(radians(OrigGeoLat)) * sin(radians(NewGeoLat)) +
    cos(radians(OrigGeoLat)) * cos(radians(NewGeoLat)) *
    cos(radians(OrigGeoLong - NewGeoLong)),15)
    )

Where we're only keeping 15 decimal places of accuracy.

Upvotes: 9

Related Questions