Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

[HELP NEEDED] : Distance calculation with (latlong) in same table/column.

Bonsoir BI community ! Anyone ever tried the calculating distance using long lat in same columns before? I am working on an academic project and want to calculate the distance btween two stores per ...
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    Anonymous Just use Going the Distance: Going the Distance - Microsoft Power BI Community

    Like this:

    Column = 
    VAR __NextLocation = MINX(FILTER('Table',[Country]=EARLIER([Country]) && [Location] > EARLIER([Location])),[Location])
    VAR __FromLat = [lat]
    VAR __ToLat = MINX(FILTER('Table15',[Country]=EARLIER([Country]) && [Location] = __NextLocation),[lat])
    VAR __FromLong = [lng]
    VAR __ToLong = MINX(FILTER('Table15',[Country]=EARLIER([Country]) && [Location] = __NextLocation),[lng])
    VAR __distanceLong = RADIANS(__ToLong - __FromLong)
    VAR __distanceLat = RADIANS(__ToLat - __FromLat)
    VAR __a = (SIN(__distanceLat/2))^2 + COS(RADIANS(__FromLat)) * COS(RADIANS(__ToLat)) * SIN((__distanceLong/2))^2
    VAR __y = SQRT(__a)
    VAR __x = SQRT(1 - __a)
    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()
        )
    VAR __c = 2 * __atan2
RETURN
    __c
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    Anonymous Try:

    Column = 
    VAR __NextLocation = MAXX(FILTER('Table',[Country]=EARLIER([Country]) && [Location] < EARLIER([Location])),[Location])
    VAR __FromLat = [lat]
    VAR __ToLat = MINX(FILTER('Table15',[Country]=EARLIER([Country]) && [Location] = __NextLocation),[lat])
    VAR __FromLong = [lng]
    VAR __ToLong = MINX(FILTER('Table15',[Country]=EARLIER([Country]) && [Location] = __NextLocation),[lng])
    VAR __distanceLong = RADIANS(__ToLong - __FromLong)
    VAR __distanceLat = RADIANS(__ToLat - __FromLat)
    VAR __a = (SIN(__distanceLat/2))^2 + COS(RADIANS(__FromLat)) * COS(RADIANS(__ToLat)) * SIN((__distanceLong/2))^2
    VAR __y = SQRT(__a)
    VAR __x = SQRT(1 - __a)
    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()
        )
    VAR __c = 2 * __atan2
RETURN
    __c
rsimpson318's avatar
rsimpson318
Frequent Visitor
4 years ago

Greg_Deckler 

Sorry to revive an old topic but...

 

I was able to modify the majority of the calculation provided to get the distance, but when I try to put in the piece that adds zero's to the start of the data (to avoid large numbers) it doesn't seem to be working.

VehicleDate/TimeLatitudeLongitudeDistance
117124/29/2022 6:4737.99484-87.38086076.2
117124/29/2022 6:4837.99484-87.38080
117124/29/2022 6:5237.99486-87.38080
117124/29/2022 6:5637.99487-87.38080
117124/29/2022 7:0037.99488-87.38080
117124/29/2022 7:0437.99488-87.38080
117124/29/2022 7:0837.99489-87.38080
117124/29/2022 7:1237.99489-87.38080
117124/29/2022 7:1637.99528-87.38190.1

 

My data is based on Date/Time rather than "Location", but when it's trying to calculate the distance with the earliest date/time, it causes an issue.

 

Any help would be greatly appreciated.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

rsimpson318 Would have to see your calculation. You should be able to get the earliest date/time using MINX but seems like you have duplicates which is not optimal.

  • rsimpson318's avatar
    rsimpson318
    Frequent Visitor
    4 years ago

    Greg_Deckler 

     

    This is the code I'm using now:

     

    Distance = 
    VAR __NextLocation = MINX(FILTER('ReportData',[Device]=EARLIER([Device]) && [Date/Time] > EARLIER([Date/Time])),[Date/Time])
    VAR __FromLat = [Latitude]
    VAR __ToLat = MINX(FILTER('ReportData',[Device]=EARLIER([Device]) && [Date/Time] = __NextLocation),[Latitude])
    VAR __FromLong = [Longitude]
    VAR __ToLong = MINX(FILTER('ReportData',[Device]=EARLIER([Device]) && [Date/Time] = __NextLocation),[Longitude])
    VAR __C = ACOS(COS(RADIANS(90-__FromLat)) * COS(RADIANS(90-__ToLat)) + SIN(RADIANS(90-__FromLat)) * SIN(RADIANS(90-__ToLat)) * COS(RADIANS(__FromLong-__ToLong))) * 3959
    RETURN
    __C
     
    I modified it slightly based on an Excel formula I found to calculate distance in miles. My issue is that it tries to calculate the last record w/ an empty lat/long, so the final row shows a distance of 6000+ miles:

     

     

    I was able to sort of get around that by adding an "IF" statement:

    IF(__C>10,0,__C)

     I just wasn't sure if there was a cleaner method to correct it.

     

    Thank you.