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.
The database file sample_zipcodes.mdb contains two tables: Zipcode_list and Mileages. Zipcode_list is not required for MPMileage, but it has been included because it was used to create the sample_zipcodes.ptm map. This contains one column that lists each and every zipcode that will be used. Note that this must be a text field. This table was imported into MapPoint using MapPoint's Data Import Wizard. The resulting sample_zipcodes.ptm map is illustrated:
The map file must have a pushpin for each and every required location. The pushpin names must match the names (zipcodes in this example) used in the table that stores the required routes. Two pushpin balloons are opened up for illustrative purposes.
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 pushpins in the above map file. 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.
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 columns that hold the start and end locations. Then select 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:
Press OK to return to the main dialog box.
Next select the required MapPoint (ptm) file by pressing the MapPoint (ptm) File "..." button. Ignore the Advanced Options for now. These should be used on larger datasets if you are running on a computer with multiple CPUs.
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:
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:
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.
