2 Replies Latest reply on Jul 5, 2018 4:17 AM by Tim Dines

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

    Jakub Grudnik

      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?
          Paul Cawford

          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?
            Tim Dines

            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.