COGO: TSQL Function For Creating a Point from Point, Bearing and Distance

Sometimes the definition of an object can be as a bearing and distance from a known point. This often happens in unbiased statistical sampling, or when creating polygons describing land titles from coordinate geometry (COGO) measurements.

Here is a version of the STPointFromBearingAndDistance PL/SQL function I wrote for Oracle Spatial many years ago for 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].[STPointFromBearingAndDistance] 
(
  @p_dStartE   Float,
  @p_dStartN   Float,
  @p_dBearing  Float,
  @p_dDistance Float,
  @p_round_xy  int = 3,
  @p_srid      int = 0 
)
RETURNS geometry
AS
/****f* COGO/STPointFromBearingAndDistance (2008)
 *  NAME
 *    STPointFromBearingAndDistance -- Returns a projected point given starting point, a bearing in Degrees, and a distance (geometry SRID units).
 *  SYNOPSIS
 *    Function STPointFromBearingAndDistance (
 *               @p_dStartE   float,
 *               @p_dStartN   float,
 *               @p_dBearing  float,
 *               @p_dDistance float
 *               @p_round_xy  int = 3,
 *               @p_srid      int = 0 
 *             )
 *     Returns float 
 *  DESCRIPTION
 *    Function that computes a new point given a starting coordinate, a whole circle bearing and a distance (SRID Units).
 *    Returned point's XY ordinates are rounded to @p_round_xy decimal digits of precision.
 *    @p_SRID is the SRID of the supplied start point.
 *  INPUTS
 *    @p_dStartE   (float) - Easting of starting point.
 *    @p_dStartN   (float) - Northing of starting point.
 *    @p_dBearing  (float) - Whole circle bearing between 0 and 360 degrees.
 *    @p_dDistance (float) - Distance in SRID units from starting point to required point.
 *    @p_round_xy    (int) - XY ordinates decimal digitis of precision.
 *    @p_srid        (int) - SRID associated with @p_dStartE/p_dStartN.
 *  RESULT
 *    point    (geometry) - Point
 *  EXAMPLE
 *    SELECT [cogo].[STPointFromBearingAndDistance] (0,0,45,100,3,0).STAsText() as endPoint;
 *    GO
 *
 *    endPoint
 *    POINT (70.711 70.711)
 *  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,
    @dAngle1    Float,
    @dAngle1Rad Float,
    @dDeltaN    Float,
    @dDeltaE    Float,
    @dEndE      Float,
    @dEndN      Float,
    @v_round_xy int = ISNULL(@p_round_xy,3),
    @v_srid     int = ISNULL(@p_srid    ,0);
  BEGIN
    IF ( @p_dStartE   IS NULL OR
         @p_dStartN   IS NULL OR
         @p_dBearing  IS NULL OR
         @p_dDistance IS NULL )
      RETURN NULL;

    SET @dBearing = ABS(@p_dBearing);

    -- First calculate dDeltaE and dDeltaN
    IF ( @dBearing < CAST(90.0 as float) )
    BEGIN
      SET @dAngle1    = CAST(90.0 as float) - @dBearing;
      SET @dAngle1Rad = @dAngle1 * PI() / CAST(180.0 as float)
      SET @dDeltaE    = Cos(@dAngle1Rad) * @p_dDistance;
      SET @dDeltaN    = Sin(@dAngle1Rad) * @p_dDistance;
      -- Calculate the easting and northing of the end point
      SET @dEndE      = @p_dStartE + @dDeltaE;
      SET @dEndN      = @p_dStartN + @dDeltaN;
      RETURN geometry::Point(ROUND(@dEndE,@v_round_xy),ROUND(@dEndN,@v_round_xy), @v_srid);
    END;

    IF ( @dBearing < CAST(180.0 as float) )
    BEGIN
      SET @dAngle1    = @dBearing - CAST(90.0 AS float);
      SET @dAngle1Rad = @dAngle1 * PI() / CAST(180.0 AS float);
      SET @dDeltaE    = Cos(@dAngle1Rad) * @p_dDistance;
      SET @dDeltaN    = Sin(@dAngle1Rad) * @p_dDistance * -1;
      -- Calculate the easting and northing of the end point
      SET @dEndE      = @p_dStartE + @dDeltaE;
      SET @dEndN      = @p_dStartN + @dDeltaN;
      RETURN geometry::Point(ROUND(@dEndE,@v_round_xy),ROUND(@dEndN,@v_round_xy), @v_srid);
    END;

    IF ( @dBearing < CAST(270.0 AS float) )
    BEGIN
      SET @dAngle1    = CAST(270.0 AS float) - @dBearing;
      SET @dAngle1Rad = @dAngle1 * PI() / CAST(180.0 AS float);
      SET @dDeltaE    = Cos(@dAngle1Rad) * @p_dDistance * -1;
      SET @dDeltaN    = Sin(@dAngle1Rad) * @p_dDistance * -1;
      -- Calculate the easting and northing of the end point
      SET @dEndE      = @p_dStartE + @dDeltaE;
      SET @dEndN      = @p_dStartN + @dDeltaN;
      RETURN geometry::Point(ROUND(@dEndE,@v_round_xy),ROUND(@dEndN,@v_round_xy), @v_srid);
    END;

    IF ( @dBearing <= CAST(360.0 AS float) )
    BEGIN
      SET @dAngle1    = @dBearing - CAST(270.0 AS float);
      SET @dAngle1Rad = @dAngle1 * PI() / CAST(180.0 as float);
      SET @dDeltaE    = Cos(@dAngle1Rad) * @p_dDistance * -1;
      SET @dDeltaN    = Sin(@dAngle1Rad) * @p_dDistance;
      -- Calculate the easting and northing of the end point
      SET @dEndE      = @p_dStartE + @dDeltaE;
      SET @dEndN      = @p_dStartN + @dDeltaN;
      RETURN geometry::Point(ROUND(@dEndE,@v_round_xy),ROUND(@dEndN,@v_round_xy), @v_srid);
    End;
    Return null;
  END;
END;
GO

Here is an example of how to generate the geometry::Point object at the end of a bearing of 45 degrees and distance of 100 meters from ( 0,0 ) .

 select cogo.STPointFromBearingAndDistance(0,0,45,100,3,0).STAsText() as NewPoint;

Or more visually:

 DECLARE
   @XY Float = SQRT(POWER(100,2)/2);
 select geometry::STGeomFromText('POINT(0 0)',0).STBuffer(10) as geom
 union all
 select geometry::STGeomFromText('LINESTRING(0 0,' + STR(@XY,10,5) + ' ' + STR(@XY,10,5) + ')',0) as geom
 union all
 select cogo.STPointFromBearingAndDistance(0,0,45,100,3,0).STBuffer(10) as geom;

I hope this is helpful to someone.