/derived/biengeo/geonames.sql - Diff - BIEN 3 - NCEAS Projects

« Previous | Next »

Revision 11443

Added by Paul Sarando about 11 years ago

Initial checkin of geoscrub install SQL files.

Added install.*.sql files that will do initial table creation for all
required tables.
Added a truncate.vegbien_geoscrub.sql script that will clear tables related to
data downloaded in load-geoscrub-input.sh.
Moved all SQL statements that modify the final geoscrub table into the
geovalidate.sql script.
Modified the geonames.sql and geovalidate.sql scripts to assume all
tables have already been created by the install scripts.

       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;
     BEGIN;
     -------------------
     -- scrub country --
     -------------------
     CREATE TABLE vcountry AS
     INSERT INTO vcountry
       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
-...
     -- 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
-...
     -- scrub stateprovince --
     -------------------------
     CREATE TABLE vstate AS
     INSERT INTO vstate
       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
-...
     -- 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
-...
     -- scrub county --
     ------------------
     CREATE TABLE vcounty AS
     INSERT INTO vcounty
       SELECT DISTINCT countryid, stateprovinceid, county
       FROM vegbien_geoscrub
       JOIN vcountry USING (country)
-...
         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
-...
     -- 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
-...
     -- UPDATE 5027
     -- Time: 1410.377 ms
     -----------------------------
     -- put everything together --
     -----------------------------
     COMMIT;
     -- 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

Also available in: Unified diff

Project

General

Profile

Revision 11443

Added by Paul Sarando about 11 years ago