Spatial SQL -> MS-SQL. Calculate Bearing between two given points (in LL coordinates)


/*
  =================================================================
   this function calculates Bearing between two points
   input: points in WGS84 coordinates (Lat/Long)
   output: Bearing in radians
  =================================================================
*/


IF EXISTS (SELECT name FROM sys.objects
  WHERE name = 'sfn_Bearing_2pts_LL' AND type IN (N'FN',N'IF', N'TF',N'FS', N'FT')
  DROP FUNCTION sfn_Bearing_2pts_LL
GO

CREATE FUNCTION sfn_Bearing_2pts_LL (@FPoint GEOGRAPHY, @TPoint GEOGRAPHY)
  RETURNS FLOAT
AS
BEGIN


  DECLARE @bearing DECIMAL(18,15);
  DECLARE @lon1 DECIMAL(18,15), @lon2 DECIMAL(18,15),
  @lat1 DECIMAL(18,15), @lat2 DECIMAL(18,15), @lon_diff DECIMAL(18,15);

  -- from point coords
  SET @lon1 = Radians(@FPoint.Long); SET @lat1 = Radians(@FPoint.Lat);
  -- to point coords
  SET @lon2 = Radians(@TPoint.Long); SET @lat2 = Radians(@TPoint.Lat);

  SET @lon_diff = @lon2 - @lon1;

  IF (@FPoint.STIsEmpty() = 1 OR @TPoint.STIsEmpty() = 1)
     SET @bearing = NULL;
  ELSE -- calculate the bearing
    SET @bearing = ATN2(SIN(@lon_diff) * COS(@lat2),(COS(@lat1) * SIN(@lat2))
    - (SIN(@lat1) * COS(@lat2) * COS(@lon_diff)));

  -- return value
  RETURN CONVERT(FLOAT, ROUND(@bearing,8));

END
GO

/* Milan city. Aproximate Bearing between Duomo and Castello Sforzesco */

DECLARE @FP GEOGRAPHY, @TP GEOGRAPHY;

SET @FP = GEOGRAPHY::STGeomFromText(
  'POINT(' + CONVERT(Varchar(30),9.1918377, 2)
  + ' ' + CONVERT(Varchar(30),45.4642822, 2) + ')',4326);
SET @TP = GEOGRAPHY::STGeomFromText(
  'POINT(' + CONVERT(Varchar(30),9.1794726, 2)
  + ' ' + CONVERT(Varchar(30),45.4702864, 2) + ')',4326);

SELECT dbo.sfn_Bearing_2pts_LL(@FP,@TP);


Result will be like this:
-0.96512694




sqlexamples.info