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;