alteryx Community

# Alteryx Designer Desktop Discussions

SOLVED

## Finding minimum of result coulmn whose inputs in same column

8 - Asteroid

Hi,

I have the Latitude and longitude data of stores in the following format.

 Group Store Latitude Longitude 1 a 10.111 12.222 1 b 12.222 16.3323 1 c 16.745 12.222 2 d 18.3232 93.233 2 e 12.3344 17.432 2 f 17.343 13.6565

I am trying to find the nearest store for each store (for which I have a formula to calculate distance using lat/long). I have to do it within each group, and result has to be distance of the nearest store for each store. I read about Batch macros, but finding it difficult to map the flow. Can someone please help me.

the formula is use:

ACOS
(
COS([Latitude]*(pi()/180))*COS([Row+1:Latitude]*(pi()/180))
+
SIN([Latitude]*(pi()/180))
*
SIN([Row+1:Latitude]*(pi()/180))
*
COS(([Longitude]-[Row+1:Longitude])*(Pi()/180))
)
*6371

2 REPLIES 2
15 - Aurora

Hey @Manojkumar

No need for that formula in Alteryx, there are included spatial tools which do the same job!

Check out these;

Create Points: Converts Lat/Lng to spatial point

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-Create-Points/ta-p/36653

Distance Tool: finds distance between 2 points

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-Distance/ta-p/87210

There is also some excellent training on community on making the most of spatial tools:

https://community.alteryx.com/t5/Live-Training/Live-Training-Spatial-Analytics-for-Beginning-Users/m...

11 - Bolide

Once you get through the great spatial tools introductory materials posted by @LordNeilLord then I think what you're after is pretty simple.

1. Create points for all Lat/Lng pairs using the Create Points tool
2. Join the datastream to itself (e.g. put it in both sides of the join tool) and join on Group.  That gets you a complete set of all possible store combinations through a cartesian join e.g. a-a, a-b, a-c, b-a, b-b, b-c....)
3. Filter out records where Store = Right_Store (to get rid of store A joining to store A)
4. Create distances between your SpatialObj and Right_SpatialObj using the Distance tool.
5. Sort records by Group ascending, then by Distance ascending
6. Use the Sample tool to take 1 row, grouped by Group.  This will get you the pair of stores in each group with the shortest distance between them.

No need for any great circle nonsense with Alteryx!

Labels