/*

  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;

*/

/*

  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

------------------------------

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

*/

/*

  SQL statements carrying out geovalidation on vegbien location data.

  Currently hard-coded to operate on the 'geoscrub' table that is

  assumed to have been generated by the geonames scrubbing process

  (see geonames.sql). This table should minimally contain 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)

     countrystd       (GADM2-mapped country name)

     stateprovincestd (GADM2-mapped state/province name)

     countystd        (GADM2-mapped county name)

  (Note that the geonames.sql script also currently creates columns with

  geonames.org IDs for country, stateprovince, and county.)

  After execution of the statements below, the table will contain the

  following new columns (along with geom and geog columns used by

  postgis):

     latlonvalidity        (validity score for lat/lon coordinate)

     countryvalidity       (validity score for country)

     stateprovincevalidity (validity score for stateprovince)

     countyvalidity        (validity score for county)

     pointcountry          (GADM2 country containing the point)

     pointstateprovince    (GADM2 stateprovince containing the point)

     pointcounty           (GADM2 county containing the point)

  Workflow with actual times (as executed 15-Nov-2012)

     12s create point geoms

     16s create point geogs

     23s index geoms

     26s index geogs

     37s index countrystd

     25s index stateprovincestd

      5s vacuum analyze

      1s mark missing coords (UPDATE 0)

    133s mark valid coords (UPDATE 1702989)

      2s mark invalid coords (UPDATE 4981)

     17s vacuum analyze

     27s mark missing countries (UPDATE 222085)

     36s mark non-gadm countries (UPDATE 6547)

   1277s mark point in country (UPDATE 1400627)

   1491s mark point near country (UPDATE 24626)

    573s mark point near country accurate (UPDATE 1182)

      0s mark point near Antarctica (UPDATE 0)

      2s mark point not in country (UPDATE 54085)

      8s vacuum analyze

     15s mark missing stateprovinces (UPDATE 299015)

     12s mark non-gadm stateprovinces (UPDATE 19716)

    920s mark point in stateprovince (UPDATE 1241068)

    864s mark point near stateprovince (UPDATE 35685)

    424s mark point near stateprovince accurate (UPDATE 1051)

      7s mark point not in stateprovince (UPDATE 111282)

       s vacuum analyze

    128s mark missing counties (UPDATE 1431161)

     29s mark non-gadm counties (UPDATE 63014)

     86s mark point in county (UPDATE 174469)

     65s mark point near county (UPDATE 12477)

      ?s mark point near county accurate (UPDATE ?)

      3s mark point not in county (UPDATE 26849)

      ?s vacuum analyze

    890s look up GADM location using given lat/lon (UPDATE 1656709)

  todo:

  * make firm decision about whether we should assume here that *std

    columns contain valid GADM names (or are NULL), or whether we should

    check that here

  Jim Regetz

  NCEAS

  Created Nov 2012

*/

-- generate point geometries from coordinates

SELECT AddGeometryColumn('public', 'geoscrub', 'geom', 4326, 'POINT', 2 );

UPDATE geoscrub

   SET geom = ST_SetSRID(ST_Point(decimallongitude, decimallatitude), 4326);

-- ... create point geographies too ...

-- note that this excludes (leaves null) any occurrences with coordinates

-- that are not valid decimal degrees

ALTER TABLE geoscrub ADD COLUMN geog geography(POINT, 4326);

UPDATE geoscrub

  SET geog = geom::geography

  WHERE geom && ST_MakeBox2D(ST_Point(-180, -90), ST_Point(180, 90));

-- create indexes

CREATE INDEX "geoscrub_geom_gist" ON geoscrub USING GIST (geom);

CREATE INDEX "geoscrub_geog_gist" ON geoscrub USING GIST (geog);

CREATE INDEX "geoscrub_countrystd_idx" ON geoscrub (countrystd);

CREATE INDEX "geoscrub_stateprovincestd_idx" ON geoscrub (stateprovincestd);

CREATE INDEX "geoscrub_countystd_idx" ON geoscrub (countystd);

-- may want to do this first, otherwise the query planner wants to do a

-- hash join instead of nested loop join, and at least when I use a limit

-- statement to time queries on either side of the threshold where it

-- switches, the hash join does waaaaay worse

SET enable_hashjoin = false;

--

-- validate lat/lon coordinates

--

VACUUM ANALYZE geoscrub;

ALTER TABLE geoscrub ADD COLUMN latlonvalidity int;

-- -1 if missing one or both coordinates

