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

1. Ensure all requirements/dependencies are met/installed:

   • access to a PostgreSQL (13-16, 17 is not yet supported) database with the PostGIS extension (>=3.1) installed
   • GDAL >=3.4
   • Python 3
   • bcdata
   • make/unzip/curl/awscli/ etc (see Dockerfile)

2. 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
   

3. 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