Crash Data Overview

Crash Reporting Process and Data Flow

Crash event records are entered electronically by public safety officers from hundreds of jurisdictions across the state. All crash data are then submitted to the Utah Department of Public Safety (DPS) for quality control, reporting, and analysis. The following is the general data flow and process for crash data reporting.

1. Officer enters crash event using an electronic form

   • The responding officer enters crash event data using their agency's software. Each agency has their own software vendors and reporting systems. 

2. Crash event data recorded on agency system

   • Each agency maintains their own database of crash records. All agencies are required to match statewide data schemas.

3. Agency submits electronic records to the DPS

   • Each agency is required by law to submit all crash event records to the DPS, who maintains a database of all crash events statewide.

4. UTAPS Retrieves data from DPS databases

   • The Utah Transportation & Public Safety (UTAPS) group is a team within the University of Utah that supports crash data analysis, research, and data quality control for UDOT and DPS. On a daily basis, UTAPS pulls crash data from the DPS system into their own in-house systems. This includes any amendments to crash reports made in the DPS system.

5. Data is available for analysis and querying

   • Within 24 hours of a crash event records submission to DPS, crash data is available for analysis or querying through UTAPS and other connected external systems.

6. Crash event is reviewed and geolocated in the UTAPS system

   • UTAPS provides a manual quality control (QC) review of all crashes. This QC provides consistency in reporting and corrects common errors. It also ensures that an accurate geolocation (lat/long and route/mp) is assigned to each crash.

   • Because crash data is made available before the QC is complete, recent crashes (during the past 6 months) may have errors or lack an accurate geolocation. Always use caution when reviewing recent crash data.

   • The “crash data verified” field indicates when a crash has been fully QC’d and geolocated. Filtering to only verified crashes assures you that all data is QC’d, but will exclude recent crashes that are not verified. Around 70-80% of crashes are QC’d within two months, the remaining crashes will be slowly submitted and QC’d for six to 18 months after the date of the crash.

7. Crash data locked

   • Locking crash data means that no new records or amendments will be accepted. Crash data for a given year is not completely locked until one year later. For example 2021 crash data is not locked until January 1st, 2023. For additional details on when data can be considered complete please see this article.

Crash Database Structure

There are three primary tables that form the backbone of the crash database. These are the crash table, vehicle table, and person table.

Crash Table

The crash table is the most general level of information. It stores data that are applicable to the entire crash. This table includes one record (row) for each unique crash. The following are a few examples of fields in the crash table.

• Manner of collision

• Date and time of crash

• Location - route/milepoint, lat/long, and street names

• Weather conditions

• Lighting conditions

Vehicle Table

The vehicle table is the second level of information and contains one record (row) per vehicle. There are usually multiple vehicles associated with each crash record. This information is unique to each vehicle and the events specific to it during the crash. The following are a few examples of fields in the vehicle table.

• Vehicle maneuver

• Travel direction

• Vehicle make, model, and year

• Sequence of events

Person Table

The third level of information is the person table and includes one record per person involved in the crash. A unique record exists for every driver, passenger, bicyclist, or pedestrian involved in a crash. Every person is associated with the vehicle they were occupying, except for pedestrians and bicyclists (non-motorists) which are not associated with a vehicle record. The following are a few examples of fields in the person table.

• General demographics such as age and gender

• Injury severity

• Type - driver, passenger, pedestrian, or bicyclist

• Seat belt or helmet use

• Non-motorist actions

Querying Crash Data

The three-table crash database structure can make querying crash data difficult. For example, in order to filter all crashes involving a motorcycle you must first filter the vehicle table for all motorcycle vehicle body types, and then filter the person and crash tables for all records associated with those motorcycle vehicles. Many database or query systems do this automatically, including AASHTOWare Safety but it still requires the analyst to know exactly which fields to search or filter. Because of this, different analysts often get different results while looking for the same data.

Rollups

Due to the complexity of data queries, UDOT uses predefined filters or data shortcuts that make it easier to filter, review, and report on crash data. UDOT refers to these filters or shortcuts as “rollups.”  Rollups are fields generated based on rules in the database system that aggregate data to the crash level and make it easier to consistently filter information.

For example, a “Motorcycle Involved” rollup provides a simple yes/no indication of whether a motorcycle was involved in a crash. This removes the complexity of querying individual tables to summarize data. In addition, it assures that different analysts obtain the same results because the rollup logic provides a consistent method to organize data.

Another example is the unrestrained rollup. In order to determine if there was an unrestrained person in a crash and summarize those crashes you must first filter to only vehicles that have restraints available, then filter to the driver and passenger person types, and then filter the safety equipment used. This is a tedious process that is familiar only to data power users. The rollup makes this information readily available to almost any user.

Most rollups provide a yes/no response indicating if a condition was or was not present in the crash. Other rollups provide a count. For example the “unrestrained fatalities count” rollup counts the number of people in a crash that were unrestrained and experienced a fatal injury. 

The UDOT Traffic & Safety Division regularly reviews rollups and their associated rollup logic to determine if changes or additions are necessary.