Matching Customers and Sales Offices

Maptitude can be used to calculate driving distances and estimated travel times. It can be tedious to calculate lots of these routes, but the process can be automated using our MPMileage* and MileCharter* add-ins. MPMileage* calculates point-to-point routes listed in a database. This can save a huge amount of time, but it requires an existing list of all the required routes to calculate.

What if you need to calculate all combinations between a group of data points ('pushpins')? This is where our MileCharter* add-in is useful. Often you are only interested in the data points, and MileCharter* can be used to efficiently find only the closest data points that meet certain criteria (see below).

First we look at an example with two groups of pushpin locations ("pushpin sets"):

Two Maptitude data layers of point locations: Texas (red stars) and Louisiana (blue stars)

Here we have a map with two data layers: Texas cities (red stars) and Louisiana cities (blue stars). MileCharter* can quickly find the distances and travel times for all combinations between these two data layers. Here are the results when MileCharter* is set to find the fastest route distances:

Results showing all driving distances from each Texas city to each Louisiana city.

This example uses two different data layers which specify the start (TX) and end (LA) locations. MileCharter* can also use the same data layer for the start and end locations. This results in a 'road atlas' table which lists all route distances between all pairs of locations within the data layer.

MileCharter* can also work with Maptitude selection sets, limiting the data points to a specific subset of the data layer. MileCharter* can calculate the shortest routes, fastest routes, and straight line distances; and estimated costs can be calculated using a rate table.

Find only the Closest Locations

Often we are only interested in the closest locations. For example, we might be trying to match customers with their closest store locations. We could use two data layers as above, but this results in a large table of numbers and further work is required to extract the closest destinations. It is very inefficient in both computer time and user time. Luckily the Professional license of MileCharter* has an option to only report the closest destinations. As well as limiting the results to only the closest destinations, MileCharter* is clever enough to only calculate the routes which have a chance of being included in the final results. This results in huge improvements in processing time.

Here is an example using data taken from Larry Petersen's POI Mega File (this has been imported into Maptitude 2015 using the new MapPoint import option). We are using Lowe's Home Improvement stores for our store locations (blue circles). The customers are marked with red triangles, and are actually derived from RV camp locations:

Map of store (blue circle) and customer (red triangle) locations.

These are large datasets with 1714 store locations and 264 customer locations. This works out at almost half a million potential routes to be calculated, and would take well over 120 hours for MileCharter in "calculate all" mode. We could work with straight line distances and these would be fast to calculate, but they would not be as accurate. For example, a straight line distance might be short, but the travel time might be long due to the existence of a river or mountain. Similarly, it might be easier to access a more distant location because a fast highway happens to go in that direction. Therefore we really want to find the fastest route distances, and not the straight line or shortest route distances.

MileCharter's* "find closest" functionality can solve this problem. It can also apply constraints such as the number of destinations to find, the maximum driving time, and the maximum driving distance. Here we have MileCharter* set to find the two closest stores (destinations) that are within 90 minutes driving time of each customer:

MileCharter set to find the two closest stores for each customer, that are also within 90 minutes driving time.

MileCharter* can calculate the results very quickly. This example typically takes less than an hour to run, and not the predicted 120 hours to calculate the full distance table! Here is the beginning of the results table:

We actually calculated the distances and the travel times. These are reported on separate worksheets (tabs), and the above worksheet shows the distances. The first column (A) displays each customer. The closest store is in column B with the distance (in miles) in C. The second closest store (if any) is in column D with the distance in column E.

Note that a couple of the customers do not have any stores that are within 90 minutes. This is due to them being located in remote, rural locations — away from the stores which tend to concentrate in and around cities.

*Maptitude is a replacement for the discontinued MPCluster, MileCharter, and MPMileage plugins