This example shows you how to use MPMileage to compute the distances between a series of US zipcodes. The example requires Microsoft Access 2000 (or later), and Microsoft MapPoint 2006 (or later) North America is required.
The example uses the sample_zipcodes.mdb and sample_zipcodes.ptm files found in the MPMileage samples file:
http://www.mpmileage.com/downloads/examples.zip
Microsoft Excel is a popular choice for MapPoint work, 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. Both MPMileage and MapPoint expect 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.
The original version of this example used a MapPoint pushpin file (sample_zipcodes.ptm or sample_zipcodes_2010.ptm). This file was created from the Zipcode_list table in the sample_zipcodes.mdb database. With MPMileage v2, it is possible to geocode addresses and partial addresses on-the-fly, so we shall use this feature to use the zipcode locations directly from the database and not to rely on a MapPoint map file. However, the map file is provided with the examples if you wish to use this method. It looks like this:
The Mileages table in sample_zipcodes.mdb initially looks like this:
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. The PKey field supplies a primary key field. This is used by MPMileage to uniquely identify each row. You must specify this column whenever routes are located using geographic coordinates or street addresses.
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)". 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 location drop-down boxes set to <none>. You would set these if you have other address information (eg. city or street). All of our locations are in the US, so set the Override Country to United States. It is possible to route across different countries by disabling this option and specifying the countries in the street address information.
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.
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, Road Preferences, and Costs buttons to display dialog boxes that let you edit these options. All of these routing options are passed directly to MapPoint.
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.
Start Processing
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.
