BEGIN;

TRUNCATE geonames;

TRUNCATE alternateNames;

TRUNCATE countries;

TRUNCATE hierarchy;

DROP INDEX IF EXISTS geonames_countrycode_idx;

DROP INDEX IF EXISTS geonames_featurecode_idx;

DROP INDEX IF EXISTS geonames_name_idx;

DROP INDEX IF EXISTS "alternatenames_alternatename_idx";

DROP INDEX IF EXISTS "alternatenames_geonameid_idx";

DROP INDEX IF EXISTS hierarchy_parentid_idx;

DROP INDEX IF EXISTS hierarchy_childid_idx;

COMMIT;

BEGIN;

SELECT setval('alternatenames_alternatenameid_seq', (SELECT max(alternatenameid) FROM alternatenames));

-- 4m45.368s

-- index alternatenames

CREATE INDEX "alternatenames_alternatename_idx" ON alternateNames (alternateName);

-- 10m42.827s

CREATE INDEX "alternatenames_geonameid_idx" ON alternateNames (geonameid);

COMMIT;

BEGIN;

--

-- insert additional custom name-scrub mappings

--

-- add some custom country alternate names that we need

INSERT INTO alt_country

    (country, alternatename)

    VALUES

    ('Central African Republic', 'Central African Repu')

  , ('British Virgin Islands', 'Virgin Islands (U.K.)')

  , ('Democratic Republic of the Congo', 'Congo, The Democratic Republic of the')

  , ('Falkland Islands', 'Falkland Isl')

  , ('Falkland Islands', 'Falkland Islands (Malvinas)')

  , ('Faroe Islands', 'Faeroe Isl.')

  , ('French Guiana', 'Guyana Francesa')

  , ('Iran', 'Iran, Islamic Republic of')

  , ('Mexico', E'Mexico\rMexico')

  , ('Nicaragua', 'Ncaragua')

  , ('Norfolk Island', 'Norfolk Isl')

  , ('North Korea', 'Korea, Democratic People''s Republic of')

  , ('Solomon Islands', 'Solomon Isl')

  , ('South Georgia and the South Sandwich Islands', 'South Georgia and the South Sand')

  , ('South Korea', 'Korea (Republic of)')

  , ('South Korea', 'Korea, Republic of')

  , ('U.S. Virgin Islands', 'US Virgin Islands')

  , ('U.S. Virgin Islands', 'Virgin Islands (U.S.)')

  , ('United States', 'ESTADOS UNIDOS DE AMERICA')

  , ('United States', 'EUA')

  , ('Vietnam', 'VIETNAM [Socialist Republic of V')

;

COMMIT;

BEGIN;

CREATE INDEX hierarchy_parentid_idx ON hierarchy (parentId);

CREATE INDEX hierarchy_childid_idx ON hierarchy (childId);

COMMIT;

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

BEGIN;

--

-- insert additional custom name-scrub mappings

--

-- add some custom stateprovince alternate names that we need

INSERT INTO alt_stateprovince

    (country, stateprovince, alternatename)

    VALUES

    ('Canada', 'British Columbia', 'B.C.')

  , ('Canada', 'Newfoundland and Labrador', 'Newfoundland & Labrador')

  , ('Canada', 'Newfoundland and Labrador', 'New Foundland And Labrador')

  , ('Canada', 'Newfoundland and Labrador', 'Labrador & Newfoundland')

  , ('Canada', 'Northwest Territories', 'North West Territories')

  , ('Canada', 'Northwest Territories', 'Northwest Territorie')

  , ('Canada', 'Northwest Territories', 'NWT')

  , ('Canada', 'Québec', 'Quebéc')

  , ('Canada', 'Québec', 'Pq')

  , ('Canada', 'Québec', 'PQ')

  , ('United States', 'Hawaii', 'Hawai`i')

  , ('United States', 'North Carolina', 'N. Carolina')

  , ('United States', 'North Dakota', 'N. Dakota')

  , ('United States', 'South Dakota', 'S. Dakota')

  , ('Kenya', 'Coast Province', 'Coast')

  , ('Mexico', 'Estado de Baja California', 'Baja California Norte')

  , ('Mexico', 'Estado de Baja California', 'Baja California (Norte)')

  , ('Mexico', 'Estado de Baja California', 'Baja California, Norte de')

  , ('Mexico', 'Estado de Baja California', 'Baja California (state)')

  , ('Mexico', 'Estado de Baja California Sur', 'Baja California, Sur de')

  , ('Mexico', 'Estado de Baja California Sur', 'Baja California Sur (state)')

  , ('Mexico', 'Estado de Baja California Sur', 'Baja Califronia')

  , ('Mexico', 'Estado de Baja California Sur', 'Baja Cal. Sur')

  , ('Ecuador', 'Provincia de Sucumbíos', 'Sucumbíos')

