Part 3: Building a routable network

These pages provide a guide to building a routable network using what are four of the most popular routing software packages used across the public sector. However, they are not intended to be a comprehensive guide to creating a network, or an endorsement of the packages. Please refer to the documentation for the specific software to be used. Furthermore, a working knowledge of the software is required before starting this type of analysis.

To simplify this the methodology has been broken down into sections: Before Starting, Network Analyst (ESRI), Routefinder, Cadcorp SIS and pg Routing to make it easier to jump to the section you are interested in.

Before starting to build a routable network there are several options to consider that will determine which data is required and which option is most appropriate to build your network.

What level of analysis is required? This will determine the level of detail in your network. If a high-level strategic overview is needed, then consider using OS OpenRoads data as the basis. However, if more detailed analysis, looking at service location and journey times is needed, this will require either OS MasterMap Highways or OS NGD Transport Network data.
What extent of data is required? Consider whether a network for Great Britain or just your local area is required. The bigger the area the longer it will take to build the network and the slower the analysis may be.
What additional data needs to be added? – For example, road speeds, bus stops for public transport networks and so on.
Some networks will be built with speed attributes set to nominal values which you may need to review and amend.
What type of analysis is required? For example, drive time buffers, A to B routing and so on
How to run the analysis? Typically, network analysis is memory intensive, and it may be more efficient to analyse the data in smaller batches or by using a smaller network.
How will the results be presented? Are the results going to be presented graphically in a report or do you just require a spreadsheet of values.
What are the processing requirements for the intended software?

OS MasterMap Highways Network

To use OSMM Highways in Network Analyst the data needs to be converted from GML into a Geodatabase. ESRI UK’s UK Data Loader will do this and create a network in the correct format. This includes incorporating the restriction and qualifiers into the data model. Once the network is built it can be added to either ArcMap or ArcGIS Pro with the Network Analyst Extension enabled for use. This typically creates a national network in approximately 30 hours (depending on hardware setup, information provided by ESRI UK).

Further details of the UK Data Loader can be find here - https://www.esriuk.com/en-gb/arcgis/products/uk-data-loader/overview. The UK Data Loader requires ArcGIS Pro v3.0 or above and the Data Interoperability Extension for ArcGIS Pro. You will need to ensure that the versions in use are compatible with each other.

OS NGD Transport Network

To use the OS NGD data in Network Analyst the same tool as for Highways, UK Data Loader, is required. The tool converts the Geopackage into a FileGeodatabase containing an ESRI network dataset comprising all the network elements required. Depending on hardware set-up this will take approximately 36hrs to process a national data set. (Figures taken from the ESRI UK help document).

OS OpenRoads

OS OpenRoads is supplied as both GML and Shapefile. To create a network from the data you will first need to convert the Shapefiles into a Geodatabase by using ‘merge’ from ArcToolbox to create a new Feature Class. This needs to be within a Feature Dataset in the Geodatabase as you can only create networks at this level. Once completed you can build a network in ArcCatalog by opening your Geodatabase, right clicking on the Feature Dataset and selecting ‘New’ followed by ‘New Network Dataset’. The wizard will take you through the process, but as OpenRoads only contains geometry you can leave all other settings as default.

As this data contains fewer features than Highways the processing time is significantly reduced and a network can be created in a few hours.

A useful tutorial is available online explaining how to build a network and the different types of analyses - http://webhelp.esri.com/arcgisdesktop/9.2/pdf/Network_Analyst_Tutorial.pdf.

OS MasterMap Highways Network

To convert the Highways GML into a TAB file for use with RouteFinder a translator is available that can be downloaded from - http://www.routeware.dk/os_highways.php. There are separate TAB files for both roads and paths data so both can be loaded together or as separate networks. Click ‘Add Files’ and select the GML for translation. To include the restrictions which are part of Highways you will need to check ‘TOID base (turn.txt)’ and click ‘Go!’.

This will produce a table of RoadLinks from which a network can be generated. RouteFinder is a tool which runs from within MapInfo. If the tool is not present on the Toolbar it can be found in MapInfo’s Tool Manager. Open your translated data in MapInfo and select ‘Network’ and ‘Create Network from Table’. Then choose the road table and click ‘OK’ to create the network. Processing a national set will take approximately a day to complete, depending on your hardware set up.

