:: Visual-tm Tutorials ::

:: Navigation
:: The Transport Database

Covered in this section:

Project data structure

Data is stored in Visual-tm in a simple hierarchical way. There are up to ten levels of databases, each level may have a number of databases contained within. In this documentation it is assumed that the databases are stored as MS Excel spreadsheet files; however it is also possible to store data in MS Access or CSV form.

Network data will be used as an example to fully explain the hierarchical structure. Data will be stored as follows. Navigation around the databases is facilitated by defining relevant pathnames to the next level of database. The coming example will therefore only mention the database name and relevant pathname field. Other information such as installation date and language are contained within the database file, but are not of concern here.

It may assist you to browse through the relevant files in the test data. Using the Dataviewer you can navigate through the databases by clicking on the relevant pathname field for your desired database followed by clicking on the Go Into Database button. Similarly, to return click the Back Out of Database button. To open the Dataviewer select Database from the View menu:

The Dataviewer

It is also worth mentioning at this stage that you will not see the full pathname to the particular file. What you actually see is the path from the project folder directory, i.e. the system assumes the files are stored within your project folder.

For example, the NetworkList.xls file, which is included with the test data, is actually stored:

C:\Program Files\Peter Davidson Consultancy\Visual-tm\TestData\Ashtest\ScenarioNetworks\NetworkList.xls

Whereas the system only shows:


Database files also contain range names, which enable the system to read specific information from each spreadsheet. A further description of range names is given in chapter 8.

The following descriptions are of files in the Ashtest project. Use the Dataviewer to follow this sequence.

This file is the top-level database and contains links to all other areas for storing data. It is structured as below:

Database name Relative pathname Range name
Transport Infrastructure Data \ScenarioNetworks\NetworkList.xls Network
Household Interview Data \Household Database\HholdProj.xls HholdData
Roadside Interview Data \Eric_Database\MyEricDatabase.xls EricData
Matrix Data \ScenarioMatrices\MyMatrixDatabase.xls MatrixData
General Data \General Data\MyGeneralData.xls GeneralData
Performance Indicator Data \Performance Indicators\MyPerformanceIndicators.xls Performance
O-D Database \Eric_Database\MyO-Ddata.xls ODData

Select the Transport Infrastructure Data Relative Pathname field and click Go Into Database. This now opens NetworkList.xls.


NetworkList.xls viewied in the Dataviewer

This is a second level database and contains information relating to the network infrastructure. It shows links to the base network files (the top line) followed by links to your scenario networks. Visual-tm requires that you have a “do nothing” scenario, which is usually given as Scenario 1. You can have as many other scenarios as required (Ashtest has a further five scenarios). The file is structured as below:

Scenario name Relative pathname Range name
2003 base \ScenarioNetworks\Base\Base.xls Network
2003 do nothing \ScenarioNetworks\Scenario1\Scenario.xls Network
2026 Guided bus (scnr 2) \ScenarioNetworks\Scenario2\Scenario.xls Network
2026 Gbus + LRT (scnr 3) \ScenarioNetworks\Scenario3\Scenario.xls Network
Scr4 + Speed Train to London (scnr 5) \ScenarioNetworks\Scenario5\Scenario.xls Network
(scnr 6) \ScenarioNetworks\Scenario6\Scenario.xls Network

Select the base network relative pathname field and click Go Into Database. This now opens the base.xls file.


Base.xls viewied in the Dataviewer

These files are the third level of database and contain the links to the Netw.xls files that point to the data. There may be a different Netw.xls file for each mode, but it is acceptable to have the data for all modes in the same spreadsheet in which case one Netw.xls file is sufficient. These files are structured as below:

Table name Relative pathname Range name
Road network \ScenarioNetworks\Base\Netw.xls Netw
Rail network \ScenarioNetworks\Base\Netw.xls Netw

Select the road network relative pathname and click Go Into Database. This will now open the Netw.xls file.


Netw.xls viewied in the Dataviewer

The Netw.xls files allow the different data types to be stored in separate files for the same mode, which is required for Comma Separated Variable network data. For network data in Excel files, it makes more sense to have all the data for a mode in one file as the ScenarioComponent.xls file in the Templates directory from which the mode network spreadsheets are derived is designed for this.

