/*
  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 2 (County) --
----------------------
BEGIN;

TRUNCATE gadm_county_lookup;

-- map gadm2 level2 to geonames ADM2
INSERT INTO gadm_county_lookup (countyid)
  SELECT DISTINCT geonameid
    FROM geonames
    WHERE featurecode='ADM2';
-- INSERT 0 32374
-- Time: 13177.539 ms

-- try matching against geonames (names and alternatenames)
UPDATE gadm_county_lookup gcl
  SET name_0 = gadm.name_0,
      name_1 = gadm.name_1,
      name_2 = gadm.name_2
  FROM (SELECT DISTINCT name_0, name_1, name_2 FROM gadm2) gadm,
       geonames g,
       hierarchy h,
       gadm_stateprovince_lookup gsl
  WHERE (lower(gadm.name_2)=lower(g.name)
         OR lower(gadm.name_2) =
            ANY (string_to_array(lower(g.alternatenames), ',')))
    AND gcl.countyid = g.geonameid
    AND gcl.countyid = h.childid
    AND h.parentid = gsl.stateprovinceid
    AND gsl.name_0 = gadm.name_0
    AND gsl.name_1 = gadm.name_1
    AND g.featurecode='ADM2';
-- UPDATE 12352
-- Time: 27390.357 ms

-- try matching against alternatenames table
UPDATE gadm_county_lookup gcl
  SET name_0 = gadm.name_0,
      name_1 = gadm.name_1,
      name_2 = gadm.name_2
  FROM (SELECT DISTINCT name_0, name_1, name_2 FROM gadm2) gadm,
       alternatenames a,
       hierarchy h,
       geonames g,
       gadm_stateprovince_lookup gsl
  WHERE lower(gadm.name_2)=lower(a.alternatename)
    AND gcl.countyid = a.geonameid
    AND a.geonameid = g.geonameid
    AND gcl.countyid = h.childid
    AND h.parentid = gsl.stateprovinceid
    AND gsl.name_0 = gadm.name_0
    AND gsl.name_1 = gadm.name_1
    AND g.featurecode='ADM2'
    AND gcl.name_2 IS NULL;
-- UPDATE 0
-- Time: 6340.441 ms

-- map geonames '/Foo/ County' to gadm2 '/Foo/'
-- todo: other mappings like this???
UPDATE gadm_county_lookup gcl
  SET name_0 = gadm.name_0,
      name_1 = gadm.name_1,
      name_2 = gadm.name_2
  FROM (SELECT DISTINCT name_0, name_1, name_2 FROM gadm2) gadm,
       geonames g,
       hierarchy h,
       gadm_stateprovince_lookup gsl
  WHERE lower(gadm.name_2||' County')=lower(g.name)
    AND gcl.countyid = g.geonameid
    AND gcl.countyid = h.childid
    AND h.parentid = gsl.stateprovinceid
    AND gsl.name_0 = gadm.name_0
    AND gsl.name_1 = gadm.name_1
    AND g.featurecode='ADM2'
    AND gcl.name_2 IS NULL;
-- UPDATE 3000
-- Time: 22248.393 ms

COMMIT;