[Bug] Crash IDs that do not have a unit.

[atdservicebot]

What application are you using?

Vision Zero (Editor)

Describe the problem.

Here is the list of crash IDs that do not have a unit.
14832414
17451702
17546179

It does not appear that there is an issue with Comprehensive Cost (#6875), but this is a minor data integrity issue.

Website Address

https://visionzero.austin.gov/editor/#/crashes/

Internet Browser: Chrome

Requested By
Xavier A.

Request ID: DTS21-102935

[frankhereford]

@patrickm02L

I put together a query to find all the crashes in the database without units. The three which are listed in the issue are correctly noted as being in that set, but I just wanted to get a handle on how big the total list was. As an aside, the query also checks to see if the crashes which are missing units are also missing people in the persons table. (They all are.) The query I used is:

with persons as (
  select crash_id, unit_nbr, prsn_nbr, prsn_type_id
  from atd_txdot_person atp
  union all
  select crash_id, unit_nbr, prsn_nbr, prsn_type_id
  from atd_txdot_primaryperson atp
  )
select c.crash_id as crash_table_crash_id, u.crash_id as units_table_crash_id, p.crash_id as persons_table_crash_id, c.case_id, c.rpt_city_id, c.crash_date
from atd_txdot_crashes c
left join atd_txdot_units u on (c.crash_id = u.crash_id)
left join persons p on (c.crash_id = p.crash_id)
where true
--and c.crash_id in (14832414, 17451702, 17546179)
and u.crash_id is null
and c.crash_id > 1000
order by crash_date desc

This query returns 14 records, which means there are only these 14 crashes in the entire database in this problem set:

I have pulled up many of these in the VZE and I have also looked at their rows in the atd_txdot_crashes table, and I don't have a hard and fast pattern identified that links them. That said, You can see in the above image that many are missing really fundamental information like case ID and nearly all of them have 9999 as the reported city ID which keys to "NOT LISTED" in the lookup table.

I believe that these records were sent to us from CRIS in this degraded (read: mostly blank) state, such as if something went wrong on the data-entry or data-processing steps upstream of us. However, confirming this will take a fair bit of work, as I would need to parse through a big chunk of CSV files to find the original receipt of these crashes and then any updates that came later.

Because of this, my recommendation for routes forward are the following, in order of preference:

1. Accept the fact that we have 14 crashes which are not complete in the VZE due to upstream issues beyond our control. Check each CR3 and if one of the crashes has an injury in the 'suspected serious injury' or 'fatal' severity, then we should hand modify the database to reflect the information shown on the CR3. This is a big 1 or a small 2.
2. By hand, look at each of the 14 CR3s and update the database with the CR3 data regardless of injury severity. This is a 2, maybe a small 3.
3. Dig into the raw data we got from CRIS to prove to ourselves that my hypothesis is correct. If so, execute # 2 on this list, and if my hypothesis is wrong, and we actually got complete data from CRIS but failed to ingest it correctly, fix the data import ETL. Then, either use the fixed ETL to update these crashes or execute # 2 on this list, whichever is easier. I size this option as a 5, but I'm making a lot of assumptions in that sizing.

[xavierapostol]

I believe that all crashes should have at least 1 unit and 1 person. What I have seen is that some investigators do not use the "UNKNOWN" flag that is available to them and there doesn't seem to be a validation tool on the system prior to it being submitted to TxDOT. In the 3 cases I provided the CR3 has a null in the field to identify the unit. Currently, no one but me is actively checking for these types of issues and I believe we should be treating it like we do when we manually QA crashes. TxDOT and the investigating agency do not provide the most accurate location, so we manually adjust it. This is similar in its concept of improving our crash detail's accuracy.

Although it is a small sample size, I believe we should create a process that provides an automatic unit and/or person if one does not exist. The unit is a MOTOR VEHICLE unit type and UNKNOWN body style and the person is of UNKNOWN injury severity.

NOTE: This is a low priority task, but it does assist us in ensuring our system is the most accurate depiction of crash data (even if it is for 14 total crashes).

[patrickm02L]

@frankhereford has this been resolved?

[patrickm02L]

Hoping to get your assistance @xavierapostol.

Would you be able to write SQL INSERT statements to add the rows you would like inserted into the units table? This would take the option 2 approach that Frank laid out in previous notes

2. By hand, look at each of the 14 CR3s and update the database with the CR3 data regardless of injury severity.

That way we'll get the exact data you want, and can make the changes to the table on our end.

[xavierapostol]

Would this work for your purposes?
INSERT INTO atd_txdot_primaryperson ('crash_id','unit_nbr','prsn_nbr','prsn_injry_sev_id','unkn_injry_cnt') VALUES (18710369,1,1,0,1) (18710022,1,1,0,1) (18710022,1,1,0,1) (18710204,1,1,0,1) (18710209,1,1,0,1) (18703857,1,1,0,1) (18704884,1,1,0,1) (18703786,1,1,0,1) (18703786,1,1,0,1) (18703765,1,1,0,1) (18703765,1,1,0,1) (18704854,1,1,0,1) (18694207,1,1,0,1) (18694195,1,1,0,1) (18688180,1,1,0,1) (18677209,1,1,0,1) (18677210,1,1,0,1) (18709931,1,1,0,1) (18682336,1,1,0,1) (18676838,1,1,0,1) (18676923,1,1,0,1) (18676923,1,1,0,1) (18666705,1,1,0,1) (18666694,1,1,0,1) (18703432,1,1,0,1) (18666693,1,1,0,1) (18703432,1,1,0,1) (18666701,1,1,0,1) (18666701,1,1,0,1) (18666701,1,1,0,1) (18666691,1,1,0,1) (18666693,1,1,0,1) (18666687,1,1,0,1) (18666600,1,1,0,1) (18660312,1,1,0,1) (18654628,1,1,0,1) (18655687,1,1,0,1) (18654395,1,1,0,1) (18647866,1,1,0,1) (18647866,1,1,0,1) (18647866,1,1,0,1) (18644779,1,1,0,1) (18644779,1,1,0,1) (18644779,1,1,0,1) (18644779,1,1,0,1) (18644764,1,1,0,1) (18640681,1,1,0,1) (18640685,1,1,0,1) (18640685,1,1,0,1) (18666510,1,1,0,1) (18644820,1,1,0,1) (18666510,1,1,0,1) (18628760,1,1,0,1) (18628613,1,1,0,1) (18628083,1,1,0,1) (18628083,1,1,0,1) (18628451,1,1,0,1) (18628451,1,1,0,1) (18619078,1,1,0,1) (18618525,1,1,0,1) (18617728,1,1,0,1) (18614313,1,1,0,1) (18616011,1,1,0,1) (18617649,1,1,0,1) (18606614,1,1,0,1) (18620565,1,1,0,1) (18606571,1,1,0,1) (18620565,1,1,0,1) (18600450,1,1,0,1) (18600450,1,1,0,1) (18597060,1,1,0,1) (18597072,1,1,0,1) (18596995,1,1,0,1) (18589858,1,1,0,1) (18589864,1,1,0,1) (18584575,1,1,0,1) (18584575,1,1,0,1) (18576693,1,1,0,1) (18566249,1,1,0,1) (18566249,1,1,0,1) (18562636,1,1,0,1) (18562693,1,1,0,1) (18562693,1,1,0,1) (18562636,1,1,0,1) (18562626,1,1,0,1) (18562626,1,1,0,1) (18562626,1,1,0,1) (18553625,1,1,0,1) (18553625,1,1,0,1) (18548797,1,1,0,1) (18551502,1,1,0,1) (18548623,1,1,0,1) (18544249,1,1,0,1) (18540151,1,1,0,1) (18535484,1,1,0,1) (18692090,1,1,0,1) (18558410,1,1,0,1) (18535221,1,1,0,1) (18532077,1,1,0,1) (18529054,1,1,0,1) (18522560,1,1,0,1) (18522511,1,1,0,1) (18522492,1,1,0,1) (18507355,1,1,0,1) (18510610,1,1,0,1) (18510610,1,1,0,1) (18510610,1,1,0,1) (18605822,1,1,0,1) (18507282,1,1,0,1) (18507567,1,1,0,1) (18507567,1,1,0,1) (18498152,1,1,0,1) (18496500,1,1,0,1) (18484342,1,1,0,1) (18481294,1,1,0,1) (18481285,1,1,0,1) (18492068,1,1,0,1) (18478638,1,1,0,1) (18478638,1,1,0,1) (18475741,1,1,0,1) (18475741,1,1,0,1) (18548352,1,1,0,1) (18471614,1,1,0,1) (18548352,1,1,0,1) (18466713,1,1,0,1) (18465468,1,1,0,1) (18461846,1,1,0,1) (18461838,1,1,0,1) (18461834,1,1,0,1) (18461838,1,1,0,1) (18460936,1,1,0,1) (18456576,1,1,0,1) (18456576,1,1,0,1) (18581464,1,1,0,1) (18465391,1,1,0,1) (18465391,1,1,0,1) (18581464,1,1,0,1) (18453290,1,1,0,1) (18450234,1,1,0,1) (18453290,1,1,0,1) (18447106,1,1,0,1) (18448572,1,1,0,1) (18600328,1,1,0,1) (18448572,1,1,0,1) (18437741,1,1,0,1) (18434649,1,1,0,1) (18431683,1,1,0,1) (18431680,1,1,0,1) (18596976,1,1,0,1) (18434649,1,1,0,1) (18431679,1,1,0,1) (18431679,1,1,0,1) (18444256,1,1,0,1) (18428905,1,1,0,1) (18428905,1,1,0,1) (18600325,1,1,0,1) (18422100,1,1,0,1) (18428393,1,1,0,1) (18421991,1,1,0,1) (18416265,1,1,0,1) (18416256,1,1,0,1) (18412915,1,1,0,1) (18410801,1,1,0,1) (18409434,1,1,0,1) (18409434,1,1,0,1) (18409434,1,1,0,1) (18407113,1,1,0,1) (18407104,1,1,0,1) (18407104,1,1,0,1) (18407104,1,1,0,1) (18407076,1,1,0,1) (18407104,1,1,0,1) (18400147,1,1,0,1) (18400147,1,1,0,1) (18398116,1,1,0,1) (18398193,1,1,0,1) (18391211,1,1,0,1) (18388349,1,1,0,1) (18388403,1,1,0,1) (18388403,1,1,0,1) (18388403,1,1,0,1) (18388349,1,1,0,1) (18382351,1,1,0,1) (18373156,1,1,0,1) (18373200,1,1,0,1) (18373154,1,1,0,1) (18367142,1,1,0,1) (18367142,1,1,0,1) (18367142,1,1,0,1) (18367142,1,1,0,1) (18367142,1,1,0,1) (18357848,1,1,0,1) (18359540,1,1,0,1) (18355664,1,1,0,1) (18368052,1,1,0,1) (18355664,1,1,0,1) (18351331,1,1,0,1) (18348757,1,1,0,1) (18348535,1,1,0,1) (18348534,1,1,0,1) (18330207,1,1,0,1) (18330207,1,1,0,1) (18330208,1,1,0,1) (18330166,1,1,0,1) (18321652,1,1,0,1) (18321655,1,1,0,1) (18321572,1,1,0,1) (18321427,1,1,0,1) (18373061,1,1,0,1) (18373061,1,1,0,1) (18379707,1,1,0,1) (18314854,1,1,0,1) (18310971,1,1,0,1) (18310971,1,1,0,1) (18310971,1,1,0,1) (18308553,1,1,0,1) (18308548,1,1,0,1) (18308545,1,1,0,1) (18308458,1,1,0,1) (18308458,1,1,0,1) (18303920,1,1,0,1) (18298180,1,1,0,1) (18295996,1,1,0,1) (18295996,1,1,0,1) (18288958,1,1,0,1) (18284303,1,1,0,1) (18285921,1,1,0,1) (18285921,1,1,0,1) (18284196,1,1,0,1) (18284196,1,1,0,1) (18286012,1,1,0,1) (18286012,1,1,0,1) (18283981,1,1,0,1) (18277278,1,1,0,1) (18277278,1,1,0,1) (18277232,1,1,0,1) (18284195,1,1,0,1) (18284195,1,1,0,1) (18271796,1,1,0,1) (18267596,1,1,0,1) (18267545,1,1,0,1) (18265455,1,1,0,1) (18260929,1,1,0,1) (18262972,1,1,0,1) (18258881,1,1,0,1) (18258881,1,1,0,1) (18258784,1,1,0,1) (18263427,1,1,0,1) (18258881,1,1,0,1) (18258645,1,1,0,1) (18258645,1,1,0,1) (18245967,1,1,0,1) (18238147,1,1,0,1) (18238858,1,1,0,1) (18240463,1,1,0,1) (18238154,1,1,0,1) (18238149,1,1,0,1) (18238154,1,1,0,1) (18246533,1,1,0,1) (18246533,1,1,0,1) (18234684,1,1,0,1) (18234685,1,1,0,1) (18234458,1,1,0,1) (18233801,1,1,0,1) (18229396,1,1,0,1) (18229396,1,1,0,1) (18227549,1,1,0,1) (18227503,1,1,0,1) (18227503,1,1,0,1) (18221080,1,1,0,1) (18226307,1,1,0,1) (18254819,1,1,0,1) (18213575,1,1,0,1) (18217588,1,1,0,1) (18213575,1,1,0,1) (18213575,1,1,0,1) (18208965,1,1,0,1) (18209052,1,1,0,1) (18208965,1,1,0,1) (18208965,1,1,0,1) (18216432,1,1,0,1) (18216432,1,1,0,1) (18198192,1,1,0,1) (18254805,1,1,0,1) (18198153,1,1,0,1) (18198153,1,1,0,1) (18198153,1,1,0,1) (18198190,1,1,0,1) (18254805,1,1,0,1) (18192244,1,1,0,1) (18197736,1,1,0,1) (18197736,1,1,0,1) (18197736,1,1,0,1) (18197736,1,1,0,1) (18192244,1,1,0,1) (18197736,1,1,0,1) (18190561,1,1,0,1) (18190561,1,1,0,1) (18183725,1,1,0,1) (18183592,1,1,0,1) (18179986,1,1,0,1) (18175503,1,1,0,1) (18175506,1,1,0,1) (18166343,1,1,0,1) (18168446,1,1,0,1) (18168446,1,1,0,1) (18177420,1,1,0,1) (18177420,1,1,0,1) (18156696,1,1,0,1) (18154797,1,1,0,1) (18152625,1,1,0,1) (18154797,1,1,0,1) (18150660,1,1,0,1) (18150660,1,1,0,1) (18155943,1,1,0,1) (18148538,1,1,0,1) (18146270,1,1,0,1) (18146270,1,1,0,1) (18146270,1,1,0,1) (18146270,1,1,0,1) (18144520,1,1,0,1) (18144520,1,1,0,1) (18142398,1,1,0,1) (18149043,1,1,0,1) (18149043,1,1,0,1) (18138422,1,1,0,1) (18136933,1,1,0,1) (18136933,1,1,0,1) (18136751,1,1,0,1) (18136752,1,1,0,1) (18136752,1,1,0,1) (18136752,1,1,0,1) (18134937,1,1,0,1) (18133846,1,1,0,1) (18134926,1,1,0,1) (18129819,1,1,0,1) (18128975,1,1,0,1) (18136679,1,1,0,1) (18128969,1,1,0,1) (18128975,1,1,0,1) (18128975,1,1,0,1) (18128975,1,1,0,1) (18128975,1,1,0,1) (18128963,1,1,0,1) (18125276,1,1,0,1) (18124858,1,1,0,1) (18124858,1,1,0,1) (18121613,1,1,0,1) (18121261,1,1,0,1) (18117767,1,1,0,1) (18118159,1,1,0,1) (18138974,1,1,0,1) (18138974,1,1,0,1) (18138974,1,1,0,1) (18107171,1,1,0,1) (18105950,1,1,0,1) (18116132,1,1,0,1) (18105289,1,1,0,1) (18102936,1,1,0,1) (18100178,1,1,0,1) (18097710,1,1,0,1) (18105283,1,1,0,1) (18094664,1,1,0,1) (18094664,1,1,0,1) (18094664,1,1,0,1) (18094664,1,1,0,1) (18089294,1,1,0,1) (18087178,1,1,0,1) (18087178,1,1,0,1) (18087178,1,1,0,1) (18087178,1,1,0,1) (18087178,1,1,0,1) (18087178,1,1,0,1) (18087178,1,1,0,1) (18086766,1,1,0,1) (18087178,1,1,0,1) (18087178,1,1,0,1) (18087178,1,1,0,1) (18084680,1,1,0,1) (18084258,1,1,0,1) (18084256,1,1,0,1) (18084258,1,1,0,1) (18084246,1,1,0,1) (18084246,1,1,0,1) (18079155,1,1,0,1) (18079155,1,1,0,1) (18076888,1,1,0,1) (18076382,1,1,0,1) (18076705,1,1,0,1) (18076881,1,1,0,1) (18075974,1,1,0,1) (18071814,1,1,0,1) (18072341,1,1,0,1) (18062672,1,1,0,1) (18062014,1,1,0,1) (18058273,1,1,0,1) (18057156,1,1,0,1) (18058268,1,1,0,1) (18058268,1,1,0,1) (18058268,1,1,0,1) (18057156,1,1,0,1) (18061624,1,1,0,1) (18062012,1,1,0,1) (18064634,1,1,0,1) (18064634,1,1,0,1) (18062012,1,1,0,1) (18062012,1,1,0,1) (18055383,1,1,0,1) (18055133,1,1,0,1) (18049985,1,1,0,1) (18062010,1,1,0,1) (18043206,1,1,0,1) (18042947,1,1,0,1) (18042947,1,1,0,1) (18043206,1,1,0,1) (18056349,1,1,0,1) (18056343,1,1,0,1) (18042461,1,1,0,1) (18061619,1,1,0,1) (18033106,1,1,0,1) (18032734,1,1,0,1) (18032728,1,1,0,1) (18032728,1,1,0,1) (18032955,1,1,0,1) (18032955,1,1,0,1) (18026311,1,1,0,1) (18023812,1,1,0,1) (18042116,1,1,0,1) (18042116,1,1,0,1) (18022013,1,1,0,1) (18022010,1,1,0,1) (18022010,1,1,0,1) (18012509,1,1,0,1) (18012509,1,1,0,1) (18012446,1,1,0,1) (18011061,1,1,0,1) (18011101,1,1,0,1) (18011061,1,1,0,1) (18021280,1,1,0,1) (18014023,1,1,0,1) (17999866,1,1,0,1) (18129952,1,1,0,1) (18129952,1,1,0,1) (18004600,1,1,0,1) (18004600,1,1,0,1) (17990075,1,1,0,1) (17987711,1,1,0,1) (17988477,1,1,0,1) (17987690,1,1,0,1) (17987471,1,1,0,1) (17987471,1,1,0,1) (17987563,1,1,0,1) (17987563,1,1,0,1) (17987563,1,1,0,1) (17987472,1,1,0,1) (17987471,1,1,0,1) (17987471,1,1,0,1) (17987461,1,1,0,1) (17977382,1,1,0,1) (17976672,1,1,0,1) (17977382,1,1,0,1) (17977372,1,1,0,1) (17977372,1,1,0,1) (17977372,1,1,0,1) (17971399,1,1,0,1) (17971399,1,1,0,1) (17976207,1,1,0,1) (17971913,1,1,0,1) (17986407,1,1,0,1) (17969616,1,1,0,1) (17968144,1,1,0,1) (17968144,1,1,0,1) (17969616,1,1,0,1) (17969595,1,1,0,1) (17964130,1,1,0,1) (17964127,1,1,0,1) (17964124,1,1,0,1) (17960327,1,1,0,1) (17960327,1,1,0,1) (17963584,1,1,0,1) (17958924,1,1,0,1) (17954107,1,1,0,1) (17954107,1,1,0,1) (17954107,1,1,0,1) (17954058,1,1,0,1) (17954107,1,1,0,1) (17954053,1,1,0,1) (18032951,1,1,0,1) (17986383,1,1,0,1) (17953910,1,1,0,1) (17953910,1,1,0,1) (17953910,1,1,0,1) (17953910,1,1,0,1) (17946475,1,1,0,1) (17948935,1,1,0,1) (17968039,1,1,0,1) (17948935,1,1,0,1) (17941969,1,1,0,1) (17941969,1,1,0,1) (17941969,1,1,0,1) (17941969,1,1,0,1) (17935789,1,1,0,1) (17940592,1,1,0,1) (17928597,1,1,0,1) (17928684,1,1,0,1) (17928521,1,1,0,1) (17928580,1,1,0,1) (17928580,1,1,0,1) (17928511,1,1,0,1) (17920933,1,1,0,1) (17920933,1,1,0,1) (17920933,1,1,0,1) (17920351,1,1,0,1) (17920933,1,1,0,1) (17918258,1,1,0,1) (17916714,1,1,0,1) (17916630,1,1,0,1) (17916630,1,1,0,1) (17916177,1,1,0,1) (17916177,1,1,0,1) (17906838,1,1,0,1) (17905313,1,1,0,1) (17905074,1,1,0,1) (17905074,1,1,0,1) (17900930,1,1,0,1) (17900930,1,1,0,1) (17900930,1,1,0,1) (17899337,1,1,0,1) (17899338,1,1,0,1) (17899337,1,1,0,1) (17893594,1,1,0,1) (17887795,1,1,0,1) (17887795,1,1,0,1) (17886484,1,1,0,1) (17886484,1,1,0,1) (17900990,1,1,0,1) (17900990,1,1,0,1) (17885995,1,1,0,1) (17878974,1,1,0,1) (17870974,1,1,0,1) (17871881,1,1,0,1) (17870974,1,1,0,1) (17870587,1,1,0,1) (17870582,1,1,0,1) (17867194,1,1,0,1) (17860178,1,1,0,1) (17860184,1,1,0,1) (17856958,1,1,0,1) (17862511,1,1,0,1) (17852748,1,1,0,1) (17854998,1,1,0,1) (17851952,1,1,0,1) (17851952,1,1,0,1) (17851725,1,1,0,1) (17889514,1,1,0,1) (17845832,1,1,0,1) (17846273,1,1,0,1) (17846273,1,1,0,1) (17845825,1,1,0,1) (17845780,1,1,0,1) (17838852,1,1,0,1) (17838847,1,1,0,1) (17842386,1,1,0,1) (17838847,1,1,0,1) (17852647,1,1,0,1) (17838218,1,1,0,1) (17854901,1,1,0,1) (17835123,1,1,0,1) (17833810,1,1,0,1) (17829314,1,1,0,1) (17835749,1,1,0,1) (17828161,1,1,0,1) (17824103,1,1,0,1) (17824103,1,1,0,1) (17821882,1,1,0,1) (17817883,1,1,0,1) (17817788,1,1,0,1) (17817788,1,1,0,1) (17856869,1,1,0,1) (17818809,1,1,0,1) (17809493,1,1,0,1) (17807320,1,1,0,1) (17807320,1,1,0,1) (17807230,1,1,0,1) (17807119,1,1,0,1) (17807119,1,1,0,1) (17798069,1,1,0,1) (17801456,1,1,0,1) (17798295,1,1,0,1) (17797385,1,1,0,1) (17787104,1,1,0,1) (17786954,1,1,0,1) (17786957,1,1,0,1) (17786957,1,1,0,1) (17786957,1,1,0,1) (17784318,1,1,0,1) (17776906,1,1,0,1) (17776906,1,1,0,1) (17776674,1,1,0,1) (17768330,1,1,0,1) (17767764,1,1,0,1) (17767760,1,1,0,1) (17767849,1,1,0,1) (17767849,1,1,0,1) (17764675,1,1,0,1) (17783393,1,1,0,1) (17764664,1,1,0,1) (17764664,1,1,0,1) (17764664,1,1,0,1) (17783388,1,1,0,1) (17757532,1,1,0,1) (17757453,1,1,0,1) (17757636,1,1,0,1) (17757636,1,1,0,1) (17752557,1,1,0,1) (17750195,1,1,0,1) (17748355,1,1,0,1) (17750195,1,1,0,1) (17748212,1,1,0,1) (17748212,1,1,0,1) (17753743,1,1,0,1) (17748058,1,1,0,1) (17745546,1,1,0,1) (17753743,1,1,0,1) (17745546,1,1,0,1) (17744960,1,1,0,1) (17745010,1,1,0,1) (17745546,1,1,0,1) (17743875,1,1,0,1) (17743875,1,1,0,1) (17737427,1,1,0,1) (17737427,1,1,0,1) (17737427,1,1,0,1) (17740497,1,1,0,1) (17730628,1,1,0,1) (17753927,1,1,0,1) (17726757,1,1,0,1) (17726757,1,1,0,1) (17726735,1,1,0,1) (17726735,1,1,0,1) (17720496,1,1,0,1) (17720496,1,1,0,1) (17720496,1,1,0,1) (17715713,1,1,0,1) (17715713,1,1,0,1) (17715679,1,1,0,1) (17715679,1,1,0,1) (17709798,1,1,0,1) (17707970,1,1,0,1) (17885919,1,1,0,1) (17885919,1,1,0,1) (17702131,1,1,0,1) (17702131,1,1,0,1) (17986101,1,1,0,1) (17862283,1,1,0,1) (17862283,1,1,0,1) (17862283,1,1,0,1) (17715015,1,1,0,1) (17715015,1,1,0,1) (17699360,1,1,0,1) (17699360,1,1,0,1) (17859941,1,1,0,1) (17685599,1,1,0,1) (17685480,1,1,0,1) (17682486,1,1,0,1) (17682486,1,1,0,1) (17681333,1,1,0,1) (17676702,1,1,0,1) (17674330,1,1,0,1) (17676670,1,1,0,1) (17674330,1,1,0,1) (17668660,1,1,0,1) (17668658,1,1,0,1) (17715394,1,1,0,1) (17662325,1,1,0,1) (17662300,1,1,0,1) (17662300,1,1,0,1) (17660647,1,1,0,1) (17660647,1,1,0,1) (17657509,1,1,0,1) (17655118,1,1,0,1) (17655118,1,1,0,1) (17655091,1,1,0,1) (17655090,1,1,0,1) (17659581,1,1,0,1) (17653164,1,1,0,1) (17648780,1,1,0,1) (17648773,1,1,0,1) (17635579,1,1,0,1) (17635579,1,1,0,1) (17635579,1,1,0,1) (17635579,1,1,0,1) (17634210,1,1,0,1) (17634197,1,1,0,1) (17634210,1,1,0,1) (17634210,1,1,0,1) (17634210,1,1,0,1) (17628300,1,1,0,1) (17624465,1,1,0,1) (17624465,1,1,0,1) (17622314,1,1,0,1) (17881695,1,1,0,1) (17622097,1,1,0,1) (17622097,1,1,0,1) (17975580,1,1,0,1) (17975580,1,1,0,1) (17609824,1,1,0,1) (17605212,1,1,0,1) (17615827,1,1,0,1) (17597846,1,1,0,1) (17597846,1,1,0,1) (17597846,1,1,0,1) (17597846,1,1,0,1) (17597846,1,1,0,1) (17597846,1,1,0,1) (17597671,1,1,0,1) (17597671,1,1,0,1) (17597671,1,1,0,1) (17972612,1,1,0,1) (18006125,1,1,0,1) (18006125,1,1,0,1) (17600762,1,1,0,1) (17597456,1,1,0,1) (17600762,1,1,0,1) (17600762,1,1,0,1) (17600762,1,1,0,1) (17583860,1,1,0,1) (17583669,1,1,0,1) (17583671,1,1,0,1) (17577601,1,1,0,1) (17577601,1,1,0,1) (17577601,1,1,0,1) (17575620,1,1,0,1) (17575620,1,1,0,1) (17583578,1,1,0,1) (17583578,1,1,0,1) (17583578,1,1,0,1) (17583578,1,1,0,1) (17573678,1,1,0,1) (17573611,1,1,0,1) (17573611,1,1,0,1) (17573611,1,1,0,1) (17577493,1,1,0,1) (17561101,1,1,0,1) (17561101,1,1,0,1) (17559707,1,1,0,1) (17559707,1,1,0,1) (17559707,1,1,0,1) (17559549,1,1,0,1) (17559554,1,1,0,1) (17559543,1,1,0,1) (17559543,1,1,0,1) (17559543,1,1,0,1) (17546422,1,1,0,1) (17546179,1,1,0,1) (17542680,1,1,0,1) (17546563,1,1,0,1) (17541374,1,1,0,1) (17541374,1,1,0,1) (17541374,1,1,0,1) (17534866,1,1,0,1) (17994566,1,1,0,1) (17994566,1,1,0,1) (17994566,1,1,0,1) (17994566,1,1,0,1) (17538974,1,1,0,1) (17528719,1,1,0,1) (17526184,1,1,0,1) (17524335,1,1,0,1) (17511029,1,1,0,1) (17510920,1,1,0,1) (17510989,1,1,0,1) (17510989,1,1,0,1) (17510920,1,1,0,1) (17497434,1,1,0,1) (17486735,1,1,0,1) (17900853,1,1,0,1) (17900853,1,1,0,1) (17900853,1,1,0,1) (17480020,1,1,0,1) (17480020,1,1,0,1) (17480020,1,1,0,1) (17475818,1,1,0,1) (17475818,1,1,0,1) (17475824,1,1,0,1) (18004587,1,1,0,1) (17475296,1,1,0,1) (17460573,1,1,0,1) (17460573,1,1,0,1) (17455220,1,1,0,1) (17455220,1,1,0,1) (17455220,1,1,0,1) (17455220,1,1,0,1) (17451851,1,1,0,1) (17867266,1,1,0,1) (17867266,1,1,0,1) (17867266,1,1,0,1) (17451702,1,1,0,1) (17429144,1,1,0,1) (17433340,1,1,0,1) (17597424,1,1,0,1) (17597424,1,1,0,1) (17421991,1,1,0,1) (17417967,1,1,0,1) (17417967,1,1,0,1) (17408396,1,1,0,1) (17389856,1,1,0,1) (17389856,1,1,0,1) (17373407,1,1,0,1) (17373407,1,1,0,1) (17368701,1,1,0,1) (17351223,1,1,0,1) (17351077,1,1,0,1) (17354768,1,1,0,1) (17343153,1,1,0,1) (17343153,1,1,0,1) (17316565,1,1,0,1) (17316565,1,1,0,1) (17303778,1,1,0,1) (17303709,1,1,0,1) (17303778,1,1,0,1) (17290598,1,1,0,1) (17290598,1,1,0,1) (17290434,1,1,0,1) (17290434,1,1,0,1) (17285839,1,1,0,1) (17285839,1,1,0,1) (17285839,1,1,0,1) (17290324,1,1,0,1) (17290324,1,1,0,1) (17305233,1,1,0,1) (17267492,1,1,0,1) (17254114,1,1,0,1) (17230618,1,1,0,1) (17230618,1,1,0,1) (17227090,1,1,0,1) (17215795,1,1,0,1) (17215795,1,1,0,1) (17216532,1,1,0,1) (17215746,1,1,0,1) (17215533,1,1,0,1) (17202987,1,1,0,1) (17267171,1,1,0,1) (17191783,1,1,0,1) (17184126,1,1,0,1) (17184126,1,1,0,1) (17169336,1,1,0,1) (17906561,1,1,0,1) (17906561,1,1,0,1) (17164425,1,1,0,1) (17164362,1,1,0,1) (17161671,1,1,0,1) (17161671,1,1,0,1) (17152338,1,1,0,1) (17145979,1,1,0,1) (17140249,1,1,0,1) (17135817,1,1,0,1) (17158692,1,1,0,1) (17158692,1,1,0,1) (17132283,1,1,0,1) (17132208,1,1,0,1) (17132208,1,1,0,1) (17119334,1,1,0,1) (17119258,1,1,0,1) (17119258,1,1,0,1) (17109531,1,1,0,1) (17092466,1,1,0,1) (17092466,1,1,0,1) (17102745,1,1,0,1) (17102745,1,1,0,1) (17083353,1,1,0,1) (17102744,1,1,0,1) (17052543,1,1,0,1) (17052540,1,1,0,1) (17052543,1,1,0,1) (17039134,1,1,0,1) (17039127,1,1,0,1) (17039127,1,1,0,1) (17039109,1,1,0,1) (17039041,1,1,0,1) (17029761,1,1,0,1) (17029754,1,1,0,1) (17026761,1,1,0,1) (17021076,1,1,0,1) (17021076,1,1,0,1) (17021055,1,1,0,1) (17021076,1,1,0,1) (17021076,1,1,0,1) (17927459,1,1,0,1) (17927459,1,1,0,1) (17007481,1,1,0,1) (17007279,1,1,0,1) (17007279,1,1,0,1) (17007279,1,1,0,1) (17007279,1,1,0,1) (16967391,1,1,0,1) (17098389,1,1,0,1) (17098389,1,1,0,1) (16950164,1,1,0,1) (16950198,1,1,0,1) (16950198,1,1,0,1) (16950012,1,1,0,1) (16942325,1,1,0,1) (16942325,1,1,0,1) (16942325,1,1,0,1) (18013991,1,1,0,1) (18013991,1,1,0,1) (16942179,1,1,0,1) (16930649,1,1,0,1) (16924138,1,1,0,1) (16924138,1,1,0,1) (16918196,1,1,0,1) (16913634,1,1,0,1) (16906897,1,1,0,1) (16893276,1,1,0,1) (16893055,1,1,0,1) (16893276,1,1,0,1) (16887575,1,1,0,1) (16882339,1,1,0,1) (16880569,1,1,0,1) (16871327,1,1,0,1) (16871327,1,1,0,1) (16863257,1,1,0,1) (16854905,1,1,0,1) (16854770,1,1,0,1) (16854770,1,1,0,1) (16854770,1,1,0,1) (16848458,1,1,0,1) (16835975,1,1,0,1) (16835448,1,1,0,1) (16848205,1,1,0,1) (16831535,1,1,0,1) (16831535,1,1,0,1) (16815970,1,1,0,1) (16815973,1,1,0,1) (16815973,1,1,0,1) (16815636,1,1,0,1) (16815636,1,1,0,1) (16815636,1,1,0,1) (16815612,1,1,0,1) (16815369,1,1,0,1) (16815369,1,1,0,1) (16888979,1,1,0,1) (16797435,1,1,0,1) (16797435,1,1,0,1) (16787822,1,1,0,1) (16786153,1,1,0,1) (16786548,1,1,0,1) (16776851,1,1,0,1) (16776851,1,1,0,1) (16772683,1,1,0,1) (16758186,1,1,0,1) (16758186,1,1,0,1) (16729215,1,1,0,1) (16729102,1,1,0,1) (16725342,1,1,0,1) (16729102,1,1,0,1) (16720121,1,1,0,1) (16716003,1,1,0,1) (16716003,1,1,0,1) (16698056,1,1,0,1) (16698233,1,1,0,1) (16698233,1,1,0,1) (16697188,1,1,0,1) (16697187,1,1,0,1) (16697187,1,1,0,1) (16700799,1,1,0,1) (16700799,1,1,0,1) (16691527,1,1,0,1) (17859774,1,1,0,1) (17859774,1,1,0,1) (16684483,1,1,0,1) (16684483,1,1,0,1) (16684483,1,1,0,1) (16686885,1,1,0,1) (16678859,1,1,0,1) (16673323,1,1,0,1) (16673323,1,1,0,1) (16678936,1,1,0,1) (16662964,1,1,0,1) (16662964,1,1,0,1) (16653563,1,1,0,1) (16653563,1,1,0,1) (16653465,1,1,0,1) (16653356,1,1,0,1) (16653356,1,1,0,1) (16653465,1,1,0,1) (16640115,1,1,0,1) (16640115,1,1,0,1) (16634101,1,1,0,1) (16634101,1,1,0,1) (16631638,1,1,0,1) (16631578,1,1,0,1) (16631578,1,1,0,1) (16619120,1,1,0,1) (16615501,1,1,0,1) (16615489,1,1,0,1) (16623594,1,1,0,1) (16623594,1,1,0,1) (16609252,1,1,0,1) ; INSERT INTO atd_txdot_units ('crash_id','unit_nbr','unit_desc_id','veh_body_styl_id') VALUES (17546179,1,1,1) (17451702,1,1,1) (14832414,1,1,1) (14720068,1,1,1) ;

[frankhereford]

Would this work for your purposes?

Thanks @xavierapostol! I'll apply those inserts to a local copy of the VZDB and check them out - I appreciate you pulling that together. I'll report back here as soon as I have that done.

[patrickm02L]

Checked the database to see if any records had been resolved with the recent action taken to QA the VZV Fatality and Mode count widgets #8424.

The number is now 6.

[patrickm02L]

Reference #8895

[patrickm02L]

@frankhereford we're going to use this Issue to resolve the missing unit missing persons in #8895.

[patrickm02L]

@patrickm02L Patrick clean up, and reissue when actions are clear.

[patrickm02L]

Asked @frankhereford to investigate why crash ID 17451702 is missing data, and created a separate Issue for him to document his findings #9754.

The new Issue will give us some room to investigate potential approaches with the hope that his findings will be able to scale to other Crash IDs that are missing persons and units.

[patrickm02L]

@frankhereford Based on your findings from #9754 where you determined that 17451702 no longer exists and that deleted crashes in CRIS do not show up in VZDB, can you find the list of crashes that were deleted in CRIS for the past year? Then delete those crashes from our system to ensure that our database is in sync .

That action should close out this Issue.

Then, I'll make another Issue to do the same process for the preceding years and bring it to the team discuss how to handle our database in a bigger picture way whether that's running a script manually to find the deleted CRIS crashes, creating an automation to find and delete them on a regularly schedule cadence or some other approach that we have yet to determine.

[frankhereford]

@patrickm02L Sure thing. I have a one-year chunk of data from CRIS from the previous investigation, and I can use it to generate a list of the records in the crashes, units, person, & primaryperson tables which should be deleted. It comes out to 13 crashes, 19 units, 9 primaryperson and 3 person records, which feels like a very reasonable and expected amount for a year's worth of crash deletion events in CRIS. I have removed these upstream-deleted records from the production VZ database which closes this issue, as these deletions cover the deficient crashes in the original comment.

The clean-up of our database only covers the time that I had downloaded data for, and so, I suggest we create another spin-off issue to do this same operation on the rest of the VZ data and formulate a plan to do it at regular intervals, perhaps every 6 months, going forward. Please feel free to catch me on slack if I can provide any more information - thanks!