Further label expressions for QGIS

Extracting junction numbers from OS point data

Many advanced users would write some Python code, but many things can be achieved in standard SQL label expressions with a little know-how or a few good examples to copy.

In many Ordnance Survey (OS) products, motorway junctions are provided as road nodes with a textstring attribute of the road numbers meeting at that point and their junction numbers.

Whilst this is great for interrogation and network analytics, in many instances of visualisation the junction number alone may suffice. So we need to find a labelling solution that will extract this information from the textstring and also bear in mind that some nodes where two motorways meet may have 2 numbers.

Scenario 1

If we have a simple motorway number and junction number, e.g. M40 J13.

To label the junction number we need to show everything after the J.

A simple way to do this would be to replace the J and everything before it with nothing.

To do this we can use the regular expression version of the SQL replace function.

We simply need to write in the label box, regexp_replace(a, b, c), where a is the field containing the junction information, b is the part of the string we are replacing, and c is what we are replacing it with.

regexp_replace("JUNCTION", '.*J', '')

Note we need to use a regular expression to allow us to use wildcards. The .* notation allows us to replace any motorway number rather than being explicit.

Also c is represented by two single quotes not to be confused with a double quote as used in a.

Scenario 2

A product like Meridian 2 will give the motorway number, junction number and other joining road numbers, e.g. M5 J17/A4018/B4055.

Although this looks complex, all we want to label is the junction number and ignore whatever becomes before and after.

This can be achieved with two regular replace expressions, or by using the SQL substring function.

Using substring, we need to write in the label box, substr(a, b, c), where a is the field containing the junction information, b is the starting point in number of characters from the left of the text string, and c is how many characters to the right if this point we wish to show.

substr( "JUNCTION", strpos("JUNCTION",'J')+1 , strpos("JUNCTION", '/')-strpos("JUNCTION",'J')-1)

Note we need the +1 to start labelling from the right of the J, and we need the -1 to discount the J when counting back from the /.

It should also be noted that in some previous versions of QGIS the string counting process doesn’t work as expected.

We could achieve the same result as above by using two nested regular replace expressions.

regexp_replace(regexp_replace("JUNCTION", '.*J', ''), '/.*', '')

Scenario 3

So what if we have two motorways merging? E.g. M4 J20/M5 J15.

This took me a while to get my head around, but my answer was to join together the label expressions from the first two scenarios using the SQL concatenate function.

Using concatenate, we need to write in the label box, concat(a + b + c), where a is the first part of the label, and b is the second, c the third, and so on.

If we start with the Scenario 2 method for a, we can get the first junction number from the substring of everything between the first J and the /.

substr("JUNCTION", strpos("JUNCTION",'J')+1, strpos("JUNCTION",'/') - strpos("JUNCTION",'J')-1)

Then we add a separating character for our label between the two junction numbers as b, for example I used 4 vertical dots with a space either side.

' ⁞ '

Finally to add c, we want the second junction number from the end of the text string. We can use Scenario 1’s method of finding everything after the last J, or rather replacing everything before it with nothing.

regexp_replace("JUNCTION", '.*J', '')

Putting this all together we have our label expression:

(substr("JUNCTION", strpos("JUNCTION",'J')+1, strpos("JUNCTION",'/') - strpos("JUNCTION",'J')-1))
' ⁞ '
(regexp_replace("JUNCTION", '.*J', ''))

Scenario 4

Bringing this all together, we can cater for all of the scenarios above, which works for a product like OS MasterMap Highways Network which can have a value such as M27 J3/M271 J0/A3057.

If we recall an earlier tutorial on CASE WHEN, THEN. We can create an expression that will do one thing when there are two motorways merging (i.e. then there is a /), and another when there is only one motorway (when there is no /).

(substr("JUNCTION", strpos("JUNCTION",'J')+1, strpos("JUNCTION",'/') - strpos("JUNCTION",'J')-1))
' ⁞ '
(regexp_replace(regexp_replace("junctionNumber", 'M.*J', ''), '/A.*', ''))
(regexp_replace("JUNCTION", 'M.*J', ''))

Note I added the M and A characters to ensure the code is only finding Motorway and A road numbers respectively.

These label expressions and more can be found in the official OS product stylesheets on Github, which are a great starting point to understand labelling.


Leave a Reply

Your email address will not be published. Required fields are marked *


Get every new post on this blog delivered to your Inbox.

Join other followers: