length_between_vertices.sql

drop table temp.length_between_vertices;

create table temp.length_between_vertices as

-- exclude ex-bc streams from sample
with exclude as (
  select distinct blue_line_key
  from whse_basemapping.fwa_stream_networks_sp
  where edge_type = 6010
),

-- pull sample of 10k blue_line_keys, within salmon/steelhead watersheds, with max order > 1
sample as (
  select
    blue_line_key
  from (
    select distinct
      v.blue_line_key
    from whse_basemapping.fwa_streams_vw v
    left outer join exclude e
    on v.blue_line_key = e.blue_line_key
    where v.stream_order_max > 1
    and e.blue_line_key is null
    and watershed_group_code in ('ADMS','ALBN','ATNA','BABL','BABR','BARR','BBAR','BELA','BLAR','BONP','BOWR','BRKS','BULK','CAMB','CARR','CHES','CHIL','CHIR','CHWK','CLAY','CLWR','COMX','COTR','COWN','DEAD','DOGC','EUCH','EUCL','FRAN','FRCN','GLAR','GOLD','GRAI','GUIC','HARR','HERR','HOLB','HOMA','HORS','INKR','JENR','JERV','KEEC','KHTZ','KINR','KISP','KITL','KITR','KLIN','KLUM','KNIG','KSHR','KTSU','KUMR','LBIR','LCHL','LCHR','LDEN','LFRA','LILL','LISR','LKEL','LNAR','LNIC','LNTH','LRDO','LSAL','LSKE','LSTR','LTRE','MAHD','MBNK','MCGR','MESC','MFRA','MIDR','MORI','MORK','MORR','MSKE','MSTR','NAHR','NAKR','NARC','NASC','NASR','NAZR','NBNK','NECL','NECR','NEVI','NICL','NIEL','NIMP','OKAN','OWIK','PARK','PORI','QUES','SAJR','SALM','SALR','SANJ','SETN','SEYM','SHER','SHUL','SQAM','STHM','STIR','STUL','STUR','SUST','SWIR','TABR','TAHR','TAHS','TAKL','TASR','TATR','TAYR','TESR','THOM','TOBA','TSAY','TSIT','TWAC','UBIR','UCHR','UFRA','UJER','UNAR','UNTH','UNUR','USHU','USKE','USTK','UTRE','VICT','WILL','WORC','ZYMO')
    and stream_order_max > 1
  ) as bl
  order by random()
  limit 10000
),

-- join back to source table so below queries can access stream attributes
segments as (
 select a.*
 from whse_basemapping.fwa_stream_networks_sp a
 inner join sample b
 on a.blue_line_key = b.blue_line_key
),

-- extract every node/vertex, plus its elevation and postion (percentange along the blue_line_key)
stream_nodes AS
    (SELECT
      generate_series(1, ST_NPoints((ST_Dump(geom)).geom)) as node_id,
      s.linear_feature_id,
      s.blue_line_key,
      s.downstream_route_measure,
      s.length_metre,
      l.stream_length AS blue_line_length,
      ST_Z(ST_PointN((ST_Dump(geom)).geom, generate_series(1, ST_NPoints((ST_Dump(geom)).geom)))) AS elevation,
      ((ST_LineLocatePoint((ST_Dump(geom)).geom, ST_PointN((ST_Dump(geom)).geom, generate_series(1, ST_NPoints((ST_Dump(geom)).geom)))) * length_metre) + downstream_route_measure) / l.stream_length AS from_pct
    FROM segments s
    -- get maximum upstream route measure / total length of stream
    CROSS JOIN LATERAL (
        SELECT DISTINCT ON (blue_line_key)
          blue_line_key,
          downstream_route_measure + length_metre AS stream_length
        FROM whse_basemapping.fwa_stream_networks_sp
        WHERE blue_line_key = s.blue_line_key
        ORDER BY blue_line_key, downstream_route_measure DESC
     ) l
    WHERE s.blue_line_key = s.watershed_key
    ORDER BY blue_line_key, downstream_route_measure
),

-- find the next node/vertex upstream, generating from / to percentages and elevations
node_elevations AS
(
    SELECT
       node_id,
       linear_feature_id,
       blue_line_key,
       downstream_route_measure,
       blue_line_length,
       from_pct,
       lead(from_pct) OVER(ORDER BY blue_line_key, downstream_route_measure, node_id) AS to_pct,
       elevation as from_elevation,
       lead(elevation) OVER(ORDER BY blue_line_key, downstream_route_measure, node_id) AS to_elevation
    FROM stream_nodes
    ORDER BY blue_line_key, downstream_route_measure, node_id
)

-- generate length and slope of the line between the generated from / to positions and elevations,
-- retain only edges of interest (basically, exclude network connectors other than river centrelines)
SELECT
  row_number() over() as id,
  ne.blue_line_key,
  ne.linear_feature_id,
  ne.node_id,
  s.edge_type,
  s.stream_order,
  s.waterbody_key,
  round((ne.blue_line_length * (ne.to_pct - ne.from_pct))::numeric, 0)::integer AS length_meters,
  round(ne.from_elevation::numeric, 2) as from_elevation,
  round(ne.to_elevation::numeric, 2) as to_elevation,
  round(((ne.to_elevation - ne.from_elevation) / (ne.blue_line_length * (ne.to_pct - ne.from_pct)))::numeric, 2) as slope
FROM node_elevations ne
inner join whse_basemapping.fwa_stream_networks_sp s on ne.linear_feature_id = s.linear_feature_id
WHERE to_elevation IS NOT NULL
AND s.edge_type in (1000,1050,1100,1150,1250,1350,2000,2300)
AND round(from_pct::numeric, 5) <> 1
AND blue_line_length * (to_pct - from_pct) > 0;