Hub and spoke Example

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:

hub and spoke example

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:

Agancies

The more important of above information is the coordinate of each agency. you can find the geographical location of each point by google map, bing,…

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:

matrix table

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:

bing API Key

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:

references

Then create a button to your project to do your command for all records.

button

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”))
End Function
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)”
http.send
HttpRequestToBing = http.responseText
End Function
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))
End If
DoCmd.GoToRecord Record:=acNext
Wend
End Sub

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.

2 dimension query

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.

پاسخی بگذارید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *