/*

  Misc notes on how well we did scrubbing names. And lots of misc SQL
  statements for examining various scrubbing outcomes, some of which may
  not be so relevant anymore as a consequences of other changes.

Provisional (and not necessarily fully updated) summary of geoname
scrubbing performance on vegbien_geoscrub
(= geoscrub_input from vegbien schema public.2012-11-04-07-34-10.r5984)
========================================================================
1707970 total unique location determinations
  32541 distinct on country, stateprovince, county (including all nulls)
========================================================================
* Country *
1485885 assertions
1479338 mapped to geonames
        (6547 not mapped to geonames)
1479338 mapped to gadm2
        (6547 not mapped to gadm2)
        (0 cases of failed mapping to gadm2 name_0)
1479338 mapped both to geonames and to gadm2
========================================================================
* Country with State/Province *
1408955 assertions
1388525 mapped to geonames
        (20430 not mapped to geonames)
        (5576 of these had unmapped country)
1389239 mapped to gadm2
        (19716 not mapped to gadm2)
        (5576 of these had unmapped country)
1387915 mapped both to geonames and to gadm2
========================================================================
* Country with State/Province with County/Parish*
 276809 assertions
 145767 mapped to geonames
 213795 mapped to gadm2
 140983 mapped both to geonames and to gadm2
========================================================================

========================================================================
Distinct country names:
439 total unique country names (non-null)
377 recognized unique country names (non-null)
 62 unrecognized unique country names (non-null)
========================================================================
Distinct country+stateprovince names:
4764 total unique country and stateprovince (stateprovince non-null)
4704 total unique country and stateprovince (both non-null)
2944 mapped to geonames
     (1760 not mapped to geonames)
2950 mapped to gadm2
     (1754 not mapped to gadm2)
2885 mapped both to geonames and to gadm2
     (1819 not mapped to both)
------------------------------------------------------------------------
* Distinct mappedcountry+stateprovince names *
3312 total unique
1895 mapped to geonames
     (1417 not mapped to geonames)
1901 mapped to gadm2
     (1411 not mapped to gadm2)
========================================================================
Distinct country+stateprovince+county names:
27440 total unique country and stateprovince and county (county non-null)
25764 total unique country and stateprovince and county (all non-null)
8507 mapped to geonames
     (17257 not mapped to geonames)
12610 mapped to gadm2
     (13154 not mapped to gadm2)
4917 mapped both to geonames and to gadm2
     (20847 not mapped to both)
------------------------------------------------------------------------
* Distinct mappedcountry+mappedstateprovince+county names *
18715 total unique
6673 mapped to geonames
     (12042 not mapped to geonames)
11310 mapped to gadm2
     (7405 not mapped to gadm2)
========================================================================


for comparison, here are bien2 stats based on boyle/donoghue scrubbing:
753302 total unique location determinations
 have asserted country
745321 have scrubbed country
 have asserted country and stateprovince
 have scrubbed country and stateprovince
 have asserted country and stateprovince and county
 have scrubbed country and stateprovince and county

  Jim Regetz
  NCEAS
  Created Nov 2012
*/

-- Generate human-readable view of all distinct cases where we haven't
-- obtained a match for the asserted country
CREATE TEMP VIEW unmatched_country AS
SELECT count(*), country FROM
  (SELECT
      CASE WHEN length(countryutf8)>4
           THEN initcap(countryutf8)
           ELSE countryutf8
       END AS country
    FROM scrubbed
    JOIN vcountry USING (country)
    WHERE scrubbed.country IS NOT NULL
      AND scrubbed.countryid IS NULL) summary
  GROUP BY country
  ORDER BY count DESC;
-- Generate human-readable view of all distinct cases where we have
-- obtained match for the asserted country, but not stateprovince
CREATE TEMP VIEW unmatched_stateprovince AS
SELECT count(*), country, stateprovince FROM
  (SELECT countries.country AS country,
      CASE WHEN length(stateprovinceutf8)>3
           THEN initcap(stateprovinceutf8)
           ELSE stateprovinceutf8
       END AS stateprovince
    FROM scrubbed
    LEFT JOIN vstate USING (countryid, stateprovince)
    LEFT JOIN countries ON vstate.countryid=geonameid
    WHERE scrubbed.stateprovince IS NOT NULL
      AND scrubbed.stateprovinceid IS NULL
      AND scrubbed.country IS NOT NULL) summary
  GROUP BY country, stateprovince
  ORDER BY count DESC;