UPDATE geoscrub o

    SET latlonvalidity = -1

    WHERE (

        decimallatitude IS NULL

        OR decimallongitude IS NULL

    );

-- 1 if the coordinates falls within the global bounding box

UPDATE geoscrub o

    SET latlonvalidity = 1

    WHERE geom && ST_MakeBox2D(ST_Point(-180, -90), ST_Point(180, 90));

-- 0 otherwise (have coordinates, but not valid)

UPDATE geoscrub o

    SET latlonvalidity = 0

    WHERE o.latlonvalidity IS NULL;

--

-- validate country

--

VACUUM ANALYZE geoscrub;

ALTER TABLE geoscrub ADD COLUMN countryvalidity int;

-- -1 if missing country name

UPDATE geoscrub o

    SET countryvalidity = -1

    WHERE o.country IS NULL;

-- 0 if a country name is asserted, but not recognized among GADM

-- country names

UPDATE geoscrub o

    SET countryvalidity = 0

    WHERE (o.countrystd IS NULL

        OR NOT EXISTS (

           SELECT 1

             FROM gadm2 c

             WHERE o.countrystd=c.name_0

         )

     ) AND o.countryvalidity IS NULL;

-- 3 if the point is in (or on the border of) the asserted country

UPDATE geoscrub AS o

    SET countryvalidity = 3

    WHERE EXISTS (

        SELECT 1

            FROM gadm2 c

            WHERE o.countrystd=c.name_0

              AND ST_Intersects(o.geom, c.simple_geom)

    ) AND o.countryvalidity IS NULL;

-- 2 for those that aren't 3's, but the point is in within 5km of

-- the asserted country

UPDATE geoscrub o

    SET countryvalidity = 2

    WHERE EXISTS (

        SELECT 1

            FROM gadm2 c

            WHERE o.countrystd=c.name_0

              AND ST_DWithin(o.geog, c.simple_geog, 5000)

    ) AND o.countryvalidity IS NULL;

-- now recheck the 2's to see if any are within the country based on the

-- original (unsimplified) geometry, and if so, upgrade to 3

UPDATE geoscrub AS o

    SET countryvalidity = 3

    WHERE EXISTS (

        SELECT 1

            FROM gadm2 c

            WHERE o.countrystd=c.name_0

              AND ST_Intersects(o.geom, c.geom)

    ) AND o.countryvalidity = 2;

-- also handle special case Antartica, which has invalid geography in

-- gadm2 and thus wasn't included in the within-5km check

UPDATE geoscrub AS o

    SET countryvalidity = 3

    WHERE EXISTS (

        SELECT 1

            FROM gadm2 c

            WHERE o.countrystd=c.name_0

              AND ST_Intersects(o.geom, c.geom)

    ) AND o.countryvalidity < 3

    AND o.countrystd = 'Antarctica';

-- 1 otherwise (have recognized country name, but point is not within

-- 5km of the asserted country)

UPDATE geoscrub o

    SET countryvalidity = 1

    WHERE o.countryvalidity IS NULL;

-----------------------------

-- validate state/province --

-----------------------------

VACUUM ANALYZE geoscrub;

ALTER TABLE geoscrub ADD COLUMN stateprovincevalidity int;

-- -1 if missing stateprovince name

UPDATE geoscrub o

    SET stateprovincevalidity = -1

    WHERE o.country IS NULL

       OR o.stateprovince IS NULL;

-- 0 if a stateprovince name is asserted, but not recognized among GADM

-- stateprovince names

UPDATE geoscrub o

    SET stateprovincevalidity = 0

    WHERE (o.countrystd IS NULL

        OR o.stateprovincestd IS NULL

        OR NOT EXISTS (

           SELECT 1

             FROM gadm2 c

             WHERE o.countrystd=c.name_0

               AND o.stateprovincestd=c.name_1

        )

    ) AND o.stateprovincevalidity IS NULL;

-- 3 if the point is in (or on the border of) the asserted stateprovince

UPDATE geoscrub AS o

    SET stateprovincevalidity = 3

    WHERE EXISTS (

        SELECT 1

            FROM gadm2 c

            WHERE o.countrystd=c.name_0

              AND o.stateprovincestd=c.name_1

              AND ST_Intersects(o.geom, c.simple_geom)

    ) AND o.countryvalidity = 3

    AND o.stateprovincevalidity IS NULL;

-- for those that aren't 3's, set to 2 if the point is in within 5km of

-- the asserted stateprovince

