Many `centreline` intersections are not connected to `centreline` lines

[Nate-Wessel]

My understanding

The table gis_core.centreline should contain all the centreline lines The table gis_core.centreline_intersection_point should contain all the intersections of those lines. (Or does gis_core.intersection contain the intersections?? I can't tell the diference between these tables)

A query like this should give me the complete, current centreline dataset:

SELECT
    centreline_id,
    geom -- linestrings
FROM gis_core.centreline
WHERE version_date = '2024-01-01'

UNION

SELECT
    intersection_id AS centreline_id,
    geom -- points
FROM gis_core.intersection
WHERE version_date = '2024-01-01'

However

The intersections table (either of them) includes many hundreds of intersection points not connected at all to the lines. It seems many of these are intersections of footpaths, railroads, etc. I know some of that is ostensibly in the centreline data. But the centreline line table doesn't contain it?

(a map of the results from the query above)

What I expected

I would expect the intersections to consistently intersect with the lines. Either we're missing lines or we have too many intersections. Or the documentation doesn't specify what is otherwise going on here.

[radumas]

Aye this documentation is a WIP. Apologies for the confusion.

The pipeline is filtering out centrelines which are not streets before inserting them into the table.

https://github.com/CityofToronto/bdit_data-sources/blob/5d4035e4d8ac30963d06ecc9eae4219ff78210a2/gis/gccview/gcc_puller_functions.py#L319

Unfortunately the challenge with the intersection layers is that there is no set of classifications that encompass “this is an intersection of two streets”. But we should make a transformation of the intersections which contains only street intersections to make life easier for us.

[Nate-Wessel]

It looks like the locations are precise enough to do a simple spatial join, though it leaves a lot of midblock "intersections".

SELECT
    centreline_id,
    geom -- linestrings
FROM gis_core.centreline
WHERE version_date = (SELECT MAX(version_date) FROM gis_core.centreline)

UNION

SELECT
    intersection_id AS centreline_id,
    intersection.geom -- points
FROM gis_core.intersection
JOIN gis_core.centreline ON ST_Intersects(
    intersection.geom,
    centreline.geom
)
WHERE
    intersection.version_date = (SELECT MAX(version_date) FROM gis_core.intersection)
    AND centreline.version_date = (SELECT MAX(version_date) FROM gis_core.centreline)

Could count the number of intersecting (centre)lines to identify these though.

[radumas]

There's some examination of this the last time someone tried updating the HIN https://github.com/CityofToronto/bdit_data-sources/issues/466

[radumas]

intersection comes from the Major Capital Infrastructure Coordination Office with description "Intersections with cross street". There appears to be one row to describe the relationship of each pair of centrelines intersecting.

Centreline Intersection Point (centreline_intersection_point ) is maintained by the Geospatial Competency Centre with description "Core layers"

http://insideto.toronto.ca/itweb/support/geospatial/MapServiceCatalogue/index.html

[chmnata]

Seems like centreline_intersection_point has more points.

-- returns 7753
select intersection_id from gis_core.centreline_intersection_point 
where version_Date = '2024-02-29' 
except
select intersection_id from gis_core.intersection 
where version_Date = '2024-02-29' 

Looking at the ones that are not in intersection, they are cul de sacs, overpass, underpass

with intersection_missing AS (
select intersection_id from gis_core.centreline_intersection_point 
where version_Date = '2024-02-29' 
except
select intersection_id from gis_core.intersection 
where version_Date = '2024-02-29' )
select * from gis_core.centreline_intersection_point 
inner join intersection_missing using (intersection_id)

-- returns 197
select intersection_id from gis_core.intersection 
where version_Date = '2024-02-29' 
except
select intersection_id from gis_core.centreline_intersection_point 
where version_Date = '2024-02-29' 

Checking the distinct linear_name_full_from for ones missing in centreline_intersection_point, they are all trails, ferry routes, or geostatistical line.

with intersection_missing AS (
select intersection_id from gis_core.intersection 
where version_Date = '2024-02-29' 
except
select intersection_id from gis_core.centreline_intersection_point 
where version_Date = '2024-02-29' )
select distinct linear_name_full_from from gis_core.intersection 
inner join intersection_missing using (intersection_id)

[gabrielwol]

This issue was solved as part of #885 in gis_core.centreline_latest and gis_core.intersection_latest: https://github.com/CityofToronto/bdit_data-sources/blob/a827eda26d11886be131772c353079bf51930950/gis/centreline/sql/create_matview_centreline_intersection_point_latest.sql#L5-L12