/*

  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;