From fish passage model, extract code/logic that:

• finds intersection points
• removes duplicates within a set threshold (currently 10m) within the same source
• removes duplicates between sources, retaining crossing from the higher priority source
• labels bridges
• match output to existing output layer and transfer existing unique ids to new layer

This can probably all be rolled into a small number of sql files.
Note that DRA has changed, update to use transport_lines table

Currently bridges are identified via:

• double line stream crossings
• TRIM FCODE

Improve by adding additional bridge data sources such as:

• https://catalogue.data.gov.bc.ca/dataset/ministry-of-transportation-mot-road-structures
• https://catalogue.data.gov.bc.ca/dataset/railway-structure-line
• https://catalogue.data.gov.bc.ca/dataset/railway-structure-point

We could also use PSCIS assessments https://catalogue.data.gov.bc.ca/dataset/pscis-assessments, but that would probably be better taken from the output of https://github.com/smnorris/pscis (after the PSCIS crossings are indexed to streams)

It is probably also worth looking at a stream_order threshold for modelling open bottom structures. Currently for fish passage modelling in HORS,LNIC,BULK,ELKR watershed groups we use a threshold of stream order 5 or less for CBS (with one manually QA'ed exception) https://github.com/smnorris/cwf_fishpassage/blob/master/03_wsg_prioritize/sql/01_create_barriers_structures.sql#L95

Integer measures would be tidier but:

• +/- .5m isn't very much but it does look odd when reviewing the output compared to the road lines
• it looks like my math is off, some measures are off a road by almost a metre
• this was only applied for DRA lines

ften_road_segment_lines_svw or ften_road_section_lines_svw ?
They are almost the same but road section lines seems to be slightly more comprehensive - and this is what is used by cumulative effects in https://github.com/smnorris/roadintegrator/blob/master/sources.csv - this script should probably use these too.

I'm not sure this is necessary (attributes can be added in later steps) but these could all be added:

• stream name
• road name
• road type
• road surface
• road operator

Like this, ordering COALESCE inputs based on which source we prefer for each:

SELECT
 x.modelled_crossing_id,
 s.gnis_name as stream_name,
 COALESCE(dra.structured_name_1, ften.road_section_name, ogc.road_number||';'||ogc.proponent, ogc06.petrlm_development_road_name, rail.operator_english_name) as road_name,
 COALESCE(ften.file_type_description, dratype.description, ogc.road_type_desc, ogc06.petrlm_development_road_type, rail.transport_type) as road_type,
 drasurf.description as road_surface,
 COALESCE(ften.client_name, ogc.proponent, ogc06.proponent, operator_english_name) as road_operator
FROM fish_passage.modelled_stream_crossings x
INNER JOIN whse_basemapping.fwa_stream_networks_sp s 
ON x.linear_feature_id = s.linear_feature_id
LEFT OUTER JOIN whse_basemapping.transport_line dra
ON x.transport_line_id = dra.transport_line_id
LEFT OUTER JOIN whse_basemapping.transport_line_type_code dratype
ON dra.transport_line_type_code = dratype.transport_line_type_code
LEFT OUTER JOIN whse_basemapping.transport_line_surface_code drasurf
ON dra.transport_line_surface_code = drasurf.transport_line_surface_code
LEFT OUTER JOIN whse_forest_tenure.ften_road_section_lines_svw ften
ON x.ften_road_section_lines_id = ften.id
LEFT OUTER JOIN whse_mineral_tenure.og_road_segment_permit_sp ogc
ON x.og_road_segment_permit_id = ogc.og_road_segment_permit_id
LEFT OUTER JOIN whse_mineral_tenure.og_petrlm_dev_rds_pre06_pub_sp ogc06
ON x.og_petrlm_dev_rd_pre06_pub_id = ogc06.og_petrlm_dev_rd_pre06_pub_id
LEFT OUTER JOIN WHSE_BASEMAPPING.GBA_RAILWAY_TRACKS_SP rail
ON x.railway_track_id = rail.railway_track_id

In the existing model, when there are duplicate crossings (with 20m) FTEN crossings will take priority over DRA. This is simply because we want the tenure information for fish passage assessment planning/prioritization.

However, DRA data is going to be more positionally accurate and is now mostly comprehensive.
We should probably retain the DRA crossing geometry and extract attributes from the nearby FTEN road.

DRA features are timestamped. It should be possible create crossing records via a TRIGGER that is fired when a DRA record is inserted/modified. This removes the need to re-run the whole job every time there is a DRA update, and as a bonus, the existing crossing IDs won't be changed. Out of scope for now, and I'm not sure how this would handle deleting crossings when road features been removed/significantly shifted.

When identifying duplicates, ST_Cluster may work better in some circumstances than randomly picking a point from the identified duplicates. When combined with road type (eg divided highways), this could really improve some results.

Currently there is a circular reference - https://github.com/smnorris/pscis depends on modelled crossings existing to create pscis_events, while this repo depends on pscis_events to identify OBS.

Perhaps creation of barriers from PSCIS / modelled crossings / dams can be merged into one set of scripts, but for now separating the flagging of OBS modelled crossings with PSCIS would work.

DRA trails (transport_line_type_code IN ('T','TR','TD','TS'), currently 44,580km in BC) are not currently considered.

The majority of these features are in the northeast (related to oil and gas exploration), where there is unlikely to be a crossing structure.

However, there are many trails in other areas that do have associated crossing structures.

It might be worth keeping the trail crossings in the modelled_stream_crossings table, filtering them out depending on requirements.

When using the higher tolerances on highways/arterial roads, the point needs to stay on the same stream.