Gabe Wolofsky

## What this pull request accomplishes: - Removes `volume_15min_mvt_uid` from pkey of `volumes_15min_mvt` so that it works as a unique constraint. ## Issue(s) this solves: - #872 ## What, in...

Miovision: Duplicates in volumes_15min_mvt

1

This would have stemmed from changes made in https://github.com/CityofToronto/bdit_data-sources/pull/735. I'm guessing the duplicates were then inserted when adding new intersections by accidentally running two `run-api` commands in parallel `(1) clear...

Anti join conditions don't account for case when there is an existing `null` classification a_r and a new, non-null a_r to be inserted. This line needs an `OR existing.classification_uid IS...

2024-01-30 Add new Miovision intersections

5

Add new Miovision intersections between August 2023 - January 2024.

We need to develop a better system for communicating issues with Miovision and tracking their responses internally.

#745 add hourly vehicle volume view

1

## What this pull request accomplishes: - - ## Issue(s) this solves: - Closes #745 ## What, in particular, needs to reviewed: - ## What needs to be done by...

There are anomalies between Miovision bike approach (classification_uid = 2) and bike tmc (classification_uid = 10) volumes

5

The [docs](https://github.com/CityofToronto/bdit_data-sources/blob/master/volumes/miovision/README.md#classifications) say: > Approach level bicycle counts should be used for the large majority of applications as the data is considered more accurate. However, for most intersection-approach combinations, bike...

miovision_csv schema cleanup

6

`miovision_csv` tables ending in 2020 can be deleted, as they now live in `miovision_api` schema. @Nate-Wessel noted that querying this older data from miovision_csv for the volume index project is...

`wys.aggregate_speed_counts_one_hour_5kph` failing with duplicate key error

2

See [airflow logs](https://trans-bdit.intra.prod-toronto.ca/airflow/log?dag_id=pull_wys&task_id=pull_wys&execution_date=2023-08-25T23%3A46%3A01.602352%2B00%3A00). I suspect this occurred because on one of the (many) retries for this date, new data was pulled for a `api_id` / `datetime_bin` combo which had previously...

The assignment of `wys.raw_data` column `speed_count_uid` within function `wys.aggregate_speed_counts_one_hour_5kph` is incorrect as it does not consider speed_uid. Will need to add a join to `wys.speed_bins_old` within `wys.aggregate_speed_counts_one_hour_5kph` UPDATE query. Will...