/*

  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;