Connecting to Address Data

OS MasterMap Highways contains USRN as a unique reference for each street. This is really important as this provides a better link to addressing than the TOID information in the AddressBase Premium cross-reference table. This also gives more accurate results to users planning and using services by allowing them to link more effectively to a road. This improves a user's ability to identify properties, for use in routing; planning services such as waste management; calculating routes to school; reviewing service location and provision; identifying those affected by street works.

Use Cases:

• Identify delivery/collection addresses

• Optimising waste collection

• Metering

• Service review and optimisation

Question: How do I connect addresses to a road?

A: AddressBase will give you a basic answer, but OSMM Highways can tell you much more… By linking across the relational mode using the unique keys in the data you can link the Roadlink geometry to AddressBase data. This will give a more reliable match than using the Cross Reference table in AddressBase as it is based upon gazetteer data.

TOID	USRN
Relates to tables of restrictions and other information in RoadLink and Road tables	Relates maintenance and reinstatement in streets table
	* USRN not currently populated in Scotland

What You Will Need

Data

• Roads Specification

• Roadlink

• RoadLink_FormsPartof *

• Roadlink TOID

And to go one step further:

• Road

• Street

* = SQL wildcard. This will display all columns in the data in the query result. As an alternative you can list only the columns you need separated by a comma e.g. column1, column2, column3….

Software

• Translation software is required to convert data from GML into your chosen format. FME workbenches are available on GitHub to help with this.

• Data storage – once translated the data will need to be stored in a format compatible with your systems, this may be on your file system as a shape file, geopackage or tab file or in a database.

• GIS or web application to manipulate and display your results.

Steps to Success: Connecting to Address Data

1. Using your preferred method translate the GML for Roadlink, Road and Street into a format of your choosing.

2. To join the RoadLink geometry to addresses in AddressBase the USRN* can be used as a cross-reference. In the technical specification the USRN of the Street the RoadLink forms part is held in the RoadLink table. However, some translators will split this out into a separate table to avoid duplication into a RoadLink_FormsPartOf.

3. Either in your database or GIS query select the RoadLink you want to find the addresses for. In the example I want to select all properties along the A3026

SELECT * FROM highways_nov.roadlink as r, as a WHERE r.roadclassificationnumber = 'A3026’

1. Next is to link to RoadLink_FormsPartOF on TOID in both tables.

SELECT * FROM highways_nov.roadlink as r, highways_nov.roadlink_formspartof as a WHERE r.roadclassificationnumber = 'A3026'AND r.toid = a.toid

1. This will return a list or RoadLinks with a reference to both the Street and Road it forms part of. As the USRN is a reference to the Street we can exclude Road from our query. In the GML formsPartOf includes these references, some translators may join these in one field or create a separate column.

<highway:formsPartOf xlink:role="Road" xlink:href="#osgb4000000003222248"/>

1. The final step is to link to either AddressBase Plus or Premium using the USRN. (AddressBase does not contain the USRN).

* USRN not currently populated in Scotland

The Results

Remember…

• USRN in AddressBase is an integer

• To remain INSPIRE compliant, OSMM Highways needs to have the USRN integer value prefixed by ‘USRN’

Solution:

• Create a lookup table to combine values from AddressBase with a character field with USRN added or

• Concatenate the 2 fields within your SQL query something like CONCAT(‘usrn’, usrn)

Completed SQL query:

SELECT * FROM highways_nov.roadlink as r, highways_nov.roadlink_formspartof as a WHERE r.roadclassificationnumber = 'A3026’ AND r.toid = a.toid AND a.reftype = 'Street’.

Example output:

Example SQL query output

With an output similar to this depending on which columns you choose to return.

SQL data query output

Additional Steps

You can enhance this output even further by considering Road and Street features. Road feature – this represents a collection of RoadLinks that share the same name or classification number and can be used to create a list of all RoadLink TOIDs for a feature and then linked to a USRN. Street feature - Additional information on the street is contained in the Street record and can be linked to the RoadLink using the RoadLink_FormsPartOf reference.

Additional information includes:

• Street Type: The type of Gazetteer record for which the USRN relates

• Operational State: indicates whether the Street is proposed, under construction, open, or closed (permanently or temporarily)

• Responsible Authority: reference to the authority who performs an administrative function – notably naming and numbering

• Local name: Unofficial local name associated to the highway