Driver licence status

samuel.mortlock
20 June 2023

Outputs:

SQL: [IDI_Modules_202606].[licencing].[driver_status]
SAS: libname cm_drvrs odbc dsn=IDI_modules_202606_srvprd schema=licencing; run ;
How to access a code module in the Data Lab: Read here

Inputs:

Dependency Dependency Type
[IDI_Clean_202606].[nzta_clean].[drivers_licence_register] Source
[IDI_Clean_202606].[security].[concordance] Source

Context

This module creates a table of individuals driver licences spells by licence class (one to six), stage (learner to full) and status. This information is sourced from Waka Kotahi’s register of driver licences and classes.

Business Concepts

In New Zealand people must hold a current licence to drive a vehicle on public roads. Licences are specific to the type of vehicle (e.g., car, heavy vehicle, and motorcycle). Licences are often used as proof of identification and are a pre-condition for applying for many types of employment.

Licence classes

There are six licence classes based on the type of vehicle:

Graduated Driver Licensing System (GDLS)

The New Zealand driver licence system is graduated, consisting of three stages for cars and motorcycles, and two stages for trucks.

For cars (class 1) the GDLS involves

For motorcyclists (class 6) the GDLS involves

For trucks driver (classes 2-5), the GDLS involves two stages: learner and full licence.

Class statuses

Status is specific to a driver licence class (e.g., car, heavy vehicle, and motorcycle).

Status changes can be made in error by a driver licence agenct or staff member. For example, a failed test incorrectly entered as a pass. Here, the status may show as “Current” then “Cancelled” a few days later.

Licence expiry

As at June 2022, the following expiry rules apply:

These rules exclude licences being invalid for other reasons. For example, a suspended licence due to breaking traffic laws. These rules need to be reviewed periodically.

Obtaining a New Zealand driver licence

In New Zealand, driver licences for motor vehicles were required from 1 April 1925. Drivers had to be 15 years or older with licences issued by local authorities for a maximum of 12 months (refer ss.20 & 21 of the Motor-vehicles Act 1924). In 1953, the renewal period was extended to five years (refer s.3 of the Transport Amendment Act 1953). From 1964 local authorities could transfer the role of issuing driver licences to Commissioner for Transport (refer s.6 of the Transport Amendment Act 1964).

In 1966 a national licence register was set up with local authorities required to forward details to the Commissioner of Transport within 14 days of each licence being issued. But local authorities still had primary responsibility for issuing driver licences (refer s.7 of the Transport Amendment Act 1966).

In December 1983 local authorities ceased issuing driver licences and the Secretary for Transport took sole responsibility (refer part III of the Transport Amendment Act (No. 3) 1983).

