-
Notifications
You must be signed in to change notification settings - Fork 0
/
database setup.sql
148 lines (141 loc) · 3.89 KB
/
database setup.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
/* Setting up a Postgres database for my purposes
1. Install PostGIS
2. Install geocoder
3. Install what else?
*/
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION address_standardizer;
CREATE EXTENSION tablefunc;
/*
psql -h census.curqunhzk3h8.us-east-1-beta.rds.amazonaws.com -p 5432 -U susubear -d wavf17
*/
CREATE SCHEMA wavf_raw
;
CREATE TABLE wavf_raw.wavf
(wavfid bigserial PRIMARY KEY
,statevoterid varchar(25)
,countyvoterid varchar(25)
,title varchar(10)
,fname varchar(55)
,mname varchar(55)
,lname varchar(55)
,namesuffix varchar(15)
,birthdate date
,gender varchar(6)
,regstnum varchar(15)
,regstfrac varchar(15)
,regstname varchar(55)
,regsttype varchar(25)
,regunittype varchar(15)
,regstpredirection varchar(15)
,regstpostdirection varchar(15)
,regunitnum varchar(15)
,regcity varchar(55)
,regstate varchar(7)
,regzipcode varchar(15)
,countycode varchar(7)
,precinctcode varchar(14)
,precinctpart varchar(15)
,legislativedistrict int
,congressionaldistrict int
,mail1 varchar(105)
,mail2 varchar(105)
,mail3 varchar(105)
,mail4 varchar(105)
,mailcity varchar(55)
,mailzip varchar(15)
,mailstate varchar(7)
,mailcountry varchar(55)
,registrationdate date
,absenteetype varchar(6)
,lastvoted date
,statuscode varchar(11)
,dflag varchar(7)
)
;
CREATE TABLE wavf_raw.county
(countycode varchar(2)
,county varchar)
;
INSERT INTO wavf_raw.county
VALUES
('AD', 'Adams' )
,('AS', 'Asotin' )
,('BE', 'Benton' )
,('CH', 'Chelan' )
,('CM', 'Clallam' )
,('CR', 'Clark' )
,('CU', 'Columbia' )
,('CZ', 'Cowlitz' )
,('DG', 'Douglas' )
,('FE', 'Ferry' )
,('FR', 'Franklin' )
,('GA', 'Garfield' )
,('GR', 'Grant' )
,('GY', 'Grays Harbor' )
,('IS', 'Island' )
,('JE', 'Jefferson' )
,('KI', 'King' )
,('KP', 'Kitsap' )
,('KS', 'Kittitas' )
,('KT', 'Klickitat' )
,('LE', 'Lewis' )
,('LI', 'Lincoln' )
,('MA', 'Mason' )
,('OK', 'Okanogan' )
,('PA', 'Pacific' )
,('PE', 'Pend Oreille' )
,('PI', 'Pierce' )
,('SJ', 'San Juan' )
,('SK', 'Skagit' )
,('SM', 'Skamania' )
,('SN', 'Snohomish' )
,('SP', 'Spokane' )
,('ST', 'Stevens' )
,('TH', 'Thurston' )
,('WK', 'Wahkiakum' )
,('WL', 'Walla Walla' )
,('WM', 'Whatcom' )
,('WT', 'Whitman' )
,('YA', 'Yakima' )
;
CREATE TABLE wavf_raw.precincts
(countycode varchar
,county varchar
,districttype varchar
,districtcode varchar
,districtname varchar
,precinctcode varchar
,precinctname varchar
,precinctpart varchar
)
;
CREATE TABLE wavf_raw.votehistory
(vhid bigserial PRIMARY KEY
,countycode varchar
,statevoterid varchar
,electiondate varchar
,votinghistoryid varchar
)
;
--
-- LC_ALL=C sed '/^$/d' thegeekstuff.txt -- Use just this one!!!
--$ sed 's/^ *//; s/ *$//; /^$/d; /^\s*$/d' file.txt > output.txt
--`s/^ *//` => left trim
--`s/ *$//` => right trim
--`/^$/d` => remove empty line
--`/^\s*$/d` => delete lines which may contain white space
/*Sources:
http://www.thegeekstuff.com/2009/09/unix-sed-tutorial-replace-text-inside-a-file-using-substitute-command/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+TheGeekStuff+(The+Geek+Stuff)
https://stackoverflow.com/questions/16414410/delete-empty-lines-using-sed
https://stackoverflow.com/questions/11287564/getting-sed-error-illegal-byte-sequence-in-bash
http://edoras.sdsu.edu/doc/sed-oneliners.html
*/
/* or maybe this one
sed -n 'n;p' 201704_VRDB_Extract.txt > 2017_singleline.txt
https://stackoverflow.com/questions/2560411/how-to-remove-every-other-line-with-sed
*/
\copy wavf_raw.wavf (statevoterid,countyvoterid,title,fname,mname,lname,namesuffix,birthdate,gender,regstnum,regstfrac,regstname,regsttype,regunittype,regstpredirection,regstpostdirection,regunitnum,regcity,regstate,regzipcode,countycode,precinctcode,precinctpart,legislativedistrict,congressionaldistrict,mail1,mail2,mail3,mail4,mailcity,mailzip,mailstate,mailcountry,registrationdate,absenteetype,lastvoted,statuscode,dflag) FROM '/Users/trevor/Desktop/vrdb-current/2017_singleline.txt';