wcrp crossings view updates

[smnorris]

prune included columns down to:

aggregated_crossings_id
modelled_crossin_id
crossing_source
crossing_feature_type
pscis_status
crossing_type_code
crossing_subtype_code
barrier_status
pscis_road_name
pscis_stream_name
pscis_assessment_comment
pscis_assessment_date
transportation_line_structured_name_1
rail_track_name
dam_name
dam_height
dam_owner
dam_use
dam_operating_status
utm_zone
utm_easting
utm_northing
blue_line_key
watershed_group_code
gnis_stream_name
barriers_anthropogenic_dnstr
barriers_anthropogenic_dnstr_count
barriers_anthropogenic_ch_cm_co_pk_sk_upstr
barriers_anthropogenic_ch_cm_co_pk_sk_upstr_count
<spp>_<hab type>_km fields
<spp>_hab type_belowupstrbarriers_km fields
all_spawning_km
all_spawning_belowupstrbarriers_km
all_rearing_km
all_rearing_belowupstrbarriers_km
all_spawningrearing_km
all_spawningrearing_belowupstrbarriers_km

Updates to existing fields:

• calculate the habitat values for passable structures as well
• Update fields barriers_anthropogenic_ch_cm_co_pk_sk_upstr, barriers_anthropogenic_ch_cm_co_pk_sk_upstr_count to only count anthropogenic barriers that have habitat upstream of them, rather than any on accessible streams (or if you want to retain these, create new count fields for having upstream habitat only)

[smnorris]

@nickw-CWF @andrewp-CWF @oakoppel-1

For barriers_anthropogenic_ch_cm_co_pk_sk_upstr, would this include upstream barriers with any modelled salmon habitat upstream - or just with upstream habitat of wcrp target species (for the given watershed)?

If we want to report on upstream barriers with wcrp target spp habitat upstream, maybe the columns could be barriers_anthropogenic_habitat_wcrp_upstr, and the barriers would include those with steelhead habitat upstream where applicable.

[nickw-CWF]

@smnorris yes, we would only want to report on barriers that have wcrp target spp upstream of them. I like the idea of calling the columns barriers_anthropogenic_habitat_wcrp_upstr

[nickw-CWF]

@smnorris is there anything you need from me on this now that it is re-opened?

[smnorris]

no, I closed accidentally - it should close when the fixes are merged

[nickw-CWF]

@smnorris

I've just been doing a quick review of the v0.5.0 results and things are looking good in crossings_wcrp_vw.

One thing I noticed though is that there are still crossings that are getting assigned NULL habitat values. I was just looking in BULK and there are 23 such crossings, including ones that definitely have habitat upstream.

For example: 197666 and 1024729348 both have NULL habitat values, but 124500 (which is directly upstream of the other two) has the correct habitat values calculated.

Another example of one that clearly has habitat upstream but is NULL is 197665.

Could you look into a fix for this?

[smnorris]

Unfortunately those crossings are on side channels to the Bulkley - we can't generate good habitat values for crossings on side channels - upstream includes the entire Bulkley according to the watershed codes.

[smnorris]

Potential work arounds:

• fix table assigning custom watershed codes to given WCRP crossings where necessary
• modify crossings_wcrp_vw query to assign values to the given crossings by taking the numbers from another nearby crossing upstream

[nickw-CWF]

Unfortunately those crossings are on side channels to the Bulkley - we can't generate good habitat values for crossings on side channels - upstream includes the entire Bulkley according to the watershed codes.

Ah ok, I missed that. It seems odd that some of these are classified as side channels, but I guess we're stuck with what is in the FWA.

Potential work arounds:

• fix table assigning custom watershed codes to given WCRP crossings where necessary
• modify crossings_wcrp_vw query to assign values to the given crossings by taking the numbers from another nearby crossing upstream

How complicated would the first work around be?

[smnorris]

How complicated would the first work around be?

For upstream habitat numbers, it would be pretty simple - link to the 'fix' table in this join to get habitat upstream (but habitat between the crossing and the confluence with the primary channel would not be counted) https://github.com/smnorris/bcfishpass/blob/main/model/02_habitat_linear/sql/load_crossings_upstream_habitat_wcrp.sql#L36

Other upstream/downstream metrics in the WCRP crossings table (barrier counts etc) would likely require a bit more work because they aren't already adjusted by WCRP specific queries.

