Database and Prioritisation Workbook – v3.0
NZ Transport Agency
6th Floor, DTZ House, 76 Cashel Street, Christchurch 8011, New Zealand
P O Box 710, Christchurch Mail Centre, Christchurch 8140, New Zealand.
T +64 3 363 8500 F +64 3 363 8501
NZ Transport Agency (NZTA)
Published November 2009
ISBN 978-0-478-33451-7 (print)
ISBN 978-0-478-33452-4 (online)
This publication is copyright ©NZTA
Material in it may be reproduced for personal or in-house use without formal permission or charge, provided suitable acknowledgement is made to this publication and the NZTA as the source. Requests and enquiries about the reproduction of material in this publication for any other purpose should be made to:
Private Bag 6995
The permission to reproduce material in this publication does not extend to any material for which the copyright is identified as being held by a third party. Authorisation to reproduce material belonging to a third party must be obtained from the copyright holder(s) concerned.
Disclaimer: This document does not override governing legislation. The NZTA does not accept liability for any consequences arising from the use of this document. If the user of this document is unsure whether the material is correct, they should make direct reference to the relevant legislation and contact the NZTA.
For more information…
Private Bag 6995
T 64 4 894 5400
F 64 4 894 6100
This document is available at www.nzta.govt.nz
Quality informationDocument / Deficiency Database and Prioritisation Workbook - v3.0
AECOM Ref / 60021494
N:\Dept_72\Current Projects\CAD LTNZ001 DDPP (60021494)\DDPP Users Guide\DDPP Users Guide v3\Deficiency Database and Prioritisation Workbook v3.0.doc
Date / November 2009
Prepared by / George JasonSmith
Reviewed by / Brendan Bisley, Mark Gordon
Revision historyRevision / Revision date / Details / Authorised
Name/Position / Signature
Production / 27/06/2007 / Mark Gordon
Maunsell Ltd /
Peer review / 17/07/2007 / Opus international Consultants Ltd / Mike Petersen
Version 1.0 / 09/08/2007 / Reformatted to Land Transport NZ style / Lesley Regan
Land Transport NZ
Software v2.0 / 20/12/2007 / Update to Software v2.0 build 4 / Mark Gordon
Maunsell Ltd /
Workbook v2.0 / Feb 2008 / Reformatted to the Land Transport NZ style / Lesley Regan
Land Transport NZ
Workbook v2.1 / Jan 2009 / Reformatted to the NZTA style with minor amendments. / Lesley Regan
Workbook V3.0 (draft) / Sept 2009 / Updated to reflect software changes / Mark Gordon
Regional Manager AECOM Ltd
Workbook V3.0 / Nov 2009 / Updated to reflect government policy changes / Bill Greenwood
Principal Engineer NZTA
NZTA DDPP course book v.3.0
3Actual/real problems versus potential problems/concerns......
5Reducing data needs......
5.1The effect of routine maintenance
6Using the information......
6.1Current recording methods
6.2A spreadsheet solution
7.1.2Benefit cost ratio
7.1.3First year rate of return (FYRR)
7.1.4Weighted multiple criteria
8.3A note of caution
10A suggested approach......
Appendix A.A spreadsheet solution......
Appendix B.Updating the database......
Appendix C.Change Log......
NZTA DDPP course book v.3.0
In its major policy statement on road safety, Road safety to 2010, the Government set goals to reduce road fatalities to less than 300, and hospitalisations to less than 4500, by 2010. Despite initial success in reducing road trauma these targets are unlikely to be achieved. These goals are now being reviewed to carry NZ forward into the next decades. The Governments ‘Safer Journeys’ vision is that by 2020 New Zealand will have A safe road system that is increasingly free of death and serious injury. Safe System is an international concept built on three key principles:
- the fallibility of the road user – errors must be expected even among compliant users
- the physical vulnerability of the road user in a crash – humans have limited ability to withstand kinetic impact
- responsibility for road trauma is shared between users and designers of the system.
Road safety is built on these foundational principles. The challenge then becomes how to design and manage the system to minimise crashes and reduce their severity, through managing interactions between the road, the driver’s speed and the vehicle, minimising the consequences of human error.
An on-going effort is required to meet these goals; to help achieve them, the NZ Transport Agency (NZTA) has been supporting and encouraging road controlling authorities (RCAs) to develop safety management systems (SMS) for their road networks. All but one of NZ’s territorial RCAs has developed an SMS.
1.1 Deficiency databases
While all of these SMSs require road hazards and safety deficiencies to be recorded, there are few proven or off-the-shelf systems available for doing this. Many RCAs have these lists in various forms.
Late in 2005, Land Transport NZ published a report entitled Deficiency database and prioritisation process report November 2005 (the DDPP report) that reviewed the state and availability of deficiency databases in NZ. Among other things, it concluded:
‘… a safety deficiency database is comprised of two key parts – a deficiency database and a prioritisation process.
The deficiency database is a system used to capture, store, manipulate and manage information on deficiencies on the road network, collected from a range of data sources.
The prioritisation process is a risk assessment model that can be applied to the data collected and stored within the database to produce a range of responses. It helps the user decide which deficiencies need to be treated and the value of the treatments (ie the safety return gained by the use of the treatments).
In order for a safety deficiency database to assist an RCA in delivering its SMS, it must have these two functions to allow it to capture required information about a range of deficiencies identified on their network and then be able to analyse and assess these deficiencies.
In the context of road-crash reduction two of the principal matters to be considered by the road controlling authority are whether the crash stimuli can only be managed (eg flooding) or whether something can be done about them (eg improve an intersection).
This booklet, and the associated workshop, has been developed on the basis of the Land Transport NZ report and stem from the NZTA’s desire to ensure that RCAs have the tools to allow them to follow the procedures they have established for themselves in their SMS and thus meet the goals set out in Road safety to 2010.
In addition to these drivers, there is a very clear requirement for local authorities controlling roads to act in the interests of the safety of the public and road users; – this is contained in Section 353 of the Local Government Act 1974, which states:
353. The council shall take all sufficient precautions for the general safety of the public and traffic and workmen employed on or near any road and, in particular, shall – …
There is a tendency for RCAs to get involved in the detail of this clause and thus overlook its key requirement, which is detailed in its first phrase: ‘The council shall take all sufficient precautions for the general safety of the public and traffic …’ This places a clear unequivocal duty on councils to manage their roads safely and severely moderates the broad discretionary powers to maintain roads as they see fit, conveyed under Section 319 of the Act. The Act can be found at: http://www.legislation.govt.nz/act/public/1974/0066/latest/DLM415532.html 
2 Some definitions
In the context of road crash reduction, two of the principal matters to be considered by the road controlling authority are whether the crash stimuli can only be managed (e.g. flooding) or whether something can be done about them (eg improve an intersection). These agents are called hazards and deficiencies (sometimes safety deficiencies) respectively.
This workbook, and the presentations made at the workshop, will use the term deficiencies rather than safety deficiencies.
A hazard is a deficiency for which there is no reasonable fix available, for example, flooding or landslide. Consequently, hazards are managed to limit their effects.
Deficiencies are fixable. They include:
- poor alignment
- insufficient surface friction
- poor pavement width
- high roughness
- capacity/number of lanes
- ‘side friction’ from roadside development and activities
Road management is moving into sustainability and similar concepts and it is becoming more relevant for RCAs to record deficiencies in network performance. Most RCAs have some record of network deficiencies to help them track levels of service. However, this workbook and the workshop do not specifically include identification or prioritisation of non-safety network deficiencies. The NZTA is currently developing some tools to assist in the identification and prioritisation of deficiencies other than safety deficiencies. The Cycle network planning guide is one example of such a tool.
The definition of a hazard or deficiencies is independent of method or methods that might be used to remedy the problem or to fund it.
3 Actual/real problems versus potential problems/concerns
There is another division of road safety issues that is important. There are often sites where the consequences of a crash are obvious but where there has never been one, ie potential sites, and those where there is a recorded history of crashes (real problems) eg crash black spots.
Until the advent of the Land Transport Management Act 2003, the benefit cost ratio approach to funding directed us towards considering only actual problems. Potential problems have been considered to a much lesser extent, often only when looking at the effects of a possible solution or in carrying out a ‘full procedures’ crash analysis. When a deficiency approach is used, both actual and potential effects must be considered.
Tools to help manage the differences between these two types of deficiency are presented below.
4 Addressing deficiencies
Before anything can be done systematically about hazards and deficiencies they need to be identified and recorded. This is best done by compiling a computerised list of hazards and deficiencies – often referred to as a deficiency database whether it is a formal ‘database’ or not.
In its Deficiency database and prioritisation process report November 2005, Land Transport NZ identified and discussed three proprietary safety deficiency databases currently available in NZ. None of them has been widely adopted by RCAs, apparently because they appear to be complex and difficult to use.
The report also identified the data needed for a deficiency database, with over 50 data items identified and over seven pages of detail devoted to it. While this is a large volume of information, much of it is already held in RCAs’ RAMM databases.
The need for fieldwork, duplicated effort and complications around data maintenance, database complexity and administrative burden can all be reduced by:
- giving careful thought to the structure of the database during its development phases
- making maximum use of existing data eg that held in RAMM and CAS
- using standard site identifiers
- using standard terminology
- automating calculations where feasible and possible
- automating links to existing data held in other databases.
- A review of the data requirements suggests that if RAMM data is good and has been properly maintained, only the following additional information will need to be collected in the field:
- Site location in terms of RAMM/GPS coordinates
- Site dimensions (for estimates of remedial works)
- Traffic operating speed
- The nature and description of the problem
- The realistic likelihoods and consequences of the deficiency/hazard. (Can be done in the office if necessary)
- Are there any realistic solutions or interventions that will reduce the risks associated with the issue? 
- Photographs of the hazard or deficiency, including photos of the likely ‘resting place’ of the vehicle or vehicles encountering it; these will be important for supporting assessments of the likelihood and consequences for crashes.
Collection of this additional data should take around a half an hour per site in the field, depending on the complexity of the problems presented.
The following data can be collected, or established on-site or in the office. If it is to be done in the office then the photographs taken in the field and sound network knowledge will be particularly relevant.
- The realistic consequences of the deficiency/hazard (best done in the field)
- The likelihoods of these consequences (best done in the field)
- The likely consequences AFTER each intervention
- The realistic likelihood of the post-intervention consequence occurring.
Once this data has been recorded there still are a few items remaining to be detailed. They are probably all office-based and are:
- the crash record (from CAS or RAMM)
- an estimate for each solution or intervention.
You should also note:
- the cost index for each estimate
- the status/accuracy of each estimate
- traffic volume (from RAMM).
5 Reducing data needs
The appropriate use of filters can significantly reduce the amount of data that is collected and recorded in the database. Similarly, inappropriate use of filters can leave you with a database that does not do what it is supposed to – record the deficiencies on the network.
Filtering is the process of removing some of the data from consideration, differentiation between hazards and deficiencies is one form of filter. Other possible filters include funding methods, work type and level of risk. Large numbers of filters can be developed, their range being limited by only practical considerations.
Not all filters have to be applied at the same stage in the process. The data collection and prioritisation process outlined in the flow diagram at Figure 3 on page 19 includes five filters; these are shown in simplified form below – two are employed very early in the process, before data is recorded, and three during prioritisation. Their places in the process are also shown in the following summary flow chart:
Figure 1 Database filtering Filter
5.1 The effect of routine maintenance
Most network inspections will throw up problems or deficiencies that fall within the scope of a maintenance contract and that should be addressed / repaired through the appropriate contract. This gives rise to the question:
If a problem will be addressed by specific work required by a maintenance contract, should it be recorded as a deficiency?
The answer to this question is: It depends! Once more, it is appropriate to refer back to the context in which the deficiency database is being used:
- Road safety to 2010
<4500 hospital admissions
- LGA 1974 S 353
- The ‘Safer Journeys’ vision is that by 2020 we will have A safe road system that is increasingly free of death and serious injury and the challenge to design and manage the system to minimise crashes and reduce their severity.
If recording a deficiency that will be addressed under routine maintenance will help in achieving these aims, it should be recorded – otherwise, it need not. Some other matters that will influence this decision are:
- When will/can the work be actioned?
- Is it part of a contract lump sum?
- Do existing contract management procedures provide a sufficiently robust and reliable record of the problem’s identification and the actions taken to remedy it?
- Whether a maintenance intervention is the only solution.
The answers to these questions may show that the deficiency is not worth recording. However, if the maintenance intervention is only one of a number of possible, mutually exclusive, interventions:
- the deficiency should be recorded with the maintenance intervention noted as one potential solution
- the deficiency should be recorded so that the other interventions can be assessed and prioritised.
Some people may prefer to err on the side of caution, recording the deficiency and then noting it as ‘resolved/fixed’ under the maintenance contract.
6 Using the information
Because of the potential size and complexity of the registers, relational databases are the best tool to use for recording deficiencies. However, as not many people have access to a relational database other than RAMM, and as reporting from databases can be an issue, spreadsheets (eg MS Excel®) are a good and easy alternative. Paper or computer-based lists may work in the very short term for a small number of deficiencies but are unlikely to be a suitable tool for most RCAs.
6.1 Current recording methods
The DDPP report found the following systems in use in NZ in 2005:
- Opus SNAP – a purpose-built database. A hazard register and deficiency database is part of wider functionality.
- MWH NM2 – a purpose built database.
- Australian Roads Research Board (ARRB) – Road Safety Risk Manager (RSRM).
- Various MS Excel® spreadsheet-based records.
The first three of these systems are discussed on page 18 of the DDPP report; the two spreadsheet solutions reviewed are discussed on page 15 of the same report.
6.2 A spreadsheet solution
An MS Excel® spreadsheet that can be used as a hazard register and deficiency database is included with this booklet. Use of the spreadsheet is described below. It has been designed so that the data can be imported easily into RAMM, from the spreadsheet, when RAMM has DDPP capability.
So far discussion has focused on the collection and recording of data, however, the DDPP report states ‘… a safety deficiency database is comprised of two key parts – a deficiency database and a prioritisation process.’
Prioritisation is the process of allocating time, finance and other resources to competing tasks so that the more important tasks are undertaken first. Therefore, the need to establish what the important aspects of projects are is intrinsic to prioritisation. A key prioritisation method for safety prioritisation is risk, which is the product of the likelihood and consequence of an event.