Sunday, November 11, 2012

Inverse trigonometric functions in SQL

Databases are primarily used to store and retrieve data by applications and occasionally by users who directly query the database. When actions, outside the domain of storing and retrieving data are needed a lot of people, who do lack SQL development knowledge will go to the application layer of a stack to build in calculation and logic functions. In some cases this makes sense however in some other cased it would make sense to build some of the logic and some of the calculations into the database side of the application stack.

To be able to make full use of the application side of the stack it is needed to understand what is available to a developer from within the Oracle database by default. For example it is not known to all Oracle SQL (PL/SQL) developers that all mathematical inverse trigonometric functions are directly available for use within the SQL language.

In mathematics, the inverse trigonometric functions (occasionally called cyclometric functions) are the inverse functions of the trigonometric functions with suitably restricted domains.

The notations sin−1, cos−1, tan−1, etc. are often used for arcsin, arccos, arctan, etc., but this convention logically conflicts with the common semantics for expressions like sin2(x), which refer to numeric power rather than function composition, and therefore may result in confusion between multiplicative inverse and compositional inverse.

In computer programming languages the functions arcsin, arccos, arctan, are usually called asin, acos, atan. Many programming languages also provide the two-argument atan2 function, which computes the arctangent of y / x given y and x, but with a range of (−π, π].

Within the Oracle database we have the functions ACOS, ASIN, ATAN and ATAN2 available as in many other programming languages. All are very straight forward in use. Below you can find the examples:

ACOS
As an example for the Oracle SQL ACOS function you can execute the below;
  
  SELECT 
        acos(.3) 
  FROM 
      dual;

This will give you the following result;
  1.2661036727794991112593187304122222822

Which is quite a precise number and might not be needed in all cases so you can apply a round to if for example from 3 which can be done by executing it in the following manner;
  
  SELECT 
        round(acos(.3),4) 
  FROM 
      dual;

This will provide you with the following outcome (rounding can be done to any number you like simply by using the ROUND function in combination with the ACOS function;
  1.2661

ASIN
As  an example to use the Oracle SQL ASIN function you can execute the below;
  
  SELECT 
        asin(.3) 
  FROM 
      dual;

This will give you the following result;
  0.3046926540153975079720029612275291599

ATAN
As an example to use the Oracle SQL ATAN function you can execute the below;
   
  SELECT 
        atan(.3) 
  FROM 
      dual;

This will give you the following result;
  0.2914567944778670919956046214328911935013

ATAN2
As an example to use the Oracle SQL ATAN2 function you can execute the below;
  
  SELECT 
        atan2(.4,.5) 
  FROM 
      dual;

This will give you the following result;
  0.674740942223552663056520973609813615077

All above mentioned functions are by default precise to 30 decimal digits (unless you use the ROUND function as showed in the ACOS example).

No comments: