SQLTeam.com | Weblogs | Forums

Converting Excel Formula to T-SQL


#1

Hello,

This is my first post.. so.. here goes!
I have the following formula for calculating bearing in Excel:

=IF(ISERR(MOD(ATAN2(COS(RADIANS([@OriginLatitude]))*SIN(RADIANS([@DestinationLatitude]))-SIN(RADIANS([@OriginLatitude]))*COS(RADIANS([@DestinationLatitude]))*COS(RADIANS([@DestinationLongitude]-[@OriginLongitude])),COS(RADIANS([@DestinationLatitude]))*SIN(RADIANS([@DestinationLongitude]-[@OriginLongitude])))*180/PI(),360)),0,MOD(ATAN2(COS(RADIANS([@OriginLatitude]))*SIN(RADIANS([@DestinationLatitude]))-SIN(RADIANS([@OriginLatitude]))*COS(RADIANS([@DestinationLatitude]))*COS(RADIANS([@DestinationLongitude]-[@OriginLongitude])),COS(RADIANS([@DestinationLatitude]))*SIN(RADIANS([@DestinationLongitude]-[@OriginLongitude])))*180/PI(),360))

I have OriginLongiutde, OriginLatitude, DestinationLongitude and DestinationLatitude columns within a table in SSMS.

Can anyone help with converting this formula to T-SQL?


#2

These old threads, which discuss formula for distance-between-two-locations, may help

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=169630#6640751
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=917601


#3

Thanks for the help! I didn't see these earlier.


#4

I have been able to calculate distance using:

SELECT
t1.*
, Distance = round(ACOS( SIN([OriginLatitude]*PI()/180)*SIN([DestinationLatitude]*PI()/180) + COS([OriginLatitude]*PI()/180)*COS([DestinationLatitude]*PI()/180)*COS([DestinationLongitude]*PI()/180-[OriginLongitude]*PI()/180) )*3959, 2)
FROM [DevelopmentDatabase].dbo.Directionality t1
ORDER BY Origin, Destination
GO

But still need some help in calculating bearing degrees.