RouteFinder uses a route hierarchy based on road classification which is added to the data as an integer. The import tool will have added an attribute column containing these values to the data. In the ‘Select Network Attributes’ dialog set the ‘Attribute Column’ to road_class and height and width to the columns containing these values if present. Finally click ‘OK’ to create your network.

OS OpenRoads

OS OpenRoads is supplied as both GML and Shapefile, if you are using MapInfo v12.5 or newer the Universal Translator can be used to convert GML directly into TAB files. If you are using an older version than v12.5 then MapInfo can read shapefiles directly, however the data will need to be saved as a TAB file and merged into a single file.

Once a TAB file is created a new attribute, road_class will need to be added to the data to hold integer values for each road classification. The following query and update in MapBasic can be used to populate the new attribute.

Select * from RoadLink where roadClassification = "Motorway"  
Browse * From Selection 
Update Query1 Set road_class = 1 
Close Table Query1 Interactive 
Select * from RoadLink where roadClassification = "A Road" And formOfWay = "Collapsed Dual Carriageway" into Selection 
Browse * From Selection 
Update Query2 Set road_class = 2 
Close Table Query2 Interactive 
Select * from RoadLink where roadClassification = "A Road" And formOfWay = "Single Carriageway" into Selection 
Browse * From Selection 
Update Query3 Set road_class = 3 
Close Table Query3 Interactive 
Select * from RoadLink where roadClassification = "A Road" And formOfWay = "Roundabout" into Selection 
Browse * From Selection 
Update Query4 Set road_class = 2 
Close Table Query4 Interactive 
Select * from RoadLink where roadClassification = "B Road" And formOfWay = "Collapsed Dual Carriageway" into Selection 
Browse * From Selection 
Update Query5 Set road_class = 4 
Close Table Query5 Interactive 
Select * from RoadLink where roadClassification = "B Road" And formOfWay = "Single Carriageway" into Selection 
Browse * From Selection 
Update Query6 Set road_class = 5 
Close Table Query6 Interactive 
Select * from RoadLink where roadClassification = "B Road" And formOfWay = "Roundabout" into Selection 
Browse * From Selection 
Update Query7 Set road_class = 4 
Close Table Query7 Interactive 
Select * from RoadLink where roadClassification = "Unclassified"  
Browse * From Selection 
Update Query8 Set road_class = 6 
Close Table Query8 Interactive 
Select * from RoadLink where roadClassification = "Not Classified"  
Browse * From Selection 
Update Query9 Set road_class = 7 
Close Table Query9 Interactive

Once the road_class attribute is added the data is ready for network conversion as described previously. However no additional attributes will be present in the data.

OS MasterMap Highways Network

There are several resources for loading data into PostGreSQL and making available to pgRouting. The data can be loaded into the database using the Astun Loader made available on GitHub - https://github.com/AstunTechnology/Loader.

Creating a network for use with pgRouting involves running SQL queries in a PostGreSQL database with the PostGIS extension. Several useful resources are available on GitHub. This include a guide written by Ross MacDonald for pgRouting which can be found here - https://github.com/mixedbredie/highways-for-pgrouting. As well as that of Tim Manners - https://github.com/tmnnrs.

Loading the data into pgRouting and building a network can be time consuming and will take 2 to 3 days to complete. The most efficient way to do this is by creating routines for each element of the build which can then be run overnight, leaving hardware and software free for other tasks during the day.

Further details on pgRouting - https://pgrouting.org/.

OS NGD Transport Network

OS NGD Transport data is available as both CSV or Geopackage which can be loaded directly into a PostGreSQL database. To create a network for use with pgRouting you will need:

NGD Transport Network Road Link
NGD RAMI Restriction - (which includes trn_rami_restriction and trn_rami_restriction_restrictnetworkref)
NGD RAMI Average and Indicative Speeds

Once loaded, as the data is very similar to OS MasterMap Highways, you will be able to follow the same instructions as for OS MasterMap Highways, amending them to fit your attributes as you work through the data.