;

-- additions for mapping to gadm2 level 1 units

--  - Los Lagos -> Los Rios is imperfect but needed for gadm

--  - Orellana -> Napo is imperfect (wrong??) but needed for gadm

--  - Mapping both Concelho de Macau and Concelho das Ilhas to Ilhas

--  - Nassa seems to be a gadm misspelling? (Niassa)

-- not addressed:

--  - Bahamas: unmapped gadm level1 entities may be obsolete?

--  - French Guiana has arrondissements as adm0 in gadm, low level?

--  - French Polynesia has no adm1 in gadm

--  - Madagascar: Sava is (was?) inside gadm's Antsiranana province

--  - Philippines: GN has 17 regions at level1, gadm has 82 provinces

--  - Sri Lanka: GN ADM2 matches gadm level1; bien data side with GN

--  - Venezuela: Dependencias Federales don't seem to be in GADM

--  - Vietnam: GN ADM1 matches gadm level2; bien data side with GN?

INSERT INTO alt_stateprovince

    (country, stateprovince, alternatename)

    VALUES

    ('Cambodia', 'Khétt Siĕm Réab', 'Siemréab')

  , ('Chile', 'Región del Biobío', 'Bío-Bío')

  , ('Chile', 'Región del Libertador General Bernardo O’Higgins', 'Libertador General Bernardo O''Higgins')

  , ('Chile', 'Región de Los Ríos', 'Los Lagos')

  , ('Chile', 'Providencia y Santa Catalina, Departamento de Archipiélago de San Andrés', 'San Andrés y Providencia')

  , ('Dominican Republic', 'Provincia de Baoruco', 'Bahoruco')

  , ('Ecuador', 'Provincia de Morona Santiago', 'Morona Santiago')

  , ('Ecuador', 'Provincia de Napo', 'Orellana')

  , ('Ecuador', 'Provincia de Zamora Chinchipe', 'Zamora Chinchipe')

  , ('Gabon', 'Province du Woleu-Ntem', 'Wouleu-Ntem')

  , ('Ghana', 'Brong-Ahafo Region', 'Brong Ahafo')

  , ('Guatemala', 'Departamento de Quetzaltenango', 'Quezaltenango')

  , ('Haiti', 'Département de l''Artibonite', 'L''Artibonite')

  , ('Liberia', 'Grand Bassa County', 'GrandBassa')

  , ('Liberia', 'Grand Gedeh County', 'GrandGedeh')

  , ('Macao', 'Concelho de Macau', 'Ilhas')

  , ('Mozambique', 'Niassa Province', 'Nassa')

  , ('Norway', 'Østfold fylke', 'Ãstfold')

  , ('Peru', 'Región de Huánuco', 'Huánuco')

  , ('Puerto Rico', 'Guánica Municipio', 'Guánica')

  , ('Puerto Rico', 'Loíza Municipio', 'Loíza')

  , ('Puerto Rico', 'Manatí Municipio', 'Manatí')

  , ('Puerto Rico', 'Peñuelas Municipio', 'Peñuelas')

  , ('Puerto Rico', 'Río Grande Municipio', 'Río Grande')

  , ('Puerto Rico', 'San Germán Municipio', 'San Germán')

  , ('Puerto Rico', 'San Sebastián Municipio', 'San Sebastián')

  , ('Russia', 'Karachayevo-Cherkesskaya Respublika', 'Karachay-Cherkess')

  , ('Syria', 'Muḩāfaz̧at al Lādhiqīyah', 'Lattakia')

  , ('Togo', 'Région Centrale', 'Centre')

;

COMMIT;

BEGIN;

SELECT setval('geonames_geonameid_seq', (SELECT max(geonameid) FROM geonames));

CREATE INDEX geonames_countrycode_idx ON geonames (countrycode);

CREATE INDEX geonames_featurecode_idx ON geonames (featurecode);

CREATE INDEX geonames_name_idx ON geonames (name);

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

-- hierarchy table accordingly

WITH newnames AS (

  INSERT INTO geonames

    (name, alternatenames, featurecode, countrycode )

    VALUES

    ('Antananarivo', NULL, 'ADM1', 'MG'),

    ('Antsiranana', NULL, 'ADM1', 'MG'),

    ('Fianarantsoa', NULL, 'ADM1', 'MG'),

    ('Mahajanga', NULL, 'ADM1', 'MG'),

    ('Toamasina', NULL, 'ADM1', 'MG'),

    ('Toliara', 'Toliary', 'ADM1', 'MG')

  RETURNING geonameid, countrycode

)

INSERT INTO hierarchy (parentid, childid)

  SELECT c.geonameid AS parentid,

         n.geonameid AS childid

    FROM newnames n,

         countries c

    WHERE n.countrycode = c.iso;

COMMIT;