/*

  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;