Check Bluetooth readers are working.

[radumas]

To look at locations

SELECT detector_name, street1, street2, bluetooth_id, wifi_id, detector_name_old, project, date_start, date_end, latitude, 
longitude, "PX", index1, loc
	FROM bluetooth.detectors_new
	WHERE date_end IS NULL AND bluetooth_id IN (
5123
,5329
,5805
,9089
,9103
)

To find routes

SELECT bt_id, routes.* FROM  mohan.detectors_history_corrected 
   	LEFT JOIN  mohan.reader_locations USING (reader_id)
	LEFT JOIN mohan.routes ON start_reader_id::INT = reader_id OR end_reader_id::INT = reader_id
	WHERE bt_id IN (
5123
,5329
,5805
,9089
,9103
)
ORDER BY bt_id

Then was double checking in the blip dashboard but probably better to mix this into a query looking at observation data.

[radumas]

Investigations can be found in Big Data Group\Data Sources\Bluetooth\Bliptrack\WO August 23 2023 BLIP bluetooth_list_offline_20230907_rad.xlsx

Vendor sent us a list of sensors and their status which can get cross-referenced here Big Data Group\Data Sources\Bluetooth\Bliptrack\Inventory\toronto_sensor_gps_location_2023_09_13.dsv

[gabrielwol]

Of the signs sent to the contractor, these 5 appear to have been working at some point in Sept/Oct, though only 2 were active as of yesterday... (partial result of below sql):

"bt_id"	"count"	"min"	"max"
5272	370520	"2023-09-01 00:00:01"	"2023-10-25 23:59:51"
9098	106642	"2023-09-01 00:00:35"	"2023-10-25 23:59:29"
9089	32016	"2023-10-06 00:03:58"	"2023-10-18 23:48:38"
9125	22828	"2023-09-01 00:02:22"	"2023-10-07 17:13:12"
9102	985	"2023-10-08 06:34:03"	"2023-10-21 14:08:53"

This does rely on mohan.routes being accurate.

WITH routes AS (
    SELECT dhc.bt_id, rts.*
    FROM mohan.detectors_history_corrected AS dhc
    LEFT JOIN mohan.reader_locations AS rl ON 
        dhc.reader_id = rl.reader_id
    LEFT JOIN mohan.routes AS rts ON
        rts.start_reader_id::INT = rl.reader_id
        OR rts.end_reader_id::INT = rl.reader_id
    --optionally filter for certain bluetooth_ids
    WHERE bt_id IN (
        --these were all the signs sent to the contractor. 
        4037, 5059, 5061, 5123, 5233, 5236, 5263, 5266, 5268, 5269, 5270, 5271, 5272, 5329, 5330, 5334, 5335, 5412, 5414, 5417, 5608, 5610, 5612, 5614, 5789, 5790, 5792, 5797, 5804, 5805, 5817, 5822, 5824, 9001, 9018, 9021, 9026, 9034, 9037, 9041, 9089, 9098, 9102, 9103, 9116, 9119, 9125
    )
)

SELECT
    bt_id,
    COUNT(observations.*),
    MIN(measured_timestamp),
    MAX(measured_timestamp),
    string_agg(DISTINCT routes.analysis_id::text, '; ') AS analysis_ids
FROM routes
LEFT JOIN bluetooth.observations ON
    routes.analysis_id = observations.analysis_id
    AND measured_timestamp >= '2023-09-01'::date
GROUP BY bt_id
ORDER BY bt_id

[gabrielwol]

I updated this file with columns indicating whether the bt_id was marked as available by Vendor and whether they were available in bigdata in September/October as per the previous comment. Big Data Group\Data Sources\Bluetooth\Bliptrack\WO August 23 2023 BLIP bluetooth_list_offline_20230907_rad.xlsx

[chmnata]

You could try bluetooth.routes_temp, and bluetooth.readers_history_corrected_temp and possibly bluetooth.reader_status_history_temp