Most Efficient Route - Based on Google API & attached data set

[udles5666]

Hi community, 

Still very new to Alteryx & Geo Spatial data. I have got a use case but it is a bit different to the article below. The article indeed was great & helpful but, I am sort of stuck & unsure how to proceed here. 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Google-Maps-Driving-Distance-and-Durat...

I have the below Geo Spatial fields available & I want to calculate the most efficient Route a truck driver should take based on the least distance travelled between the co-ordinates of a fixed Route. 

Available: 

• Latitude
• Longitude
• Addresses
• API Key from Google Maps
• Fixed Route_ID's

Answers, I am looking for? 

• What should be a Start Point & End Point of a Route? 
• What should be Point B, C, D ... End Point in a Route?
• What is the distance travelled in Kms. from Point A to Point B .....and so on in a Route?
• What is the time duration in minutes from Point A to Point B .....and so on in a Route?
• How much would be the total distance travelled in a Route in a day? 
• How much will be the total time (using Google API) duration of the travel? 

Can someone please assist me with this? Thanks a ton in advance..

[AkimasaKajitani]

This is traveling salesman problem.

Please refer this Link.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Traveling-Salesman-with-iterative-macr...

And you can refer this link about Google Directions API.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Google-Maps-Driving-Distance-and-Durat...

If you are stuck, I can help you. Please tell me.

[AkimasaKajitani]

I forgot to tell you.

The latitude field  and longitude field is reversed at your input data.

[udles5666]

Hi @AkimasaKajitani 

Thanks for the tip!

I had a go at this and now I am stuck with few queries.

1. How do I group the workflow on each Route ID from the whole data set - say for example A1. meaning Route ID - A1 only has 5  stores within that Route. I only want to see the most efficient route in that particular Route. 

2. How do I get the data in Kilometers?  

3. How do I get the travel time in HH:MM:SS format? 

Thanks heaps for helping. Attached for reference. 

[AkimasaKajitani]

Hi @udles5666 ,

Q1.How do I group the workflow on each Route ID from the whole data set - say for example A1. meaning Route ID - A1 only has 5 stores within that Route. I only want to see the most efficient route in that particular Route.

A1.
I see what you want to do. It would be more efficient to use then Join tool than use the Field Append tool. Using Join tool, you will set "Route_ID" to "Join by specific fields".
Please see attached file.

Iterative macro is the best solution to decide the most efficiency route.

Ideally, the best route would be found by calculating the all pattern.
But at this time, my workflow is calcurating from the first record as the starting point to most near point.

The problem of this way is how you choose your first point that makes a huge difference in the outcome.

Please see attached file.

If you really want to use an algorithm to determine the best route, please see the traveling salesman problem.

https://en.wikipedia.org/wiki/Travelling_salesman_problem

[AkimasaKajitani]

Hi @udles5666 

Next Answer.

Q2.How do I get the data in Kilometers?

A2.

Please see the units section at Google Maps API manual.
https://developers.google.com/maps/documentation/distance-matrix/overview?_ga=2.100915265.1171408197...

Now you set "units=imperial" at request URL, but you have to set "units=metric" when you required the distance by kilometers.

Now : "https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial&origins=" + REGEX_Replace([Concat_Coords], "([^\|]+)\|(.*)","$1&destinations=$2")+ '&key=' +[APIKey]

By kilometers : "https://maps.googleapis.com/maps/api/distancematrix/json?units=metric&origins=" + REGEX_Replace([Concat_Coords], "([^\|]+)\|(.*)","$1&destinations=$2")+ '&key=' +[APIKey]

Q3. How do I get the travel time in HH:MM:SS format?

A3.
You have to re-calculate using the "duration_text" field.