5 Replies Latest reply on Nov 30, 2018 1:17 PM by Wilfred Eze

    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.

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

              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?
                Sarah Battersby

                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?
                  Wilfred Eze

                  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.