#!/bin/bash

# Bash script to create a new postgis database and prep it with GADM

# data for geovalidation purposes.

#

# Basic workflow:

#  1. create polygon geometry upon import (loader does this)

#  2. create simplified polygon geometry after import

#  3. create polygon geography after import

#  4. create indexes

#  5. cluster data in geom index order

# 

# todo:

# * better handle Antarctica problem so it can be included instead of

#   just dropped? probably not a huge deal for plants...

#

# Jim Regetz

# NCEAS

# Created November 2012

#

# refactored and reorganized by

# Paul Sarando

# iPlant Collaborative

# Updated Oct 2013

DB_NAME="geoscrub"

DB_USER="bien"

DB_HOST_OPT=""

SCRIPT_DIR="$(dirname $0)"

# GADM data originally available at http://www.gadm.org/data2/gadm_v2_shp.zip

# gadm.org now links this file from biogeo.ucdavis.edu.

GADM_DATA_URL="http://biogeo.ucdavis.edu/data/gadm2/gadm_v2_shp.zip"

GADM_DATA_DIR="${HOME}/gadm_v2_shp"

function usage {

    echo "Usage: $0 [OPTIONS]" >&2

    echo "Valid Options:" >&2

    echo "-d, --dbname=DBNAME      database name psql commands will connect to" >&2

    echo "-h, --host=HOSTNAME      database server host or socket directory" >&2

    echo "-U, --username=USERNAME  database user name" >&2

    echo ""

    echo "Input Data Options:" >&2

    echo "-g, --gadm-data          GADM data directory (default: ${HOME}/gadm_v2_shp)" >&2

    echo "                         Delete this directory, or the GADM data in it," >&2

    echo "                         to re-download the data." >&2

    exit 1;

}

while [[ $# -gt 0  ]]; do

    case "$1" in

        -\? | --help)

            usage

            ;;

        -h)

            if [[ -z $2  ]];  then

                echo "Option $1 requires an argument." >&2

                usage

            fi

            DB_HOST_OPT="-h $2"

            shift 2

            ;;

        --host=*)

            DB_HOST_OPT="-h ${1#*=}"

            shift

            ;;

        -U)

            if [[ -z $2  ]];  then

                echo "Option $1 requires an argument." >&2

                usage

            fi

            DB_USER="$2"

            shift 2

            ;;

        --username=*)

            DB_USER="${1#*=}"

            shift

            ;;

        -d)

            if [[ -z $2  ]];  then

                echo "Option $1 requires an argument." >&2

                usage

            fi

            DB_NAME="$2"

            shift 2

            ;;

        --dbname=*)

            DB_NAME="${1#*=}"

            shift

            ;;

        -g)

            if [[ -z $2  ]];  then

                echo "Option $1 requires an argument." >&2

                usage

            fi

            GADM_DATA_DIR="$2"

            shift 2

            ;;

        --gadm-data=*)

            GADM_DATA_DIR="${1#*=}"

            shift

            ;;

        *)

            echo "Invalid option: $1" >&2

            usage

            ;;

    esac

done

#

# assemble input data

#

echo "Updating gadm2 tables from GADM data in ${GADM_DATA_DIR}"

echo -n "Note, to force data to download again from ${GADM_DATA_URL},"

echo " delete the directory ${GADM_DATA_DIR} before running this script."

# Check for GADM data (320MB zip file) unziped in data directory.

if [[ ! -d "$GADM_DATA_DIR" ]]; then

    echo "making directory ${GADM_DATA_DIR}"

    mkdir -p "$GADM_DATA_DIR"

    if [[ $? != 0 ]]; then

        echo "Could not create directory ${GADM_DATA_DIR}"

        exit 1

    fi

    pushd "$GADM_DATA_DIR"

    wget -O gadm_v2_shp.zip "$GADM_DATA_URL"

    unzip gadm_v2_shp.zip

    rm -f gadm_v2_shp.zip

    popd

fi

#

# create and populate gadm2 table

#

psql -e -U "$DB_USER" $DB_HOST_OPT -d "$DB_NAME" -c "DROP TABLE IF EXISTS gadm2"

if [[ $? != 0 ]]; then

    echo "Could not drop GADM2 table in ${DB_NAME} database."

    exit 1

fi

echo "Creating gadm2 table with shp2pgsql from ${GADM_DATA_DIR} data."

pushd "$GADM_DATA_DIR"

# load gadm2 data (took 4.7 minutes on willow, 26-Oct-2012)

shp2pgsql -s 4326 -W latin1 gadm2 gadm2 "$DB_NAME" | \

    psql --set ON_ERROR_STOP=1 -U "$DB_USER" $DB_HOST_OPT -d "$DB_NAME"

if [[ $? != 0 ]]; then

    popd

    echo "Could not load GADM2 data into ${DB_NAME} database."

    exit 1

fi

popd

# create indexes and additional columns

psql -e -U "$DB_USER" $DB_HOST_OPT -d "$DB_NAME" --set ON_ERROR_STOP=1 < "${SCRIPT_DIR}"/update.gadm2.sql

if [[ $? != 0 ]]; then

    echo "Could not update GADM2 data and indexes in ${DB_NAME} database."

    exit 1

fi

echo "Update gadm2 tables successfully completed."