Creating a Map of Winter Gritting Routes

• Special designations contains references to which streets are gritted.

• This information can be displayed against either the street geometry or road link.

Use cases

• Understanding which is the best route to use in winter avoiding untreated roads where possible

  • Emervency services

  • Logistics

What You Will Need:

Data

• Roads Specification

• Roadlink

• Street

• StreetNetworkRef

• RAMI Specification

• SpecialDesingation_Area

• SpecialDesingation_Line

• SpecialDesingation_Point

• SpecialDesingation_NoGeom

Software

• Translation software is required to convert data from GML into 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. Information on Gritting routes is held in the Special Designation tables. This can be viewed with a simple query for example:

 SELECT networkref, designation, description FROM highways_nov.specialdesignation_area where designation LIKE '%Winter%’

1. However as Special Designation is split into 4 tables depending on geometry type. The results can be merged into 1 table by querying all 4 with UNION ALL and dropping the geometry from the query output.

2. If you are working with a large data set it may be easier to create a new table of the results. (CREATE TABLE AS…)

3. The results can be linked using USRN to the Street table and use the geometry to display.

4. However you may want to add these to RoadLink as this includes classifications as well as road name and number.

5. This can be achieved by linking the USRN using the StreetNetworkRef table containing TOIDS:

SELECT * FROM highways_nov.winter AS w, highways_nov.streetnetworkref AS r WHERE w.networkref = r.usrn;

1. Use the TOID to then join the data to RoadLink, the example limits the information returned:

SELECT w.designation, w.description, r.toid, r.roadclassification, r.formofway, r.geom FROM highways_nov.winter_toids AS w, highways_nov.roadlink AS r WHERE w.roadlink_toid = r.toid;

1. The output can then be displayed on a map or used as part of routable network to add a priority to roads that have been treated.

SQL query output from address data

Tips for Success: Cleaning Geometry Created with FME

• When FME writes to some databases it writes the geometry as geometry and not point, line or area.

• This creates a problem for some software reading the metadata as it does not fully understand the data type.

• This can be resolved by cleaning the metadata table – In PostGIS use: SELECT Populate_Geometry_Columns()

• Or by pre-creating the table and in the FME writer set table handling to either ‘Use Existing’ or ‘Create If Needed’.

• An SQL script can be used within FME to create the table if necessary