/
2023-09-01, Crash DB Workgroup Meeting

2023-09-01, Crash DB Workgroup Meeting

Owned by Michael Satteson
Last updated: Sept 01, 2023
4 min read

Intro

Informal Opening Discussion

 

Crash DB Systems Review

TraCS Systems and Crash Form development

Geri and I are both out of the office today. I will read up on the notes and catch you all at the next meeting.

TOPS Database and WisTransPortal

Steven Parker / Andi Bill

Maintenance Updates and System Stability

  • Upcoming Maintenance Windows:

    • 9/13/2023 – Web server annual certificate renewal

    • 9/24/2023 – September monthly server maintenance window

  • Completed Maintenance Windows:

    • 8/20/2023 - August monthly server maintenance window

    • 8/4/2023 - SAS Annual License Update

    • 8/2/2023 - Low risk maintenance window for Resolve System updates

Prioritized JIRA Issues and Enhancements

  • 2022 Finalization

    • RP coding process for 2022 data is continuing and will be merged into the frozen data set once completed.

  • July JIRA Cases:

    • Completed:

      • CRASHDB-1353 – Server error editing Truck and Bus Section

    • In Progress

      • CRASHDB-1388 – Internal Server Error When Accessing Manage Finalization.

      • CRASHDB-1322 – Open Annual Crash Data Extracts

      • CRASHDB-1034 – DT3480 Add Underride/Override Fields

      • CRASHDB-1389 – HWYCLASS Lookup Table Update

      • System Stability (Java 11 / Oracle 19c) Migration:

        • CRASHDB-1391 – Source Code Branching

        • CRASHDB-1364 – Create Migration Inventory Documentation

        • CRASHDB-1366 – Create Migration Test Plan

    • Open:

    • In the Queue:

      • DMV Customer Number enhancements

      • Several JIRA Case Ideas to work in parallel to the migration effort.

Purge and Archiving Processes

  • Purge and Archive processes both continuing to run smoothly.

  • 999,580 total archived crashes of which 390,033 represent purged crashes (the archive adds approximately 13K crashes per month).  This includes 1,868,240 total image files.

FY24 Project Planning

  • FY24 SPR application and budget pending approval and green-light.  Current project ends 9/30/2023.

HWYCLASS Working Group

  • TOPS STN/WSLR/LINK-LINK data request received from BSHP and loaded into TOPS Lab database.

  • Working on prototype SQL queries for derived elements.

  • Will resume workgroup meetings in the Fall

WisTransPortal / Community Maps

  • GCHS 2023 Community Maps 101 and 201, Garbage In / Garbage Out, Safe System sessions

  • Train Flag added to Community Maps per discussion at the last WG meeting.

Crash Records Unit

Soujanya Jarabana / Mike Satteson

Questions from Law Enforcement Agencies

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

  • We received 17 LEA questions in the last 5 weeks, this time there were questions about different crash scenarios involving.

    • 1 involving Train/Bus

    • 3 questions about Non-Reportable Crashes

    • 1 involving On Duty Officer

    • 1 Hit and Run Crashes

    • 11 field validation errors

Transmission failures

  • We addressed 12 transmission failures in the last 5 weeks after providing solution to the agencies.

    • 5 are transmitted

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

    • 2 are in progress.

    • 1 is pending with agency.

DT4000 Fatal Crashes

  • 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 there are 3 fatal crashes meeting the three-week due date.

    • Milwaukee PD, with crash date of 2023-07-19

    • Brown County SO, with crash date of 2023-07-31

    • Buffalo County SO, with crash date of 2023-08-08

Testing Activities

Online Purchase of Crash Reports

  • The change was made to remove search by Accident/Crash Number Functionality from the homepage of online crash reports. The Reissue page looks and functions as expected.

    • UAT testing performed on 08/17.

    • Production Verification done on 08/30.

Badger Tracs Fall Pack 2023 - Round 2 Testing

The following items are tested.

  • Create a Fatal Notification Report

  • Crash and Fatal Supplement - Added an additional (optional) status of 'hold' available as a column in Forms Manager.

  • Test transmission of Fatal Supplement - Add Underride/Override fields.

  • Crash Form bug - Reconstruction By

Enhancements to Crash Database & Resolve System

  • Production verification done on 2023-08-02 to fix bug issues which causes error when Resolve edits in Unit level fails due to Blank Date of Birth field.

https://wi-state-patrol.atlassian.net/browse/CRASHDB-1354

  •  In the last five weeks, 1 normal change request was submitted to Change Advisory Board which includes:

    • Monthly server maintenance for August 2023.

FARS Systems

2022 Finalization

The 2022 crash year NHTSA FARS finalization date is 16 September. This is about 45 days later than is typical. We have no information on whether this delay will affect the public release of these data, which typically occurs at the end of the year.

Cause of Death Verification

We have added Cause of Death Confidence and Death Certificate Review Date fields to our in development Box/Jira/Julia case management system. We expect to use these new fields to improve our processes for making final fatality reportability decisions. Access to death certificate information can be bottleneck to this work.

DMV Systems and SR

Ken Matthews

Ken is unavailable but sends his .

Reference Point Coding

Ben Rouleau / Asadur Rahman

The Numbers

07/27-08/30

Jacob: 3,614

Erickson: 2,564

Total: 6,178

RP coding needed for 2022: 25,068

Personnel

Tristan LaRene will be starting the week of 10 September as a part-time RP Coder

Erickson Wilson’s last day as an RP coder will be 22 September.

 

Crash Data Analysis and Reporting

Valerie Payne

 

Scheduled Topics and Briefing documents

 

 

Close

Informal Closing Discussion

Related content