/*
SELECT DISTINCT countries.country,
    CASE WHEN length(stateprovinceutf8)>3
         THEN initcap(stateprovinceutf8)
         ELSE stateprovinceutf8
     END AS stateprovince
  FROM scrubbed
  JOIN vstate USING (countryid, stateprovince)
  JOIN countries ON vstate.countryid=geonameid
  WHERE scrubbed.stateprovince IS NOT NULL
    AND scrubbed.stateprovinceid IS NULL
  ORDER BY country, stateprovince;
*/

-- Generate human-readable view of all distinct cases where we have
-- obtained match for the asserted country and stateprovince, but not
-- county
/*
CREATE TEMP VIEW unmatched_county AS
SELECT DISTINCT countries.country,
    CASE WHEN length(stateprovinceutf8)>3
         THEN initcap(stateprovinceutf8)
         ELSE stateprovinceutf8
     END AS stateprovince
  FROM scrubbed
  JOIN vstate USING (countryid, stateprovince)
  JOIN countries ON vstate.countryid=geonameid
  WHERE scrubbed.stateprovince IS NOT NULL
    AND scrubbed.stateprovinceid IS NULL
  ORDER BY country, stateprovince;
*/

/*
-- which states aren't in gadm2?
select distinct iso3, s.name state
      from scrubbed
      join countries c on countryid=c.geonameid
      join geonames s on stateprovinceid=s.geonameid
except select iso, name_1 state from gadm2;

select country, count(*)
  from scrubbed s
  left join gadm_stateprovince_lookup using (stateprovinceid)
  where name_0 is null
    and stateprovinceid is not null
  group by country
  order by count desc;


select name
  from gadm2
  join alternatenames on name_1=alternatename
  join geonames using (geonameid)
  join countries on countrycode=countries.iso
  where featurecode='ADM1'
    and name_1='North Solomons';
*/


/*
-- report distinct geonames-scrubbed country+stateprovince pairs that
-- we didn't match to any gadm polygons
SELECT DISTINCT c.country, g.name
 FROM countries c
 JOIN vstate v ON geonameid=countryid
 JOIN geonames g ON g.geonameid=v.stateprovinceid
 WHERE stateprovinceid NOT IN (
           SELECT stateprovinceid FROM gadm_stateprovince_lookup
       )
 ORDER BY c.country, g.name;

-- report failed stateprovince mappings from gadm2 to geonames
SELECT countries.country AS countryname,
       name AS stateprovincename,
       count(*)
  FROM scrubbed
  LEFT JOIN gadm_stateprovince_lookup USING (stateprovinceid)
  LEFT JOIN countries ON countryid=countries.geonameid
  LEFT JOIN geonames ON stateprovinceid=geonames.geonameid
  WHERE stateprovinceid IS NOT NULL
    AND name_1 IS NULL
  GROUP BY countryname, stateprovincename
  ORDER BY countryname, stateprovincename;
*/

/*
select iso, name_1, geonameid as stateid
  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);
*/

-- how did we do?

-- country

-- percent of unique locations with asserted country names
SELECT ROUND(100*COUNT(*)/(SELECT COUNT(*)::numeric FROM scrubbed), 1) pct
  FROM scrubbed WHERE country IS NOT NULL;
-- 87.0%
-- percent of unique locations with geoname-scrubbed country names
SELECT ROUND(100*COUNT(*)/(SELECT COUNT(*)::numeric FROM scrubbed), 1) pct
  FROM scrubbed WHERE countryid IS NOT NULL;
-- 86.6%
-- percent of unique locations with gadm-matchedl country names
SELECT ROUND(100*COUNT(*)/(SELECT COUNT(*)::numeric FROM scrubbed), 1) pct
  FROM scrubbed WHERE countryid IN (SELECT countryid FROM gadm_country_lookup);
