This example shows you how to use MPMileage to compute the mileages for two sales representatives using route segments listed in a Microsoft Access database. Locations (i.e. customers) are referenced using named locations in a Maptitude point layer. In contrast to the other examples, this example uses Maptitude's UK Country Pack. This example also requires Microsoft Access (2007 or later) and Caliper Maptitude (2014 Build 2835 or later).
The example uses the sample_uk.accdb database found in the MPMileage for Maptitude samples file:
The database file sample_uk.accdb contains two tables: locations and routes. locations stores all of the named locations. These are primarily the customers, but we also have one called 'home' for the home office. This is used as the start and end of the two circular routes. The locations table looks like this:
Each location is located using a street address. You will need to import this address table into Maptitude as its own layer. Call the new layer 'locations'. The map should look something like this: (the locations are marked with red circles and text for clarity)
The route segments are listed in the routes table:
Two circular routes are listed here, but each row specifies one route segment to calculate. The ID field is not used by MPMileage when using named locations and will be ignored. You should, however, have a unique ID ("primary key") if you are referring to locations by coordinates or street addresses.
The startloc and endloc fields refer to the required start and end point locations in the locations layer. The distance, time, cost, and errors fields will hold the calculated results. The errors field is defined as text. The other three fields can be floating point numeric (recommended) or text.
Setting the MPMileage Parameters
Start MPMileage. The parameters may be different, but you will be presented by a dialog box that looks like this:
Press the Database Change button to display the Database Parameters dialog box. Set the database Type to "Access 2007 onwards (accdb)". Press the Data Source "..." button to select the Access ACCDB file that will be the data source. MPMileage will load this and scan it for the available tables.
Select the routes table from the list of available tables. This table contains our required routes and will store our results. MPMileage will scan the table and fill the remaining controls with the possible column options. Select the Locations as "Named Data Points" and select the Maptitude dataset (layer) containing the points (locations). You should also select the dataset's field (Name Field) that contains the identifying names for these points (locations.locname). These are the names used in your route database to identify the start and end locations.
You will need to set the columns in the input database table that store these start and end locations. Do this by pressing the Set Columns button. This displays various location drop-down boxes that are used to set the start and end locations. This example works with named locations, so there are only two drop-down boxes displayed. Set them as follows:
This dialog box will have more drop-down boxes if you have selected street addresses or coordinates for the location types. For example, street addresses require drop-down boxes for streets, cities, zipcodes/postcodes, and country. These options will also require a primary key (i.e. unique identifier).
Press OK to return to the Database Parameters dialog box.
We complete the Database Parameters by selecting the required output columns. You must set at least one of the output columns, otherwise MPMileage will not have any work to process. The resulting Database Parameters dialog box will look like this:
The Row Selection box lets you select a specific subset of rows for processing. For example, you might want to process rows which had not been completed in a previous run.
Set this to select all rows to process all of the database rows.
Press OK to save all of these settings and to return to the main dialog box.
Select the required routing parameters. Note that some of these options might be disabled if they are not required for your selected data. For example, the time units are not required if you have not selected route times. Press the Road Speeds button if you wish to modify the road speeds. Press the Costs button to edit the costs. You can specify a fill rate table that is based on route distance. We will just set a constant rate of £0.20/mile:
Once everything has been set, return to the main panel and press Start to start processing. This is a very short database and should not take long to compute. The resulting routes table should look something like this:
The exact numbers may vary according to the route parameters that you have selected. The Cost field was created as a Currency type - hence the dollar signs. You could change Access's format settings to display the correct pound signs.