At the end of May Stuart Barr attended the launch of the ITRC (Infrastructure Transitions Research Consortium) MISTRAL (Multi-Scale Infrastructure Systems Analytics) programme, an EPSRC funded 5year programme between seven universities, including ourselves, with Stuart being one of the co-investigators. Hosted at the ICE (Institute of Civil Engineers) in London, the event presented the vision and ideas behind the new programme, the next step in infrastructure systems-of-systems analysis research following the completion of the previously funded ITRC programme. Attended by over 150 people, including representatives from academia, private sector businesses and public sector organisations, the event included speeches from Professor Jim Hall, the lead investigator on the ITRC MISTRAL project, Lord Adonis, chair of the National Infrastructure Commission and Keith Clarke, the ICE vice president. A question and answer session then followed providing the opportunity for the attendees to find out more about the ITRC MISTRAL project from the key persons involved, including Stuart.
A video has since been released including snippets from some of the speakers, providing an insight into the work which will be undertaken in the ITRC MISTRAL project and the important role it can play in the future of infrastructure systems.
In the latest issue of GeoConnexion UK a short article, written by Stuart Barr and Craig Robson, details the ongoing work they are doing to develop the UK’s first national infrastructure database. Over the course of the 5 year ESPRC funded ITRC MISTRAL programme, by 2020 a national infrastructure portal will be developed as a resource that will be open to those across academia and industry as well as policy makers. This will provide access to infrastructure datasets and simulation and modelling results, including those from the already completed ITRC project, such as the results from the first national infrastructure long term planning tool. Some of the software developed and employed in the analysis undertaken will also be available under open licenses allowing the research to continue beyond the life of the ITRC MISTRAL project.
Both are based in the Geomatics group in the School of Civil Engineering and Geosciences at Newcastle University, with Stuart a lecturer on the two undergraduate degrees offered, BSc Geographic Information Science and BSc Surveying and Mapping Science, while Craig is a recent graduate of the GIS programme.
As part of Geospatial Engineering’s on going involvement in the Infrastructure Transitions Research Consortium (ITRC – www.itrc.org.uk), various network models of infrastructure networks have been developed. This has involved the development of a custom database schema within PostGIS to handle networks created within the Python package, networkx. The linkage architecture has been provided by a custom built set of Python modules allowing raw line and point data within PostGIS to be fed into networkx.
Each network is represented within PostGIS as a series of three tables; a table representing nodes, a table representing the edges within a network and their attributes, and finally a table representing the geometry of the edges. A series of node, edge and edge geometry parent tables within the custom database schema are inherited from when a specific network instance is written to the database. The inheritance from these parent tables ensures a minimum set of columns are transferred to each instance table for each network, with a series of foreign key constraints applied to enforce referential integrity within the database. These foreign key constraints for example, ensure that an edge can only exist within a network provided that the nodes at either end of that edge also exist.
The attributes of each edge created are transferred from the attributes supplied within the raw line data. If no point data is supplied to the network build functions, then nodes are created at the end of each linestring, with blank attribute values. Alternatively if a set of points are supplied, then these attributes are copied through to the node table. Each node and edges created within networkx are written to each table individually, where checks are made against each node and edge table to check if a node or edge with the same geometry has already been created. This check is performed using the ST_Equals function available within PostGIS.
To enhance performance of the Python network writing module that is used to write networkx network instances to PostGIS, a version of the writing functions has been created exploiting the PostgreSQL COPY function. Prior to writing the data to the PostGIS database each node, edge and edge geometry table is created as a CSV file, with the COPY function used to bulk write the data to the database. Furthermore, this second approach to writing the networks to the database does not require multiple read/writes to check if a node or edge already exists i.e. stored, as this is handled within memory.
The Ordnance Survey Meridian 2 Rail data has created as a spatial and topological network within the database schema, using the Python linking architecture between PostGIS and networkx. Initially a table of rail ‘junctions’ was created offline by calculating the number of intersecting lines at each vertex within the raw data. This data table was used as the point input table, whereby a “type” attribute for each node with a value of ‘junction’ was defined.
Utilising the writing functions whereby a network edge and node is written individually to the database for each within the network, the network was built from the raw data, and then written to the PostGIS database within 5 minutes. This network contained:
7995 nodes (1416 JUNCTIONS)
8484 edges
The image below shows a portion of the Meridian 2 Rail network as discussed above:
Ordnance Survey Meridian 2 Rail Network
Utilising the CSV writing functions whereby a CSV version of the Meridian Nodes, Meridian Edges and Meridian Edge Geometry are created prior to bulk writing these to the database, reduced the network build and write time, for the same number of nodes and edges as discussed previously, from approximately 5 minutes to less than 30 seconds. Furthermore the CSV representation of the Meridian Rail network, based on the level of attribution available from the raw data plus the newly-defined “type” attribute to store the ‘junction’ value, are only 5Mb in size, making this easy to share with others.
Further comparison of the two approaches to writing the network data to PostGIS, utilising the Ordnance Survey Meridian 2 Road (A, B, Motorway) data shows a similar improvement in writing performance if the PostgreSQL CSV COPY approach is used. For an area in North West England (covering approximately 22,000km2), containing a total of 291759 raw input points (taken from the road_node and rndabout for A, B, and Motorways only), and 46836 input lines (combining A, B and Motorways for the chosen North West region), it was possible to reduce the network build and write time from approximately 61 minutes to 17 minutes 25 seconds.
The image below shows a portion of the Meridian Road Network (A, B, Motorway) for the North West of England
Ordnance Survey Meridian 2 Road Network (A, B, Motorway)
As part of Geospatial Engineering’s involvement in developing a national-scale infrastructure asset database, alongside developing spatial and topological representations of multiple national-scale infrastructure networks, we have been liaising with the Ordnance Survey about our data requirements. As a project partner of the Infrastructure Transitions Research Consortium (ITRC – www.itrc.org.uk), under which this infrastructure work is being undertaken, the Ordnance Survey has supplied multiple datasets and feature types, at various geographic scales, from a range of their products, including infrastructure features from Points of Interest.
A meeting between those involved in this work from Geospatial Engineering @ Newcastle, and Peat Allan, Principal Consultant at Ordnance Survey, took place on October 11th to discuss the use of OS data for network creation. A number of examples of spatial and topological network creation from Ordnance Survey Meridian and Strategi Road and Rail data were discussed, leading to specific discussions regarding data requirements for infrastructure and environment projects being undertaken at the School of Civil Engineering and Geosciences at Newcastle University, and more widely within the research community.
As follow up to these discussions, further meetings between those involved in infrastructure projects within the School will be held within October and November to try to understand and identify where there may be common “knowledge and data” gaps across different research projects. It is intended that this information is fed back to the Ordnance Survey to help understand where further information is required to facilitate infrastructure-related research projects.
