Project

General

Profile

1 11443 psarando
/*
2
  SQL statements to create tables for country name, stateprovince name, and
3
  county name scrubbing on vegbien location data. Currently hard-coded
4
  to operate on a 'vegbien_geoscrub' table that contains the input for
5
  geoscrubbing, with the following columns:
6
     decimallatitude  (latitude, WGS84 decimal degrees)
7
     decimallongitude (longitude, WGS84 decimal degrees)
8
     country          (original asserted country name)
9
     stateprovince    (original asserted stateprovince name)
10
     county           (original asserted county name)
11
  (Note that the lat/lon columns are not needed for scrubbing, but are
12
  used later for geovalidation.)
13
14
  Jim Regetz
15
  NCEAS
16
  Created Nov 2012
17
*/
18
19
BEGIN;
20
21
CREATE TABLE geoscrub (
22
    decimallatitude double precision,
23
    decimallongitude double precision,
24
    country text,
25
    stateprovince text,
26
    county text,
27
    countryid integer,
28
    stateprovinceid integer,
29
    countyid integer,
30
    countrystd text,
31
    stateprovincestd text,
32
    countystd text,
33
    latlonvalidity int,
34
    countryvalidity int,
35
    stateprovincevalidity int,
36
    countyvalidity int,
37
    pointcountry text,
38
    pointstateprovince text,
39
    pointcounty text
40
);
41
SELECT AddGeometryColumn('public', 'geoscrub', 'geom', 4326, 'POINT', 2 );
42
ALTER TABLE geoscrub ADD COLUMN geog geography(POINT, 4326);
43
44
CREATE TABLE vegbien_geoscrub (
45
    country text,
46
    stateprovince text,
47
    county text,
48
    decimalLatitude double precision,
49
    decimalLongitude double precision
50
);
51
52
CREATE TABLE vcountry (
53
    country text,
54
    countryutf8 text,
55
    countryid integer,
56
    countrystd text
57
);
58
59
CREATE TABLE vstate (
60
    countryid integer,
61
    stateProvince text,
62
    stateprovinceutf8 text,
63
    stateprovinceid integer,
64
    stateprovincestd text
65
);
66
67
CREATE TABLE vcounty (
68
    countryid integer,
69
    stateprovinceid integer,
70
    county text,
71
    countyutf8 text,
72
    countyid integer,
73
    countystd text
74
);
75
76
COMMIT;