/*
  SQL statements to generate tables mapping geonames features at each
  relevant administative level (country, state/province, county/parish)
  to GADM2 features.

  Todo:
  * Be tolerant to presence/absence of administrative qualifiers in
    names? (Already doing this mildly for County/Co/Co.)
    - Municipality
    - Barrio
    - District

  * Get rid of rudundancy
  * Try matching against GADM2 varname_* columns?

  Jim Regetz
  NCEAS
  Created Nov 2012
*/

-----------------------
-- Level 0 (Country) --
-----------------------
BEGIN;

TRUNCATE gadm_country_lookup;

DROP INDEX IF EXISTS "gadm_country_lookup_name_0_idx";

-- map gadm2 level0 to geonames countries
INSERT INTO gadm_country_lookup (countryid)
  SELECT DISTINCT geonameid
    FROM countries
    WHERE geonameid IS NOT NULL;
-- INSERT 0 250
-- Time: 20.770 ms
-- EXPLAIN ANALYZE 
UPDATE gadm_country_lookup lu
  SET name_0 = gadm.name_0
  FROM (SELECT DISTINCT iso, name_0 FROM gadm2) gadm,
       countries c
  WHERE lu.countryid = c.geonameid
    AND c.iso3 = gadm.iso;
-- UPDATE 248
-- Time: 3562.601 ms

CREATE INDEX "gadm_country_lookup_name_0_idx" ON gadm_country_lookup (name_0);

COMMIT;