Enhanced Vision Zero Data

[atdservicebot]

We are expanding our Vision Zero crash database to incorporate data from other regional agencies besides law enforcement.

Background

Currently, our Vision Zero platform is built upon data from TxDOT's Crash Information System (CRIS), which compiles crash reports from law enforcement agencies across Texas.

The intent of this project is to expand the capabilities of our database to include data from other agencies such as Emergency Medical Services, the Medical Examiner's Office, and the County Clerk, so that Vision Zero program staff can have a better understanding of the causes and impacts of injuries happening on our roadways.

Resources

• Figma
• Requirement Spreadsheet
• Miroboard

[patrickm02L]

10/11 Meeting notes

• Joel expressed an interest in learning details about what EMS & AFD data we currently collect into the VZDB.
• I offered to speak to the mechanisms in place to consume and maintain that data, but we opted to use that time more productively for Xavier to give us a deep dive into some example data. We looked at all the columns currently available to us in the EMS table.
• We talked about how we could link the EMS data into CRIS data, using temporal, geolocation and demographic data of the victims.
  • Joel mentioned the compositing of these loose links into an algorithm to link them to crashes/units/persons.
• We spoke about the non-action taken on getting the EMS data feed fixed, and I said I’d circle back with Patrick to talk about that.
  • I’m starting to feel like the right route is for me to strike up a relationship with Lynn Cohee at EMS to see what is going on.
  • Pamala Quarels (sp?) also at EMS.
• Joel asked and Xavier spoke about the completeness of this data, and what causes a record to be sent to us.
• Joel and Xavier spoke about the relationship at EMS who can answer questions about this data and compiling some questions for them.
• I spoke briefly about what technical approaches we could use to rank crashes by how well we think they match to EMS records
• Joel expressed his first desire to get a handle on what degree of under reporting we have in our CRIS data
  • And he had the idea of taking a small sample (say a week or two) and hand QA all the records to manually match them.
• I committed to letting @patrickm02L know that Joel has interest in getting this work into the prioritization system and that he will reach out to you in the near future to start that conversation.
  • The work in particular that this means is writing a postgres function that takes scoring parameters and outputs an ordered list of crashes that it thinks an EMS incident refers to.
  • With this data available in the database, we could then augment the VZE to expose that scoring.

10/18 in Dev + Product

• Would like to see what the BI folks on the VZ team can do as initial analysis before we commit to writing a postgres function.
• Next week deadline for the Pedestrian Bike Council/Committee will not happen
• Is this Issue related? #10367

[patrickm02L]

11/6

In meeting with Xavier, he outlined the following

For EMS/VZ Database issues:

1. Create 2 fields in the ems__incidents table that allows us to add/edit a atd_txdot_crashes.crash_id, atd_txdot_person.id & atd_txdot_primaryperson.id. - MVP
2. Use the existing field apd_incident_number_1 in the ems__incidents table to use as a way to match the crash and person ( use demographics ) and update the fields in step 1. - Some automation
3. Create a "fuzzy matching" process to associate ones that don't have a match by using geospatial & temporal data. - will need further scoping

Notes

• Business rules to be generated with @xavierapostol

[patrickm02L]

11/7 - In VZ Sprint Review

• VZ Team will need to provide a proof of concept EMS dataset in order to see how we can integrate it
  • What are the matrix of rules? @xavierapostol to work on this with @MiladMAK
  • How might we verify the efficacy to create an automated approach?

Note

• Joel mentioned bringing an intern to get the records
• How long would we want to go back to backfill the EMS data? @xavierapostol "all of it." which include 36K+ records as of today.

[xavierapostol]

I believe that we may need to combine this issue with this issue and this issue.

This is because we can incorporate the next version of our data model and the ability to borrow ETL scripts and SQL triggers from previously built features.

Example 1: Using the ETL script for Non-CR3s to upload TCME data and then displaying the data in the "Other Agencies" accordion card is what we are trying to see in the VZE Crash Incident Page.

Example 2: Using the same SQL triggers we use crash data we could use on the AFD/EMS geospatial assignment. This would enable us to better match incidents across all the data sets.

[patrickm02L]

@xavierapostol I meant to turn this Issue into an a Project Issue, so I'll need to update the Description, which still has the initial SR at the moment.

The Issues that you mentioned above are a part of this "Other Agencies" epic, so they're linked. Perhaps they're not showing up in github, but with a zenhub plugin or the zenhub browser interface they're easier to see.

[xavierapostol]

This is from an email from Joel Meyer:

Thought you all would be interested in this report from the Texas Department of State Health Services: Linking 2022 Crash Data with EMSTR Records. I’m trying to set up a call with the authors of the report in the next few weeks. Wondering if we can just get access to their matched records for Austin rather than doing it ourselves. One interesting thing to note from the report is that there is free software which can be used for record matching -- https://seer.cancer.gov/tools/matchpro/ -- originally designed for cancer records but can be used for other applications as well.

[johnclary]

We will re-scope this once we complete #10430.