These files are the fourth level of database and contain the links to your actual mode network files. Each mode needs to have a file or files containing Node, Zone and Link information. There may also be optional extra data such as banned turns and public transport routes and fares. The data is always held on a sheet called Netw and these files are structured as below:

Table name Relative pathname Sheet
Node \ScenarioNetworks\Base\Road.xls Nodes
Link \ScenarioNetworks\Base\Road.xls Links
Routes \ScenarioNetworks\Base\Road.xls Routes

Thirteen different data types are specified in the file, of which the first three are shown above. The Table names must always be exactly as specified in the file as the software looks for these names in the file when loading the network. The Nodes and Links data are in ranges with the names as specified and the rest of the data types are on sheets with the names as specified. If any of the data is in comma separated variable files, there is no need to enter anything in the Sheet column beside its name.

Selecting any of the tables and clicking Go into Database will display the selected table.

The Road.xls file is the lowest level of database contained within the network data. It contains all the information relevant to building your base mode network. When viewing the file through the Dataviewer, the area of the spreadsheet which is actually shown is governed by range names or will be the part of the named sheet that has had data entered on it.

Back to top

The system files

Every Visual-tm project contains a directory called System\Working. This directory contains system files. None of the files in this folder should be edited unless specifically stated.

This file shows the relative pathnames to the files shown at the top level of the Dataviewer.

This is a MapInfo Interchange Format file. It is used to plot the background zone system in the Network display. It can be viewed and edited using a text-editing package such as Brief or WordPad. Please see the screenshot below.


The .mif file basically contains some initial drawing instructions at the very top, which can be copied for any other project.

It then contains the polygon information. As you can see above, the first polygon is called Region 1 and has 207 points. These points are then listed below as OSGR Easting and Northing co-ordinates.

The drawing of the map is very straightforward. Firstly a line is drawn between the first and second set of co-ordinates. The end point of this line is then connected to the third co-ordinate; the end of this line is then connected to the fourth co-ordinate and so on. The key point to remember is that the first co-ordinate and the last co-ordinate in the list must be identical so that the polygon is a complete shape.

Each polygon is then listed in turn with the co-ordinates of its points. The polygons should fit together with no overlaps or gaps to cover the entire geographical area of study.

Back to top

Navigating the databases

To open the Dataviewer simply click View/Database on the main Visual-tm form.

The Dataviewer

The Dataviewer operates within Visual-tm where it holds the actual data as a consistent set of databases. It provides tools for viewing, editing, adding additional fields, tabulating and outputting data.

The databases comprise data tables in a hierarchical data structure with up to ten levels of data table, each level nested within the next level in the hierarchy. The nesting is also branched to other tables so that a table at one level can point to many tables at the next level down. Each record in the table can have a field containing a path and filename of the data table at the next level down that the user can go into.

To navigate through the data tables simply click on any cell in the row with the database that you wish to view, then click Go Into Database. Click Back Out of Database to reverse.

The project tree
The quickest and easiest way to view the complete data structure within a project is to use the project data structure form. Choose Project Tree from the View menu to open it.

The Project Tree

In this form you can currently see the databases loaded into this project. Simply click on the signs to expand further.

Expanded Project Tree

You can double click on a specific data item to open the Dataviewer and display the data that was selected.

Back to top

Adding, deleting and modifying data

Editing data through the Dataviewer is achieved by clicking the View Button. This opens the View form (below). Here you can simply edit data stored in any of the records by typing the required changes into the fields. Use the Record Navigator section at the bottom right to choose which record and which field you wish to edit.

Dataviewer Field View

You can also Add New Records or Delete Existing Records by clicking on the appropriate button on the View form. Remember to click Save Record to save any changes made when exiting. You must also click Save on the Dataviewer form to save the changes to the file. Click Exit to return to the Dataviewer without saving changes.

Using the Dataviewer it is possible to open many types of file, even if they are not included in your nested hierarchy structure. At the top left corner of the Dataviewer you can click on the button to browse to your required file. Select the appropriate range name and click open. This file can then be edited and viewed in the way described above.

Back to top

Data manipulation tools

The Dataviewer provides the facilities for manipulating variables through set parameters. This is achieved by applying named ranges to a chosen set of variables and storing them in your HHI data table.

Let’s investigate the HHI test data. Open the Ashtest test data set and use the Dataviewer to open the trips level data table of the household interview data (below)

