Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculate sequential distance for the entire table

I would like to get the distance between each point in the following table.

 

(Data for representative purposes only)

 

Based on the index column, I would like to get the distance between row 1 and row 2, then again from row 2 to row 3 and so on for the entire table. The data is currently organized by date and time, but I can’t assume that it will always be organized (even though it is meant to be always ordered by date). The goal is to have a dynamic measure that based on the filters applied, it will tell me the distance traveled by a specific vehicle, multiple ones or all of them and also filtered by date and time.

 

The problem I am facing is that although I have the formula to get the distance between 2 points, I don’t have a way to calculate it as stated above.

 

I have tried many ways, but I always end up with the wrong values and I have yet to find a way to verify that they are correct although this seems unlikely as they all have a very similar but very high value. If there was a way to make a loop that could work for this, would be a pretty good alternative but I don’t know any other way. Could someone please help me? I am open to make changes to the table and any ideas are very welcome!

Need Help
Tutorial Requests

2 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    Anonymous you already have data in a shape where it is very easier to work with,if you can share your formula to calculate distance and also this data in excel, will put together solution for you.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      parry2k  I would really appreciate that! It would help me a lot.

       

      This is what I was trying to do: 

      var Lat1 = CALCULATE(LASTNONBLANK(GPS[Latitude],1),GPS[Index]=currentindex)
var Lon1 = CALCULATE(LASTNONBLANK(GPS[Longitude],1),GPS[Index]=currentindex)
 
var Lat2 = CALCULATE(LASTNONBLANK(GPS[Latitude],1),GPS[Index]=nextIndex) 
var Lon2 = CALCULATE(LASTNONBLANK(GPS[Longitude],1),GPS[Index]=nextIndex)

var P = DIVIDE( PI(), 180 )
var A = 0.5 - COS((Lat2-Lat1) * p)/2 + 
    COS(Lat1 * p) * COS(lat2 * P) * (1-COS((Lon2- Lon1) * p))/2
var final = 12742 * ASIN((SQRT(A)))
return final

      It is based on a formula I found online. 

       

      Here is the Excel file you requested. I uploaded it to One Drive as I am not sure if it is possible to upload files other than pictures or videos here.

       

      https://grupo823-my.sharepoint.com/:x:/p/lacuna/EQpxpI2yfZZAlz2pTTBqxyABiIYIsw5PkBnHBg-xuEzYgw