Bluetooth: List of WIP tables and tasks

[chmnata]

A list of WIP tasks for bluetooth!

Attempting to organize all the issues that needs to be taken care of for our bluetooth dataset. There are lots of overlap issues/ changes in code and things to decide on before tackling these issues. This will require some XXL time on QCing different tables, mainly the reader history, detector history, route history, route definition. The DAG is technically operational but everything is yet to be reviewed, we should also implement some of the universal data checks created by @gabrielwol here. Up for rethink!

Segments

• Segment definition
  • Fixing up bluetooth.routes_temp Spadina routes from Dundas to Bloor #824
    • 2 remaining routes without analysis_id and 30 routes with incorrect analysis_id
  • Bluetooth: Fix bluetooth highway segments that ends and start at Yonge/Lakeshore #651
    • Incorrectly drawn highway segments. Need to update and QC.
  • Create new bluetooth highway routes to ensure double coverage for each route  #649
    • Create new highway routes to ensure double coverage. Awaiting for review. Likely need updating since we moved detectors around.

Data checks

• Check Bluetooth readers are working. #654
  • We received a list of sensor's status from the vendor to check what sensors are not working. That was a while ago, we likely need an update.
• Bluetooth: Routes that never had data in bluetooth.aggr_5mins #459
  • Routes with no data ever. Need to create a list of routes with no data and decide what to do with them
• Delete data on Sheppard routes #426
  • Delete data for 2 sheppard routes
• Four Bluetooth routes not being aggregated #340
  • Four routes not being aggregated. Need to investigate why and try to repull

Tracking Tables

• Update bluetooth detector history  #619
  • Update bluetooth detector history table to track moved detectors. One yet to be updated and needs QC.
• Bluetooth: Fix start dates in Detector History Table  #420
  • Detector history's table start date should be the same as when the detector was installed. Should be a quick update.
• Create a table of history of Bluetooth Sensors operational status #250
  • reader history already exist in temp form. Need to QC.
• Write-up Standard Operating Procedures for Retiring Routes  #201
  • Improvement on readme for what to do when we retire segments
• Reconcile Bluetooth routes that have changed over time #105
  • Similar to above issue, can be closed after QC route and detector history + update doc

DAG

• Btdag #381
  • PR to merge DAG.
  • Needs to update all temp tables to non temp tables
  • DAG is in old format, needs to update alert formats and timezone

Open Data

Not sure if this is still operational.

• Automate Bluetooth OpenData Extraction #180
• Refresh Bluetooth Open Data #66

Outdated PR:

• Mohan Branched #326
  • We can close this, all table and readme definition is outdated

We should talk about:

• Table structure and their definition (technically Update/Modernize Bluetooth Detector Tables #196)
  • We should standardize or improve the readme for some columns to make sure we are all on the same page before changing functions and updating fields. e.g. reader history table
  • Btdag #381 (comment)