#!/bin/bash

# Script to geoscrub-enable a postgres database, developed in the

# context of BIEN3 geoscrubbing but with intentions of generality.

#

# Basic workflow:

#  1. Import table dumps obtained from geonames.org

#  2. Add additional custom mapping info, including:

#     a. Madagascar provinces used in GADM2 but not geonames

#     b. extra country name mappings/fixes specific to BIEN3 data

#     c. extra state/prov name mappings/fixes specific to BIEN3 data

#     d. custom state/prov name mappings binding GADM2 to geonames

#

# Jim Regetz

# NCEAS

# Created Nov 2012

#

# refactored and reorganized by

# Paul Sarando

# iPlant Collaborative

# Updated Oct 2013

DB_NAME="geoscrub"

DB_USER="bien"

DB_HOST="localhost"

SCRIPT_DIR="$(dirname $0)"

GEONAMES_DUMP_URL="http://download.geonames.org/export/dump"

ALL_COUNTRIES_ZIP=allCountries.zip

ALL_COUNTRIES_TXT=allCountries.txt

ALTERNATE_NAMES_ZIP=alternateNames.zip

ALTERNATE_NAMES_TXT=alternateNames.txt

COUNTRY_INFO_TXT=countryInfo.txt

HIERARCHY_ZIP=hierarchy.zip

HIERARCHY_TXT=hierarchy.txt

function run_sql_cmd {

    local SQL_CMD=$1

    psql -e -U "$DB_USER" -h "$DB_HOST" -d "$DB_NAME" -c "$SQL_CMD"

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

        echo "Error while executing SQL command."

        exit 1

    fi

}

function run_sql_script {

    local SCRIPT=$1

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

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

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

        exit 1

    fi

}

echo "Updating geonames.org tables..."

# Check for data downloaded into geonames under the current directory.

DATADIR="$(dirname $0)/geonames"

if [[ ! -d "${DATADIR}" ]]; then

    echo "making directory ${DATADIR}"

    mkdir -p "${DATADIR}"

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

        echo "Could not create directory ${DATADIR}"

        exit 1

    fi

fi

run_sql_script "${SCRIPT_DIR}/truncate.geonames.sql"

#

# load geonames.org geonames

#

if [[ ! -r "${DATADIR}/${ALL_COUNTRIES_TXT}" ]]; then

    pushd "${DATADIR}"

    wget "${GEONAMES_DUMP_URL}/${ALL_COUNTRIES_ZIP}"

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

        echo "Could not download ${ALL_COUNTRIES_ZIP}"

        exit 1

    fi

    unzip "$ALL_COUNTRIES_ZIP"

    rm -f "$ALL_COUNTRIES_ZIP"

    popd

fi

# import data

run_sql_cmd "\COPY geonames FROM '${DATADIR}/${ALL_COUNTRIES_TXT}' DELIMITER E'\t' NULL ''"

#

# load geonames.org alternate names

#

if [[ ! -r "${DATADIR}/${ALTERNATE_NAMES_TXT}" ]]; then

    pushd "${DATADIR}"

    wget "${GEONAMES_DUMP_URL}/${ALTERNATE_NAMES_ZIP}"

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

        echo "Could not download ${ALTERNATE_NAMES_ZIP}"

        exit 1

    fi

    unzip "$ALTERNATE_NAMES_ZIP"

    rm -f "$ALTERNATE_NAMES_ZIP"

    popd

fi

# import data

run_sql_cmd "\COPY alternateNames FROM '${DATADIR}/${ALTERNATE_NAMES_TXT}' DELIMITER E'\t' NULL ''"

run_sql_script "${SCRIPT_DIR}/update.alternateNames.sql"

#

# load geonames.org country info

#

if [[ ! -r "${DATADIR}/${COUNTRY_INFO_TXT}" ]]; then

    pushd "${DATADIR}"

    wget "${GEONAMES_DUMP_URL}/${COUNTRY_INFO_TXT}"

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

        echo "Could not download ${COUNTRY_INFO_TXT}"

        exit 1

    fi

    popd

fi

# import geonames country table

grep -ve '^#' "${DATADIR}/${COUNTRY_INFO_TXT}" | \

    psql -U "$DB_USER" -h "$DB_HOST" -d "$DB_NAME" -c \

    "\COPY countries FROM STDIN DELIMITER E'\t' NULL ''"

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

    echo "Could not copy countries table data."

    exit 1

fi

run_sql_script "${SCRIPT_DIR}/update.alt_country.sql"

run_sql_script "${SCRIPT_DIR}/update.alt_stateprovince.sql"

#

# load geonames.org hierarchy info

#

if [[ ! -r "${DATADIR}/${HIERARCHY_TXT}" ]]; then

    pushd "${DATADIR}"

    wget "${GEONAMES_DUMP_URL}/${HIERARCHY_ZIP}"

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

        echo "Could not download ${HIERARCHY_ZIP}"

        exit 1

    fi

    unzip "$HIERARCHY_ZIP"

    rm -f "$HIERARCHY_ZIP"

    popd

fi

# import geonames country table

run_sql_cmd "\COPY hierarchy FROM '${DATADIR}/${HIERARCHY_TXT}' DELIMITER E'\t' NULL ''"

run_sql_script "${SCRIPT_DIR}/update.hierarchy.sql"

# augment geonames with some additional places we need, and update

# hierarchy table accordingly

run_sql_script "${SCRIPT_DIR}/update.geonames.sql"

echo "Update geonames.org tables successfully completed."