1 2 Previous Next 15 Replies Latest reply on Nov 23, 2018 11:55 AM by Ombir Rathee

    Distance analysis between multiple points?

    naresh.suglani.0

      Hi all, is it possible to do the following in Tableau, and if so could someone show me please?


      Shop code
      Shop within 0.5kmShop within 1kmShop within 2kmShop within 3km

      1

      2
      3
      4

       

      I need to get an idea of shops within close proximity of each other and have a list of about 850 shops in the UK. I can supply a list of postcodes if it helps. I have done distance analysis before of all these shops from a central point in the UK but this query is asking to find the close proximity of stores to one another to maximise marketing campaigns.

        • 1. Re: Distance analysis between multiple points?
          naresh.suglani.0

          I have now added a list of all shops, together with longs and lats.

           

          What I would like is, for each shop in the list, have a table that shows all shops within 0.5km, 1km, 2km, and 3km of that shop.

           

          Also to be able to display that on a map would be useful for visual purposes.

           

          I'm hoping this has been done before or that someone would kindly assist me to find a solution.

          • 2. Re: Distance analysis between multiple points?
            Ombir Rathee

            You have 832 unique shops in your table. Therefore, there are total 832*832 -832 = 691392 combinations available. First you need to find out the distance between each shop to all other shops. Then you can filter the shop within close proximity of 3km.

             

            Google api distance matrix can do the work for you but its paid like 4USD per 1000 hits. As you have around 691392 unique combinations then it will cost you around 2800 USD. Thats a huge amount. Earlier it was cheap and I created database of around 400000 combinations with just trial version of two different 300 usd accounts. I don't know if they still offer trial version.

             

            You need to narrow down you shop combinations like there is no need to find the distance between shops that are in different city because that will be more than your limit of 3km. Once you've the distance database in place then further analysis can be done.

             

            Hope this helps.

            • 3. Re: Distance analysis between multiple points?
              Ombir Rathee

              Wait, Just remember I used the api matrix because I had the city names instead of latitude and longitude. It can be done with excel without using google api distance matrix. Will get back to you with solution.

              • 4. Re: Distance analysis between multiple points?
                naresh.suglani.0

                Hi Ombir, many thanks for your replies.

                 

                Would be great to get a solution for this problem. I appreciate that Tableau isn't a bespoke mapping product but it's the only tool we have in the office apart from Excel and Access. I vae managed to install QGIS but not used it before so need to learn how to use it for more detailed analysis.

                • 5. Re: Distance analysis between multiple points?
                  Ombir Rathee

                  I used the vincenty formula to find the distance between two points using Latitude and Longitude. This is not the road distance between two points. You need to check if this meet your requirements. If not then you've to take help of google api distance matrix to perform the analysis.

                   

                  1 of 1 people found this helpful
                  • 6. Re: Distance analysis between multiple points?
                    naresh.suglani.0

                    Hi Ombir, many thanks for this I've checked a few of these now and all seems to be working fine.

                     

                    I see you did this using macros - are you able to explain how this was done and what the macro does? The VBA code behind it looks very complex!

                     

                    Also, would it be possible to have this built into Tableau where I could select a shop, set the distance, then see all the other shops within that distance? Not sure if that is possible but would be a great addition to the data table.

                    • 7. Re: Distance analysis between multiple points?
                      Ombir Rathee

                      Yeah its a very complex code and it requires Master level knowledge of Mathematics. I am also not able to fully comprehend it. You can visit this link to know more about the logic behind this code http://en.wikipedia.org/wiki/Vincenty%27s_formulae

                       

                      Basically its a function and you can use it like any other formulae in excel. Just type =distVincenty() in any cell and pass the four parameters i.e lat1,lon1,lat2,lon2.

                       

                      Once the database is ready then its fairly easy to find the shops within specified distance in Tableau.

                       

                      1 of 1 people found this helpful
                      • 8. Re: Distance analysis between multiple points?
                        naresh.suglani.0

                        Hi Ombir,

                         

                        Thanks again, it now looks like I can easily use that in Tableau to select the distance and display all those shops within range of one another. Can this be displayed on a map too?

                         

                        Also, if the business requires a list of each shop in the format in my example table above is that also possible?

                         

                        One more thing, if the shop locations change do I simply re-run the macro to update all the distances?

                         

                        Kind regards,

                        Naresh

                        • 9. Re: Distance analysis between multiple points?
                          Ombir Rathee

                          Can this be displayed on a map too?

                           

                          I made the Network Map for Source and Destination. Select any shop and all other shops within vicinity of selected distance will be displayed on Map. I filtered the database to shops that are within maximum distance of 20km. If you want to include greater distance then you've to prepare the data again in format used by tableau for Network Map.

                           

                           

                           

                          Also, if the business requires a list of each shop in the format in my example table above is that also possible?

                           

                          Prepared another sheet in Tabular format.

                           

                           

                          One more thing, if the shop locations change do I simply re-run the macro to update all the distances?

                           

                          You have enter the =distVincenty() formula in Column G of master distance table if new shops are added to your dataset. There is no VBA code to do this. First prepare the format as shown in Column A-F of master sheet and then use the formula in Col G and drag it down to last row to find the distance between source and destination.

                           

                          If this helps then please mark the answer correct or helpful.

                          1 of 1 people found this helpful
                          • 10. Re: Distance analysis between multiple points?
                            naresh.suglani.0

                            Thanks again Ombir, good work and I have learnt a great deal with your help!

                            • 11. Re: Distance analysis between multiple points?
                              naresh.suglani.0

                              Hi Ombir, would this solution be able to solve another query I have?

                               

                              If I have a database of around 300,000 customers based around the UK and I need to find the nearest store and distance to that store, would it be possible to adjust this solution to achieve that? It would involve around 850 store locations, and I realise this would only be a straight line distance as opposed to road distance.

                               

                              I can send a sample if you prefer?

                               

                              Thanks in advance.

                               

                              Naresh

                              • 12. Re: Distance analysis between multiple points?
                                Ombir Rathee

                                Please start another thread for a new question. I will see what can be done.

                                • 13. Re: Distance analysis between multiple points?
                                  naresh.suglani.0

                                  Hi Ombir I've started one here..........

                                   

                                  Nearest store analysis?

                                   

                                  Thanks in advance.

                                  • 14. Re: Distance analysis between multiple points?
                                    naresh.suglani.0

                                    Hi Ombir,

                                     

                                    I am having trouble understanding the process to add a few more branches to my list and calculate the distance as you showed in the attached xlsm workbook. You mentioned above to set up the data in columns A-F but I was unsure how you did this.

                                     

                                    Are you able to explain and also add in the extra data for the additional branches below please?

                                     

                                    I added the new branches to the branch list tab then ran the macro but I got a debug error.

                                     

                                       

                                    FF1552ME15 9TU51.2527990.527121
                                    FF1553PA23 8HF55.961309-4.911067
                                    FF1344TF10 7AP52.766708-2.377618
                                    FF1345TF2 7DP52.71396-2.453039
                                    FF1546LE10 1DA52.54179-1.369693
                                    FF1547DH3 3AZ54.852842-1.573174
                                    FF1342B45 9JW52.393647-2.018839

                                     

                                    Many thanks

                                    1 2 Previous Next