[Enhancement] Update location trigger logic that uses road part & highway number

[atdservicebot]

What application are you using?

Vision Zero (Editor)

With more of a focus on Level 5 roadways, we would like to ensure our comprehensive cost maps (Level 1 to 4 and Level 5 maps) are as accurate as possible. During a recent review we have uncovered some issues with trying to provide more accurate representations of where these crashes are occurring when a level 5 polygon overlaps with level 1-4 polygons.

Goals:

Have the ability to manually update crashes based on corrected road part & highway number and improve the accuracy of assigning the proper street level for analysis and aggregation, including level 5 roadways.

ETL Process (potential changes for discussion in Sprint Review mtg):

• Level 5 - Runs first and assigns all crashes with rpt_road_part_id <> 2 AND UPPER(LTRIM(rpt_hwy_num)) IN ('35', '183','183A','1','290','71','360','620','45','130')
• Note: '2222', '969', '734', '973', '1825', '272', '1626', '111', '1325', '1327', '343', '2304', '1826', and '2244' may need further review (what level are they, 4 or 5?)
• Level 1 to 4 - assigns a polygon based on geospatial "position" that does not have a Level 5 location_id

Describe the outcome you'd like to see when this feature is implemented:

• Have the ability to manually overlay rpt_road_part_id and rpt_hwy_num and have the "polygonizer" rerun to ensure accrurate location attributed.

April 30th, 2023

Requested By Xavier A.

Request ID: DTS23-106320

[xavierapostol]

Let's discuss this topic in the next VZ Sprint Review (Tuesday February 7th, 2023)

[patrickm02L]

In 2/7/23 Planning:

Workarounds

• Add a new field to trigger the execution of the update.

cc: @patrickm02L review

[patrickm02L]

This is something that I think will ultimately fall to Milad, and part of the larger discussion about what to do about #8388

[mddilley]

From Xavier on 04/13/2023:

Given rpt_road_part_id and rpt_hwy_num are manually editable. Here are the steps for identifying a Level 5 location_id

Step 1. Assign a Level 5 location_id (using the filter on the locations table: public.atd_txdot_locations.location_group = 2) to the public.atd_txdot_crashes.location_id field using the following criteria, rpt_road_part_id <> 2 AND UPPER(LTRIM(rpt_hwy_num)) IN ('35', '183', '183A', '1', '290', '71', '360', '620', '45', '130') Step 2. If the criteria in Step 1 is not TRUE, then use existing process to identify the location_id (Street Level 1 through 4) Step 3. If the criteria in Step 1 is not TRUE, then assign a Level 5 location_id (using the filter on the locations table: public.atd_txdot_locations.location_group = 2) to the public.atd_txdot_crashes.location_id field or NULL if all three steps are FALSE

[frankhereford]

If it's helpful, here's the examples of what I needed to do to the SQL snippet to allow it to be inverted via the not keyword:

-- crashes to be excluded from the VZE
--select count(crashes.crash_id)
select crash_id, rpt_road_part_id, UPPER(LTRIM(rpt_hwy_num))
from atd_txdot_crashes crashes
where true
  and crash_date > (now() - interval '5 years')
  and rpt_road_part_id <> 2 
  AND coalesce(
         UPPER(LTRIM(rpt_hwy_num)) 
           IN ('35', '183','183A','1','290','71','360','620','45','130')
         , false);

-- crashes to be included       
--select count(crashes.crash_id)
select crash_id, rpt_road_part_id, UPPER(LTRIM(rpt_hwy_num))
from atd_txdot_crashes crashes
where true
  and crash_date > (now() - interval '5 years')
  AND not( true
   and rpt_road_part_id <> 2 
   and coalesce(
         UPPER(LTRIM(rpt_hwy_num)) 
           IN ('35', '183','183A','1','290','71','360','620','45','130')
         , false)
   );

[patrickm02L]

From Xavier on 04/13/2023:

Given rpt_road_part_id and rpt_hwy_num are manually editable. Here are the steps for identifying a Level 5 location_id Step 1. Assign a Level 5 location_id (using the filter on the locations table: public.atd_txdot_locations.location_group = 2) to the public.atd_txdot_crashes.location_id field using the following criteria, rpt_road_part_id <> 2 AND UPPER(LTRIM(rpt_hwy_num)) IN ('35', '183', '183A', '1', '290', '71', '360', '620', '45', '130') Step 2. If the criteria in Step 1 is not TRUE, then use existing process to identify the location_id (Street Level 1 through 4) Step 3. If the criteria in Step 1 is not TRUE, then assign a Level 5 location_id (using the filter on the locations table: public.atd_txdot_locations.location_group = 2) to the public.atd_txdot_crashes.location_id field or NULL if all three steps are FALSE

2/12/24

Removing highways 360 & 620 because they are traffic controlled.

Note

• Sections of 183, 71, 290, and 45 will also be traffic controlled
• We'll need to find the intersections where this occurs
• @MiladMAK making you aware of this rule set of which polygons to apply

[johnclary]

I renamed this to better capture what I think is needed—but this issue may just be a dupe. We'll see.