Intro

Informal Opening Discussion

Crash DB Systems Review

TraCS Systems and Crash Form development

Dave Harvey, (Acting TraCS Unit Supervisor)

TOPS Database and WisTransPortal

Steven Parker and Andi Bill

Maintenance Updates and System Stability

• 4/23/2023 - Last monthly server maintenance window

• 5/3/2027 – Planned low-risk maintenance window for Resolve updates

• CRASHDB-1264 – Intermittent Errors during the Week of 2/4/2023

  • UW-Madison campus network instability: 4/4 – 4/6

  • Impacted web services, customer purchases of crash reports, access to Resolve

  • ECR Pending

• Ongoing coordination with UW DoIT Oracle Licensing Team

  • Should hear back soon from the UW Oracle team

  • Existing Oracle license extended through April 29, 2024.

  • Discounted CPU based licensing under consideration.

Prioritized JIRA Issues and Enhancements

• 2021 RP Finalization

  • RP Coding Data Merge Successful

  • CRU & TOPS Extracts Generated

  • Next Steps:

    • Sign-Off from CRU / Workgroup

    • Update WisTransPortal & Community Maps

    • Generate RP file for John Tyson / RP Crash Shapefile

    • Generate Public Extracts

    • Provide to DHS CODES team

• 2022 Finalization

  • End of May data freeze for federal reporting purposes

  • Anticipate building upon the 2021 process for merging final RP crash coding

• In Acceptance and Ready for CAB – Planning for May 5 maintenance window

  • System Stability Follow-Up Items

    • CRASHDB-1251: Duplicate Records

    • CRASHDB-1255: View Button

    • CRASHDB-1257: XML Beautification

    • CRASHDB-1260: Query Record Limit

  • Resolve Search Enhancements

    • CRASHDB-999: Search by Agency

    • CRASHDB-1253: Search by Government Property Damage Tag

    • CRASHDB-1263: Update Resolve Contact Page

• Getting Started / In Progress

  • Customer Number - 2 months

    • Confluence Document and Code Review for Assigning Customer Numbers

    • Adding Customer Number Modulation to Record History

    • Adding Primary DID and Driver Restrictions to Record History

  • Crash Database Archive

    • Scheduling IT meeting to establish secure network access

    • What (additional) data agreements are needed?

  • In Queue

    • Migrate to the VxRail Oracle 19c – 2 months

Purge and Archiving Processes

• Purge and Archive processes both continuing to run smoothly

• 952,609 total archived crashes of which 343,103 represent purged crashes (the archive adds approximately 15K crashes per month).  This includes 1,773,194 total image files.

FY24 Project Planning

• Working on the SPR application and budget for Ben and Mike

• Requested a budget increase this year to match increases in UW costs

HWYCLASS Working Group

• 4/4 Working Group Meeting

  • Reviewed STN/WISLR data mappings from Christy and Jeff

  • Prototype planning: offline testing with 2021 Dane County crashes

  • TOPS working on the STN/WSLR/LINK-LINK data request to BSHP

  • Canceled the May meeting due to conflicts and pending data request

  • Onboarding a grad student to help with the effort

WisTransPortal / Community Maps

• Community Maps

  • March 29 User Group Planning (with Green Lake County SO)

    • 119 participants

    • Recording linked to Community Maps

  • Recent / In Progress

    • 2021 US Census Tribal Boundaries (Completed)

    • Crash Comparison Dashboard (Completed)

    • Database Migration to Oracle 19c (Completed)

    • Advanced Search Edit Enhancements (In Progress)

• WisTransPortal

  • Database Migration to Oracle 19c (Completed)

  • 2021 Final Crash Data Testing (Completed)

Crash Records Unit

Soujanya Jarabana and Mike Satteson

Questions from Law Enforcement Agencies:

• CRU business unit provided email and remote support to law enforcement agencies to resolve Validation errors in the DT4000 crash form. 

• Received 15 LEA questions in the last 4 weeks involving 1 Hit and Run Fatal Crash, 2 crashes about Crash diagram, 3 Questions about Non-Reportable Crashes.

 Transmission failures:

• We addressed 19 transmission failures between March 24th until now after providing solution to the agencies.

  • 5 are transmitted

  • 12 are transmitted as other Document (Duplicate Document Number)

  • 2 are pending with Agencies.

  • A Persistent source of Duplicate Document failures appears to have been fixed after both the CRU and Badger TraCS worked with the agency. Between 15 January and 12 April, we saw 19 of these document number reuse failures, 9 required CRU follow-up to be corrected.

