2024-01-30 Add new Miovision intersections

[gabrielwol]

Add new Miovision intersections between August 2023 - January 2024.

[gabrielwol]

Intersection_uid's 69-95 added and volumes backfilled on 2024-01-30.

A further 10 new intersections had no volumes for any date up to and including 2024-01-30. They were moved to gwolofs.intersections_backup since they were interfering with the miovision_pull process (null date_installed). We will need to follow up with Miovision about when these will be activated.

"intersection_uid"	"id"	"intersection_name"
96	"125a7852-3068-46a4-af0f-0d7e9c162643"	"Dupont St / Spadina Rd"
97	"15524515-c5ab-4e02-b99b-52611c3fed9d"	"Pearl St / University Ave"
98	"22b92671-68d2-4e00-b6f3-8a1cadd88758"	"Dupont St / Bathurst St"
99	"280dd51b-07ea-45fb-b17b-01eef0b5d1ce"	"Burnhamthorpe Rd / Mill Rd"
100	"784d9550-6ad6-4aed-a849-9392473c0d05"	"Dupont St / Avenue Rd"
101	"89942357-cb8d-417b-a6ea-0908cba00603"	"Harbord St / Bathurst St"
102	"a6dd2466-0817-480d-9d1c-bedec16692e7"	"Bloor St W / Bathurst St"
103	"aa72b991-a771-4a69-ad5d-743c0ae1026e"	"Dundas St W / Bathurst St"
104	"c259139e-a19e-42d5-9327-791693c1000e"	"Lake Shore Blvd W / Bathurst St"
105	"ed43645a-13b3-49e2-9820-0722c276d972"	"Brickworks / Bayview Ave"

[gabrielwol]

Need an admin to add to intersection_movements for these new intersections:

	WITH counts AS (
		SELECT DISTINCT
			intersection_uid,
			classification_uid,
			leg,
			movement_uid,
			COUNT(DISTINCT datetime_bin::time) AS bins,
			SUM(volume) AS volume,
			SUM(SUM(volume)) OVER w AS classification_volume
		FROM miovision_api.volumes
		WHERE
			intersection_uid >= 69 --only include new intersection_uid
			--AND datetime_bin > 'now'::text::date - interval '10 days' -- or the date of data that you pulled
			AND classification_uid IN (1,2,6,10) --will include other modes after this
		GROUP BY intersection_uid, classification_uid, leg, movement_uid
		WINDOW w AS (PARTITION BY intersection_uid, classification_uid)
	)

	INSERT INTO miovision_api.intersection_movements (intersection_uid, classification_uid, leg, movement_uid)
	SELECT
		intersection_uid,
		classification_uid,
		leg,
		movement_uid
	FROM counts
	WHERE
		--bins >= 20 OR --this filter may be irrelevant if using many days of data.
        volume::numeric / classification_volume >= 0.005;

and;

WITH wanted_veh(classification_uid) AS (
				VALUES (3), (4), (5), (8), (9)
	)
	INSERT INTO miovision_api.intersection_movements
		(intersection_uid, classification_uid, leg, movement_uid)
	SELECT
		a.intersection_uid,
		b.classification_uid,
		a.leg,
		a.movement_uid
	FROM miovision_api.intersection_movements AS a
	CROSS JOIN wanted_veh AS b
	-- Specify which intersection_uids to use.
	WHERE
		a.intersection_uid >= 69
		AND a.classification_uid = 1
	ORDER BY 1, 2, 3, 4

[chmnata]

Need an admin to add to intersection_movements for these new intersections:

	WITH counts AS (
		SELECT DISTINCT
			intersection_uid,
			classification_uid,
			leg,
			movement_uid,
			COUNT(DISTINCT datetime_bin::time) AS bins,
			SUM(volume) AS volume,
			SUM(SUM(volume)) OVER w AS classification_volume
		FROM miovision_api.volumes
		WHERE
			intersection_uid >= 69 --only include new intersection_uid
			--AND datetime_bin > 'now'::text::date - interval '10 days' -- or the date of data that you pulled
			AND classification_uid IN (1,2,6,10) --will include other modes after this
		GROUP BY intersection_uid, classification_uid, leg, movement_uid
		WINDOW w AS (PARTITION BY intersection_uid, classification_uid)
	)

	INSERT INTO miovision_api.intersection_movements (intersection_uid, classification_uid, leg, movement_uid)
	SELECT
		intersection_uid,
		classification_uid,
		leg,
		movement_uid
	FROM counts
	WHERE
		--bins >= 20 OR --this filter may be irrelevant if using many days of data.
        volume::numeric / classification_volume >= 0.005;

and;

WITH wanted_veh(classification_uid) AS (
				VALUES (3), (4), (5), (8), (9)
	)
	INSERT INTO miovision_api.intersection_movements
		(intersection_uid, classification_uid, leg, movement_uid)
	SELECT
		a.intersection_uid,
		b.classification_uid,
		a.leg,
		a.movement_uid
	FROM miovision_api.intersection_movements AS a
	CROSS JOIN wanted_veh AS b
	-- Specify which intersection_uids to use.
	WHERE
		a.intersection_uid >= 69
		AND a.classification_uid = 1
	ORDER BY 1, 2, 3, 4

done ✔️

[gabrielwol]

None of the new intersections have volumes for classification_uid 10 (bike approaches). We'll need to confirm whether this is an algorithm change or a configuration issue.

4 of the new intersection are missing any volumes for classification_uid 6 (pedestrians): intersection_uids IN (69, 71, 72, 90)

[gabrielwol]

Also noting that Lake Shore W and Spadina (intersection_uid 91) appears to be configured incorrectly. The majority of movements are Northbound (from Queen's Quay), which doesn't pass a reality check. I'm guessing the main movement should be EB along Lake Shore. Recorded this in the anomalous_ranges table to be passed on to Miovision eventually.