BEGIN;

TRUNCATE geoscrub;

DROP INDEX IF EXISTS "geoscrub_geom_gist";

DROP INDEX IF EXISTS "geoscrub_geog_gist";

DROP INDEX IF EXISTS "geoscrub_countrystd_idx";

DROP INDEX IF EXISTS "geoscrub_stateprovincestd_idx";

DROP INDEX IF EXISTS "geoscrub_countystd_idx";

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

-- put everything together --

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

-- reconstitute the whole dang thang

-- populate geoscrub from vegbien_geoscrub data

INSERT INTO geoscrub

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

COMMIT;