bdit_data-sources
bdit_data-sources copied to clipboard
CityofToronto
Fixing up `bluetooth.routes_temp` Spadina routes from Dundas to Bloor
Has incorrect analysis_id for:
- DT3_0019_Spadina-NB_College-to-Harbord is 1515251
- DT3_0020_Spadina-SB_Harbord-to-College is 1515280
Harbord to Bloor are drawn in but the routes don't exist.
College to Dundas is neither drawn in, nor do the routes exist
To update the analysis_id for Spadina to Harbord:
UPDATE bluetooth.routes_temp
SET
analysis_id = 1515280,
name = 'DT3_0020_Spadina-SB_Harbord-to-College'
WHERE name = 'Spadina-SB_Harbord-to-College';
UPDATE bluetooth.routes_temp
SET
analysis_id = 1515251,
name = 'DT3_0019_Spadina-NB_College-to-Harbord'
WHERE name = 'Spadina-NB_College-to-Harbord';
To add the Spadina from College to Dundas routes:
- not sure what to do about missing analysis_id
INSERT INTO bluetooth.routes_temp(
analysis_id, name, start_street_name, start_cross_street, start_reader_id,
end_street_name, end_cross_street, end_reader_id, date_active, date_inactive, geom,
date_last_received, direction, length
)
VALUES (
null, --analysis_id
'Spadina-SB_College-to-Dundas', --name
'Spadina', --start_street_name
'College', --start_cross_street
98, --start_reader_id,
'Spadina', --end_street_name
'Dundas', --end_cross_street
21, --end_reader_id
null::date, --date_active
null::date, --date_inactive
gis.text_to_centreline_geom('Spadina Ave', 'College St', 'Dundas St'), --geom,
null::date, --date_last_received
'Southbound', --direction
st_length(gis.text_to_centreline_geom('Spadina Ave', 'College St', 'Dundas St')::geography) --length
), (
null, --analysis_id
'Spadina-NB_Dundas-to-College', --name
'Spadina', --start_street_name
'Dundas', --start_cross_street
21, --start_reader_id,
'Spadina', --end_street_name
'College', --end_cross_street
98, --end_reader_id
null::date, --date_active
null::date, --date_inactive
gis.text_to_centreline_geom('Spadina Ave', 'College St', 'Dundas St'), --geom,
null::date, --date_last_received
'Northbound', --direction
st_length(gis.text_to_centreline_geom('Spadina Ave', 'College St', 'Dundas St')::geography) --length
);
Might be in here
bluetooth.all_analyses
Whoops, should have mentioned I looked there. No luck.
Similar to the Spadina/Harbord/College case above I identified 6 further routes where the current analysis_id did not match any row in all_analyses, but I identified other correct matches using levenshtein distance. Note that these all had the correct geom despite having the wrong analysis_id.
UPDATE bluetooth.routes_temp SET analysis_id = 1514850, name = 'DT3_0001_Bathurst-NB_Harbord-to-Bloor' WHERE analysis_id = 1600010 AND name = 'Bathurst-NB_Harbord-to-Bloor';
UPDATE bluetooth.routes_temp SET analysis_id = 1514879, name = 'DT3_0003_Bathurst-SB_Bloor-to-Harbord' WHERE analysis_id = 1600030 AND name = 'Bathurst-SB_Bloor-to-Harbord';
UPDATE bluetooth.routes_temp SET analysis_id = 1514906, name = 'DT3_0005_Bloor-EB_Runnymede-to-Highpark' WHERE analysis_id = 1600070 AND name = 'Bloor-EB_Runnymede-to-HighPark';
UPDATE bluetooth.routes_temp SET analysis_id = 1514986, name = 'DT3_0009_Bloor-WB_Highpark-to-Runnymede' WHERE analysis_id = 1600060 AND name = 'Bloor-WB_HighPark-to-Runnymede';
UPDATE bluetooth.routes_temp SET analysis_id = 1515123, name = 'DT3_0015_Queenspark-NB_Wellesley-to-Bloor' WHERE analysis_id = 1600250 AND name = 'Queens-NB_Wellesley-to-Bloor';
UPDATE bluetooth.routes_temp SET analysis_id = 1515145, name = 'DT3_0016_Queenspark-SB_Bloor-to-Hoskin' WHERE analysis_id = 1600220 AND name = 'Queens-SB_Bloor-to-Hoskin';
There are 30 others (38 minus the 2+6 that we're correcting above) that also have no match. They all have analysis_id >= 1600000:
SELECT * FROM bluetooth.routes_temp WHERE analysis_id >= 1600000
Here's a quick check I did on Gardiner/DVP segments. I see nothing out of the ordinary in their travel times over the years that would suggest a sensor was moved. They all have 85th percentile speeds around 80-100kph in all the years they were active, and all are active in 2024.
SELECT
name,
analysis_id,
date_part('year', datetime_bin),
length,
AVG(length/tt) * 3.6 AS avg_kph,
percentile_disc(0.15) within group (order by length/tt) * 3.6 AS perc_15_kph,
percentile_disc(0.85) within group (order by length/tt) * 3.6 AS perc_85_kph
FROM bluetooth.routes_temp
LEFT JOIN bluetooth.aggr_5min USING (analysis_id)
WHERE start_street_name IN ('Gardiner', 'DVP') AND tt > 0
GROUP BY 1,2,3,4
ORDER BY 1,2,3,4
@radumas can you check this for the Spadina routes? "\Vs-170-gra09\cotgra09\WES\tra\GM Office\Big Data Group\Data Sources\Bluetooth\Bliptrack\Bliptrack TT Routes 2024-01-18.xlsx"
To add the Spadina from College to Dundas routes:
- not sure what to do about missing analysis_id
INSERT INTO bluetooth.routes_temp( analysis_id, name, start_street_name, start_cross_street, start_reader_id, end_street_name, end_cross_street, end_reader_id, date_active, date_inactive, geom, date_last_received, direction, length ) VALUES ( null, --analysis_id 'Spadina-SB_College-to-Dundas', --name 'Spadina', --start_street_name 'College', --start_cross_street 98, --start_reader_id, 'Spadina', --end_street_name 'Dundas', --end_cross_street 21, --end_reader_id null::date, --date_active null::date, --date_inactive gis.text_to_centreline_geom('Spadina Ave', 'College St', 'Dundas St'), --geom, null::date, --date_last_received 'Southbound', --direction st_length(gis.text_to_centreline_geom('Spadina Ave', 'College St', 'Dundas St')::geography) --length ), ( null, --analysis_id 'Spadina-NB_Dundas-to-College', --name 'Spadina', --start_street_name 'Dundas', --start_cross_street 21, --start_reader_id, 'Spadina', --end_street_name 'College', --end_cross_street 98, --end_reader_id null::date, --date_active null::date, --date_inactive gis.text_to_centreline_geom('Spadina Ave', 'College St', 'Dundas St'), --geom, null::date, --date_last_received 'Northbound', --direction st_length(gis.text_to_centreline_geom('Spadina Ave', 'College St', 'Dundas St')::geography) --length );
Ran the query with new centreline routing function using latest centreline table + directional geom. I think we can keep the analysis_id as null for meow, will change in bliptrack in a bit
INSERT INTO bluetooth.routes_temp(
analysis_id, name, start_street_name, start_cross_street, start_reader_id,
end_street_name, end_cross_street, end_reader_id, date_active, date_inactive, geom,
date_last_received, direction, length
)
VALUES (
null, --analysis_id
'Spadina-SB_College-to-Dundas', --name
'Spadina', --start_street_name
'College', --start_cross_street
98, --start_reader_id,
'Spadina', --end_street_name
'Dundas', --end_cross_street
21, --end_reader_id
null::date, --date_active
null::date, --date_inactive
(select geom from gis_core.get_centreline_btwn_intersections(13465489, 13466288)), --geom,
null::date, --date_last_received
'Southbound', --direction
(select round(ST_length(ST_Transform(geom, 2952)))
from gis_core.get_centreline_btwn_intersections(13465489, 13466288)) --length
), (
null, --analysis_id
'Spadina-NB_Dundas-to-College', --name
'Spadina', --start_street_name
'Dundas', --start_cross_street
21, --start_reader_id,
'Spadina', --end_street_name
'College', --end_cross_street
98, --end_reader_id
null::date, --date_active
null::date, --date_inactive
(select geom from gis_core.get_centreline_btwn_intersections(13466288, 13465489)), --geom,
null::date, --date_last_received
'Northbound', --direction
(select round(ST_length(ST_Transform(geom, 2952)))
from gis_core.get_centreline_btwn_intersections(13466288, 13465489)) --length
);
Thanks @gabrielwol !!!
Updated the following in bluetooth.route_temp)
analysis_id = 1515280, name = 'DT3_0020_Spadina-SB_Harbord-to-College'
analysis_id = 1515251, name = 'DT3_0019_Spadina-NB_College-to-Harbord'
analysis_id = 1514850, name = 'DT3_0001_Bathurst-NB_Harbord-to-Bloor'
analysis_id = 1514879, name = 'DT3_0003_Bathurst-SB_Bloor-to-Harbord'
analysis_id = 1514906, name = 'DT3_0005_Bloor-EB_Runnymede-to-Highpark'
analysis_id = 1514986, name = 'DT3_0009_Bloor-WB_Highpark-to-Runnymede'
analysis_id = 1515123, name = 'DT3_0015_Queenspark-NB_Wellesley-to-Bloor'
analysis_id = 1515145, name = 'DT3_0016_Queenspark-SB_Bloor-to-Hoskin'
Inserted these two without analysis_id
'Spadina-SB_College-to-Dundas'
'Spadina-NB_Dundas-to-College'
Now we have these 30 routes with incorrect analysis_id that doesn't exist in bluetooth.all_analyses
1600330 "Wellesley-EB_Jarvis-to-Parliament"
1600000 "Bathurst-NB_College-to-Harbord"
1600190 "Ossington-NB_College-to-Bloor"
1600210 "Ossington-SB_Bloor-to-College"
1600150 "Harbord-EB_Bathurst-to-Spadina"
1600170 "Harbord-EB_Spadina-to-Queens"
1600260 "Roncesvalles-NB_Dundas-to-Bloor"
1600180 "Ossington-NB_Dundas-to-College"
1600230 "Queens-NB_College-to-Wellesley"
1600160 "Harbord-WB_Spadina-to-Bathurst"
1600240 "Queens-SB_Hoskin-to-College"
1600120 "Dufferin-NB_Dundas-to-College"
1600350 "Wellesley-WB_Yonge-to-Queens"
1600370 "Wellesley-EB_Queens-to-Yonge"
1600080 "College-WB_Ossington-to-Dufferin"
1600340 "Wellesley-WB_Parliament-to-Jarvis"
1600090 "College-EB_Dufferin-to-Ossington"
1600320 "Wellesley-WB_Jarvis-to-Yonge"
1600360 "Wellesley-EB_Yonge-to-Jarvis"
1600100 "Dufferin-SB_College-to-Dundas"
1600130 "Dufferin-SB_Bloor-to-College"
1600110 "Dufferin-NB_College-to-Bloor"
1600050 "Bloor-EB_HighPark-to-Dundas"
1600140 "Harbord-WB_Queens-to-Spadina"
1600200 "Ossington-SB_College-to-Dundas"
1600020 "Bathurst-SB_Harbord-to-College"
1600310 "Spadina-NB_Harbord-to-Bloor"
1600280 "Spadina-SB_Bloor-to-Harbord"
1600040 "Bloor-WB_Dundas-to-HighPark"
1600270 "Roncesvalles-SB_Bloor-to-Dundas"