Open chmnata opened 1 year ago
Might add identifying segments with low/no data and doing a little investigation per https://github.com/Toronto-Big-Data-Innovation-Team/rapidto/pull/38#issuecomment-1284227182
I think two of these tables are names wrong:
congestion.centreline_segments
is congestion.segment_centreline
congestion.centreline_segments_name
is congestion.segment_centreline_name
There are concerns that some meaningful segments of streets may be missing due to road classification.
Issues found from manual validation of congestion.network_segments
so far:
Bridges don't seem to be accounted for; segments are broken where they cross other segments they don't actually intersect with due to vertical separation. E.g. Prince Edward Viaduct over Bayview Ave, DVP - lots of other cases too though.
id 387
: Vaughan is two-way here, but there's no paired edge going the other way
ids: 3570
, 3571
, 3572
, 164
, 165
, 342
: Intersection of Park Lawn and Lake Shore West needs to be de-duped. There are two SW-bound edges, only one of which connects to Park Lawn.
Missing connection at Dundas / Bloor / Kipling
ids 166
, 3569
: stub segment
ids: 1692
, 4885
, 4888
: Wellington Street stub with erroneous 2-way:
Missing section of Morningside at Finch:
Should I be looking at congestion.segments_v6
instead of congestion.network_segments
? segments_v6
seems to correct some of the problems I described above.
not all highways are marked highway
I found a few issues with congestion.segment_centreline_lookup
table.
The table contains incorrect start_vid
or/and end_vid
for the segments that intersection names of their end points are missing (NULL) in gis.centreline_intersection
table.
It seems int_ids of the closets intersections with available intersections name have been assigned to these segments.
Please see the following example from the CafeTO project for more clarifications and details.
I used the following sql to get intersection names of the segments within the study area (cafe_to.“TEST_st_clair_west"
) to create lookup table with intersection names.
• Joined cafe_to.“TEST_st_clair_west"
table and congestion.segment_centreline_lookup
using segment_id
to get start_int
and end_int
ids
• Then joined gis.centreline_intersection
using int_ids to get intersection names
As it can be seen in the output table, some segments such 1172 and 1173 have incorrect vids. Their start_vid and or end_vid represent segment sets rather than individual segment.
with start_node as
(select *
from cafe_to."TEST_st_clair_west"
left join congestion.segment_centreline_lookup b
using (segment_id)
left join gis.centreline_intersection d
on (b.start_int= d.int_id)
)
select distinct
segment_id,
start_vid,
end_vid,
start_int,
end_int,
start_node.intersec5 as start_int,
e.intersec5 as end_int,
segment_list,
dir,
segment_geom
from start_node
left join
gis.centreline_intersection e
on (end_int=e.int_id),
lateral congestion.get_segments_btwn_nodes(start_vid::int,end_vid::int)
results(start_point, end_point,segment_list,length,segment_geom)
order by segment_list
I noticed some missing segments in the congestion network while I was creating study areas for the CafeTO project. Segments are broken/missing when a road class changes resulting in disconnected corridors. Please see the following example in the Downtown East study area:
Study Area:
Missing Segments:
there is no segment available in congestion.network_segments
along Jones and Greenwood between Dundas and Queen.
Listed layers that needs to be validated and suggest validation methods.
Network Segments
congestion.network_segments
Layer of the congestion network segments
[ ] Check for length, see if there are super short or super long segments and if they are valid
[ ] Check for
link_dir
that makes up a segment, are there any cases of duplicated link_dir?[ ] Is the length correct? Summing up link_dir's length to double check
congestion.network_links_22_2
(the lookup table)[ ] Is the highway label correct? Are the segments labeled as highway actually highways? (can check with centreline)
[x] Are there any segments with no direction tagged?
Network Nodes
congestion.network_nodes
Layer contains nodes that make up each network segment
[ ] If all the nodes are used in the network segment table
[ ] Are there duplicate nodes?
Centreline int_id and HERE node matching
network_int_px_22_2
Centreline segments
congestion.centreline_segments
Layer contains sets of geo_ids that represents each segments.
[ ] Compare the length of the segments and the length of the centreline, there is a lookup table created in
congestion.segment_centreline_lookup
that has segment set length and centreline set length for comparison[ ] If segment_ids were used more than once incorrectly
Centreline segments
congestion.centreline_segments_name
Layer contains sets of geo_ids that represents each segments + Street name
[ ] Are the names correct?? There are arrays of street_name, if its more than 2 its kinda weird and should be investigated
[ ] any null name ?