fwapg

fwapg extends British Columbia's Freshwater Atlas (FWA) with PostgreSQL/PostGIS. fwapg provides additional tables, indexes and functions to:

quickly translate arbitrary point locations (X,Y) to a linear reference positions (blue_line_key, measure) on the stream network
enable speedy upstream/downstream queries throughout BC
quickly and cleanly generate watershed boundaries upstream of arbitrary locations
enable cross-boundary queries by combining FWA data with data from neighbouring jurisdictions
enable querying of FWA features via spatial SQL
provide gradient values for every FWA stream
enable quickly serving FWA features as vector tiles (MVT)
enable quickly serving FWA features and custom fwapg functions
link additional data to FWA streams (PCIC mean annual discharge, modelled channel width, upstream precipitation)

See documentation for setup and usage details, plus table and function references.

Quickstart

Ensure all requirements/dependencies are met/installed:
- access to a PostgreSQL (>=13) database with the PostGIS extension (>=3.1) installed
- GDAL >=3.4
- Python 3
- bcdata
- make/unzip/wget/etc
Ensure you have a DATABASE_URL environment variable set to point to your database, for example:
```
export DATABASE_URL=postgresql://username:password@localhost:5432/fwapg
```

Get scripts, load and optimize the data:

git clone https://github.com/smnorris/fwapg.git
cd fwapg
make

The full load takes some time - but once complete, you can run fwapg enabled queries with your favorite sql client. For example:

Locate the nearest point on the FWA stream network to a X,Y location on Highway 14:

    SELECT 
      gnis_name, 
      blue_line_key, 
      downstream_route_measure
    FROM FWA_IndexPoint(ST_Transform(ST_GeomFromText('POINT(-123.7028 48.3858)', 4326), 3005));

      gnis_name  | blue_line_key | downstream_route_measure
    -------------+---------------+--------------------------
     Sooke River |     354153927 |        350.2530543284006

Generate the watershed upstream of this location:

    SELECT ST_ASText(geom) FROM FWA_WatershedAtMeasure(354153927, 350);

     st_astext
    --------------
    POLYGON((...

Select all stream upstream of this location:

    SELECT ST_ASText(geom)
    FROM FWA_UpstreamTrace(354153927, 350);

     st_astext
    --------------
    LINESTRINGZM((...

See Usage for more examples.

Tile and feature services

fwapg features and functions are served from hillcrestgeo.ca as GeoJSON or vector tiles via these web services and wrappers:

features.hillcrestgeo.ca/fwa: tables and functions served as GeoJSON
fwapgr (R): an R wrapper around the features.hillcrestgeo.ca/fwa feature service
fwatlasbc (R): an R package for higher level queries
tiles.hillcrestgeo.ca/bcfishpass: FWA features (and others) served as vector tiles (MVT)

Source data

BC Freshwater Atlas documentation and license
USGS Watershed Boundary Dataset (WBD) Metadata
Hydrosheds License and citation

Development and testing

Extremely basic tests are included for selected functions. If changing a covered function, run the individual test. For example:

psql -f tests/test_fwa_upstream.sql

All results should be true.

Documentation

Documentation is built from the markdown files in /docs. Info in the table reference page (03_tables.md) can be autogenerated from comments in the database. To dump the text to stdout:

cd docs
./table_reference.sh

smnorris / fwapg

readme