Create a table of history of Bluetooth Sensors operational status

[radumas]

We have a process for identifying routes that were not reporting data for the most recent date pulled. We would like to know which sensors were in fact down, and have a history of it. This will tie in to the modernized tables from https://github.com/CityofToronto/bdit_data-sources/issues/196#issue-449358417

So bluetooth.reader_status_history:

reader_id int, --unique id from reader_locations
dt DATE,
active BOOLEAN

This would require joining bluetooth.all_analyses to bluetooth.agg_5min using the analysis_id to determine which routes were active. Within all_analyses the route_points json column contains details of the two points making up the route including the sensor name.

An intermediary step would be creating a lookup view from all_analyses that expands that route_points json column so we can more easily join routes to sensors. i.e.

analysis_id INT,
start_detector TEXT, --e.g. CO_BA
end_detector TEXT --e.g. CO_UN

So the outputs are:

• [x] bluetooth.reader_status_history: a table of the history of whether each reader_location was active on date dt
• [ ] bluetooth.update_status_history(_dt date): a function to update the bluetooth.reader_status_history table for date _dt which would also update the date_last_received column of the bluetooth.reader_locations table if _dt is more recent that date_last_received
• [ ] An airflow function that replaces readers_down which would run bluetooth.update_status_history(_dt date) on a daily basis and send notifications when detectors are newly down.

[radumas]

See readers_not_working_yesterday to find which detectors weren't working yesterday.

Now we want to automate creating that lookup table identifying the detector_name that are part of each analysis_id as a VIEW.

[radumas]

A more useful version of bluetooth.readers_not_working_yesterday would be the status of all detectors that should be installed and operational based on the bluetooth.detector_history table to be created https://github.com/CityofToronto/bdit_data-sources/issues/196#issue-449358417

[webgisgeek]

online_offline_readers.pdf This is the map of readers and routes online and offline status showing intersections names.

[webgisgeek]

online_offline_readers_bt_id.pdf This is the map of readers and routes online and offline status showing the four digit zones that could be more handy for JJ(bliptrack)

[chmnata]

Notes of changing table names: bluetooth.route_points (which doesn't exist in mohan and bluetooth schema), BUT mohan.route does exist, and instead of bluetooth.route_point_status_history we have bluetooth.route_status_history so I'm assuming mohan.route = mohan.route_points. With this assumption, what we want for this table is to show the date_last_received for each route, updated bybluetooth.update_status_history(_dt date)(which does not exist but something that does that is calledmohan.insert_report_date`).

[chmnata]

Seems like mohan.routes is supposed to be a table that has all the information of each route (or segment). This table is originally called bluetooth.segments in the bluetooth schema, with some different columns. Is routes table going to replace segments ?

Is this table QCed? If not, can I QC it by comparing the old segments in bluetooth.segments, and the new_segments in mohan.bt_new_segments assumption this table is a merge of the two.

Also date_inactive has not been filled in.

[radumas]

Just updated the language in the comment at the top so the tables referred to here and in #250 are the same