UPDATE geoscrub o

    SET stateprovincevalidity = 2

    WHERE EXISTS (

        SELECT 1

            FROM gadm2 c

            WHERE o.countrystd=c.name_0

              AND o.stateprovincestd=c.name_1

              AND ST_DWithin(o.geog, c.simple_geog, 5000)

    ) AND (o.countryvalidity = 2 OR o.countryvalidity = 3)

    AND o.stateprovincevalidity IS NULL;

-- now recheck the 2's to see if any are within the stateprovince based

-- on the original (unsimplified) geometry, and if so, upgrade to 3

UPDATE geoscrub AS o

    SET stateprovincevalidity = 3

    WHERE EXISTS (

        SELECT 1

            FROM gadm2 c

            WHERE o.countrystd=c.name_0

              AND o.stateprovincestd=c.name_1

              AND ST_Intersects(o.geom, c.geom)

    ) AND o.stateprovincevalidity = 2;

-- 1 otherwise (have recognized stateprovince name, but point is not within

-- 5km of the asserted stateprovince)

UPDATE geoscrub o

    SET stateprovincevalidity = 1

    WHERE o.stateprovincevalidity IS NULL;

-----------------------------

-- validate county/parish --

-----------------------------

ALTER TABLE geoscrub ADD COLUMN countyvalidity int;

-- -1 if missing county name

UPDATE geoscrub o

    SET countyvalidity = -1

    WHERE o.country IS NULL

       OR o.stateprovince IS NULL

       OR o.county IS NULL;

-- 0 if a county name is asserted, but not recognized among GADM

-- county names

UPDATE geoscrub o

    SET countyvalidity = 0

    WHERE (o.countrystd IS NULL

        OR o.stateprovincestd IS NULL

        OR o.countystd IS NULL

        OR NOT EXISTS (

           SELECT 1

             FROM gadm2 c

             WHERE o.countrystd=c.name_0

               AND o.stateprovincestd=c.name_1

               AND o.countystd=c.name_2

        )

    ) AND o.countyvalidity IS NULL;

-- 3 if the point is in (or on the border of) the asserted county

UPDATE geoscrub AS o

    SET countyvalidity = 3

    WHERE EXISTS (

        SELECT 1

            FROM gadm2 c

            WHERE o.countrystd=c.name_0

              AND o.stateprovincestd=c.name_1

              AND o.countystd=c.name_2

              AND ST_Intersects(o.geom, c.simple_geom)

    ) AND o.stateprovincevalidity = 3

    AND o.countyvalidity IS NULL;

-- for those that aren't 3's, set to 2 if the point is in within 5km of

-- the asserted county

UPDATE geoscrub o

    SET countyvalidity = 2

    WHERE EXISTS (

        SELECT 1

            FROM gadm2 c

            WHERE o.countrystd=c.name_0

              AND o.stateprovincestd=c.name_1

              AND o.countystd=c.name_2

              AND ST_DWithin(o.geog, c.simple_geog, 5000)

    ) AND (o.stateprovincevalidity = 2 OR o.stateprovincevalidity = 3)

    AND o.countyvalidity IS NULL;

-- 1 otherwise (have recognized county name, but point is not within

-- 5km of the asserted county)

UPDATE geoscrub o

    SET countyvalidity = 1

    WHERE o.countyvalidity IS NULL;

--

-- Look up GADM place names based purely on coordinates for all points

--

ALTER TABLE geoscrub ADD COLUMN pointcountry text;

ALTER TABLE geoscrub ADD COLUMN pointstateprovince text;

ALTER TABLE geoscrub ADD COLUMN pointcounty text;

-- note: only using simplified geometry now, for speed reasons

UPDATE geoscrub o

    SET pointcountry = c.name_0,

        pointstateprovince = c.name_1,

        pointcounty = c.name_2

    FROM gadm2 c

    WHERE ST_Intersects(o.geom, c.simple_geom)

      AND o.latlonvalidity = 1;

SET enable_hashjoin = true;

# Script to geoscrub-enable a postgres database, developed in the

# context of BIEN3 geoscrubbing but with intentions of generality.

#

# Basic workflow:

#  1. Import table dumps obtained from geonames.org

#  2. Add additional custom mapping info, including:

#     a. Madagascar provinces used in GADM2 but not geonames

#     b. extra country name mappings/fixes specific to BIEN3 data

#     c. extra state/prov name mappings/fixes specific to BIEN3 data

#     d. custom state/prov name mappings binding GADM2 to geonames

#

# Notes:

