-
1. Re: Distance analysis between multiple points?
naresh.suglani.0 Nov 1, 2018 2:59 AM (in response to 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.
-
Distance Analysis.xlsx 51.9 KB
-
-
2. Re: Distance analysis between multiple points?
Ombir Rathee Nov 1, 2018 5:44 AM (in response to naresh.suglani.0)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 Nov 1, 2018 5:58 AM (in response to naresh.suglani.0)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 Nov 1, 2018 6:01 AM (in response to Ombir Rathee)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 Nov 1, 2018 7:25 AM (in response to naresh.suglani.0)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.
-
Distance Analysis.xlsm 39.3 MB
-
-
6. Re: Distance analysis between multiple points?
naresh.suglani.0 Nov 1, 2018 9:43 AM (in response to Ombir Rathee)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 Nov 1, 2018 2:03 PM (in response to naresh.suglani.0)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.
-
Distance analysis_v10.5.twbx 34.8 MB
-
-
8. Re: Distance analysis between multiple points?
naresh.suglani.0 Nov 2, 2018 1:46 AM (in response to Ombir Rathee)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 Nov 2, 2018 5:45 AM (in response to naresh.suglani.0)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.
-
Distance Analysis.twbx 4.8 MB
-
-
10. Re: Distance analysis between multiple points?
naresh.suglani.0 Nov 2, 2018 7:51 AM (in response to Ombir Rathee)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 Nov 7, 2018 5:49 AM (in response to Ombir Rathee)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 Nov 7, 2018 5:49 AM (in response to naresh.suglani.0)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 Nov 7, 2018 5:58 AM (in response to Ombir Rathee) -
14. Re: Distance analysis between multiple points?
naresh.suglani.0 Nov 23, 2018 5:16 AM (in response to Ombir Rathee)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.
FF1552 ME15 9TU 51.252799 0.527121 FF1553 PA23 8HF 55.961309 -4.911067 FF1344 TF10 7AP 52.766708 -2.377618 FF1345 TF2 7DP 52.71396 -2.453039 FF1546 LE10 1DA 52.54179 -1.369693 FF1547 DH3 3AZ 54.852842 -1.573174 FF1342 B45 9JW 52.393647 -2.018839 Many thanks