crossings table data model - road details (for estimating replacement cost/responsibility)

[NewGraphEnvironment]

I have had a quick look around and can't seem to locate where the dra_road_name and ften_client_name are added to bcfishpass.crossings . Nevertheless, I wonder if we might be able to select and insert some more dra and ften columns to bcfishpass.crossings that I have been using for getting rough cost estimates and obtaining a decent idea about who is responsible for the road? I had previously been pulling them out of fish_passage.modelled_crossings_closed_bottom and processing using 0280-extract-bcfishpass.R, 0330-tables-phase1-cost-estimate.R and 0340-tables-phase2-cost-estimate.R

Columns I have been using that are not in bcfishpass.crossings are:

• client_namefrom whse_forest_tenure.ften_road_segment_lines_svw
• road_surface from something similar to whse_basemapping.dra_dgtl_road_atlas_mpar_sp
• road_class from something similar to whse_basemapping.dra_dgtl_road_atlas_mpar_sp

The latest version of the table I have been using for generating cost estimates looks like this:

Additionally, something I have been doing is looking at which non forestry roads might be the responsibility of a municipality rather than MoTi. I currently do this by adding admin_area_abbreviation from whse_legal_admin_boundaries.abms_municipalities_sp then adding a column so that anything is.na(admin_area_abbreviation) ~ 'MoTi'. This is overridden if the road_class is a highway or collector or if there is a forest_file_id from whse_forest_tenure.ften_road_segment_lines_svw. For the Bulkley, none of my target crossings have a admin_area_abbreviation so not sure this should be a priority at all but thought I would put it out there in case someone else is trying to determine who's roads these are.

[smnorris]

The road data massaging is here: https://github.com/smnorris/bcfishpass/blob/main/02_model/sql/crossings.sql

DRA road class is in there, it is the transport_line_type_code. This matches the 'current' DRA data model as far as I understand it. Unfortunately the province still hasn't updated their BCGW data model so what you pull down via WFS (via both bcdata packages) doesn't match what they also posting via direct download in the Catalogue https://catalogue.data.gov.bc.ca/dataset/digital-road-atlas-dra-master-partially-attributed-roads.

• easy to add road surface if you're using it
• adding the municipality is a good idea
• I could also add a private land indicator, it may not help with finding ownership but could be interesting

I did not attempt to join to the MOT RFI data as we did for the Bulkley reporting because:

• I'm not familiar with the limitations of that data
• it isn't one of the sources for modelled crossings

I was thinking a separate road report would still be good but maybe just adding a couple of mot_rfi columns would be fine, I'd just have to make it clear that it is for the 'nearest MoT road within x metres' not attempting to say that the road is or is not MoT.

[smnorris]

Hmm, no I didn't add transport_line_type_code - only the interpretation of that used for CWF planning. I'll add that too, and use the actual value rather than the cryptic codes.

[smnorris]

If I'm adding more columns I'll remove wcrp_barrier_type_detailed column and just keep the basic one CWF requested for the plans.

So right now I'll add:

• transport_line_type_description
• transport_line_surface_description
• ften_file_type_description (FSR vs Road Permit)
• municipality
• private land indicator (via BC Parcel map)

This is still a bit less info than the old modelled crossings table. Maybe down the line we can add some MoT RFI data if we think that is valuable. Are the UTM coordinates actually useful?

Old modelled crossings columns:

 digital_road_atlas_line_id    | integer              |           |          |
 highway_route_number          | character varying    |           |          |
 road_class                    | character varying    |           |          |
 road_name_full                | character varying    |           |          |
 road_surface                  | character varying    |           |          |
 trim_transportation_lines_id  | integer              |           |          |
 ften_road_segment_line_id     | integer              |           |          |
 forest_file_id                | character varying    |           |          |
 road_section_id               | character varying    |           |          |
 road_responsibility_type_code | character varying    |           |          |
 retirement_date               | date                 |           |          |
 file_status_code              | character varying    |           |          |
 file_type_code                | character varying    |           |          |
 file_type_description         | character varying    |           |          |
 client_number                 | character varying    |           |          |
 client_name                   | character varying    |           |          |
 life_cycle_status_code        | character varying    |           |          |
 map_label                     | character varying    |           |          |
 og_road_segment_permit_id     | integer              |           |          |
 og_petrlm_dev_rd_pre06_pub_id | integer              |           |          |
 track_segment_id              | character varying    |           |          |
 track_name                    | character varying    |           |          |
 track_classification          | character varying    |           |          |
 use_type                      | character varying    |           |          |
 gauge                         | character varying    |           |          |
 status                        | character varying    |           |          |
 operator_english_name         | character varying    |           |          |
 owner_name                    | character varying    |           |          |
 fcode                         | text                 |           |          |
 fcode_label                   | text                 |           |          |
 utm_zone                      | integer              |           |          |
 utm_easting                   | double precision     |           |          |
 utm_northing                  | double precision     |           |          |
 linear_feature_id             | bigint               |           |          |
 blue_line_key                 | integer              |           |          |
 edge_type                     | integer              |           |          |
 downstream_route_measure      | double precision     |           |          |
 fwa_fcode                     | text                 |           |          |
 fwa_fcode_label               | text                 |           |          |
 fwa_watershed_code            | text                 |           |          |
 local_watershed_code          | text                 |           |          |
 watershed_group_code          | text                 |           |          |
 wscode_ltree                  | ltree                |           |          |
 localcode_ltree               | ltree                |           |          |
 geom                          | geometry(Point,3005) |           |          |
 crossing_id                   | integer              |           | not null |
 downstream_event_id           | bigint               |           |          |
 fish_habitat                  | text                 |           |          |

