Reform process for creating unique IDs for Open Data

[radumas]

For each api_id in the wys.locations table, the record with the earliest start_date, that start_date should be the first date we have data in the wys.speed_counts_agg_5kph table. This cascades into the 2 materialized views which create unique IDs for each sign installation for Open Data for mobile signs and stationary signs

e.g. for api_id 368, the start_date for the record with id=135 should be 2016-01-01 not 2019-08-16.

• [x] Update wys.locations so that the earliest start_date for each api_id is the first date on which we have data
• [ ] Change the wys.mobile_api_id materialized view to be a table with a function to insert new data monthly
• [ ] Change the wys.stationary_signs materialized view to be a table with a function to insert new data monthly
• [ ] Add those functions to the monthly WYS dag.
• [ ] delete the old materialized views.

[radumas]

Noting that updates to the addresses of the following signs didn't get updated (in the mat view). api_id IN (33956, 33953).

I ended up editing the earlier address to be the new, corrected, address.

[tahaislam]

deleted the insertion functions for wys.mobile_api_id and wys.stationary_signs from wys_2022_upgrades as these mat views haven't been converted yet into tables (437a03742f207e780ca9e47cf5a58e7bc331354f)