Top 5 Recent Articles
- Algorithms (15)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (3)
- Open Source (18)
- Oracle Spatial and Locator (192)
- PostGIS (34)
- Published Articles (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (35)
- Space Curves (9)
- Spatial Database Functions (106)
- Spatial DB comparison (1)
- Spatial XML Processing (10)
- SQL Server Spatial (General) (83)
- SQL Server Spatial (LRS) (38)
- Standards (1)
- Stored Procedure (15)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
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;
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