OS Meridian 2 Data as a Network, using PostGIS and networkx

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)

Visit of Peat Allan, Principal Consultant, Ordnance Survey – 11/10/2012

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.

TyReNe Meeting September 2012

As part of the TyReNe meeting in September 2012 a breakout group continued with writing a collaborative multi-disciplinary paper relating to the broad topic of modelling (summarised below). The group aim to carry on working together and to have a publishable paper in the not-too-distant future.

Summary of key topics

Modelling is “the process of generating a model as a conceptual representation of some phenomenon”. The output has a purpose, although this varies between disciplines and phenomena studied. Models have limitations; are unable to fully capture reality, although transparency and accuracy help to ensure credibility.

Modelling and Scenarios

Future-oriented studies widely apply scenario analysis that “attempts to describe in some detail a hypothetical sequence of events that could lead plausibly to the situation envisaged”. But different meanings exist for modelling used alongside scenarios: ‘what-if’ picture of reality; or a technique for developing scenarios, as well as contradictions to how scenario building links to forecasting.

Economic Models

The only source of data for these models is the market. The data has high degrees of abstraction and simplification; although agent based and stochastic models attempt to overcome this. Integrated assessment models (IAMs) can do be both stochastic and agent-based models. One such IAM is the Dynamic Integrated model of Climate and the Economy (DICE), which (as is the case with all IAMs) combines economics and climate sciences. Basic equations facilitate analysis, although questions have been raised about the appropriateness of simplistic assumptions.

Weather and Climate Models

Throughout history people attempted weather prediction. However, it took until the mid-20th century, to attempt modelling based on physical principles, and build global climate models; such as Atmosphere-Ocean General Circulation Models (AOGCMs).

Resource Models

Over recent decades the demand for resources has increased to which it outstrips supply to the extent that it is now widely considered to be a limiting factor and serious threat to the functionality of economies and society. As non-renewable resources are finite, the availability and abundance drives supply and demand projections. Whereas approaches for modelling infinite renewable resources tend to differ, the focus is still on projecting supply and demand, and quantifying the extent of the resource reserve.

Infrastructure Modelling

The lifeline infrastructure networks provide: safe drinking water, sanitary conditions, warmth and light, communication, and transportation. Models that integrate various infrastructure networks are in their infancy and, similarly to research in the field of climate change, many recent decision support models have embraced uncertainties, assessing various solutions/strategies under multiple possible futures. Such models facilitated decision-making in land use, transport and energy technology sectors against the various scenarios, testing their resilience to the potential futures.

Discussion Points

  • Range of model of different complexity are useful
    • Simplistic v/s complicated
    • Complexity in the modelling process v/s ease of interpreting result
    • Also models should fulfill standards
      • Transparency
      • Subjectivity
      • Take in to account criticisms