Alter Longitude / Latitude to Increase Distance Between Centroids

[bertal34]

Background: Large retail company with many stores throughout the US.  Sometimes 2-4 stores are in close proximity to each other, like in shopping malls.  Alteryx is used to generate the centroid of each store, based on longitude/latitude.  This data is used in Tableau to generate maps.

Issue:  Stores in close proximity to each other have overlapping marks in Tableau, making it appear as 1 store instead of 2-4 stores.  In most cases, cannot tell it's multiple stores unless zooming in extremely close, which is not a solution.

Possible Solution: I'm able to identify which stores are in close proximity to each other.  My thinking is that I can manually provide separation by altering the longitude / latitude of these stores.  I have provided an example with 4 stores close to each other.  If I could figure out how to loop through them and move the northern most store (max latitude) further north, the southern most store (min latitude) further south, the eastern most store further east, the western most store further west.  This is the part I'm having trouble coming up with a solution for.

I have attached example data of 4 "overlapping" stores along with screenshots of how 4 stores can appear as 1.

[JosephSerpis]

Hi @bertal34 I wrote a blog about this with a similar problem you can apply the same logic here. 

https://intersectionsandoverlaps.wordpress.com/2019/02/25/dispersing-spatial-points-with-alteryx/

[meeravijayan2011]

Hi @bertal34 

If you need to jitter the points in a map in tableau here is a nice blog about it. 

https://community.tableau.com/people/sarah.battersby.0/blog/2017/07/19/jitter-those-points

[Claje]

Hi,

I built a macro that is pretty limited in functionality to do this loop you're looking for.

This macro pretty specifically only works on cardinal directions and will not disperse more than 4 points, but should work for 2-4.

I've attached an example workflow using this macro.  I had a sample tool for debug purposes to limit records.

You can configure how large the buffer should be.  I set it up to support a min of .1 miles and a max of 10.

While this macro is quite limited, it should do what you're looking for.

If you're looking for a dispersal methodology from a singular point, the other responses to this thread cover that well.

[bertal34]

Thank you all for your responses, I greatly appreciate you providing valuable feedback.  I'll take a look at the solutions and let you know how it works out.

[bertal34]

@Claje 

Thank you so much this macro is absolutely fantastic!  My only questions is, I only provided one set of close stores (4).  The larger data set obviously has multiple sets of close stores (2-4 stores in each set).  Would the macro need to be altered to work with each set?

[Claje · Accepted solution]

Probably slightly.

Here's a tweaked version that adds an option to group by a common identifier.

To do this you'll just need some common field for each set of records.  EG a "group number" field.

In this example I hardcoded a "Group Number" of 1.  If you can identify these sets, use that field/identification as the group by.

Take a look at the macro's configuration as it is slightly different (there are 3 fields you have to fill out for grouping as this is now a Batch Macro).

[bertal34]

@Claje I noticed that any additional data fields being passed through the macros do not retain their data.  Additional fields are all being set to [Null].  Is there a simple change to allow additional data to pass through untouched?

[bertal34]

@Claje Hey hope you are doing well!  Just wondering if you could help me understand how to pass additional fields through the macros and retain the data?  This is the last part I need to add before putting the macro into production.  Appreciate your help!!

[neilgallen]

Why not join the results of the macro back to your original data instead of worrying about passing them through the macro? they're not needed for the macro calculation anyway, if I'm understanding it correctly.