Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Finding minimum of result coulmn whose inputs in same column

Manojkumar
8 - Asteroid

Hi,

 

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

 

GroupStoreLatitudeLongitude
1a10.11112.222
1b12.22216.3323
1c16.74512.222
2d18.323293.233
2e12.334417.432
2f17.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 

2 REPLIES 2
LordNeilLord
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...

 

david_fetters
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