-
Notifications
You must be signed in to change notification settings - Fork 4
Expand file tree
/
Copy pathtile-views.sql
More file actions
339 lines (331 loc) · 27.3 KB
/
tile-views.sql
File metadata and controls
339 lines (331 loc) · 27.3 KB
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
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
BEGIN;
DROP MATERIALIZED VIEW tiles.vd;
CREATE MATERIALIZED VIEW tiles.vd AS
SELECT
vd.id,
natwin.election_id,
vd.code,
COALESCE(natp.abbrev, 'TIE') as nat_win_party,
floor(natwin.perc)::int as nat_win_perc,
COALESCE(regp.abbrev, 'TIE') as reg_win_party,
floor(regwin.perc)::int as reg_win_perc,
COALESCE(provp.abbrev, 'TIE') as prov_win_party,
floor(provwin.perc)::int as prov_win_perc,
coalesce(floor(natballot.total*100.0/nullif(natballot.regpop, 0)), 0)::int as nat_turnout,
coalesce(floor(regballot.total*100.0/nullif(regballot.regpop, 0)), 0)::int as reg_turnout,
coalesce(floor(provballot.total*100.0/nullif(provballot.regpop, 0)), 0)::int as prov_turnout,
coalesce(floor(nat_anc.perc), 0)::int as nat_anc,
coalesce(floor(reg_anc.perc), 0)::int as reg_anc,
coalesce(floor(prov_anc.perc), 0)::int as prov_anc,
coalesce(floor(nat_da.perc), 0)::int as nat_da,
coalesce(floor(reg_da.perc), 0)::int as reg_da,
coalesce(floor(prov_da.perc), 0)::int as prov_da,
coalesce(floor(nat_eff.perc), 0)::int as nat_eff,
coalesce(floor(reg_eff.perc), 0)::int as reg_eff,
coalesce(floor(prov_eff.perc), 0)::int as prov_eff,
coalesce(floor(nat_ifp.perc), 0)::int as nat_ifp,
coalesce(floor(reg_ifp.perc), 0)::int as reg_ifp,
coalesce(floor(prov_ifp.perc), 0)::int as prov_ifp,
coalesce(floor(nat_vfplus.perc), 0)::int as nat_vfplus,
coalesce(floor(reg_vfplus.perc), 0)::int as reg_vfplus,
coalesce(floor(prov_vfplus.perc), 0)::int as prov_vfplus,
coalesce(floor(nat_mk.perc), 0)::int as nat_mk,
coalesce(floor(reg_mk.perc), 0)::int as reg_mk,
coalesce(floor(prov_mk.perc), 0)::int as prov_mk,
vd.geom
FROM vd
JOIN vd_single_winner natwin ON vd.id = natwin.vd_id and natwin.ballot_id = 1
LEFT JOIN party natp ON natwin.party_id = natp.id
LEFT JOIN vd_single_winner regwin ON vd.id = regwin.vd_id and regwin.ballot_id = 7 and natwin.election_id = regwin.election_id
LEFT JOIN party regp ON regwin.party_id = regp.id
LEFT JOIN vd_single_winner provwin ON vd.id = provwin.vd_id and provwin.ballot_id = 2 and natwin.election_id = provwin.election_id
LEFT JOIN party provp ON provwin.party_id = provp.id
LEFT JOIN vd_ballot_total natballot ON (vd.id = natballot.vd_id and natwin.election_id = natballot.election_id and natballot.ballot_id = 1)
LEFT JOIN vd_ballot_total regballot ON (vd.id = regballot.vd_id and regwin.election_id = regballot.election_id and regballot.ballot_id = 7)
LEFT JOIN vd_ballot_total provballot ON (vd.id = provballot.vd_id and natwin.election_id = provballot.election_id and provballot.ballot_id = 2)
LEFT JOIN vd_perc nat_anc ON (vd.id = nat_anc.vd_id and natwin.election_id = nat_anc.election_id and nat_anc.ballot_id = 1 and nat_anc.party_id = 24)
LEFT JOIN vd_perc reg_anc ON (vd.id = reg_anc.vd_id and regwin.election_id = reg_anc.election_id and reg_anc.ballot_id = 7 and reg_anc.party_id = 24)
LEFT JOIN vd_perc prov_anc ON (vd.id = prov_anc.vd_id and natwin.election_id = prov_anc.election_id and prov_anc.ballot_id = 2 and prov_anc.party_id = 24)
LEFT JOIN vd_perc nat_da ON (vd.id = nat_da.vd_id and natwin.election_id = nat_da.election_id and nat_da.ballot_id = 1 and nat_da.party_id = 106)
LEFT JOIN vd_perc reg_da ON (vd.id = reg_da.vd_id and regwin.election_id = reg_da.election_id and reg_da.ballot_id = 7 and reg_da.party_id = 106)
LEFT JOIN vd_perc prov_da ON (vd.id = prov_da.vd_id and natwin.election_id = prov_da.election_id and prov_da.ballot_id = 2 and prov_da.party_id = 106)
LEFT JOIN vd_perc nat_eff ON (vd.id = nat_eff.vd_id and natwin.election_id = nat_eff.election_id and nat_eff.ballot_id = 1 and nat_eff.party_id = 122)
LEFT JOIN vd_perc reg_eff ON (vd.id = reg_eff.vd_id and regwin.election_id = reg_eff.election_id and reg_eff.ballot_id = 7 and reg_eff.party_id = 122)
LEFT JOIN vd_perc prov_eff ON (vd.id = prov_eff.vd_id and natwin.election_id = prov_eff.election_id and prov_eff.ballot_id = 2 and prov_eff.party_id = 122)
LEFT JOIN vd_perc nat_ifp ON (vd.id = nat_ifp.vd_id and natwin.election_id = nat_ifp.election_id and nat_ifp.ballot_id = 1 and nat_ifp.party_id = 165)
LEFT JOIN vd_perc reg_ifp ON (vd.id = reg_ifp.vd_id and regwin.election_id = reg_ifp.election_id and reg_ifp.ballot_id = 7 and reg_ifp.party_id = 165)
LEFT JOIN vd_perc prov_ifp ON (vd.id = prov_ifp.vd_id and natwin.election_id = prov_ifp.election_id and prov_ifp.ballot_id = 2 and prov_ifp.party_id = 165)
LEFT JOIN vd_perc nat_vfplus ON (vd.id = nat_vfplus.vd_id and natwin.election_id = nat_vfplus.election_id and nat_vfplus.ballot_id = 1 and nat_vfplus.party_id = 374)
LEFT JOIN vd_perc reg_vfplus ON (vd.id = reg_vfplus.vd_id and regwin.election_id = reg_vfplus.election_id and reg_vfplus.ballot_id = 7 and reg_vfplus.party_id = 374)
LEFT JOIN vd_perc prov_vfplus ON (vd.id = prov_vfplus.vd_id and natwin.election_id = prov_vfplus.election_id and prov_vfplus.ballot_id = 2 and prov_vfplus.party_id = 374)
LEFT JOIN vd_perc nat_mk ON (vd.id = nat_mk.vd_id and natwin.election_id = nat_mk.election_id and nat_mk.ballot_id = 1 and nat_mk.party_id = 664)
LEFT JOIN vd_perc reg_mk ON (vd.id = reg_mk.vd_id and regwin.election_id = reg_mk.election_id and reg_mk.ballot_id = 7 and reg_mk.party_id = 664)
LEFT JOIN vd_perc prov_mk ON (vd.id = prov_mk.vd_id and natwin.election_id = prov_mk.election_id and prov_mk.ballot_id = 2 and prov_mk.party_id = 664)
;
CREATE INDEX ON tiles.vd(election_id);
CREATE INDEX ON tiles.vd USING gist(geom);
--
DROP MATERIALIZED VIEW tiles.ward;
CREATE MATERIALIZED VIEW tiles.ward AS
SELECT
ward.id,
natwin.election_id,
ward.code,
COALESCE(natp.abbrev, 'TIE') as nat_win_party,
floor(natwin.perc)::int as nat_win_perc,
COALESCE(regp.abbrev, 'TIE') as reg_win_party,
floor(regwin.perc)::int as reg_win_perc,
COALESCE(provp.abbrev, 'TIE') as prov_win_party,
floor(provwin.perc)::int as prov_win_perc,
coalesce(floor(natballot.total*100.0/nullif(natballot.regpop, 0)), 0)::int as nat_turnout,
coalesce(floor(regballot.total*100.0/nullif(regballot.regpop, 0)), 0)::int as reg_turnout,
coalesce(floor(provballot.total*100.0/nullif(provballot.regpop, 0)), 0)::int as prov_turnout,
coalesce(floor(nat_anc.perc), 0)::int as nat_anc,
coalesce(floor(reg_anc.perc), 0)::int as reg_anc,
coalesce(floor(prov_anc.perc), 0)::int as prov_anc,
coalesce(floor(nat_da.perc), 0)::int as nat_da,
coalesce(floor(reg_da.perc), 0)::int as reg_da,
coalesce(floor(prov_da.perc), 0)::int as prov_da,
coalesce(floor(nat_eff.perc), 0)::int as nat_eff,
coalesce(floor(reg_eff.perc), 0)::int as reg_eff,
coalesce(floor(prov_eff.perc), 0)::int as prov_eff,
coalesce(floor(nat_ifp.perc), 0)::int as nat_ifp,
coalesce(floor(reg_ifp.perc), 0)::int as reg_ifp,
coalesce(floor(prov_ifp.perc), 0)::int as prov_ifp,
coalesce(floor(nat_vfplus.perc), 0)::int as nat_vfplus,
coalesce(floor(reg_vfplus.perc), 0)::int as reg_vfplus,
coalesce(floor(prov_vfplus.perc), 0)::int as prov_vfplus,
coalesce(floor(nat_mk.perc), 0)::int as nat_mk,
coalesce(floor(reg_mk.perc), 0)::int as reg_mk,
coalesce(floor(prov_mk.perc), 0)::int as prov_mk,
ward.geom
FROM ward
JOIN ward_single_winner natwin ON ward.id = natwin.ward_id and natwin.ballot_id = 1
LEFT JOIN party natp ON natwin.party_id = natp.id
LEFT JOIN ward_single_winner regwin ON ward.id = regwin.ward_id and regwin.ballot_id = 7 and natwin.election_id = regwin.election_id
LEFT JOIN party regp ON regwin.party_id = regp.id
LEFT JOIN ward_single_winner provwin ON ward.id = provwin.ward_id and provwin.ballot_id = 2 and natwin.election_id = provwin.election_id
LEFT JOIN party provp ON provwin.party_id = provp.id
LEFT JOIN ward_ballot_total natballot ON (ward.id = natballot.ward_id and natwin.election_id = natballot.election_id and natballot.ballot_id = 1)
LEFT JOIN ward_ballot_total regballot ON (ward.id = regballot.ward_id and natwin.election_id = regballot.election_id and regballot.ballot_id = 7)
LEFT JOIN ward_ballot_total provballot ON (ward.id = provballot.ward_id and natwin.election_id = provballot.election_id and provballot.ballot_id = 2)
LEFT JOIN ward_perc nat_anc ON (ward.id = nat_anc.ward_id and natwin.election_id = nat_anc.election_id and nat_anc.ballot_id = 1 and nat_anc.party_id = 24)
LEFT JOIN ward_perc reg_anc ON (ward.id = reg_anc.ward_id and regwin.election_id = reg_anc.election_id and reg_anc.ballot_id = 7 and reg_anc.party_id = 24)
LEFT JOIN ward_perc prov_anc ON (ward.id = prov_anc.ward_id and natwin.election_id = prov_anc.election_id and prov_anc.ballot_id = 2 and prov_anc.party_id = 24)
LEFT JOIN ward_perc nat_da ON (ward.id = nat_da.ward_id and natwin.election_id = nat_da.election_id and nat_da.ballot_id = 1 and nat_da.party_id = 106)
LEFT JOIN ward_perc reg_da ON (ward.id = reg_da.ward_id and regwin.election_id = reg_da.election_id and reg_da.ballot_id = 7 and reg_da.party_id = 106)
LEFT JOIN ward_perc prov_da ON (ward.id = prov_da.ward_id and natwin.election_id = prov_da.election_id and prov_da.ballot_id = 2 and prov_da.party_id = 106)
LEFT JOIN ward_perc nat_eff ON (ward.id = nat_eff.ward_id and natwin.election_id = nat_eff.election_id and nat_eff.ballot_id = 1 and nat_eff.party_id = 122)
LEFT JOIN ward_perc reg_eff ON (ward.id = reg_eff.ward_id and regwin.election_id = reg_eff.election_id and reg_eff.ballot_id = 7 and reg_eff.party_id = 122)
LEFT JOIN ward_perc prov_eff ON (ward.id = prov_eff.ward_id and natwin.election_id = prov_eff.election_id and prov_eff.ballot_id = 2 and prov_eff.party_id = 122)
LEFT JOIN ward_perc nat_ifp ON (ward.id = nat_ifp.ward_id and natwin.election_id = nat_ifp.election_id and nat_ifp.ballot_id = 1 and nat_ifp.party_id = 165)
LEFT JOIN ward_perc reg_ifp ON (ward.id = reg_ifp.ward_id and regwin.election_id = reg_ifp.election_id and reg_ifp.ballot_id = 7 and reg_ifp.party_id = 165)
LEFT JOIN ward_perc prov_ifp ON (ward.id = prov_ifp.ward_id and natwin.election_id = prov_ifp.election_id and prov_ifp.ballot_id = 2 and prov_ifp.party_id = 165)
LEFT JOIN ward_perc nat_vfplus ON (ward.id = nat_vfplus.ward_id and natwin.election_id = nat_vfplus.election_id and nat_vfplus.ballot_id = 1 and nat_vfplus.party_id = 374)
LEFT JOIN ward_perc reg_vfplus ON (ward.id = reg_vfplus.ward_id and regwin.election_id = reg_vfplus.election_id and reg_vfplus.ballot_id = 7 and reg_vfplus.party_id = 374)
LEFT JOIN ward_perc prov_vfplus ON (ward.id = prov_vfplus.ward_id and natwin.election_id = prov_vfplus.election_id and prov_vfplus.ballot_id = 2 and prov_vfplus.party_id = 374)
LEFT JOIN ward_perc nat_mk ON (ward.id = nat_mk.ward_id and natwin.election_id = nat_mk.election_id and nat_mk.ballot_id = 1 and nat_mk.party_id = 664)
LEFT JOIN ward_perc reg_mk ON (ward.id = reg_mk.ward_id and regwin.election_id = reg_mk.election_id and reg_mk.ballot_id = 7 and reg_mk.party_id = 664)
LEFT JOIN ward_perc prov_mk ON (ward.id = prov_mk.ward_id and natwin.election_id = prov_mk.election_id and prov_mk.ballot_id = 2 and prov_mk.party_id = 664)
;
CREATE INDEX ON tiles.ward(election_id);
CREATE INDEX ON tiles.ward USING gist(geom);
--
DROP MATERIALIZED VIEW tiles.muni;
CREATE MATERIALIZED VIEW tiles.muni AS
SELECT
muni.id,
natwin.election_id,
muni.code,
COALESCE(natp.abbrev, 'TIE') as nat_win_party,
floor(natwin.perc)::int as nat_win_perc,
COALESCE(regp.abbrev, 'TIE') as reg_win_party,
floor(regwin.perc)::int as reg_win_perc,
COALESCE(provp.abbrev, 'TIE') as prov_win_party,
floor(provwin.perc)::int as prov_win_perc,
coalesce(floor(natballot.total*100.0/nullif(natballot.regpop, 0)), 0)::int as nat_turnout,
coalesce(floor(regballot.total*100.0/nullif(regballot.regpop, 0)), 0)::int as reg_turnout,
coalesce(floor(provballot.total*100.0/nullif(provballot.regpop, 0)), 0)::int as prov_turnout,
coalesce(floor(nat_anc.perc), 0)::int as nat_anc,
coalesce(floor(reg_anc.perc), 0)::int as reg_anc,
coalesce(floor(prov_anc.perc), 0)::int as prov_anc,
coalesce(floor(nat_da.perc), 0)::int as nat_da,
coalesce(floor(reg_da.perc), 0)::int as reg_da,
coalesce(floor(prov_da.perc), 0)::int as prov_da,
coalesce(floor(nat_eff.perc), 0)::int as nat_eff,
coalesce(floor(reg_eff.perc), 0)::int as reg_eff,
coalesce(floor(prov_eff.perc), 0)::int as prov_eff,
coalesce(floor(nat_ifp.perc), 0)::int as nat_ifp,
coalesce(floor(reg_ifp.perc), 0)::int as reg_ifp,
coalesce(floor(prov_ifp.perc), 0)::int as prov_ifp,
coalesce(floor(nat_vfplus.perc), 0)::int as nat_vfplus,
coalesce(floor(reg_vfplus.perc), 0)::int as reg_vfplus,
coalesce(floor(prov_vfplus.perc), 0)::int as prov_vfplus,
coalesce(floor(nat_mk.perc), 0)::int as nat_mk,
coalesce(floor(reg_mk.perc), 0)::int as reg_mk,
coalesce(floor(prov_mk.perc), 0)::int as prov_mk,
muni.geom
FROM muni
JOIN muni_single_winner natwin ON muni.id = natwin.muni_id and natwin.ballot_id = 1
LEFT JOIN party natp ON natwin.party_id = natp.id
LEFT JOIN muni_single_winner regwin ON muni.id = regwin.muni_id and regwin.ballot_id = 7 and natwin.election_id = regwin.election_id
LEFT JOIN party regp ON regwin.party_id = regp.id
LEFT JOIN muni_single_winner provwin ON muni.id = provwin.muni_id and provwin.ballot_id = 2 and natwin.election_id = provwin.election_id
LEFT JOIN party provp ON provwin.party_id = provp.id
LEFT JOIN muni_ballot_total natballot ON (muni.id = natballot.muni_id and natwin.election_id = natballot.election_id and natballot.ballot_id = 1)
LEFT JOIN muni_ballot_total regballot ON (muni.id = regballot.muni_id and regwin.election_id = regballot.election_id and regballot.ballot_id = 7)
LEFT JOIN muni_ballot_total provballot ON (muni.id = provballot.muni_id and natwin.election_id = provballot.election_id and provballot.ballot_id = 2)
LEFT JOIN muni_perc nat_anc ON (muni.id = nat_anc.muni_id and natwin.election_id = nat_anc.election_id and nat_anc.ballot_id = 1 and nat_anc.party_id = 24)
LEFT JOIN muni_perc reg_anc ON (muni.id = reg_anc.muni_id and regwin.election_id = reg_anc.election_id and reg_anc.ballot_id = 7 and reg_anc.party_id = 24)
LEFT JOIN muni_perc prov_anc ON (muni.id = prov_anc.muni_id and natwin.election_id = prov_anc.election_id and prov_anc.ballot_id = 2 and prov_anc.party_id = 24)
LEFT JOIN muni_perc nat_da ON (muni.id = nat_da.muni_id and natwin.election_id = nat_da.election_id and nat_da.ballot_id = 1 and nat_da.party_id = 106)
LEFT JOIN muni_perc reg_da ON (muni.id = reg_da.muni_id and regwin.election_id = reg_da.election_id and reg_da.ballot_id = 7 and reg_da.party_id = 106)
LEFT JOIN muni_perc prov_da ON (muni.id = prov_da.muni_id and natwin.election_id = prov_da.election_id and prov_da.ballot_id = 2 and prov_da.party_id = 106)
LEFT JOIN muni_perc nat_eff ON (muni.id = nat_eff.muni_id and natwin.election_id = nat_eff.election_id and nat_eff.ballot_id = 1 and nat_eff.party_id = 122)
LEFT JOIN muni_perc reg_eff ON (muni.id = reg_eff.muni_id and regwin.election_id = reg_eff.election_id and reg_eff.ballot_id = 7 and reg_eff.party_id = 122)
LEFT JOIN muni_perc prov_eff ON (muni.id = prov_eff.muni_id and natwin.election_id = prov_eff.election_id and prov_eff.ballot_id = 2 and prov_eff.party_id = 122)
LEFT JOIN muni_perc nat_ifp ON (muni.id = nat_ifp.muni_id and natwin.election_id = nat_ifp.election_id and nat_ifp.ballot_id = 1 and nat_ifp.party_id = 165)
LEFT JOIN muni_perc reg_ifp ON (muni.id = reg_ifp.muni_id and regwin.election_id = reg_ifp.election_id and reg_ifp.ballot_id = 7 and reg_ifp.party_id = 165)
LEFT JOIN muni_perc prov_ifp ON (muni.id = prov_ifp.muni_id and natwin.election_id = prov_ifp.election_id and prov_ifp.ballot_id = 2 and prov_ifp.party_id = 165)
LEFT JOIN muni_perc nat_vfplus ON (muni.id = nat_vfplus.muni_id and natwin.election_id = nat_vfplus.election_id and nat_vfplus.ballot_id = 1 and nat_vfplus.party_id = 374)
LEFT JOIN muni_perc reg_vfplus ON (muni.id = reg_vfplus.muni_id and regwin.election_id = reg_vfplus.election_id and reg_vfplus.ballot_id = 7 and reg_vfplus.party_id = 374)
LEFT JOIN muni_perc prov_vfplus ON (muni.id = prov_vfplus.muni_id and natwin.election_id = prov_vfplus.election_id and prov_vfplus.ballot_id = 2 and prov_vfplus.party_id = 374)
LEFT JOIN muni_perc nat_mk ON (muni.id = nat_mk.muni_id and natwin.election_id = nat_mk.election_id and nat_mk.ballot_id = 1 and nat_mk.party_id = 664)
LEFT JOIN muni_perc reg_mk ON (muni.id = reg_mk.muni_id and regwin.election_id = reg_mk.election_id and reg_mk.ballot_id = 7 and reg_mk.party_id = 664)
LEFT JOIN muni_perc prov_mk ON (muni.id = prov_mk.muni_id and natwin.election_id = prov_mk.election_id and prov_mk.ballot_id = 2 and prov_mk.party_id = 664)
;
CREATE INDEX ON tiles.muni(election_id);
CREATE INDEX ON tiles.muni USING gist(geom);
--
DROP MATERIALIZED VIEW tiles.dist;
CREATE MATERIALIZED VIEW tiles.dist AS
SELECT
dist.id,
natwin.election_id,
dist.code,
COALESCE(natp.abbrev, 'TIE') as nat_win_party,
floor(natwin.perc)::int as nat_win_perc,
COALESCE(regp.abbrev, 'TIE') as reg_win_party,
floor(regwin.perc)::int as reg_win_perc,
COALESCE(provp.abbrev, 'TIE') as prov_win_party,
floor(provwin.perc)::int as prov_win_perc,
coalesce(floor(natballot.total*100.0/nullif(natballot.regpop, 0)), 0)::int as nat_turnout,
coalesce(floor(regballot.total*100.0/nullif(regballot.regpop, 0)), 0)::int as reg_turnout,
coalesce(floor(provballot.total*100.0/nullif(provballot.regpop, 0)), 0)::int as prov_turnout,
coalesce(floor(nat_anc.perc), 0)::int as nat_anc,
coalesce(floor(reg_anc.perc), 0)::int as reg_anc,
coalesce(floor(prov_anc.perc), 0)::int as prov_anc,
coalesce(floor(nat_da.perc), 0)::int as nat_da,
coalesce(floor(reg_da.perc), 0)::int as reg_da,
coalesce(floor(prov_da.perc), 0)::int as prov_da,
coalesce(floor(nat_eff.perc), 0)::int as nat_eff,
coalesce(floor(reg_eff.perc), 0)::int as reg_eff,
coalesce(floor(prov_eff.perc), 0)::int as prov_eff,
coalesce(floor(nat_ifp.perc), 0)::int as nat_ifp,
coalesce(floor(reg_ifp.perc), 0)::int as reg_ifp,
coalesce(floor(prov_ifp.perc), 0)::int as prov_ifp,
coalesce(floor(nat_vfplus.perc), 0)::int as nat_vfplus,
coalesce(floor(reg_vfplus.perc), 0)::int as reg_vfplus,
coalesce(floor(prov_vfplus.perc), 0)::int as prov_vfplus,
coalesce(floor(nat_mk.perc), 0)::int as nat_mk,
coalesce(floor(reg_mk.perc), 0)::int as reg_mk,
coalesce(floor(prov_mk.perc), 0)::int as prov_mk,
dist.geom
FROM dist
JOIN dist_single_winner natwin ON dist.id = natwin.dist_id and natwin.ballot_id = 1
LEFT JOIN party natp ON natwin.party_id = natp.id
LEFT JOIN dist_single_winner regwin ON dist.id = regwin.dist_id and regwin.ballot_id = 7 and natwin.election_id = regwin.election_id
LEFT JOIN party regp ON regwin.party_id = regp.id
LEFT JOIN dist_single_winner provwin ON dist.id = provwin.dist_id and provwin.ballot_id = 2 and natwin.election_id = provwin.election_id
LEFT JOIN party provp ON provwin.party_id = provp.id
LEFT JOIN dist_ballot_total natballot ON (dist.id = natballot.dist_id and natwin.election_id = natballot.election_id and natballot.ballot_id = 1)
LEFT JOIN dist_ballot_total regballot ON (dist.id = regballot.dist_id and regwin.election_id = regballot.election_id and regballot.ballot_id = 7)
LEFT JOIN dist_ballot_total provballot ON (dist.id = provballot.dist_id and natwin.election_id = provballot.election_id and provballot.ballot_id = 2)
LEFT JOIN dist_perc nat_anc ON (dist.id = nat_anc.dist_id and natwin.election_id = nat_anc.election_id and nat_anc.ballot_id = 1 and nat_anc.party_id = 24)
LEFT JOIN dist_perc reg_anc ON (dist.id = reg_anc.dist_id and regwin.election_id = reg_anc.election_id and reg_anc.ballot_id = 7 and reg_anc.party_id = 24)
LEFT JOIN dist_perc prov_anc ON (dist.id = prov_anc.dist_id and natwin.election_id = prov_anc.election_id and prov_anc.ballot_id = 2 and prov_anc.party_id = 24)
LEFT JOIN dist_perc nat_da ON (dist.id = nat_da.dist_id and natwin.election_id = nat_da.election_id and nat_da.ballot_id = 1 and nat_da.party_id = 106)
LEFT JOIN dist_perc reg_da ON (dist.id = reg_da.dist_id and regwin.election_id = reg_da.election_id and reg_da.ballot_id = 7 and reg_da.party_id = 106)
LEFT JOIN dist_perc prov_da ON (dist.id = prov_da.dist_id and natwin.election_id = prov_da.election_id and prov_da.ballot_id = 2 and prov_da.party_id = 106)
LEFT JOIN dist_perc nat_eff ON (dist.id = nat_eff.dist_id and natwin.election_id = nat_eff.election_id and nat_eff.ballot_id = 1 and nat_eff.party_id = 122)
LEFT JOIN dist_perc reg_eff ON (dist.id = reg_eff.dist_id and regwin.election_id = reg_eff.election_id and reg_eff.ballot_id = 7 and reg_eff.party_id = 122)
LEFT JOIN dist_perc prov_eff ON (dist.id = prov_eff.dist_id and natwin.election_id = prov_eff.election_id and prov_eff.ballot_id = 2 and prov_eff.party_id = 122)
LEFT JOIN dist_perc nat_ifp ON (dist.id = nat_ifp.dist_id and natwin.election_id = nat_ifp.election_id and nat_ifp.ballot_id = 1 and nat_ifp.party_id = 165)
LEFT JOIN dist_perc reg_ifp ON (dist.id = reg_ifp.dist_id and regwin.election_id = reg_ifp.election_id and reg_ifp.ballot_id = 7 and reg_ifp.party_id = 165)
LEFT JOIN dist_perc prov_ifp ON (dist.id = prov_ifp.dist_id and natwin.election_id = prov_ifp.election_id and prov_ifp.ballot_id = 2 and prov_ifp.party_id = 165)
LEFT JOIN dist_perc nat_vfplus ON (dist.id = nat_vfplus.dist_id and natwin.election_id = nat_vfplus.election_id and nat_vfplus.ballot_id = 1 and nat_vfplus.party_id = 374)
LEFT JOIN dist_perc reg_vfplus ON (dist.id = reg_vfplus.dist_id and regwin.election_id = reg_vfplus.election_id and reg_vfplus.ballot_id = 7 and reg_vfplus.party_id = 374)
LEFT JOIN dist_perc prov_vfplus ON (dist.id = prov_vfplus.dist_id and natwin.election_id = prov_vfplus.election_id and prov_vfplus.ballot_id = 2 and prov_vfplus.party_id = 374)
LEFT JOIN dist_perc nat_mk ON (dist.id = nat_mk.dist_id and natwin.election_id = nat_mk.election_id and nat_mk.ballot_id = 1 and nat_mk.party_id = 664)
LEFT JOIN dist_perc reg_mk ON (dist.id = reg_mk.dist_id and regwin.election_id = reg_mk.election_id and reg_mk.ballot_id = 7 and reg_mk.party_id = 664)
LEFT JOIN dist_perc prov_mk ON (dist.id = prov_mk.dist_id and natwin.election_id = prov_mk.election_id and prov_mk.ballot_id = 2 and prov_mk.party_id = 664)
;
CREATE INDEX ON tiles.dist(election_id);
CREATE INDEX ON tiles.dist USING gist(geom);
--
DROP MATERIALIZED VIEW tiles.prov;
CREATE MATERIALIZED VIEW tiles.prov AS
SELECT
prov.id,
natwin.election_id,
prov.code,
COALESCE(natp.abbrev, 'TIE') as nat_win_party,
floor(natwin.perc)::int as nat_win_perc,
COALESCE(regp.abbrev, 'TIE') as reg_win_party,
floor(regwin.perc)::int as reg_win_perc,
COALESCE(provp.abbrev, 'TIE') as prov_win_party,
floor(provwin.perc)::int as prov_win_perc,
coalesce(floor(natballot.total*100.0/nullif(natballot.regpop, 0)), 0)::int as nat_turnout,
coalesce(floor(regballot.total*100.0/nullif(regballot.regpop, 0)), 0)::int as reg_turnout,
coalesce(floor(provballot.total*100.0/nullif(provballot.regpop, 0)), 0)::int as prov_turnout,
coalesce(floor(nat_anc.perc), 0)::int as nat_anc,
coalesce(floor(reg_anc.perc), 0)::int as reg_anc,
coalesce(floor(prov_anc.perc), 0)::int as prov_anc,
coalesce(floor(nat_da.perc), 0)::int as nat_da,
coalesce(floor(reg_da.perc), 0)::int as reg_da,
coalesce(floor(prov_da.perc), 0)::int as prov_da,
coalesce(floor(nat_eff.perc), 0)::int as nat_eff,
coalesce(floor(reg_eff.perc), 0)::int as reg_eff,
coalesce(floor(prov_eff.perc), 0)::int as prov_eff,
coalesce(floor(nat_ifp.perc), 0)::int as nat_ifp,
coalesce(floor(reg_ifp.perc), 0)::int as reg_ifp,
coalesce(floor(prov_ifp.perc), 0)::int as prov_ifp,
coalesce(floor(nat_vfplus.perc), 0)::int as nat_vfplus,
coalesce(floor(reg_vfplus.perc), 0)::int as reg_vfplus,
coalesce(floor(prov_vfplus.perc), 0)::int as prov_vfplus,
coalesce(floor(nat_mk.perc), 0)::int as nat_mk,
coalesce(floor(reg_mk.perc), 0)::int as reg_mk,
coalesce(floor(prov_mk.perc), 0)::int as prov_mk,
prov.geom
FROM prov
JOIN prov_single_winner natwin ON prov.id = natwin.prov_id and natwin.ballot_id = 1
LEFT JOIN party natp ON natwin.party_id = natp.id
LEFT JOIN prov_single_winner regwin ON prov.id = regwin.prov_id and regwin.ballot_id = 7 and natwin.election_id = regwin.election_id
LEFT JOIN party regp ON regwin.party_id = regp.id
LEFT JOIN prov_single_winner provwin ON prov.id = provwin.prov_id and provwin.ballot_id = 2 and natwin.election_id = provwin.election_id
LEFT JOIN party provp ON provwin.party_id = provp.id
LEFT JOIN prov_ballot_total natballot ON (prov.id = natballot.prov_id and natwin.election_id = natballot.election_id and natballot.ballot_id = 1)
LEFT JOIN prov_ballot_total regballot ON (prov.id = regballot.prov_id and regwin.election_id = regballot.election_id and regballot.ballot_id = 7)
LEFT JOIN prov_ballot_total provballot ON (prov.id = provballot.prov_id and natwin.election_id = provballot.election_id and provballot.ballot_id = 2)
LEFT JOIN prov_perc nat_anc ON (prov.id = nat_anc.prov_id and natwin.election_id = nat_anc.election_id and nat_anc.ballot_id = 1 and nat_anc.party_id = 24)
LEFT JOIN prov_perc reg_anc ON (prov.id = reg_anc.prov_id and regwin.election_id = reg_anc.election_id and reg_anc.ballot_id = 7 and reg_anc.party_id = 24)
LEFT JOIN prov_perc prov_anc ON (prov.id = prov_anc.prov_id and natwin.election_id = prov_anc.election_id and prov_anc.ballot_id = 2 and prov_anc.party_id = 24)
LEFT JOIN prov_perc nat_da ON (prov.id = nat_da.prov_id and natwin.election_id = nat_da.election_id and nat_da.ballot_id = 1 and nat_da.party_id = 106)
LEFT JOIN prov_perc reg_da ON (prov.id = reg_da.prov_id and regwin.election_id = reg_da.election_id and reg_da.ballot_id = 7 and reg_da.party_id = 106)
LEFT JOIN prov_perc prov_da ON (prov.id = prov_da.prov_id and natwin.election_id = prov_da.election_id and prov_da.ballot_id = 2 and prov_da.party_id = 106)
LEFT JOIN prov_perc nat_eff ON (prov.id = nat_eff.prov_id and natwin.election_id = nat_eff.election_id and nat_eff.ballot_id = 1 and nat_eff.party_id = 122)
LEFT JOIN prov_perc reg_eff ON (prov.id = reg_eff.prov_id and regwin.election_id = reg_eff.election_id and reg_eff.ballot_id = 7 and reg_eff.party_id = 122)
LEFT JOIN prov_perc prov_eff ON (prov.id = prov_eff.prov_id and natwin.election_id = prov_eff.election_id and prov_eff.ballot_id = 2 and prov_eff.party_id = 122)
LEFT JOIN prov_perc nat_ifp ON (prov.id = nat_ifp.prov_id and natwin.election_id = nat_ifp.election_id and nat_ifp.ballot_id = 1 and nat_ifp.party_id = 165)
LEFT JOIN prov_perc reg_ifp ON (prov.id = reg_ifp.prov_id and regwin.election_id = reg_ifp.election_id and reg_ifp.ballot_id = 7 and reg_ifp.party_id = 165)
LEFT JOIN prov_perc prov_ifp ON (prov.id = prov_ifp.prov_id and natwin.election_id = prov_ifp.election_id and prov_ifp.ballot_id = 2 and prov_ifp.party_id = 165)
LEFT JOIN prov_perc nat_vfplus ON (prov.id = nat_vfplus.prov_id and natwin.election_id = nat_vfplus.election_id and nat_vfplus.ballot_id = 1 and nat_vfplus.party_id = 374)
LEFT JOIN prov_perc reg_vfplus ON (prov.id = reg_vfplus.prov_id and regwin.election_id = reg_vfplus.election_id and reg_vfplus.ballot_id = 7 and reg_vfplus.party_id = 374)
LEFT JOIN prov_perc prov_vfplus ON (prov.id = prov_vfplus.prov_id and natwin.election_id = prov_vfplus.election_id and prov_vfplus.ballot_id = 2 and prov_vfplus.party_id = 374)
LEFT JOIN prov_perc nat_mk ON (prov.id = nat_mk.prov_id and natwin.election_id = nat_mk.election_id and nat_mk.ballot_id = 1 and nat_mk.party_id = 664)
LEFT JOIN prov_perc reg_mk ON (prov.id = reg_mk.prov_id and regwin.election_id = reg_mk.election_id and reg_mk.ballot_id = 7 and reg_mk.party_id = 664)
LEFT JOIN prov_perc prov_mk ON (prov.id = prov_mk.prov_id and natwin.election_id = prov_mk.election_id and prov_mk.ballot_id = 2 and prov_mk.party_id = 664)
;
CREATE INDEX ON tiles.prov(election_id);
CREATE INDEX ON tiles.prov USING gist(geom);
END;