Need all possible combinations for 18 capital cities.

[abhilash_t]

Hello please anyone can help me, I need to find shortest distance to travel for 18 capitals cities. I need combinations to create that logic. How to create those combinations? 

I tried by using append tool, but it is taking huge time to complete run and the size is too big. Need the simplest solution to get this. suggest me any ideas to do this. or else suggest me how can we build the logic for this by using Macros.

Take the capital cities for an example of 18 Alphabets. 

I'm attaching sample file for 4 capital cities how it needs to be appear.

[FinnCharlton]

@abhilash_t  There are 6.4 quintillion possible combinations of 18 things, I wouldn't recommend trying to make that many rows. You'll need to find an algorithmic way of doing this.

Do you want to find the shortest path that goes between all 18 cities? This is the Travelling Salesman problem, which you can read about here: https://en.wikipedia.org/wiki/Travelling_salesman_problem 

Very maths-y but there are algorithms that can solve / give very good approximate answers to this. Doesn't look easy though.

Alternatively I think the Google Maps API might have something that could help, it has an optimising waypoints feature: https://developers.google.com/maps/documentation/routes/opt-way#:~:text=You%20can%20have%20the%20Rou... 

Hope this is vaguely helpful and good luck!

[Prometheus]

I used the Append Fields tool to create combinations of all 18 cities then filtered out where city=Source_city so I wouldn't be calculating the distance between a city and itself. Then, because my dataset had latitude and longitude (yours may not), I turned those into Centroids so I could use the Distance tool to calculate the distance between the city and the Source_city. I finished the workflow off by sorting on DistanceMiles - Ascending then the Sample tool to give me just one record.

 

[abhilash_t]

Hi Prometheus,

Thanks for your Response.

Here your solution gives only shortest linear distance between two cities amongst all 18 capital locations right.

But I need shortest path to travel all 18 capital locations.

I'm glad if you can suggest a logic to build this.

[PhilipMannering]

@abhilash_t I would try what @FinnCharlton has suggested. Let us know how you get on!