Use the former municipality element of the "highway" field

[radumas]

When a street name is duplicated or triplicated across the city due to amalgamation, there will be a two-character code in the highway field to specify which former municipality is referenced, this should be used to ensure the correct intersections are getting matched. These are currently being deleted with regexp_replace(). Example below of one of the issues.

For bylaw_id = 6645 where highway = "McGillivray Ave" and between = "The west end of McGillivray Ave and Kelso Ave", it's routed as shown below and that is not right. (highlighted is the road segment we want)

This happen because there are two 'McGillivray Ave' in Toronto (highlighted in yellow and maroon above) and they both are cul de sac as shown below. int_id 13452712 is the right one but 13453328 is the one return from the function gis._get_intersection_id

Originally posted by @jovenc in https://github.com/CityofToronto/bdit_data-sources/issues/271#issuecomment-594176750

[radumas]

Example of the regular expression to catch these https://github.com/CityofToronto/bdit_data-sources/blob/text_to_centreline/gis/bylaw_text_to_centreline/sql/create-function-clean_bylaws_text2.sql#L37

[radumas]

To get all the highway with the two character code

SELECT id, city, deleted, bylaw_no, chapter, schedule, schedule_name, highway, "between", speed_limit_km_per_h
	FROM jchew.bylaws_2020
	WHERE highway ~ '.*\(..\).*'

There are 162 bylaws with this in the highway column, but I suspect not many of them lead to duplicate intersections, or other problematic formulations per the above example.

[jovenc]

For example,