#  * One of the INSERT statements below contains the string 'Hawai\`i'.

#    The backslash is there to escape the backtick for bash, so that

#    what gets inserted into the database is 'Hawai`i'. If manually

#    copy-and-pasting the statement into psql rather than running as a

#    script (i.e., not having bash interpret the command), the backslash

#    must not be included.

#

# Jim Regetz

# NCEAS

# Created Nov 2012

# !! this is specific to regetz's testing environment !!

DATADIR="$HOME/biengeo/geonames"

cd ${DATADIR}

#

# load geonames.org geonames

#

wget http://download.geonames.org/export/dump/allCountries.zip

unzip allCountries.zip

psql -c \

   'CREATE TABLE geonames (

        geonameid serial primary key,

        name text,

        asciiname text,

        alternatenames text,

        latitude numeric,

        longitude numeric,

        featureclass char(1),

        featurecode text,

        countrycode char(2),

        cc2 char(60),

        admin1code text,

        admin2code text,

        admin3code text,

        admin4code text,

        population bigint,

        elevation int,

        dem int,

        timezone text,

        modification date

    )' geoscrub

# import data

psql -c "COPY geonames FROM '${DATADIR}/allCountries.txt' DELIMITER E'\t' NULL ''" geoscrub

psql -c \ "SELECT setval('geonames_geonameid_seq', (SELECT max(geonameid) FROM geonames))" geoscrub

psql -c \ "CREATE INDEX geonames_countrycode_idx ON geonames (countrycode)" geoscrub

#

# load geonames.org alternate names

#

wget http://download.geonames.org/export/dump/alternateNames.zip

unzip alternateNames.zip

psql -c \

   'CREATE TABLE alternateNames (

        alternateNameId serial primary key,

        geonameid int references geonames (geonameid),

        isolanguage varchar(7),

        alternateName varchar(200),

        isPreferredName char(1),

        isShortName char(1),

        isColloquial char(1),

        isHistoric char(1)

    )' geoscrub

# import data

psql -c "COPY alternateNames FROM '${DATADIR}/alternateNames.txt' DELIMITER E'\t' NULL ''" geoscrub

psql -c \ "SELECT setval('alternatenames_alternatenameid_seq', (SELECT max(alternatenameid) FROM alternatenames))" geoscrub

# 4m45.368s

# index alternatenames

psql -c 'CREATE INDEX "alternatenames_alternatename_idx" ON alternateNames (alternateName)' geoscrub

# 10m42.827s

#

# load geonames.org country info

#

psql -c \

   'CREATE TABLE countries (

        iso char(2),

        iso3 char(3),

        isonumeric text,

        fips text,

        country text UNIQUE,

        capital text,

        area text,

        population text,

        continent text,

        tld text,

        currencyCode text,

        currencyName text,

        phone text,

        postalCodeFormat text,

        postalCodeRegex text,

        languages text,

        geonameid int,

        neighbours text,

        equivalentFipsCode text

    )' geoscrub

# import geonames country table

grep -ve '^#' $DATADIR/countryInfo.txt | \

  psql -c "COPY countries FROM STDIN DELIMITER E'\t' NULL ''" geoscrub

#

# load geonames.org hierarchy info

#

wget http://download.geonames.org/export/dump/hierarchy.zip

unzip hierarchy.zip

psql -c \

   'CREATE TABLE hierarchy (

        parentId int,-- references geonames (geonameid),

        childId int,-- references geonames (geonameid),

        type text

    )' geoscrub

# import geonames country table

psql -c "COPY hierarchy FROM '${DATADIR}/hierarchy.txt' DELIMITER E'\t' NULL ''" geoscrub

#

# insert additional custom name-scrub mappings

#

# augment geonames with some additional places we need, and update

# hierarchy table accordingly

psql -c \

"WITH newnames AS (

  INSERT INTO geonames

    (name, alternatenames, featurecode, countrycode )

    VALUES

    ('Antananarivo', NULL, 'ADM1', 'MG'),

    ('Antsiranana', NULL, 'ADM1', 'MG'),

    ('Fianarantsoa', NULL, 'ADM1', 'MG'),

    ('Mahajanga', NULL, 'ADM1', 'MG'),

    ('Toamasina', NULL, 'ADM1', 'MG'),

    ('Toliara', 'Toliary', 'ADM1', 'MG')

  RETURNING geonameid, countrycode

)

INSERT INTO hierarchy (parentid, childid)

  SELECT c.geonameid AS parentid,

         n.geonameid AS childid

    FROM newnames n,

         countries c

    WHERE n.countrycode = c.iso;

