/*
  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) --
-----------------------

-- map gadm2 level0 to geonames countries
CREATE TABLE gadm_country_lookup (
        countryid integer primary key references geonames (geonameid),
        name_0 text
);
INSERT INTO gadm_country_lookup (countryid)
  SELECT DISTINCT geonameid
    FROM countries
    WHERE geonameid IS NOT NULL;
-- INSERT 0 250
-- Time: 20.770 ms
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);

------------------------------
-- Level 1 (State/Province) --
------------------------------

-- map gadm2 level1 to geonames ADM1
CREATE TABLE gadm_stateprovince_lookup (
        stateprovinceid integer primary key references geonames (geonameid),
        name_0 text,
        name_1 text
);
INSERT INTO gadm_stateprovince_lookup (stateprovinceid)
  SELECT DISTINCT geonameid
    FROM geonames
    WHERE featurecode='ADM1';
-- INSERT 0 3841
-- Time: 7085.635 ms


-- try matching against alternatenames table
UPDATE gadm_stateprovince_lookup gs
  SET name_0 = gadm.name_0,
      name_1 = gadm.name_1
  FROM (SELECT DISTINCT iso, name_0, name_1 FROM gadm2) gadm,
       alternatenames a,
       geonames g,
       hierarchy h,
       gadm_country_lookup gc
  WHERE lower(gadm.name_1)=lower(alternatename)
    AND gs.stateprovinceid = a.geonameid
    AND a.geonameid = g.geonameid
    AND gs.stateprovinceid = h.childid
    AND h.parentid = gc.countryid
    AND gc.name_0 = gadm.name_0
    AND g.featurecode='ADM1'
    AND gs.name_1 IS NULL;
-- UPDATE 2145
-- Time: 3453.568 ms

-- try matching against geonames (names and alternatenames)
UPDATE gadm_stateprovince_lookup gs
  SET name_0 = gadm.name_0,
      name_1 = gadm.name_1
  FROM (SELECT DISTINCT iso, name_0, name_1 FROM gadm2) gadm,
       geonames g,
       hierarchy h,
       gadm_country_lookup gc
  WHERE (lower(gadm.name_1)=lower(g.name)
         OR lower(gadm.name_1) =
            ANY (string_to_array(lower(g.alternatenames), ',')))
    AND gs.stateprovinceid = h.childid
    AND h.parentid = gc.countryid
    AND gc.name_0 = gadm.name_0
    AND gs.stateprovinceid = g.geonameid
    AND g.featurecode='ADM1'
    AND gs.name_1 IS NULL;
-- UPDATE 319
-- Time: 857.885 ms


-- now again but against our manual mapping
UPDATE gadm_stateprovince_lookup gs
  SET name_0 = gadm.name_0,
      name_1 = gadm.name_1
  FROM (SELECT DISTINCT iso, name_0, name_1 FROM gadm2) gadm,
       alt_stateprovince asp,
       geonames g,
       countries c
  WHERE gadm.name_1=asp.alternatename
    AND gadm.iso=c.iso3
    AND asp.country=c.country
    AND gs.stateprovinceid = g.geonameid
    AND g.countrycode=c.iso
    AND g.name=asp.stateprovince
    AND gs.name_0 IS NULL;
-- UPDATE 29
-- Time: 777.376 ms

----------------------
-- Level 2 (County) --
----------------------

-- map 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
);
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


/*
-- 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
*/



/*
-- these geonameids match multiple gadm2 state/provinces, and that's bad
-- because we don't know which one to use for geovalidation
select stateprovinceid, name_0, array_agg(name_1) as name_1
  from gadm_stateprovince_lookup
  group by stateprovinceid, name_0
  having count(*)>1;

 stateprovinceid |  name_0  |                name_1
-----------------+----------+--------------------------------------
         3653890 | Ecuador  | {Orellana,Orellana}
          453751 | Bulgaria | {Razgrad,Ruse}
         1831095 | Cambodia | {"Phnom Penh",Kândal}
         1506272 | Russia   | {Altay,Gorno-Altay}
          128222 | Iran     | {Kermanshah,Kordestan}
         3457415 | Brazil   | {"Mato Grosso do Sul","Mato Grosso"}
          170652 | Syria    | {Damascus,"Rif Dimashq"}
(7 rows)

-- for now, manually clean up after these ambiguous cases
-- ... this countryid should only apply to Mato Grosso do Sul
DELETE FROM gadm_stateprovince_lookup
  WHERE name_0 = 'Brazil'
    AND name_1 = 'Mato Grosso'
    AND stateprovinceid = '3457415';
*/

/*
-- these gadm2 state/provinces match multiple geonameids, but i'm pretty sure
-- we don't really care in this direction
select iso, name_1, count(*)
  from (select distinct iso, name_1 from gadm2) gadm2
  left join (
      select distinct gadm2.iso,
             name_1,
             alternatenames.geonameid,
             geonames.name
        from (select distinct iso, name_1 from gadm2) gadm2
        join alternatenames on name_1=alternatename
        join geonames using (geonameid)
        join countries on countrycode=countries.iso
        where featurecode='ADM1'
          and gadm2.iso=countries.iso3) foo
    using (iso, name_1) group by iso, name_1 having count(*)>1;

 iso |   name_1    | count
-----+-------------+-------
 AZE | Yevlax      |     2
 BGR | Ruse        |     2
 BMU | Hamilton    |     2
 BRA | Mato Grosso |     2
 KAZ | Almaty      |     2
 KHM | Phnom Penh  |     2
 RUS | Altay       |     2
 RUS | Moskva      |     2
(8 rows)
*/