Finding minimum of result coulmn whose inputs in same column

[Manojkumar]

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 

[LordNeilLord · Accepted solution]

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...

[david_fetters · Accepted solution]

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!