/*
  SQL statements carrying out country name, stateprovince name, and
  county name scrubbing on vegbien location data. Currently hard-coded
  to operate on a 'vegbien_geoscrub' table that contains the input for
  geoscrubbing, with the following columns:
     decimallatitude  (latitude, WGS84 decimal degrees)
     decimallongitude (longitude, WGS84 decimal degrees)
     country          (original asserted country name)
     stateprovince    (original asserted stateprovince name)
     county           (original asserted county name)
  (Note that the lat/lon columns are not needed for scrubbing, but are
  used later for geovalidation.)

 Misc issues to contemplate and possibly address:

  * Congo can be either "Democratic Republic of the Congo" (COD) or
    "Republic of the Congo" (COG), eh?
  * Vietnam: gadm2 seems to treat provinces as level2 units
  * Yugoslavia: no longer exists, not in geonames nor gadm2

  * Azerbaijan: Yevlakh City and Yevlakh Rayon both ADM1 in geonames,
    but just Yevlax in gadm2

  * England is not a geoname country, only the UK is

  * the general problem of historical names in cases where countries
    have either been split, combined, removed (?), or otherwise
    redefined

  * Cases with multiple alternativename matches???

 Notes:
 - the vegbien_geoscrub input used during development and testing this
   scrubbing procedure came from a select distinct on (lat, lon,
   country, state, province) from analytical_aggregate on vegbiendev

  Jim Regetz
  NCEAS
  Created Nov 2012
*/

-- define custom function to pass characters back through latin-1
-- encoding to revert errors; in principle this could introduce new
-- errors, but it seems to do more good than harm
CREATE OR REPLACE FUNCTION u2L2u(text) RETURNS text AS $$
DECLARE
    string text;
BEGIN
  string :=
    convert_from(convert($1::bytea, 'UTF8', 'LATIN1'), 'UTF8');
  RETURN string;
EXCEPTION
  WHEN data_exception THEN
    RETURN $1;
END;
$$ LANGUAGE plpgsql;

-------------------
-- scrub country --
-------------------

CREATE TABLE vcountry AS
  SELECT DISTINCT country
    FROM vegbien_geoscrub
    WHERE country IS NOT NULL;
-- SELECT 439
-- Time: 862.413 ms
ALTER TABLE vcountry ADD COLUMN countryutf8 text;
ALTER TABLE vcountry ADD COLUMN countryid integer;

-- try to clean up character representations
UPDATE vcountry
    SET countryutf8 = u2L2u(country);
-- UPDATE 439
-- Time: 24.706 ms

-- try matching against direct country names
UPDATE vcountry vc
    SET countryid = gc.geonameid
    FROM countries gc
    WHERE lower(vc.countryutf8) = lower(gc.country);
-- UPDATE 219
-- Time: 12.384 ms

-- try matching against ISO 3166-1 alpha-3
UPDATE vcountry vc
    SET countryid = gc.geonameid
    FROM countries gc
    WHERE vc.countryutf8=gc.iso3
      AND vc.countryid IS NULL;
-- UPDATE 1
-- Time: 20.146 ms

-- try matching against ISO 3166-1 alpha-2
UPDATE vcountry vc
    SET countryid = gc.geonameid
    FROM countries gc
    WHERE vc.countryutf8 = iso
      AND vc.countryid IS NULL;
-- UPDATE 64
-- Time: 3.378 ms

/*
-- try matching against FIPS, but not if already matched (i.e.,
-- the 2-digit ISO match takes preference)
UPDATE vcountry vc
    SET countryid = gc.geonameid
    FROM countries gc
    WHERE vc.countryutf8 = fips
      AND vc.countryid IS NULL;
-- UPDATE 1
-- Time: 2.514 ms
*/

-- try matching against BIEN alt country names
UPDATE vcountry vc
    SET countryid = c.geonameid
    FROM alt_country a, countries c
    WHERE vc.countryutf8 = a.alternatename
      AND a.country = c.country
      AND vc.countryid IS NULL;
-- UPDATE 21
-- Time: 4.769 ms

-- try case-insensitive matching on geonames.org 'alternatenames'
-- note: this is a little sloppy because the set condition will return
-- more than one row if there are more than one matched alternate
-- country names, and which one gets used is non-deterministic; however,
-- we'll go back and remove those in the next update (specifically for
-- the cases where there are more than one *unique* matched countries)
UPDATE vcountry vc
    SET countryid = gc.geonameid
    FROM alternatenames a, countries gc
    WHERE lower(vc.countryutf8) = lower(a.alternatename)
      AND a.geonameid = gc.geonameid
      AND vc.countryid IS NULL;
