In this project I analysed the data of vehicles without registration and apply rules to assess corresponding risk based on the vehicle type. I performed rules implementation for classification of risks and calculate a quantitative risk score.
This rule-based classification system + scoring model can be easliy modified to a rule engine with more rules and related data.
I used SQL queries in Azure Databricks SQL warehouse to clean, transform, and analyse data. I also used Power BI tools to visually present the results. All SQL queries and other codes are provided in the directory sql_and_jupyter_files/ and the Power BI files can be found in power_bi_files/.
Rules and logic for non-compliance determination and risk classicifaction of non-compliant vehicle types
If the vehicle has 'inactive' registration status in the Waka Kotahi dashboard data, the vehicle is being operated without valid registration, and is therefore non-compliant.
These are the ones where non-compliance actually matters a lot.
- BUS
- GOODS VAN/TRUCK/UTILITY
- PASSENGER CAR/VAN
- MOTORCYCLE
- HIGH SPEED AGRICULTURAL VEHICLE
Why:
- Regular road use
- Higher accident impact
- Often tied to transport/economic activity
- More likely to be actively used even if non-compliant
- MOTOR CARAVAN
- MOPED
- SPECIAL PURPOSE VEHICLE
Why:
- Road-legal but not always heavily used
- Mixed usage patterns
- Lower exposure than cars/trucks
These are often off-road, seasonal, or low exposure
- TRAILER/CARAVAN
- TRAILER NOT DESIGNED FOR H/WAY USE
- ATV
- MOBILE MACHINE
- TRACTOR
- AGRICULTURAL MACHINE
Why:
- Often not used on public roads
- Can be inactive without being “non-compliant in practice”
- Lower safety/system impact
nzta_rule_project/
├── analysis_images/
│ ├── non_compliance_rules-1.png
│ ├── non_compliance_rules-2.png
│ ├── non_compliance_rules-3.png
│ ├── non_compliance_rules-4.png
│ ├── risk_analysis_git_bar.png
│ ├── risk_analysis_git_donut.png
│ ├── risk_analysis_git_map.png
│ └── risk_analysis_git_tables.png
├── data/
│ ├── input/
│ │ ├── from_waka_kotahi_portal_all_regions_active_inactive.csv
│ │ └── tms_daily_traffic_counts.parquet
│ └── output/
│ ├── counts_combined_with_non_compliance_grouped_by_region_oct22_using_sql.csv
│ └── sql_non_compliance_risk_and_score_full_database_creation.csv
├── power_bi_files/
│ ├── non_compliance_rules_git.pbix
│ └── risk_analysis_git.pbix
├── sql_and_jupyter_files
│ ├── column_names_cleaning_all_regions_active_inactive.ipynb
│ ├── non_compliance_counts_oct2022_sql.sql
│ ├── risk_analysis_high_risk_vehicle_type.sql
│ ├── risk_analysis_region_wise_high_risk.sql
│ ├── risk_analysis_region_wise_risk_score.sql
│ ├── risk_analysis_risk_categories.sql
│ ├── risk_analysis_vehicle_type.sql
│ └── sql_non_compliance_risk_and_score_full_database_creation.sql
└── README.md
- I used the bronze layer counts data from my other project https://github.com/shreyas-tiruvaskar/nz-road-network-traffic-etl-pipeline which gives daily counts of vehicles with information like region, date, etc. This is
data/input/tms_daily_traffic_counts.parquet - The NZTA open data portal provides a Dashboard (https://opendata-nzta.opendata.arcgis.com/search?tags=vehicles). I used that dashboard and selected different fields for all the months' data. All months can be selected using the filters at the top of the dashboard. This data is in .csv format which I provide in
data/input/from_waka_kotahi_portal_all_regions_active_inactive.csv.
- I used
sql_and_jupyter_files/column_names_cleaning_all_regions_active_inactive.ipynbandnzta_project/rules_project/for_git/sql_and_jupyter_files/non_compliance_counts_oct2022_sql.sqlto clean, transform, and combine the two data sources into a signle tabledata/output/counts_combined_with_non_compliance_grouped_by_region_oct22_using_sql.csv. - For the risk related rules application and an extended database creation with risk related columns, I used
sql_and_jupyter_files/sql_non_compliance_risk_and_score_full_database_creation.sql. This database with all the rule applied risk information can be found indata/output/sql_non_compliance_risk_and_score_full_database_creation.csv. This table was used for the risk analysis SQL queries.
I present the static images from the Power BI analysis. The provided Power BI files can be used to look at the interactive results.
For the following 3 plots, the table data/output/sql_non_compliance_risk_and_score_full_database_creation.csv was subject to SQL queries. I present the images and the corresponding SQL queries that can be used to regenerate the tables, which I used in Power BI to produce these 3 images.
SQL query used: sql_and_jupyter_files/risk_analysis_risk_categories and sql_and_jupyter_files/risk_analysis_high_risk_vehicle_types.
SQL query used: sql_and_jupyter_files/risk_analysis_vehicle_types.
Avergae (weighted) risk score of all vehicles by region and Number of non-compliant vehicles by region
SQL query used: sql_and_jupyter_files/risk_analysis_region_wise_high_risk and sql_and_jupyter_files/risk_analysis_region_wise_risk_score.
The table- data/output/counts_combined_with_non_compliance_grouped_by_region_oct22_using_sql.csv was imported in Power BI to generate the following results.
Percentage = (Non-compliant / (Compliant + Non-compliant)) * 100
- The 'inactive' status means that the vehicle was registered and then de-registered in the same month. For this project, we call that non-compliance by assuming that the 'inactive' vehicles are still in use after de-registration.
- Due to lack of access, I could only factor this 'inactive' status towards 'vehicles without active registration'.
- This project reports the aggregated non-compliance over the time period covered on the Waka Kotahi vehicle data portal (Jan 2021 - Feb 2026) unless stated otherwise.
- In region-wise classification, we assume that the region of latest registration is the region of regular use.
- While approximating the total number of non-compliant vehicles using Waka Kotahi 'TMS dailiy counts data', we assumed that the non-compliance percentage from the Dashboard data propagates to the total counts.
- All the assumptions stated above can be improved using the actual active registration data which is not available in public domain (not accessible to me while I performed this analysis).
- This project can be elevated to a full rule engine with multiple compliances and rule logics.