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;