smnorris / fwapg

PostgreSQL tools for working with British Columbia's Freshwater Atlas
https://smnorris.github.io/fwapg
MIT License
9 stars 5 forks source link

Tidy, modify, or remove function FWA_LengthInstream() #48

Closed smnorris closed 3 years ago

smnorris commented 3 years ago

Although I don't think I'm currently using the function, the code in FWA_LengthInstream() is outdated. It is useful though, and could be used for fish habitat modelling. It should be updated to use the FWA_Upstream/ FWA_Downstream funcs directly.

Also, the func would probably be much more useful if it was generalized to be similar to those functions - simply return true if a feature is 'instream' between two points. Length can be derived from the returned features.

To find features between two locations (without creating a graph/making routing queries), find downstream path of both input locations and return only features that are not common to the two result sets:

Something like this, but with some kind of logic that ensures no results are returned when querying features that are not connected (I think this would connect features across the ocean):

WITH dnstr_a AS
(
    SELECT
      linear_feature_id,
      blue_line_key,
      downstream_route_measure,
      wscode_ltree,
      localcode_ltree,
      geom
    FROM whse_basemapping.fwa_stream_networks_sp s
    WHERE FWA_Downstream(360878058, 10524, '400.431358.815756'::ltree, '400.431358.815756.466791'::ltree,
    s.blue_line_key, s.downstream_route_measure, s.wscode_ltree, s.localcode_ltree)
),

dnstr_b AS
(
    SELECT
      linear_feature_id,
      blue_line_key,
      downstream_route_measure,
      wscode_ltree,
      localcode_ltree,
      geom
    FROM whse_basemapping.fwa_stream_networks_sp s
    WHERE FWA_Downstream(360881038, 5115, '400.431358.918528'::ltree, '400.431358.918528.104334'::ltree,
    s.blue_line_key, s.downstream_route_measure, s.wscode_ltree, s.localcode_ltree)
)

SELECT
  COALESCE(a.linear_feature_id, b.linear_feature_id) as linear_feature_id,
  COALESCE(a.geom, b.geom) as geom
FROM dnstr_a a
FULL OUTER JOIN dnstr_b b
on a.linear_feature_id = b.linear_feature_id
WHERE (a.linear_feature_id IS NULL AND b.linear_feature_id IS NOT NULL)
OR (a.linear_feature_id IS NOT NULL AND b.linear_feature_id IS NULL)
smnorris commented 3 years ago

function removed