# 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.447213595499958LOCATION:  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.447213595499958LOCATION:  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.447213595499958LOCATION:  exec_stmt_raise, pl_exec.c:3337      angle      ----------------- 63.434948822922(1 row)`

# Original Source

--

-- ## Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com