6 Replies Latest reply on Feb 5, 2019 6:29 AM by Sukhchain Jangla

How to determine stores within a given radius of other stores on a map?

How would one do this for a relatively large dataset (e.g. 100,000 locations+) of lat/long coordinates? This dashboard is an example of exactly what I'm looking for:

https://public.tableau.com/views/StarbucksProximityAnalysis/StarbucksProximityAnalysis?:embed=y&:showVizHome=no&:display_count=y&:display_static_image=y&:bootstrapWhenNotified=true

Having some trouble deciphering how they are able to accomplish this. Is the heavy lifting analysis of distances between each point being done as prep using a third party software, or is this something that Tableau is taking care of?

What first comes to mind is cross-joining the data on itself and determining distance between each of the points, but this won't be a scalable solution for this many locations. Trying to get a sense of which direction I should go in. Have SQL experience but willing to learn some new skills to get this done. Appreciate any tips!

• 1. Re: How to determine stores within a given radius of other stores on a map?

Hi Jakub,

As a starting point the Tableau public dashboard you have referenced is pre calculated.  The 'distance' filter only has pre-calculated values for yes and no and only allows 0.25 to 1 in the distances at 0.25 steps as these are the values that have been pre calculated.

As for your dataset it sounds like an inefficient task to pre-calculated distances for all iterations (100,0002 = 1,000,000,000 or 1 billion combinations)

Assuming that you have decimal latitude and longitude figures the distance between two points can be calculated as:

```ACOS(COS(RADIANS(90-[Latitude1])) *COS(RADIANS(90-[Latitude2])) +SIN(RADIANS(90-[Latitude1])) *SIN(RADIANS(90-[Latitude2])) *COS(RADIANS([Longitude 1]-[Longitude 2]))) *6371
```

The above formula will work in tableau although as stated the 1 billion combinations is too many to be feasible.

Instead, to replicate the dashboard that you looked at, I would look to pre-calculate the minimum distance to the nearest site, outside of tableau i.e. each of the 100,000 data items may look like:

Store name, Latitude, Longitude, Distance to nearest store, any other data items...

I've attached an example of this in tableau with a map.  Please note the map looks awful as I've randomly generated worldwide co-ordinates completely at random for 1000 data items.

As for calculating the initial data set.  This 'could' be done in Excel as a one off although for 100,000 data items this would take some time.  I've attached an excel spread sheet with a Macro showing one way to achieve this.  Hopefully you are familiar with VBA although this macro has been quickly written and is reliant on cell references but hopefully you get the idea.

Finally, the 'circles' around the stores are created in the example using pre-calculated paths.  In the example you've linked to you will actually see these are 16 sided polygons.  I have not implemented this in my example as this would multiply the data set by 16 times.  For completeness the data in the starbucks example is for 9,714 stores but actually contains 887,523 lines of data to facilitate distances and the circles.  The example I've given without circles only requires one line per store.

I hope this points you in the right direction.  That said this is only one possibility, there are many tableau experts on here who may have a better solution.

Kind Regards,

Paul

1 of 1 people found this helpful
• 2. Re: How to determine stores within a given radius of other stores on a map?

Tableau Public is blocked here at work, but if you go there and search my name, you will find a dashboard created for the BMV that has the pieces that you need in order to do this.

• 3. Re: How to determine stores within a given radius of other stores on a map?

Hi everyone, I am very new to tableau and currently faced with a problem like this.

I have a shapefile containing Saskatchewan Population (Census 2016) per Dissemination Block Units. Combinations of many of these block units make up a town/city or metro. My challenge is to draw concentric circles using the city center lat/long as the center and be able to find sum of population living within circles by increasing or decreasing the circle radius.

So far I am able to create stand alone circles around the city center lat long, but not able to combine the circles with the population data (shapefile). Can anyone help me with this? Thank you.

• 4. Re: How to determine stores within a given radius of other stores on a map?

Hi Wilfred,

If you centroids for the block units as an attribute in your dataset, you could consider using a variant on this trick with Set Actions to do the selection of all block centroids within the specified radius around a selected city center: Set actions for dynamic distance calculations

I think that if you are trying to do something that involves finding any block polygon that intersects at all with your concentric circles that is probably going to involve some more complicated analytics - either working with the data in SQL Server (since we support SQL Server spatial data types and you could run some complex queries using their spatial functionality), or doing some other external processing to pre-calculate out the possible intersections.

-Sarah

Tableau Research

• 5. Re: How to determine stores within a given radius of other stores on a map?

Thanks Sarah,

I have a separate excel file of the city centroids (lat and long). I am not sure how the post you referenced would work with mine, but I have made a separate post about this and also attached a workbook. Could you take a look and help? Find it here Help on mapping population census blocks using dynamic radius filter.

• 6. Re: How to determine stores within a given radius of other stores on a map?

For large data sets leverage the power of Database Engines (Oracle/MySQL) to calculate distances between each point.

If data resides in a sheet or flat file, then first dump it in an Oracle table using Alteryx/ SQL Loader.

Using cross join with the same table, distance of each store can be calculated from every other store.

Following SQL can be used as custom SQL in Tableau Extract.

SELECT a."Store ID",

a."Store Name",

b."Store ID" other_store_id,

b."Store Name" other_store_name,

a."latitude",

a."longitude",

(3959 * ACOS ( SIN((a."latitude")/ 57.29577951308232087679815481410517033235) * SIN((b."latitude")/ 57.29577951308232087679815481410517033235)

+ COS((a."latitude")/ 57.29577951308232087679815481410517033235) * COS((b."latitude")/ 57.29577951308232087679815481410517033235) * COS((b."longitude")/ 57.29577951308232087679815481410517033235 - (a."longitude")/ 57.29577951308232087679815481410517033235) )) distance

FROM "Store" a

CROSS JOIN "Store" b

WHERE a."Store ID" <> b."Store ID"

ORDER BY a."Store ID"

--To get the distance from nearest store, use :

SELECT a."Store ID",

a."Store Name",

a."latitude",

a."longitude",

MIN(3959 * ACOS ( SIN((a."latitude")/ 57.29577951308232087679815481410517033235) * SIN((b."latitude")/ 57.29577951308232087679815481410517033235)

+ COS((a."latitude")/ 57.29577951308232087679815481410517033235) * COS((b."latitude")/ 57.29577951308232087679815481410517033235) * COS((b."longitude")/ 57.29577951308232087679815481410517033235 - (a."longitude")/ 57.29577951308232087679815481410517033235) )) distance

FROM "Store" a

CROSS JOIN "Store" b

WHERE a."Store ID" <> b."Store ID"

GROUP BY a."Store ID" ,

a."Store Name",

a."latitude",

a."longitude"

ORDER BY a."Store ID"