Calculating distance between segments / stages in a journey

Question

Hi everyone

I have latitude and longitude data including distance travelled depicting a journey from London to Edinburgh (attached). In between the journey are destinations (example row 10 there is a destination named ML).

The distance travelled column is a sum of the distance travelled from the start (row 2).

I was wondering how I might be able to determine the distance travelled between each destination, so the distance travelled 'resets' and starts again from each destination, almost in stages?

Fairly new to Alteryx and was thinking:

* The Sample tool could be configured to take rows between a range? Or
* Maybe the multi row formula tool could be used?
* I'm aware there are also spatial analytical tools which could be used, but I've never used those before!

Thanks,

KA

KA Example Clean Route Map.xlsx

MikeD · Answer

To compute the distance between two lat-long points, you want the Haversine Formula (https://en.wikipedia.org/wiki/Haversine_formula).  I find it easier to compute all the component parts, and then to compute the final equation, since it has many terms.  Note that this formula gives you the distance as if you were flying directly from one lat-long to another, not along an actual ground route.  But, once you have it coded in the Formula tool, you can add more points to your route to better approximate the ground route.

AngelosPachis · Answer

Hi @arundaka02 ,

I think the multi-row formula tool might be your friend here. The expression I used was the following :

IF Contains([Row-1:stage],"Destination") THEN [distance (mi)]-[Row-1:distance (mi)]

//If the previous row of the field stage contains the
//word Destination, restart the calc

ELSE ([distance (mi)]-[Row-1:distance (mi)])+[Row-1:distance between dest (mi)]

//Else estimate the distance per destination

ENDIF

and it seems to work fine.

Let me know if that works for you.

Cheers,

Angelos

example.yxmd