Skip to content
forked from smnorris/fwapg

PostgreSQL tools for working with British Columbia's Freshwater Atlas

License

Notifications You must be signed in to change notification settings

franTarkenton/fwapg

 
 

Repository files navigation

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

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) database with the PostGIS extension (>=3.1) installed
    • GDAL >=3.4
    • GNU parallel
    • make/unzip/wget/etc
  2. Ensure you have a DATABASE_URL environment variable set to point to your database, for example:

     export DATABASE_URL=postgres://username:password@localhost:5432/fwapg
    
  3. Get scripts, load and optimize the data (this takes some time):

     git clone https://github.com/smnorris/fwapg.git
     cd fwapg
     make
    
  4. 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(-123.7028, 48.3858, 4326);
    
       gnis_name  | blue_line_key | downstream_route_measure
     -------------+---------------+--------------------------
      Sooke River |     354153927 |        350.2530543284006
    

    Generate the watershed upstream of above location:

     SELECT ST_ASText(geom) FROM FWA_WatershedAtMeasure(354153927, 350);
    
      st_astext
     --------------
     POLYGON((...
    

    See Usage for more examples.

Docker

Download the repo, create containers, create database, load fwa data:

git clone https://github.com/smnorris/fwapg.git
cd fwapg
docker-compose build
docker-compose up -d
docker-compose run --rm loader psql -c "CREATE DATABASE fwapg" postgres
docker-compose run --rm loader make

As long as you do not remove the container fwapg-db, it will retain all the data you put in it. If you have shut down Docker or the container, start it up again with this command:

docker-compose up -d

Connect to the db from your host OS via the port specified in docker-compose.yml:

psql -p 8002 -U postgres fwapg

Or see the FWA data in the browser as vector tiles/geojson features:

http://localhost:7800/
http://localhost:9000/

Delete the containers (and associated fwa data):

docker-compose down

Tile and feature services

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

Source data

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. The table reference page is autogenerated from comments in the database. To update:

cd docs
./table_reference.sh

About

PostgreSQL tools for working with British Columbia's Freshwater Atlas

Resources

License

Stars

Watchers

Forks

Packages

 
 
 

Languages

  • PLpgSQL 87.3%
  • Shell 7.0%
  • Makefile 4.4%
  • Dockerfile 1.3%