#!/bin/bash

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

# data for geovalidation purposes.

#

# Basic workflow:

#  1. Load geoscrub input data into database

#  2. Scrub geoscrub input data with the geonames.sql script

#  3. Scrub geoscrub input data with the geovalidate.sql script

# 

# Paul Sarando

# iPlant Collaborative

# Oct 2013

DB_NAME="geoscrub"

DB_USER="bien"

DB_HOST="localhost"

SCRIPT_DIR="$(dirname $0)"

function run_sql_script {

    local SCRIPT=$1

    psql -e -U "$DB_USER" -h "$DB_HOST" -d "$DB_NAME" --set ON_ERROR_STOP=1 < "$SCRIPT"

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

        echo "Error while executing SQL script ${SCRIPT}"

        exit 1

    fi

}

"${SCRIPT_DIR}"/load-geoscrub-input.sh

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

    echo "Could not load ${DB_NAME} database with geonames.org data."

    exit 1

fi

echo "Scrubbing input with geonames data..."

run_sql_script "${SCRIPT_DIR}/geonames.sql"

echo "Scrubbing input with geovalidate data..."

run_sql_script "${SCRIPT_DIR}/geovalidate.sql"

echo "Input successfully scrubbed."

echo "Scrubbed input available in the geoscrub table of the ${DB_NAME} database."

4. geoscrub.sh

   - runs the following scripts in order to load and scrub vegbien input data:

   * load-geoscrub-input.sh

     - dumps geoscrub_input from vegbien and loads it into the geoscrub db

   * geonames.sql

     - contains SQL statements that scrub asserted names and (to the

       extent possible) map them to GADM2

   * geovalidate.sql

     runtime: 5.5 h

     - contains (postgis-extended) SQL statements that score the validity

       of GADM2-scrubbed names against given point coordinates

The bash and SQL statements contained in the files as ordered above

SCRIPT_DIR="$(dirname $0)"

# load vegbien_geoscrub table with input data