sql server 2008 - Determining cardinal (compass) direction between points -

sql server 2008 - Determining cardinal (compass) direction between points -

is there way know in sql server 2008r2 if point @ south,east,etc...of point?

for example, have origin point(lat1,lng1) , want know point(lat2,lng2) located origin: north, west,etc...

i'm trying build wind rose graph , might useful me.

in order calculate bearing between 2 coordinates while using geography type in sql server 2008 r2, can utilize function:

create function [dbo].[calculatebearing] ( @pointa geography ,@pointb geography ) returns decimal(18,12) begin -- declare homecoming variable declare @bearing decimal(18,12) -- declare local variables declare @x decimal(18,12) declare @y decimal(18,12) declare @dlat decimal(18,12) declare @dlong decimal(18,12) declare @rlat1 decimal(18,12) declare @rlat2 decimal(18,12) if(@pointa.stisempty() = 1 or @pointb.stisempty() = 1) set @bearing = null else begin -- calculate delta between coordinates set @dlat = radians(@pointb.lat - @pointa.lat) set @dlong = radians(@pointb.long - @pointa.long) -- calculate latitude radians set @rlat1 = radians(@pointa.lat) set @rlat2 = radians(@pointb.lat) set @y = sin(@dlong)*cos(@rlat2) set @x = cos(@rlat1)*sin(@rlat2)-sin(@rlat1)*cos(@rlat2)*cos(@dlong) if (@x = 0 , @y = 0) set @bearing = null else begin set @bearing = cast((degrees(atn2(@y,@x)) + 360) decimal(18,12)) % 360 end end -- homecoming result of function homecoming @bearing end go

and after this, can utilize function this:

declare @pointa geography declare @pointb geography set @pointa = geography::stgeomfromtext('point(3 45)', 4326) set @pointb = geography::stgeomfromtext('point(4 47)', 4326) select [dbo].[calculatebearing](@pointa, @pointb)

update: adding schema

sql-server-2008 geometry geography