BEGIN;

-----------------------

-- Level 0 (Country) --

-----------------------

-- maps gadm2 level0 to geonames countries

CREATE TABLE gadm_country_lookup (

        countryid integer primary key references geonames (geonameid),

        name_0 text

);

------------------------------

-- Level 1 (State/Province) --

------------------------------

-- maps gadm2 level1 to geonames ADM1

CREATE TABLE gadm_stateprovince_lookup (

        stateprovinceid integer primary key references geonames (geonameid),

        name_0 text,

        name_1 text

);

/*

-- another way to create gadm_stateprovince_lookup, though this won't

-- pick up multiple matches from geonames (good or bad??)

CREATE TABLE gadm_stateprovince_lookup AS

SELECT DISTINCT name_0, name_1

  FROM gadm2;

ALTER TABLE gadm_stateprovince_lookup ADD COLUMN stateprovinceid integer;

UPDATE gadm_stateprovince_lookup gs

  SET stateprovinceid = g.geonameid

  FROM gadm_country_lookup gc,

       alternatenames a,

       geonames g,

       countries c

  WHERE gs.name_0=gc.name_0

    AND gs.name_1=a.alternatename

    AND a.geonameid=g.geonameid

    AND g.countrycode=c.iso

    AND gc.countryid=c.geonameid

    AND g.featurecode='ADM1';

-- UPDATE 2137

-- Time: 600.345 ms

*/

----------------------

-- Level 2 (County) --

----------------------

-- maps gadm2 level2 to geonames ADM2

CREATE TABLE gadm_county_lookup (

        countyid integer primary key references geonames (geonameid),

        name_0 text,

        name_1 text,

        name_2 text

);

COMMIT;

BEGIN;

-- define custom function to pass characters back through latin-1

-- encoding to revert errors; in principle this could introduce new

-- errors, but it seems to do more good than harm

CREATE OR REPLACE FUNCTION u2L2u(text) RETURNS text AS $$

DECLARE

    string text;

BEGIN

  string :=

    convert_from(convert($1::bytea, 'UTF8', 'LATIN1'), 'UTF8');

  RETURN string;

EXCEPTION

  WHEN data_exception THEN

    RETURN $1;

END;

$$ LANGUAGE plpgsql;

CREATE TABLE geonames (

    geonameid serial primary key,

    name text,

    asciiname text,

    alternatenames text,

    latitude numeric,

    longitude numeric,

    featureclass char(1),

    featurecode text,

    countrycode char(2),

    cc2 char(60),

    admin1code text,

    admin2code text,

    admin3code text,

    admin4code text,

    population bigint,

    elevation int,

    dem int,

    timezone text,

    modification date

);

--

-- load geonames.org alternate names

--

CREATE TABLE alternateNames (

    alternateNameId serial primary key,

    geonameid int references geonames (geonameid),

    isolanguage varchar(7),

    alternateName varchar(200),

    isPreferredName char(1),

    isShortName char(1),

    isColloquial char(1),

    isHistoric char(1)

);

--

-- load geonames.org country info

--

CREATE TABLE countries (

    iso char(2),

    iso3 char(3),

    isonumeric text,

    fips text,

    country text UNIQUE,

    capital text,

    area text,

    population text,

    continent text,

    tld text,

    currencyCode text,

    currencyName text,

    phone text,

    postalCodeFormat text,

    postalCodeRegex text,

    languages text,

    geonameid int,

    neighbours text,

    equivalentFipsCode text

);

--

-- load geonames.org hierarchy info

--

CREATE TABLE hierarchy (

    parentId int,-- references geonames (geonameid),

    childId int,-- references geonames (geonameid),

    type text

);

--

-- tables for additional custom name-scrub mappings

--

-- table for custom country alternate names that we need

CREATE TABLE alt_country (

    country text references countries (country),

    alternatename text

);

-- table for custom stateprovince alternate names that we need

CREATE TABLE alt_stateprovince (

    country text references countries (country),

    stateprovince text,

    alternatename text

);

COMMIT;

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

/*

  SQL statements to create tables for country name, stateprovince name, and

  county name scrubbing on vegbien location data. Currently hard-coded

  to operate on a 'vegbien_geoscrub' table that contains the input for

  geoscrubbing, with the following columns:

     decimallatitude  (latitude, WGS84 decimal degrees)

     decimallongitude (longitude, WGS84 decimal degrees)

     country          (original asserted country name)

     stateprovince    (original asserted stateprovince name)

     county           (original asserted county name)

  (Note that the lat/lon columns are not needed for scrubbing, but are

  used later for geovalidation.)

  Jim Regetz

  NCEAS

  Created Nov 2012

*/

BEGIN;

CREATE TABLE geoscrub (

    decimallatitude double precision,

    decimallongitude double precision,

    country text,

    stateprovince text,

    county text,

    countryid integer,

    stateprovinceid integer,

    countyid integer,

    countrystd text,

    stateprovincestd text,

    countystd text,

    latlonvalidity int,

    countryvalidity int,

    stateprovincevalidity int,

    countyvalidity int,

    pointcountry text,

    pointstateprovince text,

    pointcounty text

);

SELECT AddGeometryColumn('public', 'geoscrub', 'geom', 4326, 'POINT', 2 );

ALTER TABLE geoscrub ADD COLUMN geog geography(POINT, 4326);

CREATE TABLE vegbien_geoscrub (

    country text,

    stateprovince text,

    county text,

    decimalLatitude double precision,

    decimalLongitude double precision

);

CREATE TABLE vcountry (

    country text,

    countryutf8 text,

    countryid integer,

    countrystd text

);

CREATE TABLE vstate (

    countryid integer,

    stateProvince text,

    stateprovinceutf8 text,

    stateprovinceid integer,

    stateprovincestd text

);

CREATE TABLE vcounty (

    countryid integer,

    stateprovinceid integer,

    county text,

    countyutf8 text,

    countyid integer,

    countystd text

);

COMMIT;

1. install.sh