Hub and spoke Model
There are a problem when you need to create some branches or agency as a batch. To find out which of them can be set on a category its recommend to use the hub and spoke model. Assume there are a range of agencies throughout the world like the following picture:
The hub and spoke model is not our main topic, for more information about this model please just google “hub and spoke model”.
Here we are going to solve a problem practically.
The question is “How you can set you central hub?” or “How many hubs do you need?” how much is the capacity of them? how many agencies could be connected to the each hub directly?
To answer correctly we need gather the information of each agencies. In our case there are more than 300 agencies that we get them as the following:
The problem will be more complex whenever the information changes or need to add a new agency. So whats the solution?
Our solution and software
Assume a list of new agencies in the table DigiKalaDCs:
Then calculate the distance of every agencies from the new one.
So each new agencies could be connect with one or more the old agencies.
To solve this problem we must create a matrix table to calculate the distance or each agencies to another.
The matrix table is designed from a union query like this:
You need to know how we can calculate the distance between to point by geographical location?
There are a few web services help us to answer our request. first of all you need to get an API key.
We use the services of Bing. for more information click here
After you get the Private key write the code in your project as the picture says:
The API key is private and it should not be shared.
Now its ready to use function getDistance() for each two points.
Programming by VBA language in Microsoft Access
First download the module JsonConverter and add it to the your project. and then to run the script as well you must add the Microsoft scripting Runtime to the references:
Then create a button to your project to do your command for all records.
By a loop method it will happen simply as in the following picture you see:
The rest of code that is necessary to get correct result is like:
Const BASEURL As String = “http://dev.virtualearth.net/REST/V1/Routes/DistanceMatrix?origins=”
Const BINGKEY As String = “?????????????????” ‘your API Key
Public Function GetDistance(FirstLocation As String, SecondLocation As String)
Dim JSON As Object
Set JSON = JsonConverter.ParseJson(HttpRequestToBing(FirstLocation, SecondLocation))
GetDistance = Trim(JSON(“resourceSets”)(1)(“resources”)(1)(“results”)(1)(“travelDistance”))
Function HttpRequestToBing(FirstLocation As String, SecondLocation As String)
Dim BingURL As String
Dim http As Object
‘ Setup the Map URL
BingURL = BASEURL & (FirstLocation) & “&destinations=” & (SecondLocation) & _
“&travelMode=driving&avoid=minimizeTolls&du=km&key=” & BINGKEY
‘ Setup the request and authorization
Set http = CreateObject(“MSXML2.ServerXMLHTTP”)
http.Open “GET”, BingURL, False
http.setRequestHeader “User-Agent”, “Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)”
HttpRequestToBing = http.responseText
Private Sub Command24_Click()
DoCmd.GoToRecord , , acFirst
While Me.CurrentRecord < Me.Recordset.RecordCount
If Not IsNull(Me.GeoLocation) Then
Me.Distance = GetDistance(Trim(coordinate), Trim(Me.GeoLocation))
After you do click on the button Command24 it will start calculate distance between coordinate and GeoLocation then set the result on the field Distance.
Then you can create a cross-tab query to see the distance in 2 dimensions.
Its all about update your batch if there is enough capacity. It’s clear that there are some limitation in the Hub and spoke model. although the machinary can be upgrade, its better to work by the maximum capacity of them. You put all the points that are close together in one area. And you add up enough points to reach your machine’s maximum capacity.