COGO: Calculating the bearing between two points (SQL Server 2008 Spatial)

Another useful function for use along with projected data in SQL Server 2008 is a function that calculates the bearing between any two points. The following function I coded for use with Oracle Spatial many years ago and have recently converted it to SQL Server.

Note that I have a schema call cogo in which I create functions like this. You can use anything you like.

 /**
 * @function   : Bearing
 * @precis     : Returns a bearing between two point coordinates
 * @version    : 1.0
 * @usage      : FUNCTION Bearing(@p_dE1  float,
 *                                 @p_dN1 float,
 *                                 @p_dE2 float,
 *                                 @p_dN2 float )
 *                RETURNS GEOMETRY
 *               eg select cogo.Bearing(0,0,45,45) * (180/PI()) as Bearing;
 * @param      : p_dE1     : X Ordinate of start point of bearing
 * @paramtype  : p_dE1     : FLOAT
 * @param      : p_dN1     : Y Ordinate of start point of bearing
 * @paramtype  : p_dN1     : FLOAT
 * @param      : p_dE2     : X Ordinate of end point of bearing
 * @paramtype  : p_dE2     : FLOAT
 * @param      : p_dN2     : Y Ordinate of end point of bearing
 * @paramtype  : p_dN2     : FLOAT
 * @return     : bearing   : Bearing between point 1 and 2 from 0-360 (in radians)
 * @rtnType    : bearing   : Float
 * @note       : Does not throw exceptions
 * @note       : Assumes planar projection eg UTM.
 * @history    : Simon Greener  - Feb 2005 - Original coding.
 * @history    : Simon Greener  - May 2011 - Converted to SQL Server
   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License. (http://creativecommons.org/licenses/by-sa/2.5/au/)
 */
 Create Function [cogo].[Bearing](@p_dE1 Float, @p_dN1 Float,
                                    @p_dE2 Float, @p_dN2 Float)
 Returns Float 
 AS
 Begin
     Declare
         @dBearing Float,
         @dEast    Float,
         @dNorth   Float;
     BEGIN
         If (@p_dE1 IS NULL OR
             @p_dN1 IS NULL OR
             @p_dE2 IS NULL OR
             @p_dE1 IS NULL ) 
            Return NULL;
  
         If ( (@p_dE1 = @p_dE2) AND 
              (@p_dN1 = @p_dN2) ) 
            Return NULL;
  
         SET @dEast  = @p_dE2 - @p_dE1;
         SET @dNorth = @p_dN2 - @p_dN1;
         If ( @dEast = 0 ) 
         Begin
             If ( @dNorth < 0 ) 
                 SET @dBearing = PI();
             Else
                 SET @dBearing = 0;
         End
         Else
             SET @dBearing = -aTan(@dNorth / @dEast) + PI() / 2.0;
              
         If ( @dEast < 0 ) 
             SET @dBearing = @dBearing + PI();
  
         Return @dBearing;
     End
 End;
 GO

Here are some examples.

Test invalid input.

 select cogo.Bearing(0,0,0,0) as Bearing;
Bearing
NULL

To convert result to degrees use built-in DEGREES ( ) function

 select DEGREES(cogo.Bearing(0,0,45,45)) as Bearing;
Bearing
45

Or, more relevantly to Spatial users:

 DECLARE
    @pt1 geometry = geometry::STGeomFromText('POINT(0 0)',0),
    @pt2 geometry = geometry::STGeomFromText('POINT(-45 45)',0); 
 BEGIN
 select DEGREES(cogo.Bearing(@pt1.STX, @pt1.STY, 
                             @pt2.STX, @pt2.STY)) as Bearing;
 END
 GO
Bearing
315

Let’s Rock around the clock!

 select DEGREES(a.Bearing) as bearing
   from (select cogo.Bearing(0,0,x.IntValue,y.IntValue) as Bearing
           from dbo.generate_series(-45,45,45) x
                cross apply
                dbo.generate_series(-45,45,45) y ) a
   where a.Bearing is not null
   order by 1;
Bearing
0
45
90
135
180
225
270
315