Show/Hide Toolbars

MPCluster for Maptitude

Navigation: Examples

Example: Using an External Table

Scroll Prev Top Next More

This example shows you how to use MPCluster with an external distance table. MPCluster will usually use straight line ('great circle') distances to determine cluster sizes and separation. If you choose to use the Hierarchical algorithm with Median centers, you also have the option to use your own pre-computed distance table. This would typically be a driving distance (as with this example), but it could also be a travel time, or an actual calculated cost. This option gives clusters that more accurately reflect the local geography (e.g. natural boundaries such as rivers and mountains) and/or actual provisioning costs.


The sample data is a subset of the restaurant data used in the Finding Clusters of UK Restaurants example, and consists of restaurants in northern Scotland. The source data file is called scot_restaurants.xlsx and contains one worksheet called restaurants. It can be found in the MPCluster examples file:


It is assumed that you are familiar with basic Maptitude operations. Import this worksheet into Maptitude as a data view. Note that the worksheet includes coordinates in both conventional degrees (Decimal Longitude, and Decimal Latitude) as well as the Maptitude convention of millionths of a degree (Longitude and Latitude). Te import can be performed using the Create-a-Map Wizard or File->Add on the main menu. The resulting map will look something like this:




Compared to the other examples, this data is more sparse although a number of possible clusters are visible (e.g. Inverness and Aberdeen).


The external distance table can be a text file (e.g. comma separated or CSV file) or an Excel worksheet. Both follow the format produced by our Milecharter add-in product. Here we use the Distance worksheet of route distances in the distances_tables.xlsx workbook. Note that the workbook also has a worksheet (Time) of calculated travel times. This is what the beginning of the table looks like:




The table has the location identifiers along the top and left axes. The actual distances then make up the rest of the table. Note that the distances are bidirectional (i.e. the same in both directions), and MPCluster will ignore any duplicates. In the example above, the distance from location 81 to location 5, is "24.37" (miles), and the distance from location 5 to location 81 is also "24.37".


We wish to find clusters in this data in order to efficiently allocate sales representatives with areas that have the most restaurants. Start MPCluster by selecting MPCluster on Maptitude's Tools->Add-ins menu. This will display MPCluster's main panel. Set the parameters as follows:




Refer to the previous examples for an explanation for most of these settings. We are searching for small clusters that are smaller than 15 miles ('external table' units) in size and have between 5 and 50 restaurants.


We have selected the Hierarchical algorithm with Median centers. This has enabled the Distances box. We have set the Use an External Table check box, to tell MPCluster that we wish to use the pre-computed route distance table from above. Set the Dataset Name File to ID. This is the field that MPCluster will use to cross-reference Maptitude data points with the table's data. The ID values can be found on the left and top exes in the table. This should be a unique identifier or name. Press the Set External Table button to set the table's file details using the External Distance Table dialog box:




Select the Type of distance table as Excel Worksheet, and select the input workbook by pressing the "..." button. MPCluster will scan the workbook and list the available worksheets. Select Distance. For text files, the Worksheet list will be disabled, but the Field Separator will be enabled. Valid text file field separators are comma, tab, and semi-colon. Comma separators allow you to use "CSV" files.


Next press Start on the main panel to start processing. As with the previous examples, MPCluster will prompt you for the FFA file for the new data view that stores the cluster allocations and will be joined to your input data. It will also ask you for the output file prefix for the new data layer DBD files. MPCluster will then start the processing and display a processing indicator.




This is a small dataset, and processing will complete in seconds.  Here are the final results:




Most of the clusters are very small and concentrated on individual towns and large villages, hence most are only visible by their central triangles. You may zoom in to see the actual clusters. Here is a view of the largest city (Aberdeen) which was large enough to create two clusters:




Note that Aberdeen has more than 50 restaurants, so MPCluster has split it into two clusters. Both have centers in central Aberdeen, but also include outlying restaurants that are also within the specified 15 mile maximum driving  diameter - e.g. the restaurants in Stonehaven.


Further details on how to use external tables can be found on the Using an External Distance Table page.