" geoscrub

# add some custom country alternate names that we need

psql -c "

CREATE TABLE alt_country (

    country text references countries (country),

    alternatename text

);

INSERT INTO alt_country

    (country, alternatename)

    VALUES

    ('Central African Republic', 'Central African Repu')

  , ('British Virgin Islands', 'Virgin Islands (U.K.)')

  , ('Democratic Republic of the Congo', 'Congo, The Democratic Republic of the')

  , ('Falkland Islands', 'Falkland Isl')

  , ('Falkland Islands', 'Falkland Islands (Malvinas)')

  , ('Faroe Islands', 'Faeroe Isl.')

  , ('French Guiana', 'Guyana Francesa')

  , ('Iran', 'Iran, Islamic Republic of')

  , ('Mexico', E'Mexico\rMexico')

  , ('Nicaragua', 'Ncaragua')

  , ('Norfolk Island', 'Norfolk Isl')

  , ('North Korea', 'Korea, Democratic People''s Republic of')

  , ('Solomon Islands', 'Solomon Isl')

  , ('South Georgia and the South Sandwich Islands', 'South Georgia and the South Sand')

  , ('South Korea', 'Korea (Republic of)')

  , ('South Korea', 'Korea, Republic of')

  , ('U.S. Virgin Islands', 'US Virgin Islands')

  , ('U.S. Virgin Islands', 'Virgin Islands (U.S.)')

  , ('United States', 'ESTADOS UNIDOS DE AMERICA')

  , ('United States', 'EUA')

  , ('Vietnam', 'VIETNAM [Socialist Republic of V')

;" geoscrub

# add some custom stateprovince alternate names that we need

psql -c "

CREATE TABLE alt_stateprovince (

    country text references countries (country),

    stateprovince text,

    alternatename text

);

INSERT INTO alt_stateprovince

    (country, stateprovince, alternatename)

    VALUES

    ('Canada', 'British Columbia', 'B.C.')

  , ('Canada', 'Newfoundland and Labrador', 'Newfoundland & Labrador')

  , ('Canada', 'Newfoundland and Labrador', 'New Foundland And Labrador')

  , ('Canada', 'Newfoundland and Labrador', 'Labrador & Newfoundland')

  , ('Canada', 'Northwest Territories', 'North West Territories')

  , ('Canada', 'Northwest Territories', 'Northwest Territorie')

  , ('Canada', 'Northwest Territories', 'NWT')

  , ('Canada', 'Québec', 'Quebéc')

  , ('Canada', 'Québec', 'Pq')

  , ('Canada', 'Québec', 'PQ')

  , ('United States', 'Hawaii', 'Hawai\`i')

  , ('United States', 'North Carolina', 'N. Carolina')

  , ('United States', 'North Dakota', 'N. Dakota')

  , ('United States', 'South Dakota', 'S. Dakota')

  , ('Kenya', 'Coast Province', 'Coast')

  , ('Mexico', 'Estado de Baja California', 'Baja California Norte')

  , ('Mexico', 'Estado de Baja California', 'Baja California (Norte)')

  , ('Mexico', 'Estado de Baja California', 'Baja California, Norte de')

  , ('Mexico', 'Estado de Baja California', 'Baja California (state)')

  , ('Mexico', 'Estado de Baja California Sur', 'Baja California, Sur de')

  , ('Mexico', 'Estado de Baja California Sur', 'Baja California Sur (state)')

  , ('Mexico', 'Estado de Baja California Sur', 'Baja Califronia')

  , ('Mexico', 'Estado de Baja California Sur', 'Baja Cal. Sur')

  , ('Ecuador', 'Provincia de Sucumbíos', 'Sucumbíos')

;

" geoscrub

# additions for mapping to gadm2 level 1 units

#  - Los Lagos -> Los Rios is imperfect but needed for gadm

#  - Orellana -> Napo is imperfect (wrong??) but needed for gadm

#  - Mapping both Concelho de Macau and Concelho das Ilhas to Ilhas

#  - Nassa seems to be a gadm misspelling? (Niassa)

# not addressed:

#  - Bahamas: unmapped gadm level1 entities may be obsolete?

#  - French Guiana has arrondissements as adm0 in gadm, low level?

#  - French Polynesia has no adm1 in gadm

#  - Madagascar: Sava is (was?) inside gadm's Antsiranana province

#  - Philippines: GN has 17 regions at level1, gadm has 82 provinces

