#!/bin/bash

set -e
set -x

# Export settings required to run psql non-interactively
export PGHOST=$(cat /etc/mmw.d/env/MMW_DB_HOST)
export PGDATABASE=$(cat /etc/mmw.d/env/MMW_DB_NAME)
export PGUSER=$(cat /etc/mmw.d/env/MMW_DB_USER)
export PGPASSWORD=$(cat /etc/mmw.d/env/MMW_DB_PASSWORD)

# Ensure that the PostGIS extension exists
psql -c "CREATE EXTENSION IF NOT EXISTS postgis;"

# Run migrations
envdir /etc/mmw.d/env /opt/app/manage.py migrate

#!/bin/bash

set -e
set -x

usage="$(basename "$0") [-h] [-d] \n
--Sets up a postgresql database for MMW \n
\n
where: \n
    -h  show this help text\n
    -d  load/reload base data\n
"

load_data=false

while getopts ":hd" opt; do
    case $opt in
        h)
            echo -e $usage
            exit
            ;;
        d)
            load_data=true
            ;;
        \?)
            echo "invalid option: -$OPTARG"
            exit
            ;;
    esac
done
# Export settings required to run psql non-interactively
export PGHOST=$(cat /etc/mmw.d/env/MMW_DB_HOST)
export PGDATABASE=$(cat /etc/mmw.d/env/MMW_DB_NAME)
export PGUSER=$(cat /etc/mmw.d/env/MMW_DB_USER)
export PGPASSWORD=$(cat /etc/mmw.d/env/MMW_DB_PASSWORD)

# Ensure that the PostGIS extension exists
psql -c "CREATE EXTENSION IF NOT EXISTS postgis;"

# Run migrations
envdir /etc/mmw.d/env /opt/app/manage.py migrate

if [ "$load_data" = "true" ] ; then
	# Fetch boundary layer sql files
	FILE_HOST="https://s3.amazonaws.com/data.mmw.azavea.com"
	FILES=("boundary_huc12.sql.gz" "boundary_huc10.sql.gz" "boundary_huc08.sql.gz")

	for f in "${FILES[@]}"; do
	    curl -s $FILE_HOST/$f | gunzip -q | psql --single-transaction
	done
fi

Update script to load HUC data to database
Update script to load HUC data to database

This script is intended to be run from an `app` vm in either development
or production.

* gzipped SQL files are hosted on external source (S3)
* `setupdb.sh` can be run idempotently, it will download/create/insert
  HUC boundary data safely and repeatedly.

Original shp data can be found on the Azavea project fileshare for
Stroud/downloads.  They were transformed to pgsql via `shp2pgsql` using
the following pattern:

```sql
shp2pgsql -W "LATIN1" /path/to/shape.shp
public.boundary_{layer} | psql -h localhost -d mmw -U mmw
```

The schema was modified via `psql` to remove unncessary attributes, and
in the case of HUC12, simplified the polygon with a .00008 threshold

```sql
update boundary_huc12 set geom =
ST_Multi(ST_SimplifyPreserveTopology(geom_detailed, 0.00008));
```

I then exported the tables via `pg_dump`:

```bash
 pg_dump -c --if-exists -O -t boundary_huc12 -h localhost -U mmw mmw >
 boundary_huc12.sql
```

The resulting files are gzipped for distribution.
