COGO: TSQL Function to Calculate the Bearing Between Two Points

Another useful function for use along with projected data in SQL Server 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 is now available in SQL Server.

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

CREATE FUNCTION [cogo].[STBearing] 
(
  @p_dE1 Float,
  @p_dN1 Float,
  @p_dE2 Float,
  @p_dN2 Float
)
Returns Float
AS
/****f* COGO/STBearing (2008)
 *  NAME
 *    STBearing -- Returns a (Normalized) bearing in Degrees between two non-geodetic (XY) coordinates
 *  SYNOPSIS
 *    Function STBearing (
 *               @p_dE1 float,
 *               @p_dN1 float,
 *               @p_dE2 float,
 *               @p_dN2 float
 *             )
 *     Returns float 
 *  DESCRIPTION
 *    Function that computes the bearing from the supplied start point (@p_dx1) to the supplied end point (@p_dx2).
 *    The result is expressed as a whole circle bearing in decimal degrees.
 *  INPUTS
 *    @p_dE1 (float) - X ordinate of start point.
 *    @p_dN1 (float) - Y ordinate of start point.
 *    @p_dE2 (float) - Z ordinate of start point.
 *    @p_dN2 (float) - M ordinate of start point.
 *  RESULT
 *    decimal degrees (float) - Bearing between point 1 and 2 from 0-360.
 *  EXAMPLE
 *    SELECT [$(cogoowner)].[STBearing](0,0,45,45) as Bearing;
 *    GO
 *
 *    Bearing
 *    45
 *  AUTHOR
 *    Simon Greener
 *  HISTORY
 *    Simon Greener - December 2011 - Original TSQL Coding for SQL Server.
 *  COPYRIGHT
 *    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
******/
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.0 ) 
    Begin
      If ( @dNorth < 0.0 ) 
        SET @dBearing = PI();
      Else
        SET @dBearing = 0.0;
    End
    Else
    Begin
      SET @dBearing = -aTan(@dNorth / @dEast) + PI() / CAST(2.0 as float);
    End;
          
    IF ( @dEast < 0.0 ) 
      SET @dBearing = @dBearing + PI();

    -- Turn radians into degrees
    SET @dBearing = @dBearing * CAST(180.0 as float) / PI();
    -- Normalize bearing ...
    Return case when @dBearing < 0.0
                then @dBearing + CAST(360.0 as float)
                when @dBearing >= 360.0
                then @dBearing - CAST(360.0 as float)
                else @dBearing
            end;
    End
End;
GO

Here are some examples.

Test invalid input.

SELECT cogo.STBearing(0,0,0,0) as Bearing;
GO

A simple 45 degree bearing.

SELECT cogo.STBearing(0,0,45,45) as Bearing;
GO

Or, more relevantly to Spatial users:

DECLARE
  @pt1 geometry = geometry::STGeomFromText('POINT(0 0)',0),
  @pt2 geometry = geometry::STGeomFromText('POINT(-45 45)',0); 
SELECT cogo.STBearing(@pt1.STX, @pt1.STY, @pt2.STX, @pt2.STY) as Bearing;
GO

Let’s Rock around the clock!

SELECT a.Bearing
  FROM (SELECT cogo.STBearing(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;

STBearingBetweenPoints

Three is also a wrapper function which takes two SQL Server Spatial Points, extracts the XY ordinates and calls STBearing.

CREATE FUNCTION [cogo].[STBearingBetweenPoints] 
(
  @p_start_point geometry,
  @p_end_point   geometry
)
RETURNS Float

Here’s an example:

SELECT [cogo].[STBearingBetweenPoints] (
            geometry::Point(0,0,0),
            geometry::Point(45,45,0) 
       ) as Bearing;
GO

Bearing
45