#  - Sri Lanka: GN ADM2 matches gadm level1; bien data side with GN

#  - Venezuela: Dependencias Federales don't seem to be in GADM

#  - Vietnam: GN ADM1 matches gadm level2; bien data side with GN?

psql -c "

INSERT INTO alt_stateprovince

    (country, stateprovince, alternatename)

    VALUES

    ('Cambodia', 'Khétt Siĕm Réab', 'Siemréab')

  , ('Chile', 'Región del Biobío', 'Bío-Bío')

  , ('Chile', 'Región del Libertador General Bernardo O’Higgins', 'Libertador General Bernardo O''Higgins')

  , ('Chile', 'Región de Los Ríos', 'Los Lagos')

  , ('Chile', 'Providencia y Santa Catalina, Departamento de Archipiélago de San Andrés', 'San Andrés y Providencia')

  , ('Dominican Republic', 'Provincia de Baoruco', 'Bahoruco')

  , ('Ecuador', 'Provincia de Morona Santiago', 'Morona Santiago')

  , ('Ecuador', 'Provincia de Napo', 'Orellana')

  , ('Ecuador', 'Provincia de Zamora Chinchipe', 'Zamora Chinchipe')

  , ('Gabon', 'Province du Woleu-Ntem', 'Wouleu-Ntem')

  , ('Ghana', 'Brong-Ahafo Region', 'Brong Ahafo')

  , ('Guatemala', 'Departamento de Quetzaltenango', 'Quezaltenango')

  , ('Haiti', 'Département de l''Artibonite', 'L''Artibonite')

  , ('Liberia', 'Grand Bassa County', 'GrandBassa')

  , ('Liberia', 'Grand Gedeh County', 'GrandGedeh')

  , ('Macao', 'Concelho de Macau', 'Ilhas')

  , ('Mozambique', 'Niassa Province', 'Nassa')

  , ('Norway', 'Østfold fylke', 'Ãstfold')

  , ('Peru', 'Región de Huánuco', 'Huánuco')

  , ('Puerto Rico', 'Guánica Municipio', 'Guánica')

  , ('Puerto Rico', 'Loíza Municipio', 'Loíza')

  , ('Puerto Rico', 'Manatí Municipio', 'Manatí')

  , ('Puerto Rico', 'Peñuelas Municipio', 'Peñuelas')

  , ('Puerto Rico', 'Río Grande Municipio', 'Río Grande')

  , ('Puerto Rico', 'San Germán Municipio', 'San Germán')

  , ('Puerto Rico', 'San Sebastián Municipio', 'San Sebastián')

  , ('Russia', 'Karachayevo-Cherkesskaya Respublika', 'Karachay-Cherkess')

  , ('Syria', 'Muḩāfaz̧at al Lādhiqīyah', 'Lattakia')

  , ('Togo', 'Région Centrale', 'Centre')

;" geoscrub

BIEN geovalidation notes

========================

[Also see comments embedded in specific scripts in this directory.]

The bash and SQL statements contained in the files as ordered below

should be applied to carry out geographic name scrubbing and

geovalidation on a given corpus of BIEN location records.

That said, given the tight deadline under which this was done in order

to produced a geovalidated BIEN3 corpus in advance of the Nov 2013

working group meeting, and the corresponding manner in which much of

this was actually executed piecemeal in an iterative and interactive

fashion within a bash shell and psql session, I can't guarantee that the

code in its current state could be run end-to-end without intervention.

It's close, but probably not bulletproof.

1. geovalidate.sh

   - creates postgis DB and loads GADM2 data

2. geonames.sh

   - loads geonames.org data and adds some custom mapping logic

3. geonames-to-gadm.sql

   - contains SQL statements that build linkages between geonames.org

     names and GADM2 names

4. load-geoscrub-input.sh

   - dumps geoscrub_input from vegbien and loads it into the geoscrub db

5. geonames.sql

   - contains SQL statements that scrub asserted names and (to the

     extent possible) map them to GADM2

6. geovalidate.sql

   - contains (postgis-extended) SQL statements that score the validity

     of GADM2-scrubbed names against given point coordinates

The resulting 'geoscrub' table is what contains the scrubbed (i.e.,

GADM2-matched) names and various geovalidation scores.

Notes/Caveats/Todos:

* Clearly the SQL statements used in this procedure suffer from a lot of

  redundancy, and it might be worth trying to refactor once we're happy

  with the particular approach taken.

* Need to pull out more known notes/caveats/todos and highlight them :)

/*

  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