Update bluetooth detector history

[chmnata]

Wellington and Blue Jays way detector moved to Gardiner and parliament on July 12th of 2021. As of 2023-05-31, the spadina and bremner got moved to gardiner and dufferin as well We need to get rid of data for analysis_id 1454508 start July 11th 2021. (look into the data first)

• Update the detector history table
• Set that analysis_id to not pull in the old analyses table
• See what else I need to update

[chmnata]

For Wellington and Bremner:

• Update routes table to indicate that route is inactive since 2021-07-11 (when the detector moved)

UPDATE bluetooth.routes_temp 
SET date_inactive = '2021-07-11'
where analysis_id = 1454508;

• Delete aggregated 5 min bluetooth data for the inactive route that were getting incorrect data after detector moved

delete from bluetooth.aggr_5min 
where analysis_id = 1454508 AND datetime_bin >= '2021-07-11'

• Delete raw observation data for the inactive route

delete from bluetooth.observations
where analysis_id = 1454508 AND measured_timestamp >= '2021-07-11'

• Update reader location table

update  bluetooth.reader_locations_temp
set date_inactive = '2021-07-11', 
	date_last_received = '2021-07-11'
where reader_id = 178 and name = 'WE_BJ' 

[chmnata]

Received detector status from Jacob today, some weirdness:

Detectors not in Toronto

• 000EA50097AC, not online
• 000EA5008E58, not online

Location discrepancy

• bt_id 9080 (not online) from Jacob's spreadsheet is at A, but in our data is at B

Status discrepancy

• bt 5122, readerid 186, at DVP and York Mills. Inactive in our data, online in theirs, but we also don't have routes configured with that detector.

• bt 5121, readerid 175, DVP and Spanbridge. Inactive in our data, online in theirs, we also dont have routes configured with that detector.

• These two bt 9096 and bt 9039 shows as inactive in our data (because no other active detectors are around), are online in theirs

• The ones circled in black (9100, 9090, 9093) on the other hand..... shows as inactive in ours, active in theirs but we are not getting any data for those routes. HMMMM. The one highlighted in yellow (9097) shows as inactive in ours but active in theirs, but it makes sense cause no detector connecting to it is online.

• This wellington one is inactive cause I updated it, but its active in theirs.... maybe their location is not accurate?

• These (4033, 5124) are inactive in ours, but active in theirs, I think the routes are not configured for these two.

• 5017 is inactive in ours but active in theirs, I think it is indeed active cause we have data for some routes that uses it

• 5623 inactive in ours but active in theirs, it should be active cause we are getting data for the route

• 9312, 5819 at bloor inactive in ours but active in theirs, there is no data for these routes, I think its not configured.

• Bloor Yonge 9102, offline in both, but last status update from jacob's list is today
  

Missing detectors in Jacob's list

• The three highlighted blob (5824, 5822, 5817) in not in jacob's list, they are all inactive but has no inactive date in our database. 5824 - all connected route's last active date is 2018-10-10 5817 - connected route's last active date varies but its around 2018 oct or 2018 aug 5822 - connected route's last active date varies but its around 2018 oct or 2018 aug as well

[chmnata]

Re: routes that should have 2 working sensors but haven't produced data.

Our analysis_id in bluetooth.routes_temp does not match with their analysis_id. Our all_analyses have the correct analysis_id that matched with their routes tagged with 'HISTORICAL'. Except for 2 spadina routes between harbord to bloor, they do not exist in their dashboard or the all_analyses table.

Ran the following to set pull_data to TRUE for the above analysis_id:

UPDATE bluetooth.all_analyses 
SET pull_data = TRUE 
where analysis_id in (1514906,
                            1515280,
                            1514986,
                            1514879,
                            1514850,
                             1515251)

[chmnata]

Tried pulling those bluetooth routes (1514906,1515280,1514986,1514879,1514850,1515251) with an updated pull_data, they successfully backfilled (june1 to jun15), and pulled with blip daily

[chmnata]

Slightly confused as to where some analysis_id comes from in the bluetooth.routes_temp table.. for example, these analysis_ids are incorrect

• 1600300 "Spadina-SB_Harbord-to-College" "Spadina" "Harbord" "133" "Spadina" "College" should be 1515280 Historic DT3_0020_Spadina-SB_Harbord-to-College

• 1600290 "Spadina-NB_College-to-Harbord" "Spadina" "College" "98" "Spadina" "Harbord" should be 1515251 Historic DT3_0019_Spadina-NB_College-to-Harbord

(pull_data is set as true for these two routes, so its just an error in our routes table)

[chmnata]

The earliest data backfilled for (1514906,1515280,1514986,1514879,1514850,1515251) is 2022-05-17, even though some of them has a date_active of Aug 2018 and Aug 2020.

[radumas]

Detector 5272 is in two locations.

I ran the following updates

UPDATE bluetooth.readers_history_corrected_temp
	SET --date_start=?,
		date_end='2018-09-20'
	WHERE 
	reader_history_id=1677;

UPDATE bluetooth.readers_history_corrected_temp
	SET --date_start=?,
		date_start='2018-10-11'
	WHERE 
	reader_history_id=1511;

But I didn't update the Castlefield routes, where the bt_id should be 5799 (or maybe the query used by https://github.com/CityofToronto/bdit_data-sources/issues/654 isn't properly accounting for moving readers)