extras - calculate upstream area for watersheds about fwapg

[smnorris]

This is done but it is worth noting that joining fwa_watersheds_upstream_area back to streams is not quite as simple as might be expected. Joining on the watershed codes kind of works if selecting DISTINCT matches (one stream can match >1 watersheds) - but some streams also have 0 matching watersheds with the same watershed code. So, while it takes much longer to process it is easiest to just do a spatial join based on the centroid of the stream line. Note that selecting distinct matches is still required because watersheds boundaries can be co-incident with stream lines, more than one watershed can intersect a point on a stream.

Anyway, to get area upstream of stream segments:

SELECT DISTINCT ON (linear_feature_id)
  s.linear_feature_id,
  ua.upstream_area
FROM whse_basemapping.fwa_stream_networks_sp s
INNER JOIN whse_basemapping.fwa_watersheds_poly w
ON ST_Intersects(ST_pointonsurface(s.geom), w.geom)
INNER JOIN whse_basemapping.fwa_watersheds_upstream_area ua 
ON w.watershed_feature_id = ua.watershed_feature_id
WHERE NOT s.wscode_ltree  <@ '999'
ORDER BY s.linear_feature_id, s.stream_order