Calculating the Bearing from One Point to Another
Sometimes it is helpful to know the bearing (i.e. the direction) of a path between two points. Here's two functions to help you calculate the bearing between two points, for the geometry datatype and the geography datatype respectively.
You can test out this function by checking one of the angles of a simple right-angled triangle with angles 30-60-90, as follows:
Note that this is a simplistic determination of the bearing, based on a perfectly spherical model of the earth - it does not take into consideration the actual ellipsoidal model specified by the SRID of the geography instances supplied.
You can then use this function to calculate the angle (0' being N) at which you'd initially have to set off from Vienna in order to take the shortest path to Moscow, as follows:
Geometry
The Bearing of the path between two points of the geometry datatype is a case of simple trigonometry:CREATE FUNCTION dbo.GeometryBearing ( @Point1 geometry, @Point2 geometry ) RETURNS FLOAT AS BEGIN DECLARE @Bearing DECIMAL(18,15) DECLARE @dx FLOAT = @Point2.STX - @Point1.STX DECLARE @dy FLOAT = @Point2.STY - @Point1.STY IF (@Point1.STEquals(@Point2) = 1) SET @Bearing = NULL ELSE SET @Bearing = ATN2(@dx,@dy) SET @Bearing = (DEGREES(@Bearing) + 360) % 360 RETURN @Bearing END GO
DECLARE @Point1 geometry = geometry::Point(0,0,0) DECLARE @Point2 geometry = geometry::Point(1,SQRT(3),0) SELECT dbo.GeometryBearing(@Point1, @Point2)
Geography
The Bearing of the path between two points of the geography datatype is a bit more complicated:CREATE FUNCTION dbo.GeographyBearing ( @Point1 geography, @Point2 geography ) RETURNS FLOAT AS BEGIN DECLARE @Bearing DECIMAL(18,15) DECLARE @Lat1 FLOAT = RADIANS(@Point1.Lat) DECLARE @Lat2 FLOAT = RADIANS(@Point2.Lat) DECLARE @dLon FLOAT = RADIANS(@Point2.Long - @Point1.Long) IF (@Point1.STEquals(@Point2) = 1) SET @Bearing = NULL ELSE SET @Bearing = ATN2( SIN(@dLon)*COS(@Lat2), (COS(@Lat1)*SIN(@Lat2)) - (SIN(@Lat1)*COS(@Lat2)*COS(@dLon)) ) SET @Bearing = (DEGREES(@Bearing) + 360) % 360 RETURN @Bearing END GO
DECLARE @Vienna geography = geography::Point(16.37, 48.21, 4326) DECLARE @Moscow geography = geography::Point(37.60, 55.75, 4326) SELECT dbo.GeographyBearing(@Vienna,@Moscow)
Comments
Post new comment