Calculating distance between segments / stages in a journey
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Labels:
- Help
- Spatial Analysis
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
