#!/bin/bash

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

# data for geovalidation purposes.

#

# Basic workflow:

#  1. Create postgis-enabled template db

#  2. Create postgis-enabled db from the template

#  3. Create required tables in postgis-enabled db

#  4. Load gadm2 and geonames.org data into database

#

# For postgis installation see:

#   http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS20Ubuntu1204

#

# 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="localhost"

SCRIPT_DIR="$(dirname $0)"

function run_sudo_sql_cmd {

    local SQL_CMD="$1"

    sudo -u postgres psql -e -h "$DB_HOST" -d "$DB_NAME" -c "$SQL_CMD"

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

        echo "Error while executing SQL command."

        echo -n "You may drop the ${DB_NAME} database"

        echo " and attempt to run this $0 script again."

        exit 1

    fi

}

function run_sql_script {

    local SCRIPT="$1"

    psql -e --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

}

# create postgis template if one doesn't already exist

echo "Setting up template_postgis database..."

sudo -u postgres createdb template_postgis

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

    # successfully created template_postgis, which means it didn't already exist

    sudo -u postgres psql -e -d template_postgis < "${SCRIPT_DIR}"/update.template_postgis.sql

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

        echo "Could not create postgis extesion on template_postgis database."

        exit 1

    fi

fi

# create postgis db from template

echo "Creating the ${DB_NAME} database..."

createdb -U "$DB_USER" -h "$DB_HOST" "$DB_NAME" -T template_postgis

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

    echo "Could not create ${DB_NAME} database."

    exit 1

fi

run_sudo_sql_cmd "ALTER TABLE spatial_ref_sys OWNER TO ${DB_USER}"

run_sudo_sql_cmd "ALTER VIEW geography_columns OWNER TO ${DB_USER}"

run_sudo_sql_cmd "ALTER VIEW geometry_columns OWNER TO ${DB_USER}"

echo "Setting up ${DB_NAME} database tables..."

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

run_sql_script "${SCRIPT_DIR}"/install.geonames-to-gadm.sql

run_sql_script "${SCRIPT_DIR}"/install.geoscrub.sql

echo "Install successfully completed."

#!/bin/bash

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

# data for geovalidation purposes.

#

# Basic workflow:

#  1. Load gadm2 data into database

#  2. Load geonames data into database

#  3. Update geonames to gadm2 data tables

# 

# 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

}

echo "Updating geoscrub validation data."

"${SCRIPT_DIR}"/update_gadm_data.sh

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

    echo "Could not update ${DB_NAME} database with GADM data."

    exit 1

fi

"${SCRIPT_DIR}"/update_geonames_data.sh

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

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

    exit 1

fi

run_sql_script "${SCRIPT_DIR}/geonames-to-gadm.country.sql"

run_sql_script "${SCRIPT_DIR}/geonames-to-gadm.stateprovince.sql"

run_sql_script "${SCRIPT_DIR}/geonames-to-gadm.county.sql"

echo "Validation data successfully updated."

BEGIN;

CREATE EXTENSION IF NOT EXISTS postgis;

UPDATE pg_database SET datistemplate = true WHERE datname = 'template_postgis';

COMMIT;

BEGIN;

-- create indexes

CREATE INDEX "gadm2_iso_idx" ON gadm2 (iso);

CREATE INDEX "gadm2_geom_gist" ON gadm2 USING GIST (geom);

CREATE INDEX "gadm2_name_0_idx" ON gadm2 (name_0);

CREATE INDEX "gadm2_name_1_idx" ON gadm2 (name_1);

-- physically reorder tuples on disk in country name index order

-- nb: i _think_ this helped, but am not 100% sure

CLUSTER gadm2 USING gadm2_name_0_idx;

-- create simplified country polygons

SELECT AddGeometryColumn('public', 'gadm2', 'simple_geom', 4326, 'MULTIPOLYGON', 2 );

UPDATE gadm2 SET simple_geom = (SELECT st_simplify(geom, 0.001));

CREATE INDEX "gadm2_simple_geom_gist" ON gadm2 USING gist (simple_geom);

-- ... and create geography versions ...

-- note workaround to exclude Antarctica because it has an invalid

-- western extent of -180.000015258789

ALTER TABLE gadm2 ADD COLUMN simple_geog geography;

UPDATE gadm2 SET simple_geog = simple_geom::geography WHERE name_0<>'Antarctica';

CREATE INDEX "gadm2_simple_geog_gist" ON gadm2 USING gist (simple_geog);

COMMIT;

#!/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="localhost"

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="${SCRIPT_DIR}/gadm_v2_shp"

#

# 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" -h "$DB_HOST" -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" -h "$DB_HOST" -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" -h "$DB_HOST" -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."