How You Can Use Trigonometric Functions in PostgreSQL to Obtain Further Insights

By Digoal

A few days ago, I wrote an article about the implementation of the SDT algorithm in PostgreSQL in which the ST_Azimuth function in PostGIS was used to compute the included angle. In reality, in PostgreSQL, you can also use trigonometric functions and three sides to compute the included angle. Consider the following figure:

The method used in this article to compute the included angle is as follows:

      SELECT 180-ST_Azimuth(  
                              ST_MakePoint(o_x, o_val+i_radius),    -- the point on the upper door  
                              ST_MakePoint(v_x, v_val)              -- next point   
                           )/(2*pi())*360 as degAz,                 -- the upper included angle  
                 ST_Azimuth(  
                              ST_MakePoint(o_x, o_val-i_radius),    -- the point on the lower door  
                              ST_MakePoint(v_x, v_val)              -- next point  
                           )/(2*pi())*360 As degAzrev               -- the lower included angle  
      INTO v_angle1, v_angle2;

The Cosine theorem

I'm sure everyone remembers the Cosine theorem from Math class, which for us now can be summarized as: cosA=(b²+c²-a²)/(2bc). Well, this is also the basis of the discussion of this blog, too. For reference, consider the following figure:

With this formula, the fixed points are A, B, and C, and the sides they are facing are a, b, and c, which are the unknown values. You can use the fixed point values to determine the unknown values.

Trigonometric Functions in PostgreSQL

Here are some other trigonometric functions supported by PostgreSQL:

Example:

Consider this example. Three points are given: A(3,2), B(1,2.5), and C(1,1). Now you need to evaluate the included angles B and C. For reference, consider the figure below:

Now, use the cosine formula.

cosB=(a²+c²-b²)/(2ac)     
    
cosC=(b²+a²-c²)/(2ba)

First, calculate the length of the three sides.

postgres=# select point_distance(point(3,2), point(1,2.5)) as c , point_distance(point(3,2), point(1,1)) as b , point_distance(point(1,1), point(1,2.5)) as a;  
        c         |        b         |  a    
------------------+------------------+-----  
 2.06155281280883 | 2.23606797749979 | 1.5  
(1 row)

Then, perform the following operations:

cosB=(a²+c²-b²)/(2ac)        
=(1.5^2 + 2.06155281280883^2 - 2.23606797749979^2) / (2*1.5*2.06155281280883)    
=0.24253562503633260164    
    
cosC=(b²+a²-c²)/(2ba)       
=(1.5^2 + 2.23606797749979^2 - 2.06155281280883^2) / (2*2.23606797749979*1.5)    
=0.44721359549995825124

Evaluate the degree of included angle 1.

postgres=# select acosd(0.24253562503633260164);  
      acosd         
------------------  
 75.9637565320735  
(1 row)

Evaluate the degree of included angle 2.

postgres=# select acosd(0.44721359549995825124);  
      acosd        
-----------------  
 63.434948822922  
(1 row)

The result is consistent with PostGIS results.

test=>  SELECT 180-ST_Azimuth(  
                              ST_MakePoint(1,2.5),    -- the point on the upper door  
                              ST_MakePoint(3,2)              -- next point  
                           )/(2*pi())*360 as degAz,          -- the upper included angle  
                 ST_Azimuth(  
                              ST_MakePoint(1,1),      -- the point on the lower door   
                              ST_MakePoint(3,2)              -- next point  
                           )/(2*pi())*360 As degAzrev ;  
      degaz       |    degazrev       
------------------+-----------------  
 75.9637565320735 | 63.434948822922  
(1 row)

The Source Code

Trigonometric functions belongs to functions for floating point operations.

src/backend/utils/adt/float.c.

Consider the following:

/*  
 *              acosd_q1                - returns the inverse cosine of x in degrees, for x in  
 *                                                the range [0, 1].  The result is an angle in the  
 *                                                first quadrant --- [0, 90] degrees.  
 *  
 *                                                For the 3 special case inputs (0, 0.5 and 1), this  
 *                                                function will return exact values (0, 60 and 90  
 *                                                degrees respectively).  
 */  