Household data in the Dataviewer

If we look at the main mode of trip and main mode number columns there were 12 options for the interviewee. These and their relative codes are shown below:

1 = Car
2 = Car Passenger
3 = Car Share
4 = Work Bus
5 = Public Bus
6 = Bicycle
7 = Walk
8 = Underground
9 = Train
10 = Taxi
11 = Motorbike
12 = Other

It is very unlikely that all 12 of these modes would be of interest in a multi-modal modelling project. These modes can actually be “squeezed” down to just 5 modes, for example:

1 = Car
2 = Walk
3 = Cycle
4 = Bus
5 = Train

The squeeze pattern for these codes would be:

Original Code Aggregate Code Aggregate Mode
1 1 Car
2 1 Car
3 4 Bus
4 4 Bus
5 4 Bus
6 3 Cycle
7 2 Walk
8 5 Train
9 5 Train
10 4 Bus
11 1 Car
12 1 Bus

This squeeze pattern has been saved to the HHI datatable and called AggregateMode.

Once the trips level data has loaded click the Look Up Screen button. The form below appears.

Lookup form

In the Run Lookups section of the screen select the Lookup Table to use as Aggregate Mode. At the top of the screen you can now see the squeeze pattern just mentioned.

The Source Column should be the un-squeezed mode column in the trips data table. This is Main Mode number.

The Target Column should be the destination you wish the Aggregate Mode code to be output to. An Aggregate Mode column has been added to the trips data table for this purpose. The completed form should look like this:

Completed Lookup form

Now click Go to run the Lookup. When completed a message saying Aggregation Complete is displayed. Click Back to Grid to view the trips data table with the new entries in the Aggregate Mode column (below)

Household data with Aggregate Mode column filled in

Click Save in the Dataviewer to save the changes back to the spreadsheet for additional editing or analysis.

As you can imagine, Lookup can be quite a complex tool to set up but the functionality it provides can be invaluable to the transport planner.

The Dataviewer provides the functionality required to tabulate some of your transportation data. This is an important tool and can be very useful, particularly when attempting to analyse large data files with possibly several thousands of records. The Dataviewer also provides the capability to export and save your tabulated data to an MS Excel spreadsheet, which can then be used in external applications.

In order to demonstrate this, let’s have a look at the household interview person level data. Open the Dataviewer and navigate so this data table is open (below).

Household data in the dataviewer

There are 132 records in this file. Say for instance you want to generate a profile of the ages of people who were interviewed in each ward. Manually this would be a painstaking process. The tabulate facility makes it quick and easy. Click Tabulate Screen and the form below opens.

The Tabulate Data form

At the top left of this new screen you are asked to select which variables you wish to tabulate. The options to choose from are taken directly from the headings of the data table you wish to tabulate. In this example select the Person range from the drop down menu under the file name. Choose Household District as the Vertical axis and Age as the horizontal axis. The Dataviewer also enables the user to apply weighting and expansion factors to the tabulated data. These are optional and are not required for this example (they are described later in this section). Now click Tabulate.

The system now initialises the tabulation process. This can take a few minutes depending upon the processing power of your CPU and the complexity of your data table. Once completed the tabulated database appears (below).

Tabulated data

Click Show values to display the age groups and Ward names as shown below.

Tabulated data

Saving your tabulation is a simple process. At the top right of the Tabulation form click to display the Open File dialogue box. Either enter a new filename or browse to your required Excel spreadsheet file (in which case you should also select a sheet name from the drop down menu) and click Save to Excel. The table is then saved below any previous tabulation outputs that have been saved to that file.

Weighting and expansion factors
If your data contains columns with weighting factors or expansion factors which correspond to the columns you select in the Vertical axis or Horizontal axis box, then these can be selected from the relevant drop down menu. The records will be multiplied by these values before they are displayed in the table.

Simple tabulation of whole range into Excel file
The tick box Simple Tabulation of whole range into Excel file will output a simple count of the entries in every column of your data to the Excel file you select in the top right of the form. It is not necessary to fill in the Horizontal and Vertical axes boxes, simply choose the you want tabulated from the box under the filename, enter the name of your Excel file, tick the box and click the Tabulate button.

Back to top


  Back to tutorials     On to networks