🆕 Visualising a road gradient using OSMM Highways

Introduction

Visualising a road gradient is very useful for a number of reasons and for various road users. It helps drivers, especially drivers of HGVs, to avoid roads that may be too difficult to negotiate in their vehicles.

It can help cyclists or hikers choose routes that are appropriate for their fitness levels and avoid those that are too long or too steep;

Conversely, tourists may want to find steeper roads as these may offer better viewpoints or are more scenic; and

Blue light services can identify flatter routes that mean they can spot a potentially quicker route to an emergency.

What do you need?

In this demonstration we are going to create a visualisation of the gradient for one road.

• The GIS software used is QGIS 3.22.4 – Białowieża.

• Access to a Postgres database with the PostGIS extension activated

• Software to view and interrogate the database; DBeaver is used in this demonstration.

• The Light_27700 map from the OS Maps API is also used as background mapping in QGIS to add some geographic context.

• The data being used is the OSMM Highways Network Roads GML option downloaded from the OS Data Hub.  For the purposes of this exercise a rectangle was drawn to capture all the roads from Stockbridge to Sutton Scotney in Hampshire.

Method

Download and unzip your data package from the OS Data Hub.

Start a QGIS session and load the RoadLinks into QGIS (they will be named something like this: Highways_Roads_RoadLink_FULL_001.gml.gz (there is no need to unzip the .gz.))

The GML roadlinks will look something like this in QGIS

Select the road that we want to visualise

We are going to look at just the one road, the A30, so we need to find this and export it.

Make sure the roadlinks are selected in the layer window in your QGIS session and click on the 'Select Features using an Expression' button, which has a yellow square and an Ɛ on it. This will open a window in which you can build expressions. In this case we are doing something fairly straightforward:

The expression builder window

1. Click in the drop down arrow next to Fields and Values.

2. Double click on road_classification_number (this will add this attribute to the Expression window)

3. Type = 'A30' next to "road_classification_number" in the Expression window.

4. Click Select Features

Save the Road

To save the A30 right click on the roadlinks layer and select Export > Save Selected Features As. This will open a new window where you can browse to a location and save the road. Save this as a Geography Markup Language (GML) and call it the A30.

Save the selected road

We could now style this map based on the value in the inDirection attribution, but this will not be an indication of how steep the road is. For example, the largest in direction elevation gain on the A30 is 30.2m from the start node to the end node, but if this stretch of road is 2km long this doesn’t make this a particularly steep road.

What we need is a gradient attribute! But this isn’t in the data, so we’ll have to make one! We do have the necessary attribution already in the data, the inDirection elevation gain calculated between the start and end node of the road link and we also have the length of the road link!

We could do this in QGIS but the nature of GML means that the results would not be permanent, we would only be creating a virtual field and we also have no control over the field length or precision of the output value.

So, how can we create a gradient value? We’ll export the A30 GML to a PostGIS database and use SQL!!

Export the road to PostGIS

This is a straightforward process in QGIS. Click on Database > DB Manager

This will open the DB Manager window. Select PostGIS as the database option, click on the database that you want to connect to and then the schema that you want to import the data into (in this demo the schema is called dev_eddie, you'll need to change this to the schema that you're using). Then click Import Layer/File.

Select database and schema

When you click the Import Layer/File button a new window will open:

Import vector layer interface

Make sure that the A30 is selected in the input dropdown. PostGIS doesn't like upper case letters, so call the output table a30 and also check 'Convert field names to lowercase'.

It's always good practice to make sure a geospatial table has got a spatial index, so check the 'Create spatial index' box too. Then click OK! If all goes well, you will get a small window pop up that tells you the Import to Database was successful; click OK on this.

Create the gradient attribute

Open DBeaver and navigate to the database that you have imported your A30 road data into. Right click on the database name and select SQL Editor > New SQL script

Open a SQL editor window

This will open a SQL panel where we can create and populate our gradient attribute.

The schema name in the following queries is dev_eddie so change this to your own schema but the data attribution names should be the same.

The first thing we want to do is create the column for the gradient value and define the type of data that will populate it.

Run this SQL script:

ALTER TABLE [your schema name here].a30 ADD COLUMN gradient real;

This has now created the gradient column in our data, but now we need to populate it.

So how do you find the gradient of a road as a percentage?

Gradients are calculated by working out the ratio between the rise in the road between 2 points and the run, or the horizontal length of the road. To express this a percentage the result is multiplied by 100.

How does this translate to SQL and how do we populate the gradient column with this calculation?

We can run this script:

UPDATE [your schema name here].a30 SET gradient = ((indirection/length)*100);

We now have a table in our database with a gradient attribute so now we can style it up in QGIS.

Load the new road table from PostGIS to QGIS

Back in our QGIS session we want to load the road, the A30, with the new gradient attribute from the PostGIS database. To do this, first click on the elephant's head!

This button can be found on the left-hand side of the QGIS session and will open a window that will allow you to navigate to the A30 road data.

1. Connect to your database. If you have connections to more than one, select the correct one from the dropdown.

2. Once you've got the right database click Connect.

3. Select the schema that holds the table for the a30 data (click on the little triangle to the left of the schema name to expose all the tables within it).

4. Select the a30 table by clicking on it.

5. Click Add and then Close!

The view of PostGIS from QGIS

Style the road based on the new gradient attribute

The a30 road should be visible as a line and as a layer in the Layers pane in your QGIS session.

Right click on the a30 in the Layers pane and click on Properties at the bottom of the pop-up menu that will appear.

This will open the Layer Properties window. If it's not already selected, click on Symbology on the right-hand side of the window.

Layer Properties window. Note the top of the window; this is for the a30 data

At the top of the window, you'll notice that the default symbol for a line is Single Symbol. We don't want this; we want Graduated so click on the drop-down arrow on the right and select this:

The Graduated option

The window will change slightly because of this selection and give you some different options. One of these will be Value. Click on the drop-down arrow next to value and select gradient. Next, we want to select a colour ramp to show the gradient. The most used would be green (flattest) to red (steepest). To find the colour ramp that we want click on the drop down arrow next to Color (sic) ramp.

Sometimes, the colour ramp that we want to use isn't listed so we have to click on All Color Ramps and then select RdYlGn from the extended list

Now that we've selected the RdYlGn colour ramp you'll notice that it will be going from red to green. We want it to go from green (flatter) to red (steepest)!! This is easily remedied, click on the drop-down arrow again and click on Invert Color Ramp:

Invert the red to green colour ramp

We now want to apply this colour ramp to the road.

Classifying the gradient

• Click on Classify

• Choose 4 for the number of classes

• Click OK

You will now see the A30 styled up in to reflect the gradient!

A30 showing the gradients along its length using Equal Count

However, there is a significant jump in the final value, it is classifying a gradient between 0.99% and >10% which is a little misleading! Let's try a different classification method, let's use Pretty Breaks and see what happens. Pretty Breaks splits the results into more equal divisions.

The result isn't quite so colourful but gives a better representation of the gradient of the road.

The A30 gradient represented by Pretty Breaks

You now have the skills to create a gradient model for a road and have experimented databases, SQL and with different presentation methods!