static double  
acosd_q1(double x)  
{  
        /*  
         * Stitch together inverse sine and cosine functions for the ranges [0,  
         * 0.5] and (0.5, 1].  Each expression below is guaranteed to return  
         * exactly 60 for x=0.5, so the result is a continuous monotonic function  
         * over the full range.  
         */  
        if (x <= 0.5)  
        {  
                volatile float8 asin_x = asin(x);  
  
                return 90.0 - (asin_x / asin_0_5) * 30.0;  
        }  
        else  
        {  
                volatile float8 acos_x = acos(x);  
  
                return (acos_x / acos_0_5) * 60.0;  
        }  
}  
  
  
/*  
 *              dacosd                  - returns the arccos of arg1 (degrees)  
 */  
Datum  
dacosd(PG_FUNCTION_ARGS)  
{  
        float8          arg1 = PG_GETARG_FLOAT8(0);  
        float8          result;  
  
        /* Per the POSIX spec, return NaN if the input is NaN */  
        if (isnan(arg1))  
                PG_RETURN_FLOAT8(get_float8_nan());  
  
        INIT_DEGREE_CONSTANTS();  
  
        /*  
         * The principal branch of the inverse cosine function maps values in the  
         * range [-1, 1] to values in the range [0, 180], so we should reject any  
         * inputs outside that range and the result will always be finite.  
         */  
        if (arg1 < -1.0 || arg1 > 1.0)  
                ereport(ERROR,  
                                (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),  
                                 errmsg("input is out of range")));  
  
        if (arg1 >= 0.0)  
                result = acosd_q1(arg1);  
        else  
                result = 90.0 + asind_q1(-arg1);  
  
        CHECKFLOATVAL(result, false, true);  
        PG_RETURN_FLOAT8(result);  
}  
man asin   
NAME  
       asin, asinf, asinl - arc sine function  
  
SYNOPSIS  
       #include <math.h>  
  
       double asin(double x);  
       float asinf(float x);  
       long double asinl(long double x);  
  
///  
  
CONFORMING TO  
       C99, POSIX. 1-2001.  The variant returning double also conforms to SVr4, 4.3BSD, C89.  
  
SEE ALSO  
       acos(3), atan(3), atan2(3), casin(3), cos(3), sin(3), tan(3)

Now we need to functionalize this feature so that we can easily evaluate the degree of an included angle. To do so, run the following:

create or replace function angle (a point, b point, c point, d int) returns float8 as 
$$

declare
  ab float8 := point_distance(a, b); 
  ac float8 := point_distance(a, c); 
  bc float8 := point_distance(b, c); 
  cosa float8 := (ac^2 + ab^2 - bc^2) / (2*ac*ab);
  cosb float8 := (bc^2 + ab^2 - ac^2) / (2*bc*ab);
  cosc float8 := (ac^2 + bc^2 - ab^2) / (2*ac*bc);
begin
  -- raise notice '%,%,%,  %,%,%', ab, ac, bc, cosa, cosb, cosc;
  case d
    when 1 then return acosd(cosa);  -- The first parameter point is the vertex of the included angle
    when 2 then return acosd(cosb);  -- The second parameter point is the vertex of the included angle
    when 3 then return acosd(cosc);  -- The third parameter point is the vertex of the included angle
    else return null;
  end case;
end;

$$
 language plpgsql strict immutable;


postgres=# select angle(point(3,2), point(1,2.5), point(1,1), 1);
NOTICE:  00000: 2.06155281280883,2.23606797749979,1.5,  0.759256602365297,0.242535625036333,0.447213595499958
LOCATION:  exec_stmt_raise, pl_exec.c:3337
      angle       
------------------
 40.6012946450045
(1 row)

postgres=# select angle(point(3,2), point(1,2.5), point(1,1), 2);
NOTICE:  00000: 2.06155281280883,2.23606797749979,1.5,  0.759256602365297,0.242535625036333,0.447213595499958
LOCATION:  exec_stmt_raise, pl_exec.c:3337
      angle       
------------------
 75.9637565320735
(1 row)

postgres=# select angle(point(3,2), point(1,2.5), point(1,1), 3);
NOTICE:  00000: 2.06155281280883,2.23606797749979,1.5,  0.759256602365297,0.242535625036333,0.447213595499958
LOCATION:  exec_stmt_raise, pl_exec.c:3337
      angle      
-----------------
 63.434948822922
(1 row)

Community

How You Can Use Trigonometric Functions in PostgreSQL to Obtain Further Insights

The Cosine theorem

Trigonometric Functions in PostgreSQL

Example:

The Source Code

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

AnalyticDB for PostgreSQL

IoT Solution

PolarDB for PostgreSQL