# 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:

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 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:

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.

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:

###### 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.

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.