CREATE FUNCTION CalculateCardinalDirection
(
	@fromLatitude  FLOAT,
	@fromLongitude FLOAT,
	@toLatitude  FLOAT,
	@toLongitude FLOAT
)
RETURNS int
AS
BEGIN

	DECLARE @dLon    float,
			@dPhi    float,
			@bearing float,
			@pi180	FLOAT,
			@piDoubled	FLOAT,
			@piPlus2	FLOAT,
			@direction int
	        
	SET @pi180 = pi() / 180
	SET @piDoubled = 2 * pi()
	SET @piPlus2 = pi() / 4

	SET @dLon = ((@toLongitude - @fromLongitude) * @pi180)
	SET @dPhi = log(tan((@toLatitude * @pi180) / 2 + @piPlus2) / tan((@fromLatitude * @pi180) / 2 + @piPlus2))

	IF (abs(@dLon) > pi())
	BEGIN
		IF @dLon > 0
		BEGIN
			SET @dLon = -(@piDoubled - @dLon)
		END
		ELSE
		BEGIN
			SET @dLon = (@piDoubled + @dLon)
		END
	END

	SET @bearing = cast(((ATN2(@dLon, @dPhi) * 180 / pi()) + 360) AS DECIMAL(18,15)) % cast(360 AS DECIMAL(18,15))

	RETURN CASE
			--NE
			WHEN @bearing >= 22.5 AND @bearing < 67.5 THEN 2
			--E
			WHEN @bearing >= 67.5 AND @bearing < 112.5 THEN 4
			--SE
			WHEN @bearing >= 112.5 AND @bearing < 157.5 THEN 6
			--S
			WHEN @bearing >= 157.5 AND @bearing < 202.5 THEN 8
			--SW
			WHEN @bearing >= 202.5 AND @bearing < 247.5 THEN 10
			--W
			WHEN @bearing >= 247.5 AND @bearing < 292.5 THEN 12
			--NW
			WHEN @bearing >= 292.5 AND @bearing < 337.5 THEN 14
			--N
			ELSE 0
		END

END
GO

drop table dbo.bearings

create table dbo.bearings(bearingstart decimal(10,1), bearingend decimal(10,1), bearingid int,
 CONSTRAINT [PK_bearings] PRIMARY KEY CLUSTERED 
(
	bearingid asc,
	bearingstart ASC,
	bearingend ASC
	
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

insert into dbo.bearings
select 22.5, 67.4, 2
UNION
select 67.5, 112.4, 4
UNION
select 112.5, 157.4, 6
UNION
select 157.5, 202.4, 8
UNION
select 202.5, 247.4, 10
UNION
select 247.5, 292.4, 10
UNION
select 292.5, 337.4, 14

select * from bearings

--Addis Abeba	09°02'N	38°42'E
--Bahir Dar		11°37'N	37°10'E


declare @fromLatitude  FLOAT,
	@fromLongitude FLOAT,
	@toLatitude  FLOAT,
	@toLongitude FLOAT

SET @fromLatitude  = 9.022736
SET	@fromLongitude = 38.746799
SET	@toLatitude  = 11.616667
SET	@toLongitude = 38.066667	

DROP TABLE dbo.Cities

create table dbo.Cities(FromCityName varchar(50), FromLatitude float, FromLongitude float, ToCityName varchar(50),ToLatitude float, ToLongitude float) ON [PRIMARY]


insert into Cities
SELECT 'Addis Abeba', 	@fromLatitude, 	@fromLongitude, 'Bahir Dar', 	@toLatitude	, @toLongitude

select * from Cities

DECLARE @dLon    float,
			@dPhi    float,
			@bearing float,
			@pi180	FLOAT,
			@piDoubled	FLOAT,
			@piPlus2	FLOAT,
			@direction int
	        
	SET @pi180 = pi() / 180
	SET @piDoubled = 2 * pi()
	SET @piPlus2 = pi() / 4
PRINT @pi180
PRINT @piDoubled
PRINT @piPlus2
SELECT [FromCityName]
      ,[FromLatitude]
      ,[FromLongitude]
      ,[ToCityName]
      ,[ToLatitude]
      ,[ToLongitude] , 
       ABS( (( ToLongitude- FromLongitude) * @pi180) ) dLon, 
       log(tan((ToLongitude * @pi180) / 2 + @piPlus2) / tan((FromLongitude * @pi180) / 2 + @piPlus2)) dPhi,
       CASE
          WHEN (( ToLongitude- FromLongitude) * @pi180) > 0 THEN -( @piDoubled - ((ToLongitude - FromLongitude) * @pi180) ) 
          ELSE ( @piDoubled + ((ToLongitude - FromLongitude) * @pi180) )
       END dLonCalculated
        
  FROM dbo.Cities
  
;with cteCities([FromCityName]
      ,[FromLatitude]
      ,[FromLongitude]
      ,[ToCityName]
      ,[ToLatitude]
      ,[ToLongitude]
      ,dLon
      ,dPhi
      ,dLonCalculated
      ,bearing
      
      )
AS
(
SELECT [FromCityName]
      ,[FromLatitude]
      ,[FromLongitude]
      ,[ToCityName]
      ,[ToLatitude]
      ,[ToLongitude] , 
       ABS( (( ToLongitude- FromLongitude) * @pi180) ) dLon, 
       log(tan((ToLongitude * @pi180) / 2 + @piPlus2) / tan((FromLongitude * @pi180) / 2 + @piPlus2)) dPhi,
       CASE
          WHEN (( ToLongitude- FromLongitude) * @pi180) > 0 THEN -( @piDoubled - ((ToLongitude - FromLongitude) * @pi180) ) 
          ELSE ( @piDoubled + ((ToLongitude - FromLongitude) * @pi180) )
       END dLonCalculated,
       CASE
          WHEN (( ToLongitude- FromLongitude) * @pi180) > 0 
              THEN cast(((ATN2(-( @piDoubled - ((ToLongitude - FromLongitude) * @pi180) ), (log(tan((ToLongitude * @pi180) / 2 + @piPlus2) / tan((FromLongitude * @pi180) / 2 + @piPlus2))) ) * 180 / pi()) + 360) AS DECIMAL(18,15)) % cast(360 AS DECIMAL(18,15))
          ELSE  cast(((ATN2(( @piDoubled + ((ToLongitude - FromLongitude) * @pi180) ), (log(tan((ToLongitude * @pi180) / 2 + @piPlus2) / tan((FromLongitude * @pi180) / 2 + @piPlus2))) ) * 180 / pi()) + 360) AS DECIMAL(18,15)) % cast(360 AS DECIMAL(18,15))
       END bearing

       
        
  FROM dbo.Cities
)
SELECT *
  FROM cteCities cte
 cross apply dbo.bearings br
where bearing between br.bearingstart and bearingend