-- 86.6%

-- stateprovince

-- note: 212221 records have a stateprovince but no country! those are
--       considered missing below

-- percent of unique locations with asserted stateprovince names
SELECT ROUND(100*COUNT(*)/(SELECT COUNT(*)::numeric FROM scrubbed), 1) pct
  FROM scrubbed WHERE country IS NOT NULL AND stateprovince IS NOT NULL;
-- 82.5%
-- percent of unique locations with geoname-scrubbed stateprovince names
SELECT ROUND(100*COUNT(*)/(SELECT COUNT(*)::numeric FROM scrubbed), 1) pct
  FROM scrubbed WHERE stateprovinceid IS NOT NULL;
-- 80.2%
-- percent of unique locations with gadm-matchedl stateprovince names
SELECT ROUND(100*COUNT(*)/(SELECT COUNT(*)::numeric FROM scrubbed), 1) pct
  FROM scrubbed
  WHERE stateprovinceid IN (SELECT stateprovinceid FROM gadm_stateprovince_lookup);
-- 79.5%

-- stateprovince

-- percent of unique locations with asserted county names
SELECT ROUND(100*COUNT(*)/(SELECT COUNT(*)::numeric FROM scrubbed), 1) pct
  FROM scrubbed
  WHERE country IS NOT NULL AND stateprovince IS NOT NULL AND county IS NOT NULL;
-- 16.2%
-- percent of unique locations with geoname-scrubbed county names
SELECT ROUND(100*COUNT(*)/(SELECT COUNT(*)::numeric FROM scrubbed), 1) pct
  FROM scrubbed WHERE countyid IS NOT NULL;
-- 6.3%
-- percent of unique locations with gadm-matchedl county names
SELECT ROUND(100*COUNT(*)/(SELECT COUNT(*)::numeric FROM scrubbed), 1) pct
  FROM scrubbed
  WHERE countyid IN (SELECT countyid FROM gadm_county_lookup);
-- tbd

SELECT CASE WHEN countryid IS NOT NULL
            THEN 'matched'
            ELSE 'unmatched' END AS matches,
       COUNT(*)
  FROM vcountry
  GROUP BY matches;

SELECT CASE WHEN stateprovinceid IS NOT NULL
            THEN 'matched'
            ELSE 'unmatched' END AS matches,
       COUNT(*)
  FROM vstate
  GROUP BY matches;
--  matched   |  1770
--  unmatched |  1541

SELECT CASE WHEN countyid IS NOT NULL
            THEN 'matched'
            ELSE 'unmatched' END AS matches,
       COUNT(*)
  FROM vcounty
  GROUP BY matches;
--  matched   |  4295
--  unmatched | 14354

SELECT CASE WHEN country IS NULL
                 THEN 'no country provided'
            WHEN country IS NOT NULL AND countryid IS NULL
                 THEN 'unrecognized country'
            ELSE
                 'scrubbed country'
            END results,
       COUNT(*)
  FROM scrubbed
  GROUP BY results;

SELECT CASE WHEN country IS NULL
                 THEN 'no fully qualified stateprovince provided'
            WHEN country IS NOT NULL AND stateprovince IS NOT NULL AND stateprovinceid IS NULL
                 THEN 'unrecognized stateprovince'
            ELSE
                 'scrubbed stateprovince'
            END results,
       COUNT(*)
  FROM scrubbed
  GROUP BY results;

-- file output
SELECT DISTINCT countryid, country,
    stateprovinceid, stateprovince,
    countyid, county
  FROM scrubbed
  ORDER BY country, stateprovince, county;

/*
-- look at the whole iso2 vs fips thing
SELECT DISTINCT vc.country, i.country iso, f.country fips, stateprovince
  FROM vcountry vc
  LEFT JOIN countries i ON vc.country=i.iso
  LEFT JOIN countries f ON vc.country=f.fips
  LEFT JOIN scrubbed s ON s.country=vc.country
  WHERE vc.countryid IS NULL
    AND i.country IS NOT NULL
    AND f.country IS NOT NULL
    ORDER BY vc.country;
*/