In 1976, the government introduced the Graduated Driver Licensing System (GDLS) for motorcyclists (class 6) (refer The Motor Drivers Regulations 1964, Amendment #15). This was extended to car and truck drivers on 1 August 1987.

The Driver Licence Register (DLR) was created in May 1999, therefore history of class/stage status changes prior to this date is unavailable.

Business History

Date Area Change
1976-8-1 Licensing Graduated Driver Licensing System (GDLS) introduced for motorcyclists
1976-8-1 Licensing Graduated Driver Licensing System (GDLS) introduced for cars and trucks
2011-8-1 Licence age Applying for a learners licence increased from 15 to 16
2012-2-27 Licence testing Stricter testing to gain a restricted licence
2014-12-1 Licence expiry Restricted and learner licence expiry decreased from 7 to 5 years
2019-12 Licence expiry Licences due to expire by December 2019 had their expiry extended by two years to December 2021

Waka Kotahi Open Data Comparison

To verify the accuracy of IDI sourced Driver Licence spell data, we compared numbers of licence holders (via the IDI) to the numbers that Waka Kotahi publish in their open data portal: https://opendata-nzta.opendata.arcgis.com/

The IDI sourced data was snapshotted on 1 July 2012 to 2021 to match the extract date of the open data number. We will continue to monitor the consistency of these numbers over time.

Total number of driver licence holders aged 16 plus on a class 1 licence by stage from 2012 to 2021 (numbers rounded to nearest 1,000).

Full Learner Restricted All
IDI Open Data Match IDI Open Data Match IDI Open Data Match IDI Open Data Match
Jul-12 2,738 2,753 99.4% 226 227 99.7% 275 286 96.4% 3,239 3,266 99.2%
Jul-13 2,759 2,775 99.4% 234 234 99.8% 264 272 96.9% 3,256 3,282 99.2%
Jul-14 2,786 2,803 99.4% 247 247 99.8% 266 273 97.6% 3,300 3,323 99.3%
Jul-15 2,836 2,853 99.4% 253 254 99.8% 271 276 98.2% 3,360 3,383 99.3%
Jul-16 2,902 2,918 99.5% 257 257 99.8% 284 288 98.6% 3,442 3,463 99.4%
Jul-17 2,963 2,984 99.3% 260 260 99.7% 289 293 98.8% 3,512 3,537 99.3%
Jul-18 3,032 3,052 99.3% 263 264 99.8% 293 296 99.0% 3,588 3,612 99.3%
Jul-19 3,093 3,113 99.4% 268 269 99.8% 297 300 99.2% 3,659 3,681 99.4%
Jul-20 3,132 3,151 99.4% 273 274 99.8% 297 299 99.3% 3,702 3,723 99.4%
Jul-21 3,207 3,228 99.4% 281 282 99.8% 301 304 99.0% 3,789 3,813 99.4%

References

  1. Begg D. Stephenson, S. (2003). Graduated driver licensing: the New Zealand experience. Journal of Safety Research 34 (2003) 99-105.
  2. New Zealand driver licence classes: what you can drive.

Community of Interest

Who Agency Involvement
Marc de Boer MSD Encyclopaedic & Experiential Documentation review.
Tony Marlow Waka Kotahi Encyclopaedic Documentation review.
Boah Rasmussen Waka Kotahi Preparation of table of NZTA driver license register status
Tom Zhou Waka Kotahi Preparation of table of NZTA driver license register status
Sam Mortlock MSD Initial draft of the code. Module Steward
Victoria Van Loenhout Nicholson Consulting Module coder
Vinay Benny Nicholson Consulting Module coder

Key Business Rules

  1. For licences granted under GDLS (1 August 1987 onward), we calculate the end date of a status as one day before the start of the next status.
  2. Licences before 1 August 1987 will only have the full status.
  3. Assumes pseudo-licences and diplomatic licences are out of scope for most research projects.

Parameters

The following parameters should be supplied to this module to run it in the database:

  1. {targetdb}: The SQL database on which the spell datasets are to be created.
  2. {targetschema}: The project schema under the target database into which the spell datasets are to be created.
  3. {projprefix}: A (short) prefix that enables you to identify the spell dataset easily in the schema, and prevent overwriting any existing datasets that have the same name.
  4. {idicleanversion}: The IDI Clean version that the spell datasets need to be based on.

Variable Descriptions

The business key for this spell table is one row per snz_uid, licence class, licence stage, licence status, and spell start.

Type Column Name Description
Entity snz_uid The unique STATSNZ person identifier for the licence holder
Entity snz_nzta_uid The encrypted person identifier from Waka Kotahi’s source data
Source data_source Hard-coded to “nzta_dlr”
Period spell_start Drivers licence class from date i.e. a date stamp for each update of licence stage/class.
Period spell_end End date is derived from the following spell_start date minus one day. If there is no end date (i.e., this is the latest licence record), this will be high end dated (e.g., 31Dec9999)
Event information nzta_dlr_licence_class_text Type of vehicle a driver is permitted to drive. Licence classes 1 to 6, as described in the documentation.
Event information nzta_dlr_licence_stage_text Stage a driver is in of the graduated driver licencing system for a vehicle class. Full, Restricted, Learner.
Event information nzta_dlr_class_status_text Status of driver to drive under licence class. Current, Expired, etc.

Module Version & Change History

Date Version Comments
01 June 2023 Initial version based on specifications from Commissioning document.

Code

 :setvar targetdb "{targetdb}"
:setvar targetschema "{targetschema}"
:setvar projprefix "{projprefix}"
:setvar idicleanversion "{idicleanversion}"

/* Delete the database object if it already exists */
DROP TABLE IF EXISTS $(targetdb).$(targetschema).$(projprefix)_driver_licence_status;

DROP TABLE IF EXISTS #driver_licences_status_temp;
WITH nz_licences AS(
    SELECT DISTINCT [snz_nzta_uid]
        , 'nzta_dlr' AS [data_source]
        , [nzta_dlr_class_status_text]
        , [nzta_dlr_licence_class_text]
        , [nzta_dlr_licence_stage_text]
        , [nzta_dlr_class_from_date]
        , CASE 
			WHEN [nzta_dlr_licence_stage_text] = 'FULL' THEN 1
			WHEN [nzta_dlr_licence_stage_text] = 'RESTRICTED' THEN 2
			WHEN [nzta_dlr_licence_stage_text] = 'LEARNER' THEN 3 
			ELSE 4 
		 END AS stage
        , CASE 
			WHEN [nzta_dlr_class_status_text] = 'CURRENT' THEN 1 
			ELSE 0 
		 END AS current_indicator
    FROM $(idicleanversion).[nzta_clean].[drivers_licence_register]
    WHERE [nzta_dlr_licence_type_text] = 'STANDARD'
)
    , nz_licences_s AS(
    SELECT *
        , ROW_NUMBER() OVER (
			PARTITION BY [snz_nzta_uid], nzta_dlr_licence_class_text, nzta_dlr_class_from_date 
			ORDER BY current_indicator DESC,nzta_dlr_class_from_date DESC, stage ASC
			) AS rn
    FROM nz_licences
)
    , licences_spine AS(
    SELECT c.snz_uid
        , dl.*
        , COALESCE(DATEADD(dd, -1, LEAD(dl.nzta_dlr_class_from_date) OVER (
			PARTITION BY dl.snz_nzta_uid, dl.nzta_dlr_licence_class_text 
			ORDER BY dl.nzta_dlr_class_from_date)), '9999-12-31') AS spell_end
    FROM nz_licences_s dl
    INNER JOIN $(idicleanversion).[security].concordance c
    ON dl.snz_nzta_uid = c.snz_nzta_uid
    WHERE rn = 1
)
SELECT [snz_uid]
    , [snz_nzta_uid]
    , [data_source]
    , [nzta_dlr_class_from_date] AS spell_start
    , [spell_end]
    , [nzta_dlr_licence_class_text]
    , [nzta_dlr_licence_stage_text]
    , [nzta_dlr_class_status_text]
INTO #driver_licences_status_temp
FROM licences_spine; 

SELECT * 

INTO [$(targetdb)].[$(targetschema)].[$(projprefix)_driver_status]

FROM #driver_licences_status_temp
 ;