Finding minimum of result coulmn whose inputs in same column

Question

Hi,

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

GroupStoreLatitudeLongitude1a10.11112.2221b12.22216.33231c16.74512.2222d18.323293.2332e12.334417.4322f17.34313.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 Answer

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-p/108975#M208

david_fetters · Accepted Answer

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

* Create points for all Lat/Lng pairs using the Create Points tool
* 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....)
* Filter out records where Store = Right_Store (to get rid of store A joining to store A)
* Create distances between your SpatialObj and Right_SpatialObj using the Distance tool.
* Sort records by Group ascending, then by Distance ascending
* 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!