bdit_data-sources
bdit_data-sources copied to clipboard
CityofToronto
Create New Bluetooth Routes for Downtown/Eastern Expansion
41 new readers were created to monitor travel times on the Gardiner while bridge rehab occurring.
I created a script that will look for every possible valid detector combination, so the route creation process is semi-automated.
WITH test AS (
SELECT * FROM cnangini.detectors
WHERE project = 'King Pilot'
), dist_ew AS (
SELECT
a.detector_name as a_name,
a.street1 as a_street1,
a.street2 as a_street2,
a.bluetooth_id as a_id,
b.detector_name as b_name,
b.street1 as b_street1,
b.street2 as b_street2,
b.bluetooth_id as b_id,
ST_distance(a.loc, b.loc) as dist_ew,
CASE WHEN
ST_Azimuth(a.loc, b.loc)-3.14159 > 0 THEN 'W' ELSE 'E' END as az_ew
FROM test a
LEFT JOIN test b USING (street1)
WHERE ST_distance(a.loc, b.loc) <>0
), min_ew AS (
SELECT min(dist_ew) AS dist_ew, a_id, az_ew FROM dist_ew
GROUP BY a_id, az_ew
ORDER BY a_id
), dist_ns AS (
SELECT
a.detector_name as a_name,
a.street1 as a_street1,
a.street2 as a_street2,
a.bluetooth_id as a_id,
b.detector_name as b_name,
b.street1 as b_street1,
b.street2 as b_street2,
b.bluetooth_id as b_id,
ST_distance(a.loc, b.loc) as dist_ns,
CASE WHEN
ST_Azimuth(a.loc, b.loc)-3.14159 > 0 THEN 'N' ELSE 'S' END as az_ns
FROM test a
LEFT JOIN test b USING (street2)
WHERE ST_distance(a.loc, b.loc) <>0
), min_ns AS (
SELECT min(dist_ns) AS dist_ns, a_id, az_ns FROM dist_ns
GROUP BY a_id, az_ns
ORDER BY a_id
)
SELECT a_name, a_street1, a_street2, a_id, b_name, b_street1, b_street2, b_id, az_ew
FROM dist_ew
INNER JOIN min_ew USING (dist_ew, a_id, az_ew)
UNION
SELECT a_name, a_street1, a_street2, a_id, b_name, b_street1, b_street2, b_id, az_ns
FROM dist_ns
INNER JOIN min_ns USING (dist_ns, a_id, az_ns)
The query self-joins the reader list onto itself using common N/S or E/W streets, then classifies the joined reader's if it's N,S,E,W of the original reader depending on the azimuth. Then, it chooses the closest joined reader to the original reader to create a route
Is the Dundas/Roncesvalles reader operating? Its in the postgres tables/excel, but on google maps, its been marked as "moved".
So according to #105, that reader is not in operation, but its still in bluetooth.detectors, cnangini.detectors and the latest version of the spreadsheet.
Yeah you're correct - that reader is no longer at that location. I'll updated the spreadsheet
@KatiRG I made this change and have saved it in a new spreadsheet dated as 2019-07-11. Good catch @rickl4
Should I connect the reader at Lakeshore/Don Roadway to DVP or Gardiner Readers?
The previous detectors table has now been replaced by the new spreadsheet 2019-07-11 made by @aharpalaniTO. Detector DU_RO has date_end = 2018-08-31.
@aharpalaniTO Routes are created in the spreadsheet. Just need the location to Broadview/Dundas, and whether to connect Lakeshore/Don Roadway to the DVP.
Also added a segment connecting Wellesley to Hoskin.
changing Sheppard and Meadowville to Sheppard and Meadowvale
changing "Heron`s Hill" to "Heron's Hill"