/
2024-02-23, Crash DB Workgroup Meeting

2024-02-23, Crash DB Workgroup Meeting

Owned by Michael Satteson
Last updated: Feb 23, 2024
5 min read

Intro

Informal Opening Discussion

 

Crash DB Systems Review

TraCS Systems and Crash Form development

@David Malisch / @Geraldine Polster

This is the tentative schedule for the March 2024 Pack testing and release:

  • February 16th -     Create the 1st package

    • Feb 19th- Start testing

    • Feb 28th-  Stop testing ← Last Day for Table changes to be submitted for Crash Unit

    • Feb 1st- Check for critical fixes, fix and repackage

  • March 1st -     Create the 2nd package

    • Mar 4th- Start testing

    • Mar 13th-  Stop testing

    • Mar 15th- Check for critical fixes, fix and repackage

  • March 15th -      Final packaging for update 

  • March 22th -       Go / No Go decision to release pack 

  • March 25th -       Release software to all agencies

TOPS Database and WisTransPortal

@Steven Parker / @Andi BILL

Maintenance Updates and System Stability

  • February server maintenance window postponed until March

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.

  • December / January JIRA Cases:

    • In Progress

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

      • CRASHDB-1536 - Resolve Pipeline Duplicate Crash Issue

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

        • JIRA case aggregator to investigate and clean up impacted crash records. Current theory is a configuration mismatch in the time zone settings between the new application server and the database that interfered with the integration process.  TOPS will schedule a new maintenance window once the issue has been thoroughly analyzed and resolved.

    • Under Development

Purge and Archiving Processes

  • Purge and Archive processes both continuing to run smoothly.

  • 1,072,483 total archived crashes of which 472,826 represent purged crashes (the archive adds approximately 13K crashes per month).  This includes 2,005,543 total image files.

HWYCLASS Working Group

  • Next workgroup meeting: 2/27.  Will present roadway ownership query results.  These elements provide an opportunity to compare derived values to values from the police report.

  • General concurrence on merit to continue line of investigation.

  • Follow-up on TLT information in deer crash short form.

WisTransPortal / Community Maps

  • Community Maps:

    • User Preferences enhancement moved into production.

    • Designing for new training video page in progress

    • Tribal Area filter based on US Census boundaries in progress, along with a few new flags.

    • Crash flags technical documentation in progress.

WisTransPortal:

  • Data download enhancement in progress.

Crash Records Unit

Polly Ebbinger / Mike Satteson

Duplicate Crash Reports

75 Potential Duplicate Crashes

14 resolved in last 30 days

61 remaining to resolve

Transmission Failures

(since the 1/26/24)

52 total unique documents failures

40 reuse failures

34 retransmissions

3 reuse, agency fixed

3 reuse, solution provided – 2 outstanding

1 CJIS flag missing or empty

5 Police Report Image is missing or empty

1 Document Number and Crash document Number must be different

1 invalid EMS Agency ID

1 Hwy direction is missing

1 LastName is invalid

1 Airbag invalid

1 PhotosBy invalid

Issues Submitted

BTRSD-6984 Issue - Convert to Amended

BTRSD-6985 Issue - Secondary Crash / Primary Crash Agency

LE Questions

8 LE questions – all related to validation issues of crash reports

1 Bomgar session related to the CJIS flag error

FARS Work

For 2024, there are currently 41 fatalities (from 38 crashes) in our case management system

julia> combine(groupby(fatality, :jira_root), nrow=>:fatalities) |> x->freqtable(x, :fatalities) 3-element Named Vector{Int64} fatalities │ ────────────┼─── 1 │ 36 2 │ 1 3 │ 1

Of these 37 are in the crash database, and 4 are not:

julia> filter(x->ismissing(x[:INJSVR]), fatality)[!, [:jira_root, :case_status, :person_status]] 4×3 DataFrame Row │ jira_root case_status person_status │ String15? String31? String31? ─────┼────────────────────────────────────────────────── 1 │ FARSCASE-4158 FARS Case Fatality 2 │ FARSCASE-4150 FARS Case Fatality 3 │ FARSCASE-4135 FARS Case Fatality 4 │ FARSCASE-4017 Not Yet Considered Federal Only

 

  • FARSCASE-4158, This crash occurred in Monroe County on Monday February 19, 2024 at about 9:22 AM. There was 1 fatality associated with this crash, a 38 year-old male driver. (19 February Daily Fatality Report)

  • FARSCASE-4150, This crash occurred in Wood County on Friday February 16, 2024 at about 7:35 PM. There was 1 fatality associated with this crash, a 41 year-old male driver. (19 February Daily Fatality Report)

  • FARSCASE-4135, This crash occurred in Milwaukee County on Friday February 9, 2024 at about 1:41 PM. There was 1 fatality associated with this crash, a 93 year-old male pedestrian. (9 February Daily Fatality Report)

  • FARSCASE-4017, This crash occurred in Ashland County on Tuesday January 2, 2024. There was 1 fatality associated with this crash, a 43 year-old male driver (not reportable to WisDOT). This is a DNR ATV crash--Single operator/victim lost control on a curve and was ejected. We will not be able to evaluate it until we obtain case materials from the DNR,

