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;