SQL 2005+SQL Server Trigonometric Functions
The forty-first part of the SQL Server Programming Fundamentals tutorial continues the examination of Transact-SQL (T-SQL) mathematical functions. This article considers the trigonometric functions that allow SQL Server to work with angular data.
Trigonometric Functions
SQL Server's Transact-SQL (T-SQL) scripting language provides several trigonometric functions. These are functions that operate with angles. Most developers who have studied trigonometry will have used trigonometric functions to calculate the angles and the lengths of the sides of triangles.
T-SQL allows the use of eight trigonometric functions, each working with or calculating angles measure in radians. T-SQL also includes related functions that allow the translation of angles between radians and degrees and return the value of Pi. In this article we will examine a total of eleven functions. These will be related to the right-angled triangle pictured below:
The diagram shows a standard 3-4-5- triangle, meaning that the lengths of the three sides are in the ratio 3:4:5. The angle at the bottom-right of the triangle is labelled with the Greek letter theta (θ). The three other sides are shown with letters that relate to their positions relative to θ. The "a" side is adjacent to the angle, the "o" is the side opposite θ and the hypotenuse is marked with an "h".
Sine
The sine of an angle returns the ratio of the lengths of the opposing side and the hypotenuse. This function makes it simple to calculate either of these two lengths given the other length and the angle.
In the diagram the ratio between the opposite side to the angle θ and the hypotenuse is 0.6 (3m / 5m). We can use the Sin function to calculate this ratio based upon the angle. However, as the trigonometric functions work with radians, not degrees, we cannot use 36.86 directly. Instead, we must convert this to the equivalent radians value, which is approximately 0.6433.
print sin(0.6433) // 0.599839 NB: The result is not exact because the original angle of 36.86 degrees was an approximation and rounding was introduced in the conversion to radians.
If you prefer to work in degrees, you can use the Radians function to perform the conversion, as in the following example:
print sin(radians(36.86)) // 0.599862 To calculate the length of the opposite side, you can multiply the sine of the angle by the length of the hypotenuse. As in the previous examples, rounding errors mean that the result will not be exactly three:
print sin(radians(36.86)) * 5 // 2.99931 Similarly, to calculate the hypotenuse you can divide the length of the opposite side by the sine of the angle:
print 3 / sin(radians(36.86)) // 5.00115 Cosine
The cosine of an angle returns the ratio of the lengths of the adjacent side and the hypotenuse. In the diagram this ratio is 0.8 (4m / 5m). To calculate the cosine, use the Cos function:
print cos(radians(36.86)) // 0.800104 Tangent
The tangent of an angle gives the ratio of the lengths of the opposite and adjacent sides of a right-angled triangle. In the 3-4-5 triangle this ratio is 0.75 (3m / 4m). To calculate the tangent, use the Tan function:
print tan(radians(36.86)) // 0.74973 Cotangent
The cotangent of an angle is the ratio of the lengths of the adjacent and opposite sides of the triangle. This is the reverse of the tangent's ratio. In the sample triangle, the ratio is approximately 1.3333 (4m / 3m). The cotangent is calculated using the Cot function:
print cot(radians(36.86)) // 1.33381
Arcsine
Arcsine is the inverse function of sine. Where the sine of an angle returns the ratio of the lengths of the opposite side and the hypotenuse, the arcsine of that ratio returns the angle. In T-SQL, we can demonstrate this by applying the Asin function to the result of a Sin operation. As you can see from the sample below, the original angle is returned. The angles are show in radians in this case:
print sin(0.6433) // 0.599839
print asin(0.599839) // 0.6433 To convert an angle from radians to degrees, you can use the Degrees function. We can use a combination of the Radians and Degrees functions to repeat the above example:
print sin(radians(36.86)) // 0.599862
print degrees(asin(0.599862)) // 36.86 Arccosine
Arccosine is the inverse of cosine. Given the ratio of the adjacent side and the hypotenuse of a right-angled triangle, arccosine returns the angle θ. In the diagram this ratio is 0.8 (4m / 5m). We can calculate the angle from this ratio using T-SQL's Acos function.
print degrees(acos(0.8)) // 36.8699 Arctangent
The inverse of tangent is known as arctangent. Accessible using the Atan function, arctangent calculates an angle based upon the ratio of the opposite and adjacent sides. For the example triangle, this ratio is 0.75.
print degrees(atan(0.75)) // 36.8699 The last of the trigonometric functions that T-SQL provides is Atn2. This variation upon Atan allows the opposite and adjacent side lengths to be provided using two arguments. The arctangent of the ratio of the two lengths is returned.
print degrees(atn2(3, 4)) // 36.8699 Pi
Although not technically a trigonometric function, T-SQL provides a function named Pi. This returns a constant value that approximates Pi (π), which is the ratio between a circle's circumference and its diameter. The Pi functions returns a value that is rounded to sixteen decimal places.
print convert(numeric(17,16), pi())) // 3.1415926535897931 |