3

I have a problem with an SQL query and i've narrowed it down to the following code

SELECT ACOS( (SIN(PI()* 52.9519918465976/180)*SIN(PI()* 52.9519918465976/180))+(COS(PI()*   52.9519918465976/180)*cos(PI()* 52.9519918465976/180)*COS(PI()* -1.14304013581239/180-PI()* -1.14304013581239/180))) AS test

I get the following error message 'An invalid floating point operation occurred'

Can abody see what the issue is?

Thanks in advance

James Privett
  • 1,079
  • 3
  • 15
  • 23

2 Answers2

8

Your result in ACOS() is bigger than 1 which can't be.

That is because of floating point inaccuracy. it could be 1.00000001 for instance. Putting it just a little below 1 works like this:

SELECT ACOS( 
             (SIN(PI()* 52.9519918465976/180.0)* SIN(PI()* 52.9519918465976/180.0))
             + (COS(PI()* 52.9519918465976/180)*cos(PI()* 52.9519918465976/180.0)*COS(PI()* -1.14304013581239/180.0-PI()* -1.14304013581239/180.0)) 
             - 0.0000001
           )
juergen d
  • 201,996
  • 37
  • 293
  • 362
4

I'm using ACOS to calculate distances between geo points. The subtraction of '- 0.0000001' is enough to skew my results a bit. So I instead, but used a MIN function (as posted here by Craig) like this:

SELECT ACOS( 
        (SELECT MIN(x) FROM (VALUES (
             (SIN(PI()* 52.9519918465976/180.0)* SIN(PI()* 52.9519918465976/180.0))
             + (COS(PI()* 52.9519918465976/180)*cos(PI()* 52.9519918465976/180.0)*COS(PI()* -1.14304013581239/180.0-PI()* -1.14304013581239/180.0)) 
         ),(1)) AS value(x))
   )

This way ACOS of floats between 0 and 1 remain accurately calculated.

Grits
  • 121
  • 1
  • 5