OS OpenRoads

OS OpenRoads is delivered as Shapefiles which can be loaded directly into PostGIS using the PostGIS ‘Shapefile Import/Export Manager’. However, once loaded into PostGIS you will need to append the tables into one table to make the data easier to work with. To use the data in pgRouting you will also need to add the following columns to the data:

source (integer) – Contains the value of the start node for each link
target (integer) - Contains the value of the end node for each link
fid (serial) – Unique value for each link, used as a primary key
cost (double precision) – Cost value for traversing along the link
rcost (double precision) – Reverse cost value for traversing along the link

As the data contains references to the start and end nodes held in the roadnode table, the source and target columns can be populated using these values using an update query, removing the OSGB text and recasting the column.

The following SQL has been used to populate these values. However, some editing may be necessary to reflect differences in data structure. An additional column temp_id has also been added to make the conversion of the references easier.

UPDATE roadnode SET temp_id = trim(leading '#osgb' from gml_id ) :: Bigint; 

UPDATE roadlink set source_temp =trim(leading '#osgb' from l.startnode_xlink_href ) :: Bigint 

FROM roadlink AS l where l.gml_id = roadlink.fid; 

UPDATE roadlink set target_temp =trim(leading '#osgb' from l.endnode_xlink_href ) :: Bigint 

FROM roadlink AS l where l.gml_id = roadlink;

The cost values can be set to either the road length or assigned an integer value dependent on the road classification.

Examples

With ever tighter financial pressures on Public Services a few Local Authorities have looked to using network analytics to improve services and reduce costs. Ordnance Survey has collected several cases studies demonstrating the use of the network data in route optimisation for waste collection and school bus routing. Full details of all Ordnance Survey case studies can be found at - https://www.ordnancesurvey.co.uk/business-and-government/case-studies/index.html.

Please note: This is a methodology and not an OS product. The methodology can be taken and adapted, but OS will not be maintaining this methodology.

PreviousPart 2: Routing software and data options NextUnlocking the Power of Geospatial Data

Last updated 7 months ago

Select * from RoadLink where roadClassification = "Motorway" Browse * From Selection Update Query1 Set road_class = 1 Close Table Query1 Interactive Select * from RoadLink where roadClassification = "A Road" And formOfWay = "Collapsed Dual Carriageway" into Selection Browse * From Selection Update Query2 Set road_class = 2 Close Table Query2 Interactive Select * from RoadLink where roadClassification = "A Road" And formOfWay = "Single Carriageway" into Selection Browse * From Selection Update Query3 Set road_class = 3 Close Table Query3 Interactive Select * from RoadLink where roadClassification = "A Road" And formOfWay = "Roundabout" into Selection Browse * From Selection Update Query4 Set road_class = 2 Close Table Query4 Interactive Select * from RoadLink where roadClassification = "B Road" And formOfWay = "Collapsed Dual Carriageway" into Selection Browse * From Selection Update Query5 Set road_class = 4 Close Table Query5 Interactive Select * from RoadLink where roadClassification = "B Road" And formOfWay = "Single Carriageway" into Selection Browse * From Selection Update Query6 Set road_class = 5 Close Table Query6 Interactive Select * from RoadLink where roadClassification = "B Road" And formOfWay = "Roundabout" into Selection Browse * From Selection Update Query7 Set road_class = 4 Close Table Query7 Interactive Select * from RoadLink where roadClassification = "Unclassified" Browse * From Selection Update Query8 Set road_class = 6 Close Table Query8 Interactive Select * from RoadLink where roadClassification = "Not Classified" Browse * From Selection Update Query9 Set road_class = 7 Close Table Query9 Interactive

UPDATE roadnode SET temp_id = trim(leading '#osgb' from gml_id ) :: Bigint; UPDATE roadlink set source_temp =trim(leading '#osgb' from l.startnode_xlink_href ) :: Bigint FROM roadlink AS l where l.gml_id = roadlink.fid; UPDATE roadlink set target_temp =trim(leading '#osgb' from l.endnode_xlink_href ) :: Bigint FROM roadlink AS l where l.gml_id = roadlink;