CODEWITHSUNDEEP
sql-serverfloating-pointtrigonometry
Aaron
Aaron

Reputation: 1832

SQL Server trig functions throwing Invalid Floating Point Operation Occured

I'm calling this function with legitimate Lat/Long values to try to calculate the distance between two points. It works perfectly well for 99.99% of all my points, but on this particular point, it's crashing:

SELECT ACOS(SIN(-34.8821289 * PI() / 180) 
          * SIN(-34.882129 * PI() / 180) 
          + (COS(-34.8821289 * PI() / 180) 
           * COS(-34.882129 * PI() / 180) 
           * COS((150.7406616 - 150.740662) * PI() / 180))) / (PI() / 180) * 111.325

How can I toughen up the function to check for and handle this?

I'd really like to keep using the trig functions and not the native SQL Server geography type for performance reasons.

Upvotes: 1

Views: 106

Answers (1)

Aaron
Aaron

Reputation: 1832

Damn, looks like having the mix of some 7 digit decimal place values with the other 6 digit decimal place values, was causing the COS function to be rounded to equal 1, which was then blowing up the overall ACOS function because the 1 is out of range?

By reducing all my input values to 6 digits seems to have fully fixed the issue.

Upvotes: 0

Related Questions