improve labelling of bridges/open bottom structures about modelled_stream_crossings

[smnorris]

This is complete for known bridge sources, but a review of results - especially based on stream order - is needed before release/publishing.

[smnorris]

Just based on the counts, 6th order and greater does seem like a reasonable break point for OBS:

All crossings on 6th order and greater streams:

# SELECT count(*) FROM fish_passage.modelled_stream_crossings
   WHERE modelled_crossing_type_source && ARRAY['FWA_STREAM_ORDER'];
 count
-------
  2263

Crossings on 6th order and greater streams with no other source indicating that the structure is a bridge:

# SELECT count(*) FROM fish_passage.modelled_stream_crossings
   WHERE modelled_crossing_type_source = ARRAY['FWA_STREAM_ORDER'];
 count 
-------
   391

This does seem to vary spatially, I'm not sure if it is regional terrain differences or variability in the mapping:

# SELECT *, round((n_order_only::float / n_order::float)::numeric, 2) as pct_order_only
 FROM 
(SELECT 
  watershed_group_code,
  count(*) FILTER (WHERE modelled_crossing_type_source && ARRAY['FWA_STREAM_ORDER']) as n_order,
  count(*) FILTER (WHERE modelled_crossing_type_source = ARRAY['FWA_STREAM_ORDER']) as n_order_only
FROM fish_passage.modelled_stream_crossings
GROUP BY watershed_group_code) as f
WHERE n_order > 5 AND n_order_only > 0
ORDER BY pct_order_only desc

watershed_group_code | n_order | n_order_only | pct_order_only 
----------------------+---------+--------------+----------------
 BBAR                 |       7 |            7 |           1.00
 ATNA                 |      11 |            8 |           0.73
 UCHR                 |      12 |            8 |           0.67
 LCHL                 |      37 |           24 |           0.65
 LIAR                 |       6 |            3 |           0.50
 NECR                 |      34 |           17 |           0.50
 MAHD                 |      15 |            7 |           0.47
 NARC                 |      19 |            9 |           0.47
 GUIC                 |      17 |            8 |           0.47
 COLR                 |      47 |           21 |           0.45
 LCHR                 |      16 |            7 |           0.44
 OKAN                 |      45 |           19 |           0.42
 TWAC                 |       8 |            3 |           0.38
 LBTN                 |      45 |           16 |           0.36
 BONP                 |      42 |           15 |           0.36
 BULK                 |      67 |           24 |           0.36
 SIML                 |      56 |           20 |           0.36
 STHM                 |      80 |           27 |           0.34
 LNTH                 |      27 |            9 |           0.33
 UFRT                 |      12 |            4 |           0.33
 DEAD                 |      18 |            6 |           0.33
 BLAR                 |      12 |            4 |           0.33
 NICL                 |      25 |            8 |           0.32
 SAJR                 |      29 |            8 |           0.28
 UBTN                 |      15 |            4 |           0.27
 NAZR                 |      12 |            3 |           0.25
 UDEN                 |       8 |            2 |           0.25
 BULL                 |      33 |            8 |           0.24
 SETN                 |      61 |           12 |           0.20
 BABL                 |      10 |            2 |           0.20
 BRID                 |      29 |            5 |           0.17
 CARP                 |       6 |            1 |           0.17
 NATR                 |       6 |            1 |           0.17
 FIRE                 |       6 |            1 |           0.17
 LSIK                 |      14 |            2 |           0.14
 STUL                 |       7 |            1 |           0.14
 MORR                 |       7 |            1 |           0.14
 UISR                 |       8 |            1 |           0.13
 HORS                 |       8 |            1 |           0.13
 PCEA                 |      27 |            3 |           0.11
 KISP                 |      18 |            2 |           0.11
 MORK                 |      28 |            3 |           0.11
 LHAF                 |      10 |            1 |           0.10
 LNIC                 |      30 |            3 |           0.10
 KOTR                 |      21 |            2 |           0.10
 KETL                 |      54 |            5 |           0.09
 COWN                 |      22 |            2 |           0.09
 PARA                 |      13 |            1 |           0.08
 LNAR                 |      24 |            2 |           0.08
 UHAF                 |      13 |            1 |           0.08
 UPCE                 |      14 |            1 |           0.07
 FRAN                 |      16 |            1 |           0.06
 DUNC                 |      32 |            2 |           0.06
 LMUS                 |      18 |            1 |           0.06
 SQAM                 |      22 |            1 |           0.05
 KOTL                 |      26 |            1 |           0.04
 ELKR                 |      52 |            1 |           0.02
(57 rows)

[smnorris]

Definitely appears to be terrain related - watershed groups where > 1/3 of the crossings tagged as OBS are only tagged via stream order:
watershed_group_code IN ('BBAR','ATNA','UCHR','LCHL','LIAR','NECR','MAHD','NARC','GUIC','COLR','LCHR','OKAN','TWAC','LBTN','BONP','BULK','SIML','STHM')

[smnorris]

This can be closed for now - just keep in mind that there is a small potential for culverts to exist on stream order 6 and greater, particularly for these interior watershed groups. When running an assessment project, a quick manual review of modelled OBS crossings WHERE modelled_crossing_type_source = ARRAY['FWA_STREAM_ORDER'] would be valuable.