Create Miovision Reporting View(s)

[radumas]

Want to reduce some of the confusion in querying the data for data requests and reporting. Generally we report using the ATR-style data in volumes_15min.

Non-exhaustive list of requirements:

• aggregates from the atr data
• because we are only aggregating cycling approach counts, this should be entrance volumes only
• aggregation: checks if there's the appropriate number of hours of data for the day (bins where volume IS NOT NULL)
• anti-join on the anomalous ranges table and filters for a particular level of concern?
• aggregates vehicle volumes (class_type = 'Vehicles')
• joins to the centreline
• handles the transition from bicycle TMC to approach volumes (see Should we avoid having duplicative bicycle classifications in aggregate data? #695 )

[gabrielwol]

I began working on this issue with an hourly_vehicle_volumes view. need to regroup with @radumas to see if on the right track.

[radumas]

Phase 1 can:

• exclude bike volumes
• include unacceptable gaps (filter out the hour with a missing 15-min bin)
• include anomalous gaps (filter out do-not-use, questionable, include valid-*)

Phase 2: figure out cycling volumes

Phase 3: incorporate alerts...?