bearing between 2 latitude longitude sets

[Paulus]

Hi,

I need to calculate the bearing in degrees between two sets of latitude /longitude points in a DAX formula. I can do this in Power Query, but need it in DAX.

Thank you.

Paul

[amitchandak]

Not sure about bearing in dergee. But for distance you can refer : https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-h...

[Paulus]

Thank you, indeed this is for distance, and it works. Bearing seems another matter, surprisingly little mentioned about it in DAX.

[Greg_Deckler]

@Paulus  - How is this? PBIX is attached. Please confirm you get the right answers for your data.

b = 
    VAR __FromCity = SELECTEDVALUE('From City'[City])
    VAR __ToCity = SELECTEDVALUE('To City'[City])
    VAR __FromLat = LOOKUPVALUE('Table'[Latitude],'Table'[City],__FromCity)
    VAR __ToLat = LOOKUPVALUE('Table'[Latitude],'Table'[City],__ToCity)
    VAR __FromLong = LOOKUPVALUE('Table'[Longitude],'Table'[City],__FromCity)
    VAR __ToLong = LOOKUPVALUE('Table'[Longitude],'Table'[City],__ToCity)
    VAR __distanceLong = RADIANS(ABS(__ToLong - __FromLong))
    VAR __x = COS(RADIANS(__ToLat)) * SIN(__distanceLong)
    VAR __y = COS(RADIANS(__FromLat)) * SIN(RADIANS(__ToLat)) - SIN(RADIANS(__FromLat)) * COS(RADIANS(__ToLat)) * COS(__distanceLong)
    VAR __atan2 = 
        SWITCH(
            TRUE(),
            __x > 0, ATAN(__y/__x),
            __x < 0 && __y >= 0, ATAN(__y/__x) + PI(),
            __x < 0 && __y < 0, ATAN(__y/__x) - PI(),
            __x = 0 && __y > 0, PI()/2,
            __x = 0 && __y < 0, PI()/2 * (0-1),
            BLANK()
        )
RETURN
    DEGREES(__atan2)

[Paulus]

Hi Greg,

Thanks.

Not quite yet, the result between Kansas and St.Louis returns -6,5126.....  it should be 97 degrees.

Paul

[Greg_Deckler · Accepted solution]

I believe this is it. Attached PBIX matches with my Excel file, will post both. 

Bearing (Degrees) = 
    VAR __FromCity = SELECTEDVALUE('From City'[City])
    VAR __ToCity = SELECTEDVALUE('To City'[City])
    VAR __FromLat = RADIANS(LOOKUPVALUE('Table'[Latitude],'Table'[City],__FromCity))
    VAR __ToLat = RADIANS(LOOKUPVALUE('Table'[Latitude],'Table'[City],__ToCity))
    VAR __FromLong = RADIANS(LOOKUPVALUE('Table'[Longitude],'Table'[City],__FromCity))
    VAR __ToLong = RADIANS(LOOKUPVALUE('Table'[Longitude],'Table'[City],__ToCity))
    VAR __distanceLong = (__ToLong - __FromLong)
    VAR __y = COS(__ToLat) * SIN(__distanceLong)
    VAR __x = COS(__FromLat) * SIN(__ToLat) - SIN(__FromLat) * COS(__ToLat) * COS(__distanceLong)
    VAR __atan2 = 
        SWITCH(
            TRUE(),
            __x > 0, ATAN(__y/__x),
            __x < 0 && __y >= 0, ATAN(__y/__x) + PI(),
            __x < 0 && __y < 0, ATAN(__y/__x) - PI(),
            __x = 0 && __y > 0, PI()/2,
            __x = 0 && __y < 0, PI()/2 * (0-1),
            BLANK()
        )
RETURN
    DEGREES(__atan2)

Bearing 2 = MOD([Bearing (Degrees)]+360,360)

[Paulus]

Great!, Thanks. I have been struggling with this for quite a while, digging as hole for myself, not being in my comfort zone.

Briefly why I need it; deliveries to droppoints, many of them available, service engineer on his way to work, in his allocated working area, needs to collect spare parts from a droppoint. The nearest droppoint may be say 25 miles but in southern direction, whereas his allocated working area is in the North. I convert degrees into something like 0-22,5 = N 22,6-66,5= NE etc. 

Paul

[Greg_Deckler]

Cool @Paulus, glad we got it, I'll post the solution to the Quick Measures gallery for future reference for everyone.

[az38]

Hi @Paulus 

Im not sure what do you mean exactly but try a solution by @Greg_Deckler  https://community.powerbi.com/t5/Quick-Measures-Gallery/Going-the-Distance/td-p/963267

[Greg_Deckler]

Sure, what is the formula for that? Should be doable. I have one for distance. https://community.powerbi.com/t5/Quick-Measures-Gallery/Going-the-Distance/td-p/963267

[Paulus]

Hi Greg,

In Excel

=ATAN2(COS(G5)*SIN(I5)-SIN(G5)*COS(I5)*COS(J5-H5); SIN(J5-H5)*COS(I5)), where

G5=latitude 1, H5=longitude 1, I5=latitude 2, J5=longitude 2, all in radians from latitude longitude as decimal number.

The result using DEGREES RETURNS a value, then use MOD(this value+360;360) for final bearing. 

This is what I have got in DAX, it is not working properly, it should basically avoid ATAN2.

Bearing =

VAR lat1rad = RADIANS(PI()/180)*SELECTEDVALUE(Distance_Bearing[Latitude_1])

VAR lon1rad = RADIANS(PI()/180)*SELECTEDVALUE(Distance_Bearing[Longitude_1])

VAR lat2rad = RADIANS(PI()/180)*SELECTEDVALUE('To Locations'[Latitude_2])

VAR lon2rad = RADIANS(PI()/180)*SELECTEDVALUE('To Locations'[Longitude_2])

VAR lat2lat1 = RADIANS(PI()/180)*(lat2rad-lat1rad)

VAR lon2lon1 = RADIANS(PI()/180)*(lon2rad-lon1rad)

VAR var_a = SIN(lat2lat1/2)*SIN(lat2lat1/2)+

COS(lat1rad)*COS(lat2rad)*

SIN(lon2lon1/2)*SIN(lon2lon1/2)

VAR var_c = 2 * ASIN(MIN(1;SQRT(var_a)))

VAR bearing =var_c*(180/PI())

RETURN

bearing * 6371

**

Paul

[Greg_Deckler]

OK, I modified the formula like this (below). Since you have this in Power Query, any chance you can share lat/long points and the bearings you are expecting? ATAN2 is included in the formula below, no reason to avoid it. https://community.powerbi.com/t5/Quick-Measures-Gallery/ATAN2/td-p/963263

Bearing (Degrees) = 
    VAR __FromCity = SELECTEDVALUE('From City'[City])
    VAR __ToCity = SELECTEDVALUE('To City'[City])
    VAR __FromLat = LOOKUPVALUE('Table'[Latitude],'Table'[City],__FromCity)
    VAR __ToLat = LOOKUPVALUE('Table'[Latitude],'Table'[City],__ToCity)
    VAR __FromLong = LOOKUPVALUE('Table'[Longitude],'Table'[City],__FromCity)
    VAR __ToLong = LOOKUPVALUE('Table'[Longitude],'Table'[City],__ToCity)
    VAR __distanceLong = RADIANS(__ToLong - __FromLong)
    VAR __x = COS(RADIANS(__ToLat)) * SIN(__distanceLong)
    VAR __y = COS(RADIANS(__FromLat)) * SIN(RADIANS(__ToLat)) - SIN(RADIANS(__FromLat)) * COS(RADIANS(__ToLat)) * COS(__distanceLong)
    VAR __atan2 = 
        SWITCH(
            TRUE(),
            __x > 0, ATAN(__y/__x),
            __x < 0 && __y >= 0, ATAN(__y/__x) + PI(),
            __x < 0 && __y < 0, ATAN(__y/__x) - PI(),
            __x = 0 && __y > 0, PI()/2,
            __x = 0 && __y < 0, PI()/2 * (0-1),
            BLANK()
        )
RETURN
    MOD(
        DEGREES(__atan2) + 360,
        360
    )

[Greg_Deckler]

Are you using this?

Bearing from point A to B, can be calculated as,

β = atan2(X,Y),

where, X and Y are two quantities and can be calculated as:

X = cos θb * sin ∆L

Y = cos θa * sin θb – sin θa * cos θb * cos ∆L