This example shows you how to use MPMileage to compute the distances between a series of US zipcodes. The example requires Microsoft Access and Caliper Maptitude (2014 Build 2835 or later) with the US Country Pack.
The example uses the sample_zipcodes.mdb database found in the MPMileage for Maptitude samples file:
sample_zipcodes.mdb is for Access 2003. An Access 2007 (and later) version of the same database can be found in the same archive and is called zipcode_test.accdb.
Microsoft Excel is a popular choice for data tables, and MPMileage does support Excel even though it is not a true relational database. However, Excel has a number of problems when used as a database. This can make it difficult to setup, as well as inefficient during processing. Zipcodes pose a particular problem because Excel thinks they are numbers. Zipcodes are actually text and MPMileage expects text labels. Although Excel can be forced to store zipcodes as text labels, this is cumbersome. A strongly-typed database should be used instead. Therefore we shall use Microsoft Access for our database.
We will tell MPMileage to use Maptitude to geocode the zipcodes. An alternative would be to create a Maptitude layer of all the required zipcodes before running MPMileage. To do this, you would need to import the Zipcode_list table in the sample database.
MPMileage can use Maptitude to geocode addresses and partial addresses. Zipcodes on their own, are simply partial addresses. The Mileages table in the sample database lists the required routes as source and destination zipcodes. It also has empty fields to receive the results. This is what it looks like:
Each row specifies one route mileage to calculate. The Source and Destination fields refer to the required start and end zipcodes. We are using zipcodes as our names, so they store the zipcodes. They must be text. 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. The Cost field can also be of currency type - as it is here.
Note that we also have a PKey column. This is a unique primary key that is used by MPMileage to identify each row. You must specify this column whenever routes are located using geographic coordinates or street addresses. The primary key must be an integer or a text string. It cannot be a floating point number or decimal. It does not need to be specified for Maptitude Data View sources.
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 1997-2003 (mdb)" or "Access 2007 onwards (accdb)" if using Access 2007 (or later). Press the Data Source "..." button to select the Access MDB file that will be the data source. MPMileage will load this and scan it for the available tables.
Select the Mileages 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 output columns, and set the Input Columns Locations setting to Street Addresses to produce dialog box that looks like this:
Press the Input Columns Set Columns button to set the input data fields:
Set the Postcode (Zipcode) drop-down boxes for the Start Locations and End Locations. Leave all of the other location drop-down boxes set to <none>. You would set these if you have other address information (e.g. city or street).
Street addresses and map coordinates also require a primary key. This is used to uniquely identify each data row, and to make sure the correct data rows are updated correctly. We set the Primary Key to Pkey - a database field we created specially for this. Any unique identifier is sufficient.
It is possible that Maptitude may find multiple locations that match your street addresses (zipcodes in this case). Where possible, Maptitude tries to return the most likely location first – but you cannot rely on this being always correct. MPMileage can either use the first location returned, or it can consider multiple locations to be ambiguous and skip them. Press OK 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 set individual road speeds for each road type. We have selected costs for output, so you must select a rate table for the costs. Press the Costs button to do this. You can specify a text file containing different rates for different distances. Here we will used a simple fixed rate of $0.20/mile:
Press OK to return to the main dialog box. The completed dialog box should like this:
As you can see, setting MPMileage up can require a lot parameters. These are automatically saved as defaults between runs, but they can also be saved and restored using the Configuration buttons.
Press Start to start processing. This is a very short database and should not take long to compute. The resulting Mileages table should look something like this (this example excludes the Pkey field):
The exact numbers may vary according to the parameters that you have selected. Note that all rows were computed okay. MPMileage will add error notes in the Errors field if there are problems finding a location or calculating a route.