Establish DB relationships between most important data products

dhardestylewis commented 2 years ago

@mepearson

1-to-1 relationship between the data tiles & the visualization tiles:

256 pixels x 256 pixels as a balance between vis & data
.25m .5m 1m 2m 4m 8m 16m 32m ... on this scheme in order to enable 1m by default and get close to existing 3m, 10m, 30m, etc
@brentporter insists quarter-quad gridding scheme

many-to-many relationship between HUC watersheds --to--> tiles

once at the HUC8 level
once at the HUC12 level
the focus is on enabling select watershed --> receive corresponding tiles
@wmobley has uploaded both these watershed levels to PostGIS

many-to-many relationship between counties --to--> tiles

dhardestylewis commented 2 years ago

Also include

1-to-many relationship between single_seamless_elevation DEM tiles and source TNRIS Lidar DEM tiles

dhardestylewis commented 2 years ago

README.md blurb to be incorporated to the grand README.md later

refs https://github.com/dhardestylewis/terrain_aggregator/issues/61 https://gis.stackexchange.com/q/83016/193758

We rely on the USGS's National Watershed Boundary Dataset (WBD_National) as the single data source from which to derive the latest watershed boundary polygons.

## Download USGS's single National Watershed Boundary Dataset (`WBD_National`)
wget https://prd-tnm.s3.amazonaws.com/StagedProducts/Hydrography/WBD/National/GDB/WBD_National_GDB.zip

and then upload WBD zoom levels HUC8 & HUC12 to the terrain_aggregator database

/* Create a table for WBD at HUC8 zoom level in the `terrain_aggregator` database */
SINGULARITYENV_POSTGRES_PASSWORD=pgpass SINGULARITYENV_PGDATA=$WORK/pgdata singularity exec --cleanenv --bind $SCRATCH:/var $WORK/postgis_14-3.2-gdalogr.sif /work2/04950/dhl/stampede2/miniconda3/envs/gdal/bin/ogr2ogr -f "PostgreSQL" PG:"host=127.0.0.1 dbname=postgres user=postgres" WBD_National_GDB.gdb/ -progress --config PG_USE_COPY YES -nln public.wbdhu8 WBDHU12
/* Create a table for WBD at HUC8 zoom level in the `terrain_aggregator` database */
SINGULARITYENV_POSTGRES_PASSWORD=pgpass SINGULARITYENV_PGDATA=$WORK/pgdata singularity exec --cleanenv --bind $SCRATCH:/var $WORK/postgis_14-3.2-gdalogr.sif /work2/04950/dhl/stampede2/miniconda3/envs/gdal/bin/ogr2ogr -f "PostgreSQL" PG:"host=127.0.0.1 dbname=postgres user=postgres" WBD_National_GDB.gdb/ -progress --config PG_USE_COPY YES -nln public.wbdhu12 WBDHU12

dhardestylewis commented 2 years ago

@dhardestylewis Publish DB connections, using tables similar to the TDIS tables or this https://d9-wret.s3.us-west-2.amazonaws.com/assets/palladium/production/s3fs-public/atoms/files/WBD%20v2.3%20Model%20Poster%2006012020.pdf

@gsmithhart @paul-csr Can I put an example of the TDIS DB relationship tables here?

dhardestylewis / terrain_aggregator

Establish DB relationships between most important data products #59