DT4000 Fatal Crashes:

Quality Check process for DT4000 fatal crashes is performed which comes in Resolve. The process starts with creating a Jira issue for DT4000 basic check and then and assigned to the FARS team,

• FARS team performs quality check for

  • All Fatal Injuries Shown

  • DT3480 Available

  • Diagram Good

  • Narrative Good

  • Sequence of Events Good

Based on the results of the quality check process the agency is contacted to amend the crash report.

• 5 fatal crashes are under agency review for missing Fatal Supplement & 2 crashes needs Agency Amendment.

• Tracking is done for missing DT4000 fatal crashes, and the agency is contacted when a 3-week due date is met from the crash date.

Currently, we are missing 1 crash from Washington County SO 

 Enhancements to Crash Database & Resolve System

• Currently in ready for production for planned deployment on 2023-05-03 which includes.

• Resolve Search by Agency, Government Property Tag, Update contact information in Resolve system and bug fixes in System Stability.

•  In the last four weeks, 1 change request was submitted which includes:

  • 1 monthly server maintenance on 2023-04-23.

DMV Systems and SR

Ken Matthews

Ken is unavailable but sends his regards and .

Reference Point Coding

Asadur Rahman

Jacob and Erickson coded since 03/31:

Jacob: 2,359

Erickson: 1,629

Total: 3,988

RP coding needed for 2022: 43,784

Crash Data Analysis and Reporting

Valerie Payne

• Wisconsin Traffic Crash Facts

Scheduled Topics and Briefing documents

2021 Crash Data Refinalized to Add RPs

2021 RP Coding was completed in early April. The TOPS Lab worked to create a new version of the 2021 finalized data that differs from the initial version only in the added RP fields. The CRU checked the results to verify that this was the case.

This process had to go through a couple of iterations because of challenges posed by DMV customer number assignment changes (which do not increment the crash record number). The original 2021 finalized data had 20,271 crashes with RPs. The post freeze finalization added 33,768 RP for a total of 54,039 RP in the refinalized data:

julia> freqtable(x, :old_has_rp, :new_has_rp)
2×2 Named Matrix{Int64}
old_has_rp ╲ new_has_rp │ false   true
────────────────────────┼─────────────
false                   │ 74257  33768
true                    │     0  20271

The check process verified successful refinalization of both flat file and SAS library extracts. According to a cell by cell comparison, the updated version of these data is identical to the initial version in all respects except for

• Data Record Number, Record Timestamp, and the Agent responsible for the record change (RECDNMBR and RECDDTTM in all tables and OPCODE in crash)

• RP data in the roadway. table (additions only, no changes were made to RP data that had been entered when the data were finalized).  These fields are:

  • RPRTEID

  • RPRTETY

  • RPRTENB

  • RPRTEDIR

  • RPTYPE

  • RPNMBR

  • RPDIS

  • RPLINK

  • RPLKOT

  • ALTLAT

  • ALTLON

  • ALTSRC

• The order of records in the two versions may be different, but records can be matched exactly by appropriate sorts on a single key field in each table. (Order was preserved in the extract to dataframe datasets that I had examined earlier.)

We made the SAS library version of the these refinalized data this week as test libraries. Other at large version of the 2021 finalized crash data re unchanged pending workgroup decision

2022 Finalization

We turned our attention to 2022 Crash data Finalization, which we need to complete by the end of May. We were not able to keep up the continuous data cleaning in response to daily batch edit checks that we had begun to realize in late 2021. In part this is because Tejal had done much of this work, but we are also working to rebuild our data quality and data query infrastructure to use Jira. We should still be able to get the data clean for critical checks, but our quality measures against important edit checks will likely be poorer than what we had been able to achieve for 2021.

Changes to Edit Checks

We discontinued 12 FARS related edit checks. FARS checks are not done as part of our in development new case management system, which is being implement in Jira. We’ve begun to implement checks in that system. Currently we have 31 checks implemented in that system, and we typically clear those each day.

Moved Narrative: Bicycle Mentioned, but not a Unit from Critical to Important. This had been an Informational check prior to 2022-01-13.

Current Status

For checks run on 2023-04-27 against 2023-04-26 production 2022 data. We had

The tally for critical checks is

Based on past experience, most of these critical check hits can be cleared without laborious agency follow-up. We ought to be able to clear them in time to finalize with data that is clean of critical checks.

Close

Informal Closing Discussion