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.
-
Ensure all requirements/dependencies are met/installed:
-
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.
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)
-
BC Freshwater Atlas documentation and license
-
USGS Watershed Boundary Dataset (WBD) Metadata
-
Hydrosheds License and citation
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 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