/*
  SQL statements to create tables for 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.)

  Jim Regetz
  NCEAS
  Created Nov 2012
*/

BEGIN;

CREATE TABLE geoscrub (
    decimallatitude double precision,
    decimallongitude double precision,
    country text,
    stateprovince text,
    county text,
    countryid integer,
    stateprovinceid integer,
    countyid integer,
    countrystd text,
    stateprovincestd text,
    countystd text,
    latlonvalidity int,
    countryvalidity int,
    stateprovincevalidity int,
    countyvalidity int,
    pointcountry text,
    pointstateprovince text,
    pointcounty text
);
SELECT AddGeometryColumn('public', 'geoscrub', 'geom', 4326, 'POINT', 2 );
ALTER TABLE geoscrub ADD COLUMN geog geography(POINT, 4326);

CREATE TABLE vegbien_geoscrub (
    country text,
    stateprovince text,
    county text,
    decimalLatitude double precision,
    decimalLongitude double precision
);

CREATE TABLE vcountry (
    country text,
    countryutf8 text,
    countryid integer,
    countrystd text
);

CREATE TABLE vstate (
    countryid integer,
    stateProvince text,
    stateprovinceutf8 text,
    stateprovinceid integer,
    stateprovincestd text
);

CREATE TABLE vcounty (
    countryid integer,
    stateprovinceid integer,
    county text,
    countyutf8 text,
    countyid integer,
    countystd text
);

COMMIT;

