Create some useful artery code views

[radumas]

We frequently respond to data requests for volume data at signalized intersections. We should internalize these two transformation:

WITH location_px AS (
    -- Select from only arterycodes corresponding to PXs.
    -- regexp_matches implicitly discards rows without a match.
    SELECT arterycode,
           (regexp_matches(location::text, 'PX (\d+)'::text))[1]::integer AS px,
           regexp_replace(location::text, E'[\\n\\r]+', ' ', 'g') as location
    FROM traffic.arterydata
), location_sigint AS (
    -- Filter for only PXs that have traffic signals.
    SELECT a.*
    FROM location_px a
    LEFT JOIN (SELECT *
               FROM vz_safety_programs_staging.signals_cart
               WHERE asset_type = 'Traffic Signals') b USING (px)
    WHERE b.asset_type IS NOT NULL
)

I already created the below but didn't commit because...

CREATE VIEW traffic.artery_locations_px AS

	SELECT arterycode,
           (regexp_matches(location::text, 'PX (\d+)'::text))[1]::integer AS px,
           regexp_replace(location::text, E'[\\n\\r]+', ' ', 'g') as location
    FROM traffic.arterydata;
	
COMMENT ON VIEW traffic.artery_locations_px IS 'Lookup between artery codes and px numbers (intersections)';
GRANT SELECT ON TABLE traffic.artery_locations_px TO bdit_humans;

• [x] The second part should be created
• [ ] Both should be documented in the volumes/README.md

[chmnata]

In vz_safety_programs_staging.signals_cart, there are 6 unique asset type:

• "Leading Pedestrian Intervals"
• "Audible Pedestrian Signals"
• "Uninterruptable Power Supply"
• "Pedestrian Crossovers"
• "Traffic Signals"
• "LED Blankout Signs"

I'm not sure if the types other than Traffic signals and pedestrian crossover would be useful in the view

[radumas]

Good check.

I'm guessing those other four are already installed as signalized intersections.

[chmnata]

Looks like px will be duplicated if multiple asset types are present. I'm gonna continue with filtering for Traffic Signals in the view then :meow_salute: