calc distance between all possible combinations

GARAD_RUSHIKESH

Hi,

I want to know if the following can be done on Alteryx, if yes, how?

I have 2 lists of locations in Excel (I have their latitudes and longitudes). I want to calculate the distance between all possible combinations that can be made between list 1 and 2. and then I want to know which of the locations in list 2 are closest to locations in list 1.

I know this can be done using the haversine formula in excel, but my list is too large for excel to handle.

Datasets

Google Analytics

Accepted answers

Hi @GARAD_RUSHIKESH

Here is what I recommend

1. Use the Create Points tool to turn your lat/longs into spatial objects

2. Use the Append Fields tool to attach every record in set 1 with every record in set 2

3. Use the Distance tool to calculate the distance between each pair of points

4. Sort your data by distance

All comments

Hey Garad,

I believe what you need is a "Cortesian Product" or Combination of your locations - everything to everything. Simple 3 steps:

1. Add a dummy column with a Formula tool say "JoinBy" = 1 to both tables

2. Use Join tool and use the JoinBy column as merger

3. Use the Distance tool now!

Let me know if you need more assistance.

Best,

Oly

Hi @GARAD_RUSHIKESH

Here is what I recommend

1. Use the Create Points tool to turn your lat/longs into spatial objects

2. Use the Append Fields tool to attach every record in set 1 with every record in set 2

3. Use the Distance tool to calculate the distance between each pair of points

4. Sort your data by distance

Correct, Append in this case will work same as join on all!

Quick Links

Tags

This months top contributors

atcodedog05 19458

Qiu 15866

MarqueeCrew 13708

apathetichell 13703