Of the fatalities in the database

julia> filter(x->!ismissing(x[:INJSVR]), fatality) |> x->freqtable(x, :case_status, :person_status) 4×3 Named Matrix{Int64} case_status ╲ person_status │ Fatality NMVTF State Only ─────────────────────────────┼─────────────────────────────────── Dismissed from Consideration │ 0 1 0 FARS Case │ 32 0 1 Not Yet Considered │ 1 0 0 Under FARS Review │ 2 0 0

Of these the Not FARS Cases are

julia> filter(x->!ismissing(x[:INJSVR])&&x[:case_status]!="FARS Case", fatality)[!, [:jira_root, :case_number, :case_status, :person_status]] 4×4 DataFrame Row │ jira_root case_number case_status person_status │ String15? String15? String31? String31? ─────┼───────────────────────────────────────────────────────────────────────── 1 │ FARSCASE-4167 240205335 Not Yet Considered Fatality 2 │ FARSCASE-4108 240200571 Dismissed from Consideration NMVTF 3 │ FARSCASE-4083 240110793 Under FARS Review Fatality 4 │ FARSCASE-4021 240105450 Under FARS Review Fatality

 

  • FARSCASE-4167, This crash occurred in Grant County on Sunday February 11, 2024 at about 6:56 PM. There was 1 fatality associated with this crash, a 22 year-old male pedestrian. Crash report arrived late last night.

  • FARSCASE-4108, This crash occurred in Green County on Friday February 2, 2024 at about 2:54 PM. There was 1 fatality associated with this crash, a 28 year-old female pedestrian (not considered a traffic fatality). This was a parking lot aisle crash and so not a traffic crash.

  • FARSCASE-4083, This crash occurred in Dane County on Friday January 26, 2024 at about 10:22 PM. There was 1 fatality associated with this crash, a 23 year-old female pedestrian.

  • FARSCASE-4021, This crash occurred in Racine County on Saturday January 13, 2024. There was 1 fatality associated with this crash, a 72 year-old male driver.

DMV Systems and SR

Ken Matthews

 

Reference Point Coding

Asadur Rahman

 

Crash Data Analysis and Reporting

Valerie Payne / Brian Porter

Analysts working on various data requests

  • motorcycle data MSAC

  • pursuit data - Data Dictionary development

  • ABATE - request for information

  • Internal Request for tribal crash data

  •  

Scheduled Topics and Briefing documents

Dave Harvey

Discussions with DNR on crash reports related to wildlife management.

TraCS animal species list.

Deer

Bear

Turkey

Raccoon(s)

Opossum

Other Non Domesticated

Coyote

Multiple animals selected

julia> freqtable(crash, :animal_COUNT) 3-element Named Vector{Int64} animal_COUNT │ ──────────────┼─────── 0 │ 443297 1 │ 71188 2 │ 21 julia> filter(x->x[:animal_COUNT]>1, crash)[!, [:CRSHNMBR, :ANMLTY01, :ANMLTY02]] 21×3 DataFrame Row │ CRSHNMBR ANMLTY01 ANMLTY02 │ Int64 String? String? ─────┼─────────────────────────────────────────────── 1 │ 200300945 Turkey Other Non Domesticated 2 │ 200709926 Deer Opossum 3 │ 200806372 Deer Other Non Domesticated 4 │ 201004501 Raccoon(s) Opossum 5 │ 210601153 Raccoon(s) Opossum 6 │ 210704846 Deer Raccoon(s) 7 │ 210902065 Deer Other Non Domesticated 8 │ 210909263 Deer Bear 9 │ 211211954 Deer Bear 10 │ 220506098 Deer Opossum 11 │ 220602783 Deer Turkey 12 │ 220910984 Deer Other Non Domesticated 13 │ 230209863 Deer Other Non Domesticated 14 │ 230709362 Opossum Coyote 15 │ 230800934 Bear Coyote 16 │ 230804557 Deer Raccoon(s) 17 │ 231005827 Deer Raccoon(s) 18 │ 231109268 Deer Other Non Domesticated 19 │ 231201756 Deer Other Non Domesticated 20 │ 231201757 Deer Other Non Domesticated 21 │ 240100321 Deer Coyote

Close

Informal Closing Discussion

Related content