-- UPDATE 76
-- Time: 22019.592 ms

-- go back and remove if altname lookup was ambiguous
UPDATE vcountry vc
    SET countryid = NULL
    WHERE countryutf8 IN (
        SELECT countryutf8
          FROM (
            SELECT DISTINCT vc.countryutf8, gc.iso3
              FROM vcountry vc,
                   alternatenames a,
                   countries gc
              WHERE lower(vc.countryutf8) = lower(a.alternatename)
                AND a.geonameid = gc.geonameid) lookup
          GROUP BY countryutf8
          HAVING COUNT(*) > 1);
-- UPDATE 2
-- Time: 2296.257 ms
-- Removes: Congo, CONGO

-- merge in GADM country names (our standard names)
ALTER TABLE vcountry ADD COLUMN countrystd text;
UPDATE vcountry vc
  SET countrystd = name_0
  FROM gadm_country_lookup gadm0
  WHERE vc.countryid=gadm0.countryid;
-- UPDATE 377
-- Time: 6.254 ms


-------------------------
-- scrub stateprovince --
-------------------------

CREATE TABLE vstate AS
  SELECT DISTINCT countryid, stateprovince
  FROM vegbien_geoscrub join vcountry using (country)
  WHERE countryid IS NOT NULL
    AND stateprovince IS NOT NULL;
-- SELECT 3312
-- Time: 4164.948 ms
ALTER TABLE vstate ADD COLUMN stateprovinceutf8 text;
ALTER TABLE vstate ADD COLUMN stateprovinceid integer;

-- try to clean up character representations
UPDATE vstate
    SET stateprovinceutf8 = u2L2u(stateprovince);
-- UPDATE 3312
-- Time: 92.702 ms

-- replace common mojibake with valid utf8 replacement character
-- note: second pass of u2L2u would do the same thing, but this is a
-- little more explicit about the intended effect
UPDATE vstate
    SET stateprovinceutf8 = replace(stateprovinceutf8, '�', chr(65533));

