This example shows you how to use One2OneMileage to compute the mileages between named locations in Microsoft Access database. All routes are specified using named locations. A separated table is uised to provide the locations' coordinates.
The example uses the locations and loc_routes tables in the one2one_samples.accdb database that can be found in the Ultra Mileage examples file:
The routes are specified in the loc_routes table which has a design view that looks like this:
This has columns defining the source (src) and destination (dest) named points, and four columns (distance, time, cost, and error) to receive the results. The error field is strongly recommended because this can be used to diagnose problems such as bad source/destination locations. The error field must be text, but the other result fields can be floating point (recommended) or text. You also need a primary key to be set. This can be an integer or a text field. All primary key values must be unique, and the field should have an index. Here, we have called the primary key ID.
The source and destination named points are simply text labels which refer to points in a second table (locations, see below). This is what the data actually looks like:
The named locations are specified in the locations table. This has the following design:
Each data row provides the location of a named point. The named point is specified in a text field (label) and two double precision numeric fields (longitude and latitude) provide the actual coordinate.
This is what the table looks like with data:
Setting the Ultra One2OneMileage Parameters
Start Ultra One2OneMileage. Many of the parameters should be set the same as with the coordinate example. Press the "..." button in the Road Data box to select the Contiguous USA Fastest road pack (downloaded using the Download Manager). Set the Routing Parameters to use Miles for the distance units and Minutes for the time units. Set the Find option to Fastest Routes.
Next press the Change button in the Database box 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. One2OneMileage will load this and scan it for the available tables.
Select the loc_routes table from the list of available tables. This table contains our required routes and will store the results. One2OneMileage will scan the table and fill the remaining controls with the possible column options. Select the Locations are as "Named Points" and set Points are in a to Database Table. The alternative is a Maptitude point layer.
Press the Set Input Columns button to display the Input Locations dialog box to specify the database table with the named point locations. Set it to look like this:
Set the data fields that specify the primary key, and the start & end locations in the main route table (loc_routes). The Named Point Source box defines the table and data fields that store the named point definitions. In this example, they are in the locations table. Three fields need to be specified: the point's name, plus the longitude and latitude of the point.
Press OK to return to the Database Parameters dialog box. Set the required Output Columns and you should have a dialog box that looks like this:
Press OK to return to the main One2OneMileage 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. For this example we set the Distance Units to be Miles and the Time Units to Minutes. We also select Fastest Routes in the Find box. Press the Rate Table Costs button to display the Costs and Rate Tables dialog box:
You can use this to define a rate table that applies a different cost according to the route distance. Here we simply apply a Constant Rate of 0.50 per mile.
Press OK to return to the main dialog box. This should now look like this:
Press Start to start processing. This is a very short database and should not take long to compute. The resulting loc_routes table should look something like this:
The exact numbers may vary according to the parameters that you have selected or the road pack version. The Cost field was created as a Currency type - hence the dollar signs.
Note that all rows were computed okay. One2OneMileage will add error notes in the Errors field if there were problems finding a location or calculating a route.