SK spawning: implement bcfishpass downstream trace + upstream lake proximity

[NewGraphEnvironment]

Bug: lake outlet ordering picks wrong tributary

.frs_connected_spawning line 1385:

ORDER BY s2.waterbody_key, s2.downstream_route_measure ASC

Should be:

ORDER BY s2.waterbody_key, s2.wscode_ltree, s2.localcode_ltree, s2.downstream_route_measure

DRM ordering picks an arbitrary segment on any BLK with the smallest measure. wscode ordering picks the actual network-topological outlet of the lake.

Also line 1392: PARTITION BY lo.blue_line_key should be PARTITION BY lo.waterbody_key so the cumulative distance calculation stays within one lake's downstream trace.

Example

waterbody_key 329064462 (BULK) spans 10 BLKs. Current code picks BLK 360504780 DRM 0 (wrong tributary). Correct ordering picks BLK 360846413 DRM 9718 (actual lake outlet). Downstream trace from the wrong outlet misses 7+ km of spawning habitat.

Proven

Corrected query on BULK SK spawning: 18.88 km → 24.41 km (bcfishpass 24.38 km). From -22.6% to +0.1%.

-- Corrected outlet selection
SELECT DISTINCT ON (s2.waterbody_key)
  s2.waterbody_key, s2.blue_line_key, s2.downstream_route_measure
FROM fresh.streams s2
INNER JOIN fresh.streams_habitat hr ON s2.id_segment = hr.id_segment
WHERE hr.species_code = 'SK' AND hr.rearing IS TRUE
ORDER BY s2.waterbody_key, s2.wscode_ltree, s2.localcode_ltree, s2.downstream_route_measure

BULK regression also noted

BABL SK spawning -13.6% likely the same bug. ADMS +2.6% works because the rearing lake has only one outlet BLK.

Versions

• fresh: 0.13.4
• bcfishpass: v0.5.0
• link: 0.1.0

[NewGraphEnvironment]

BULK regression: -39.9% SK spawning

ADMS works (+2.6%) but BULK shows -39.9% SK spawning. Connected spawning step reduces 3,726 → 52 segments (98.6% removed). bcfishpass keeps 24.38 km, we produce 14.65 km. SK rearing is exact (64.56 = 64.56).

bcfishpass SK spawning has two phases in load_habitat_linear_sk.sql:

1. Downstream: FWA_Downstream from lake outlets, within 3km, above any 5% gradient
2. Upstream: FWA_Upstream from rearing lakes → ST_ClusterDBSCAN(geom, 1, 1) → st_dwithin(cluster_geom, lake_poly, 2) — only clusters within 2m of a qualifying lake (200ha+)

The upstream phase uses actual lake polygon geometry (fwa_lakes_poly, fwa_manmade_waterbodies_poly) for the proximity check, not just waterbody_key. BULK has more scattered SK-supporting lakes than ADMS — the algorithm divergence is more pronounced with complex lake geometry.

Not related to dfo_known_sockeye_lakes.csv — bcfishpass doesn't use it in the SK model.

Versions

• fresh: 0.13.3
• bcfishpass: v0.5.0
• link: 0.1.0