-- manually replace html character codes found in the names
-- todo: use some library to do this exhaustively rather than using
-- manually specified updates
UPDATE vstate
    SET stateprovinceutf8 = replace(stateprovinceutf8, ''', chr(39));
UPDATE vstate
    SET stateprovinceutf8 = replace(stateprovinceutf8, 'á', chr(225));
UPDATE vstate
    SET stateprovinceutf8 = replace(stateprovinceutf8, 'ã', chr(227));
UPDATE vstate
    SET stateprovinceutf8 = replace(stateprovinceutf8, 'é', chr(233));
UPDATE vstate
    SET stateprovinceutf8 = replace(stateprovinceutf8, 'é', chr(233));
UPDATE vstate
    SET stateprovinceutf8 = replace(stateprovinceutf8, 'í', chr(237));
UPDATE vstate
    SET stateprovinceutf8 = replace(stateprovinceutf8, 'ñ', chr(241));
UPDATE vstate
    SET stateprovinceutf8 = replace(stateprovinceutf8, 'ó', chr(243));
UPDATE vstate
    SET stateprovinceutf8 = replace(stateprovinceutf8, 'ô', chr(244));
UPDATE vstate
    SET stateprovinceutf8 = replace(stateprovinceutf8, 'ü', chr(252));

-- try matching against direct stateprovince names
UPDATE vstate vs
    SET stateprovinceid = gn.geonameid
    FROM geonames gn,
         countries c
    WHERE lower(vs.stateprovinceutf8) = lower(gn.name)
      AND gn.countrycode=c.iso
      AND c.geonameid=vs.countryid
      AND gn.featurecode='ADM1';
-- UPDATE 238
-- Time: 2919.512 ms

-- try case-insensitive matching on geonames.org 'alternatenames'
-- note: this is a little sloppy because the set condition will return
-- more than one row if there are more than one matched alternate
-- stateprovince names, and which one gets used is non-deterministic
UPDATE vstate vs
    SET stateprovinceid = gn.geonameid
    FROM alternatenames a,
         geonames gn,
         countries c
    WHERE lower(vs.stateprovinceutf8) = lower(a.alternatename)
      AND a.geonameid = gn.geonameid
      AND gn.countrycode=c.iso
      AND c.geonameid=vs.countryid
      AND gn.featurecode='ADM1'
      AND vs.stateprovinceid IS NULL;
-- UPDATE 1281
-- Time: 5229.274 ms

-- try doing a 'like' query that ignores replacement characters (�)
UPDATE vstate vs
    SET stateprovinceid = gn.geonameid
    FROM alternatenames a,
         geonames gn,
         countries c
    WHERE a.alternatename ILIKE
          regexp_replace(stateprovinceutf8, chr(65533), '_')
      AND stateprovinceutf8 LIKE '%'||chr(65533)||'%'
      AND a.geonameid = gn.geonameid
      AND gn.countrycode=c.iso
      AND c.geonameid=vs.countryid
      AND gn.featurecode='ADM1'
      AND vs.stateprovinceid IS NULL;
-- UPDATE 112
-- Time: 6232.106 ms

-- try matching against alternatenames column in geonames table
UPDATE vstate vs
    SET stateprovinceid = gn.geonameid
    FROM geonames gn,
         countries c
    WHERE lower(vs.stateprovinceutf8) =
          ANY (string_to_array(lower(gn.alternatenames), ','))
      AND gn.countrycode=c.iso
      AND c.geonameid=vs.countryid
      AND gn.featurecode='ADM1'
      AND vs.stateprovinceid IS NULL;
-- UPDATE 220
-- Time: 5053.520 ms

-- try matching against BIEN alt stateprovince names
UPDATE vstate vs
    SET stateprovinceid = g.geonameid
    FROM alt_stateprovince asp,
         countries c,
         geonames g
    WHERE lower(vs.stateprovinceutf8) = lower(asp.alternatename)
      AND asp.country = c.country
      AND c.iso = g.countrycode
      AND asp.stateprovince = g.name
      AND g.featurecode='ADM1'
      AND vs.stateprovinceid IS NULL;
-- UPDATE 45
-- Time: 3452.274 ms

-- todo: the same stuff done for country to deal with Congo-type case

-- merge in GADM stateprovince names (our standard names)
ALTER TABLE vstate ADD COLUMN stateprovincestd text;
UPDATE vstate vs
  SET stateprovincestd = name_1
  FROM gadm_stateprovince_lookup gadm1
  WHERE vs.stateprovinceid=gadm1.stateprovinceid;
-- UPDATE 1896
-- Time: 23.946 ms

-- match any remaining unknowns directly to gadm
UPDATE vstate vs
  SET stateprovincestd = name_1
  FROM (SELECT DISTINCT name_0, name_1 FROM gadm2) gadm,
       vcountry vc
  WHERE vc.countryid = vs.countryid
    AND countrystd = name_0
    AND lower(stateprovinceutf8) = lower(name_1)
    AND stateprovincestd IS NULL;
-- UPDATE 55
-- Time: 4799.837 ms

-- try to recover geonameids for any of these so we can drill down to
-- the next level
UPDATE vstate vs
  SET stateprovinceid = gadm1.stateprovinceid
  FROM gadm_country_lookup gadm0,
       gadm_stateprovince_lookup gadm1
  WHERE vs.countryid = gadm0.countryid
    AND gadm0.name_0 = gadm1.name_0
    AND vs.stateprovincestd = gadm1.name_1
    AND vs.stateprovinceid IS NULL;
-- UPDATE 0
-- Time: 13.882 ms


/* TODO: is this still needed???
-- now reverse map
INSERT INTO gadm_stateprovince_lookup
select name_0, alternatename as name_1, g.geonameid
  from vstate vs,
       alt_stateprovince asp,
       geonames g,
       countries c,
       gadm_country_lookup gc
  where vs.stateprovinceid=g.geonameid
    AND asp.stateprovince=g.name
    AND vs.countryid=c.geonameid
    AND vs.countryid=gc.countryid
    AND asp.country=c.country
    AND vs.stateprovinceid not in (select stateprovinceid from gadm_stateprovince_lookup);
-- INSERT 0 4
-- Time: 20.228 ms
*/


------------------
-- scrub county --
------------------

CREATE TABLE vcounty AS
  SELECT DISTINCT countryid, stateprovinceid, county
  FROM vegbien_geoscrub
  JOIN vcountry USING (country)
  JOIN vstate USING (countryid, stateprovince)
  WHERE countryid IS NOT NULL
    AND stateprovinceid IS NOT NULL
    AND county IS NOT NULL;
-- SELECT 18715
-- Time: 3590.725 ms
ALTER TABLE vcounty ADD COLUMN countyutf8 text;
ALTER TABLE vcounty ADD COLUMN countyid integer;

-- try to clean up character representations
UPDATE vcounty
    SET countyutf8 = u2L2u(county);
-- UPDATE 18715
-- Time: 683.781 ms

-- replace common mojibake with valid utf8 replacement character
-- note: second pass of u2L2u would do the same thing, but this is a
-- little more explicit about the intended effect
UPDATE vcounty
    SET countyutf8 = replace(countyutf8, '�', chr(65533));
-- UPDATE 18715
-- Time: 153.395 ms

-- clean up some common municipality label abbreviations
-- todo: other fixes like this; check what brad did for bien2 on this front?
UPDATE vcounty
    SET countyutf8 = regexp_replace(countyutf8, ' Co[.]?$', ' County', 'i');

-- try matching against geonames (names and alternatenames)
UPDATE vcounty vc
  SET countyid = gn.geonameid
  FROM geonames gn,
       hierarchy h
  WHERE vc.stateprovinceid = h.parentid
    AND h.childid=gn.geonameid
    AND (lower(vc.countyutf8) = lower(gn.name)
        OR lower(vc.countyutf8) =
          ANY (string_to_array(lower(gn.alternatenames), ',')))
    AND gn.featurecode='ADM2';
-- UPDATE 6673
-- Time: 8369.737 ms

-- use alternatenames
UPDATE vcounty vc
    SET countyid = gn.geonameid
  FROM geonames gn,
       alternatenames a,
       hierarchy h
  WHERE vc.stateprovinceid = h.parentid
    AND h.childid = gn.geonameid
    AND a.geonameid = gn.geonameid
    AND lower(vc.countyutf8) = lower(a.alternatename)
    AND gn.featurecode='ADM2'
    AND vc.countyid IS NULL;
-- UPDATE 0
-- Time: 11255.926 ms

ALTER TABLE vcounty ADD COLUMN countystd text;

-- merge in GADM county names (our standard names)
-- for cases where we have geonameid
UPDATE vcounty vs
  SET countystd = name_2
  FROM gadm_county_lookup gadm1
  WHERE vs.countyid=gadm1.countyid
    AND countystd IS NULL;
-- UPDATE 6673
-- Time: 118.378 ms

-- match directly to gadm
UPDATE vcounty vco
  SET countystd = name_2
  FROM (SELECT DISTINCT name_0, name_1, name_2 FROM gadm2) gadm,
       vcountry vc,
       vstate vs
  WHERE vco.countryid = vc.countryid
    AND vco.stateprovinceid = vs.stateprovinceid
    AND countrystd = name_0
    AND stateprovincestd = name_1
    AND lower(countyutf8) = lower(name_2)
    AND countystd IS NULL;
-- UPDATE 5027
-- Time: 1410.377 ms

-----------------------------
-- put everything together --
-----------------------------

-- reconstitute the whole dang thang

-- new approach
CREATE TABLE geoscrub AS
SELECT decimallatitude, decimallongitude,
       country, stateprovince, county,
       countryid, stateprovinceid, countyid,
       countrystd, stateprovincestd, countystd
  FROM vegbien_geoscrub v
  LEFT JOIN vcountry USING (country)
  LEFT JOIN vstate USING (countryid, stateprovince)
  LEFT JOIN vcounty USING (countryid, stateprovinceid, county);
-- SELECT 1707970
-- Time: 26172.154 ms

-- try to squeeze out a few more direct stateprovince mappings to gadm2
UPDATE geoscrub
  SET stateprovincestd = name_1
  FROM (SELECT DISTINCT name_0, name_1 FROM gadm2) gadm
  WHERE country IS NOT NULL
    AND stateprovince IS NOT NULL
    AND countrystd = name_0
    AND stateprovincestd IS NULL
    AND stateprovince = name_1;
-- UPDATE 0
-- Time: 3630.973 ms

-- try to squeeze out a few more direct county mappings to gadm2
UPDATE geoscrub
  SET countystd = name_2
  FROM (SELECT DISTINCT name_0, name_1, name_2 FROM gadm2) gadm
  WHERE country IS NOT NULL
    AND stateprovince IS NOT NULL
    AND county IS NOT NULL
    AND countrystd = name_0
    AND stateprovincestd = name_1
    AND countystd IS NULL
    AND county = name_2;
-- UPDATE 69
-- Time: 3982.715 ms