Setting up a Stored Route Costs Database

Route Cost databases can now be created as a SQLite file or in a schema in SQL Server. Creating a Route Cost database is similar to creating a Workload Modeller database in Access or SQL Server.

Things to remember:

1. If creating a Route Cost database using routing expression(s), we recommend having those expressions handy (e.g. in Notepad) so they can be quickly pasted into the Route Cost Database Wizard when required.
2. Start SIS Desktop with a blank map. The Route Cost Database wizard does not use any data already open in SIS Desktop.

In this example you have created a Route Cost database in SQL Server. After connecting to an already existing database called test you see:

The Route Cost tables can be created in any schema. Existing schemas are listed in the drop-down list.

To create a new schema, (e.g. rc1) for the Route Cost tables, enter “rc1” in the drop-down box and click ‘Create’.

If you have the relevant permissions you will see:

Routing options determine if the database contains travel distances.

To do this, select the bds or osrm file containing the road network. You would have already created this file using the Network Manager Wizard inside SIS Desktop

If using the OSRM option, remember to apply the Export option- this will export the data to an OSRM dataset after processing all the GZ files.

Click Next. If using OSRM, you will be taken directly to the final screen skipping steps 3, 4 and 5 below. This is because OSRM automatically uses built-in expressions and vehicle class mapping.

Routing can now be based on user-supplied routing expressions or based on Ordnance Survey Average Speed data (see below).

Select an option and click Next.

Using your own expressions

Enter all routing expressions to be used at this point (i.e. before the calculation is done).

1. To enter a routing expression into the database, click Add.
2. To delete a routing expression, select it on the list and click Delete.

Adding a new expression

Click Add to open the "Add new expression" window.

Enter a descriptive name.

Copy and paste your expression into the Expression pane.

Here is an example of a routing expression for OS Highways being added to the database.

Example routing expression

( _length# / CASE 
 WHEN RouteHierarchy$ ='Local Road' THEN 20
WHEN RouteHierarchy$ ='Minor Road' THEN 30
WHEN RouteHierarchy$ ='B Road' THEN 60
WHEN RouteHierarchy$ ='A Road' THEN 70
WHEN RouteHierarchy$ ='A Road Primary' THEN 70
WHEN RouteHierarchy$ ='B Road Primary' THEN 60
WHEN RouteHierarchy$ ='Motorway' THEN 70
WHEN RouteHierarchy$ ='Local Access Road' THEN 20
WHEN RouteHierarchy$ ='Restricted Local Access Road' THEN 5
WHEN RouteHierarchy$ ='Secondary Access Road' THEN 5
WHEN RouteHierarchy$ ='Restricted Secondary Access Road' THEN 5
END ) * ( 60 / 1609.344)

The expression is also added to the list in the main wizard:

To inspect the actual routing expression, simply double-click on the name in the list.

Click Next.

Next define which vehicle class in the Workload Modeller database will use which routing expression.

If there is only one routing expression used by all vehicle classes, the wizard will add the default mapping automatically.

Mappings can only be entered at time of database creation.

An alternative to routing expressions is to use Ordnance Survey Average Speed Data. This is a CSV file of average speeds for every road link in Britain.

The average speeds are merged with the road data (by matching GML_ID) in the next stage of the wizard.

Note: You should also specify a default speed. This is used on road links for which there is no average speed data in the CSV file.

OS Average Speeds - Times

Route Cost calculations in Workload Modeller use a customised blend of speed data that is derived from OS Average Speed data.