[NewGraphEnvironment]

I end up using utms for things every once and a while.

I am hoping we will have client_name there. that will detail the road licensee name (ex. Canfor) sometimes.

[smnorris]

ften_client_name is in there now. I'm trying to prefix the column names with where the info is coming from.

[NewGraphEnvironment]

Thanks!!

[smnorris]

I haven't actually added all these yet, keep the issue open for now...

[smnorris]

Adding a few more - regional districts, parks, IRs, NR regions, a bit more rail info.

It is a bit redundant keeping all this in the crossings table when a simple point in poly query is easy enough but will be very handy for common reports.

CREATE TABLE bcfishpass.crossings
(
    aggregated_crossings_id integer PRIMARY KEY GENERATED ALWAYS AS
       (COALESCE(COALESCE(stream_crossing_id, modelled_crossing_id + 1000000000), dam_id + 1000000000)) STORED,
    stream_crossing_id integer UNIQUE,
    dam_id integer UNIQUE,
    modelled_crossing_id integer UNIQUE,
    crossing_source text,                 -- pscis/dam/model, can be inferred from above ids

    -- basic crossing status/info - use PSCIS where available, insert model info where no PSCIS
    pscis_status text,                    -- ASSESSED/HABITAT CONFIRMATION etc
    crossing_type_code text,              -- PSCIS crossing_type_code where available, model CBS/OBS otherwise
    crossing_subtype_code text,           -- PSCIS crossing_subtype_code info (BRIDGE, FORD, ROUND etc) (NULL for modelled crossings)
    modelled_crossing_type_source text[], -- for modelled crossings, what data source(s) indicate that a modelled crossing is OBS
    barrier_status text,                  -- PSCIS barrier status if available, otherwise 'POTENTIAL' for modelled CBS, 'PASSABLE' for modelled OBS
    pscis_road_name text,

    -- DRA info
    transport_line_structured_name_1 text,
    transport_line_type_description text,
    transport_line_surface_description text,

    -- forest road tenure info
    ften_forest_file_id text,
    ften_file_type_description text,
    ften_client_number text,
    ften_client_name text,
    ften_life_cycle_status_code text,
    -- TODO insert ogc tenure data here (ogc roads are not a priority at the moment)

    -- rail info 
    rail_track_name text,
    rail_owner_name text,
    rail_operator_name text,

    -- dam info
    dam_name text,
    dam_owner text,

    -- CWF WCRP specific type (rail/road/trail/dam)
    wcrp_barrier_type text,      

    -- other admin info
    abms_regional_district text,
    abms_municipality text,
    clab_indian_reserve text,
    clab_national_park text,
    tantalis_park_ecores_pa text,
    pmbc_private text,
    adm_nr_region text,
    adm_nr_district text,

    -- FWA info
    linear_feature_id integer,
    blue_line_key integer,
    downstream_route_measure double precision,
    wscode_ltree ltree,
    localcode_ltree ltree,
    watershed_group_code text,
    geom geometry(Point, 3005),
    -- add a unique constraint on linear location
    -- (so that we don't have points in the same spot messing up subsequent joins)
    UNIQUE (blue_line_key, downstream_route_measure)
);

[NewGraphEnvironment]

How's your crossing.sql query!!! OMG! Epic! You are so far advanced it makes my head spin. FYI.... Looks like the ften_xxxxxxx attributes from whse_forest_tenure.ften_road_section_lines_svw are not coming through into bcfishpass.crossings (same story on my system as bcbarriers. Could be one of a million case_whens going sideways I guess or potentially as you noted "-- note the id supplied by WFS is the link, may be unstable?". Interestingly the output you burned to ali.crossings on the server has the info so IDK.

[smnorris]

Are you running locally or is this on an existing db that the ften attributes are missing? read comment more closely - I'll look into this on bcbarriers db.

[NewGraphEnvironment]

No rush for me as I should be fine to get the info I need for reporting.

[NewGraphEnvironment]

right. Thinking about it - I think this will create a pile of complexity (in my already unruley scripts) over the next couple months. Let me know when Elk, bulk and morr are updated. I'll use ali.crossings to get these PSCIS input spreadsheets updated for now

[NewGraphEnvironment]

Hmmm. My build was after this last commit so I wonder if that means that it is something other than you forgot to run crossings_admin.sql ...

[smnorris]

sorry, crossings admin is unrelated. Not sure why ften attributes are not coming through.