[nickw-CWF]

I also noticed an issue with the new crossings_wcrp_vw, there are some structures that are being assigned negative habitat values for sk and co belowupstrbarriers fields (which propagates to all_rearing_belowupstrbarriers_km and all_spawningrearing_belowupstrbarriers_km. E.g., 1024756857, 1017104199, 1017104205.

Also, I was a little overzealous in pruning the attributes for crossings_wcrp_vw, could we add back in:

• downstream_route_measure
• wscode
• localcode
• barriers_ch_cm_co_pk_sk_dnstr

[nickw-CWF]

I also noticed an issue with the new crossings_wcrp_vw, there are some structures that are being assigned negative habitat values for sk and co belowupstrbarriers fields (which propagates to all_rearing_belowupstrbarriers_km and all_spawningrearing_belowupstrbarriers_km. E.g., 1024756857, 1017104199, 1017104205.

Also, I was a little overzealous in pruning the attributes for crossings_wcrp_vw, could we add back in:

• downstream_route_measure
• wscode
• localcode
• barriers_ch_cm_co_pk_sk_dnstr

@smnorris Would it be possible to do a re-run with these updates this week? We're meeting with SFC next week to look at barrier results and it would be helpful to have these updates.

[smnorris]

Easy to restore those attributes right away but I'll have to look in to the crossings with negative habitat values.

[smnorris]

@nickw-CWF What about the steelhead access model column, barriers_st_dnstr?

[nickw-CWF]

@nickw-CWF What about the steelhead access model column, barriers_st_dnstr?

Sure, we can include that as well. Thanks!

[smnorris]

👍 and I'll add barriers_wct_dnstr as well for ELKR.

Negative upstream rearing values are not present in the general crossings view, so presumably this is a bug in the various calculations in https://github.com/smnorris/bcfishpass/blob/main/model/02_habitat_linear/sql/load_crossings_upstream_habitat_wcrp.sql

[smnorris]

@andrewp-CWF @nickw-CWF

I've created a migration script with the new columns added to the view - it ran fine on _dev. But it fails on primary CWF db due to dependent objects:

BEGIN
psql:sql/wcrp_crossings_vw.sql:4: ERROR:  cannot drop materialized view bcfishpass.crossings_wcrp_vw because other objects depend on it
DETAIL:  view wcrp_bowr_ques_carr.ranked_barriers_bowr_ques_carr depends on materialized view bcfishpass.crossings_wcrp_vw
view wcrp_tho_shu.ranked_barriers_eagle depends on materialized view bcfishpass.crossings_wcrp_vw
view wcrp_tho_shu.ranked_barriers_bessette depends on materialized view bcfishpass.crossings_wcrp_vw
view wcrp_tho_shu.ranked_barriers_bonp depends on materialized view bcfishpass.crossings_wcrp_vw
view wcrp_elkr.ranked_barriers_elkr_upstr depends on materialized view bcfishpass.crossings_wcrp_vw
view wcrp_elkr.ranked_barriers_elkr_dnstr depends on materialized view bcfishpass.crossings_wcrp_vw
view wcrp_bela_atna_necl.ranked_barriers_bela_atna_necl depends on materialized view bcfishpass.crossings_wcrp_vw
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
psql:sql/wcrp_crossings_vw.sql:135: ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

Can you drop these views? Or should I try and CASCADE the drop?

Noting the names of the views, I wonder if you're still using jobs/auto_rank.py, or can this be removed from repository?

[andrewp-CWF]

@smnorris @nickw-CWF The views are dropped now.

I am still using jobs/auto_rank.py and then manually creating these views for each wcrp schema which joins the crossings_wcrp_vw fields with the wcrp_ranked_barriers fields

[andrewp-CWF]

@smnorris @nickw-CWF I can add the creation of these views to jobs/auto_rank.py and I can create them as tables rather than views to avoid future dependency issues like this

[smnorris]

Thanks Andrew - upgrade of crossings_wcrp_vw is complete and I'll trigger a model run with the latest data.

Views and tables are both fine - I think it is safe to presume crossings_wcrp_vw schema will be fairly stable but its up to you.

We can also call auto_rank.py from the automated workflows in the CWF workflows repo so you don't have to call it manually each time.