# 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

#

# Notes:

#  * One of the INSERT statements below contains the string 'Hawai\`i'.

#    The backslash is there to escape the backtick for bash, so that

#    what gets inserted into the database is 'Hawai`i'. If manually

#    copy-and-pasting the statement into psql rather than running as a

#    script (i.e., not having bash interpret the command), the backslash

#    must not be included.

#

# Jim Regetz

# NCEAS

# Created Nov 2012

# !! this is specific to regetz's testing environment !!

DATADIR="$HOME/biengeo/geonames"

cd ${DATADIR}

#

# load geonames.org geonames

#

wget http://download.geonames.org/export/dump/allCountries.zip

unzip allCountries.zip

psql -c \

   '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

    )' geoscrub

# import data

psql -c "COPY geonames FROM '${DATADIR}/allCountries.txt' DELIMITER E'\t' NULL ''" geoscrub

psql -c \ "SELECT setval('geonames_geonameid_seq', (SELECT max(geonameid) FROM geonames))" geoscrub

psql -c \ "CREATE INDEX geonames_countrycode_idx ON geonames (countrycode)" geoscrub

psql -c "CREATE INDEX geonames_name_idx ON geonames (name)" geoscrub

#

# load geonames.org alternate names

#

wget http://download.geonames.org/export/dump/alternateNames.zip

unzip alternateNames.zip

psql -c \

   '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)

    )' geoscrub

# import data

psql -c "COPY alternateNames FROM '${DATADIR}/alternateNames.txt' DELIMITER E'\t' NULL ''" geoscrub

psql -c \ "SELECT setval('alternatenames_alternatenameid_seq', (SELECT max(alternatenameid) FROM alternatenames))" geoscrub

# 4m45.368s

# index alternatenames

psql -c 'CREATE INDEX "alternatenames_alternatename_idx" ON alternateNames (alternateName)' geoscrub

# 10m42.827s

#

# load geonames.org country info

#

psql -c \

   '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

    )' geoscrub

# import geonames country table

grep -ve '^#' $DATADIR/countryInfo.txt | \

  psql -c "COPY countries FROM STDIN DELIMITER E'\t' NULL ''" geoscrub

#

# load geonames.org hierarchy info

#

wget http://download.geonames.org/export/dump/hierarchy.zip

unzip hierarchy.zip

psql -c \

   'CREATE TABLE hierarchy (

        parentId int,-- references geonames (geonameid),

        childId int,-- references geonames (geonameid),

        type text

    )' geoscrub

# import geonames country table

psql -c "COPY hierarchy FROM '${DATADIR}/hierarchy.txt' DELIMITER E'\t' NULL ''" geoscrub

psql -c "CREATE INDEX hierarchy_childid_idx ON hierarchy (childId)" geoscrub

#

# insert additional custom name-scrub mappings

#

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

# hierarchy table accordingly

psql -c \

"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;

" geoscrub

# add some custom country alternate names that we need

psql -c "

CREATE TABLE alt_country (

    country text references countries (country),

    alternatename text

);

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')

;" geoscrub

# add some custom stateprovince alternate names that we need

psql -c "

CREATE TABLE alt_stateprovince (

    country text references countries (country),

    stateprovince text,

    alternatename text

);

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')

;

" geoscrub

# 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?

psql -c "

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')

;" geoscrub