#87 TEPS 2023

[gabrielwol]

Summary of edits:

• New Schema: teps
• Did not reexamine the RESCU thresholds.
• We are reusing the 2022 centreline + conflation.
• volume_project/extract/2022/miovision_sqls/miovision_centreline_20220705.sql: Sare, I think you already fixed most of these duplicate issues with the miovision-centreline but had not committed them so I had to recreate them. Will use this as the basis for #923.
• volume_project/extract/2022/rescu_sqls/rescu_centreline.sql: Added the 2023 rescu detectors to the centreline conflation script. There were 4 nulls that I manually assigned + an update script for the missing geoms.
• volume_project/extract/2023/miovision_sqls/miovision_atr_2023.sql: just a rename.
• The rest is just fluff!

Some high level checks: RESCU: Despite the outages we have noticed the last few months of 2023, there was more data for in 2023 than 2022.

SELECT yr, COUNT(*) AS num_detectors, ROUND(AVG(count), 1) AS avg_days, SUM(count) AS sensor_x_days
FROM (
    SELECT detector_id, '2022' AS yr, COUNT(*) FROM teps.rescu_enuf_vol_22 GROUP BY 1
    UNION
    SELECT detector_id, '2023' AS yr, COUNT(*) FROM teps.rescu_enuf_vol_23 GROUP BY 1
) AS summ
GROUP BY yr

"yr"	"num_detectors"	"avg_days"	"sensor_x_days"
"2022"	98	144.1	14125
"2023"	98	205.4	20131

Classification counts:

• There was some concern that Spectrum classification counts have not started making it into the database yet, which shows up in the max date of 2023-08 below. However, there are still more counts conducted in 2023 vs 2022 overall.

  SELECT '2022' AS yr, MIN(count_bin), MAX(count_bin), COUNT(DISTINCT volume_id) FROM teps.classvol_20220705
  UNION
  SELECT '2023' AS yr, MIN(count_bin), MAX(count_bin), COUNT(DISTINCT volume_id) FROM teps.classvol_2023;

  "yr"	"min"	"max"	"count"
  "2022"	"2022-03-21 00:00:00"	"2022-11-24 23:45:00"	234
  "2023"	"2023-03-28 00:00:00"	"2023-08-03 23:45:00"	807

Results look good for speedvol tables:	"yr"	"min"	"max"	"count"
"2022"	"2022-02-22 00:00:00"	"2022-12-05 23:45:00"	5665
"2023"	"2023-01-10 00:00:00"	"2023-12-07 23:45:00"	5027

Miovision - centreline check: Double checked and there were 4 legs missing from miovision centreline. All are valid omissions:

/*
5   "W" --bathurst and front, 3 legged intersection, delete this approach
78  "S" --private entrance at bloor and kingsway, delete this approach
68  "N" --this approach is north of steeles so outside of Toronto centreline.
1   "W" --bathurst and adelaide, 3 legged intersection, delete this approach.
*/
WITH mio_data AS (
    SELECT DISTINCT
        intersection_uid, leg
    FROM miovision_api.volumes_15min AS volumes
    WHERE
        volumes.datetime_bin >= '2023-01-01 00:00:00'::timestamp without time zone
        AND volumes.datetime_bin < '2024-01-01 00:00:00'::timestamp without time zone 
        AND (volumes.classification_uid <> ALL (ARRAY[2, 6, 10]))
)

SELECT mio_data.*, cm.centreline_id FROM mio_data
LEFT JOIN teps.centreline_miovision_20220705 cm
    ON mio_data.intersection_uid = cm.intersection_uid
    AND mio_data.leg = cm.leg
WHERE cm.centreline_id IS NULL