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;

BEGIN;

TRUNCATE geoscrub;

DROP INDEX IF EXISTS "geoscrub_geom_gist";

DROP INDEX IF EXISTS "geoscrub_geog_gist";

DROP INDEX IF EXISTS "geoscrub_countrystd_idx";

DROP INDEX IF EXISTS "geoscrub_stateprovincestd_idx";

DROP INDEX IF EXISTS "geoscrub_countystd_idx";

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

-- put everything together --

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

-- reconstitute the whole dang thang

-- populate geoscrub from vegbien_geoscrub data

INSERT INTO geoscrub

SELECT decimallatitude, decimallongitude,

       country, stateprovince, county,

       countryid, stateprovinceid, countyid,

       countrystd, stateprovincestd, countystd

  FROM vegbien_geoscrub v

  LEFT JOIN vcountry USING (country)

  LEFT JOIN vstate USING (countryid, stateprovince)

  LEFT JOIN vcounty USING (countryid, stateprovinceid, county);

-- SELECT 1707970

-- Time: 26172.154 ms

-- try to squeeze out a few more direct stateprovince mappings to gadm2

UPDATE geoscrub

  SET stateprovincestd = name_1

  FROM (SELECT DISTINCT name_0, name_1 FROM gadm2) gadm

  WHERE country IS NOT NULL

    AND stateprovince IS NOT NULL

    AND countrystd = name_0

    AND stateprovincestd IS NULL

    AND stateprovince = name_1;

-- UPDATE 0

-- Time: 3630.973 ms

-- try to squeeze out a few more direct county mappings to gadm2

UPDATE geoscrub

  SET countystd = name_2

  FROM (SELECT DISTINCT name_0, name_1, name_2 FROM gadm2) gadm

  WHERE country IS NOT NULL

    AND stateprovince IS NOT NULL

    AND county IS NOT NULL

    AND countrystd = name_0

    AND stateprovincestd = name_1

    AND countystd IS NULL

    AND county = name_2;

-- UPDATE 69

-- Time: 3982.715 ms

COMMIT;

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;

TRUNCATE vegbien_geoscrub;

TRUNCATE vcountry;

TRUNCATE vstate;

TRUNCATE vcounty;

COMMIT;

BEGIN;

INSERT INTO vcountry

INSERT INTO vstate

INSERT INTO vcounty

COMMIT;

SCRIPT_DIR=$(dirname $0)

DATADIR="${SCRIPT_DIR}/input"

echo "Loading vegbien data from ${DATAFILE}"

# clear previous data

psql -e -d geoscrub --set ON_ERROR_STOP=1 < "${SCRIPT_DIR}/truncate.vegbien_geoscrub.sql"

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

    echo "Could not clear data from vegbien_geoscrub tables."

    exit 1

fi

/*

  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;