· 8 years ago · May 15, 2018, 08:02 AM
1root : INFO
2root : INFO Start gnaf-loader
3root : INFO - running Python 3.6.4 with Psycopg2 2.7.4
4root : INFO - on Linux #40~16.04.1-Ubuntu SMP Fri Feb 16 23:25:58 UTC 2018
5root : INFO - using Postgres 10.1 on x86_64-pc-linux-gnu and PostGIS 2.4.3 (with GEOS 3.6.1-CAPI-1.10.1)
6root : INFO
7root : INFO Part 1 of 6 : Create schemas : 2018-05-15 15:19:01.312356
8root : INFO Part 1 of 6 : Schemas created! : 0:00:00.010341
9root : INFO
10root : INFO Part 2 of 6 : Start raw GNAF load : 2018-05-15 15:19:01.322955
11root : INFO - Step 1 of 7 : tables dropped : 0:00:00.002828
12root : INFO - Step 2 of 7 : database NOT vacuumed
13root : INFO - Step 3 of 7 : UNLOGGED tables created : 0:00:00.041260
14root : INFO - Loading state ACT
15root : INFO - Loading state NSW
16root : INFO - Loading state NT
17root : INFO - Loading state OT
18root : INFO - Loading state QLD
19root : INFO - Loading state SA
20root : INFO - Loading state TAS
21root : INFO - Loading state VIC
22root : INFO - Loading state WA
23root : INFO - Step 4 of 7 : tables populated : 0:05:11.779272
24root : INFO - Step 5 of 7 : indexes created: 0:05:52.746429
25root : INFO - Step 6 of 7 : primary & foreign keys NOT created
26root : INFO - Step 7 of 7 : tables analysed : 0:00:25.776239
27root : INFO Part 2 of 6 : Raw GNAF loaded! : 0:11:30.356715
28root : INFO
29root : INFO Part 3 of 6 : Start raw admin boundary load : 2018-05-15 15:30:31.680591
30root : INFO Importing aus_mb_2011_polygon - Couldn't cluster on spatial index
31root : INFO - Step 1 of 3 : raw admin boundaries loaded : 0:00:01.859617
32root : INFO SQL FAILED! : --------------------------------------------------------------------------------------------
33-- state electoral boundaries - choose bdys that will be current until at least 3 months from now
34---------------------------------------------------------------------------------------------------
35
36-- create lower house table
37DROP TABLE IF EXISTS admin_bdys.state_lower_house_electorates CASCADE;
38CREATE TABLE admin_bdys.state_lower_house_electorates AS
39SELECT bdy.gid,
40 tab.se_pid AS se_lower_pid,
41 tab.name,
42 tab.dt_gazetd,
43 tab.eff_start,
44 tab.eff_end,
45 aut.name_aut AS electorate_class,
46 ste.st_abbrev AS state,
47 bdy.geom
48 FROM raw_admin_bdys.aus_state_electoral AS tab
49 INNER JOIN raw_admin_bdys.aus_state_electoral_polygon AS bdy ON tab.se_pid = bdy.se_pid
50 INNER JOIN raw_admin_bdys.aus_state AS ste ON tab.state_pid = ste.state_pid
51 INNER JOIN raw_admin_bdys.aus_state_electoral_class_aut AS aut ON tab.secl_code = aut.code_aut
52 WHERE (tab.eff_end > now() + interval '3 months'
53 OR (tab.eff_start <= now() + interval '3 months' AND tab.eff_end IS NULL))
54 AND tab.secl_code <> '3';
55
56ALTER TABLE admin_bdys.state_lower_house_electorates ADD CONSTRAINT state_lower_house_electorates_pk PRIMARY KEY (gid);
57CREATE INDEX state_lower_house_electorates_geom_idx ON admin_bdys.state_lower_house_electorates USING gist(geom);
58ALTER TABLE admin_bdys.state_lower_house_electorates CLUSTER ON state_lower_house_electorates_geom_idx;
59
60 : relation "raw_admin_bdys.aus_state_electoral" does not exist
61LINE 17: FROM raw_admin_bdys.aus_state_electoral AS tab
62 ^
63
64root : INFO SQL FAILED! :
65-- create upper house table
66DROP TABLE IF EXISTS admin_bdys.state_upper_house_electorates CASCADE;
67CREATE TABLE admin_bdys.state_upper_house_electorates AS
68SELECT bdy.gid,
69 tab.se_pid AS se_upper_pid,
70 tab.name,
71 tab.dt_gazetd,
72 tab.eff_start,
73 tab.eff_end,
74 aut.name_aut AS electorate_class,
75 ste.st_abbrev AS state,
76 bdy.geom
77 FROM raw_admin_bdys.aus_state_electoral AS tab
78 INNER JOIN raw_admin_bdys.aus_state_electoral_polygon AS bdy ON tab.se_pid = bdy.se_pid
79 INNER JOIN raw_admin_bdys.aus_state AS ste ON tab.state_pid = ste.state_pid
80 INNER JOIN raw_admin_bdys.aus_state_electoral_class_aut AS aut ON tab.secl_code = aut.code_aut
81 WHERE (tab.eff_end > now() + interval '3 months'
82 OR (tab.eff_start <= now() AND tab.eff_end IS NULL))
83 AND tab.secl_code = '3'
84 AND ste.st_abbrev NOT IN ('NSW', 'SA');
85
86ALTER TABLE admin_bdys.state_upper_house_electorates ADD CONSTRAINT state_upper_house_electorates_pk PRIMARY KEY (gid);
87CREATE INDEX state_upper_house_electorates_geom_idx ON admin_bdys.state_upper_house_electorates USING gist(geom);
88ALTER TABLE admin_bdys.state_upper_house_electorates CLUSTER ON state_upper_house_electorates_geom_idx;
89
90
91 : relation "raw_admin_bdys.aus_state_electoral" does not exist
92LINE 14: FROM raw_admin_bdys.aus_state_electoral AS tab
93 ^
94
95root : INFO SQL FAILED! : -------------------------------------------------------------------------------
96-- commonwealth electoral boundaries
97--------------------------------------------------------------------------------------
98
99DROP TABLE IF EXISTS admin_bdys.commonwealth_electorates CASCADE;
100CREATE TABLE admin_bdys.commonwealth_electorates AS
101SELECT bdy.gid,
102 tab.ce_pid,
103 tab.name,
104 tab.dt_gazetd,
105 ste.st_abbrev AS state,
106 tab.redistyear,
107 bdy.geom
108 FROM raw_admin_bdys.aus_comm_electoral AS tab
109 INNER JOIN raw_admin_bdys.aus_comm_electoral_polygon AS bdy ON tab.ce_pid = bdy.ce_pid
110 INNER JOIN raw_admin_bdys.aus_state AS ste ON tab.state_pid = ste.state_pid;
111
112ALTER TABLE admin_bdys.commonwealth_electorates ADD CONSTRAINT commonwealth_electorates_pk PRIMARY KEY (gid);
113CREATE INDEX commonwealth_electorates_geom_idx ON admin_bdys.commonwealth_electorates USING gist(geom);
114ALTER TABLE admin_bdys.commonwealth_electorates CLUSTER ON commonwealth_electorates_geom_idx;
115
116
117 : relation "raw_admin_bdys.aus_comm_electoral" does not exist
118LINE 14: FROM raw_admin_bdys.aus_comm_electoral AS tab
119 ^
120
121root : INFO SQL FAILED! : -------------------------------------------------------------------------------
122-- states
123--------------------------------------------------------------------------------------
124
125DROP TABLE IF EXISTS admin_bdys.state_bdys CASCADE;
126CREATE TABLE admin_bdys.state_bdys AS
127SELECT bdy.gid,
128 tab.state_pid,
129 tab.state_name AS name,
130 tab.st_abbrev AS state,
131 bdy.geom
132 FROM raw_admin_bdys.aus_state AS tab
133 INNER JOIN raw_admin_bdys.aus_state_polygon AS bdy ON tab.state_pid = bdy.state_pid;
134
135ALTER TABLE admin_bdys.state_bdys ADD CONSTRAINT state_bdys_pk PRIMARY KEY (gid);
136CREATE INDEX state_bdys_geom_idx ON admin_bdys.state_bdys USING gist(geom);
137ALTER TABLE admin_bdys.state_bdys CLUSTER ON state_bdys_geom_idx;
138
139
140 : relation "raw_admin_bdys.aus_state" does not exist
141LINE 12: FROM raw_admin_bdys.aus_state AS tab
142 ^
143
144root : INFO SQL FAILED! : -------------------------------------------------------------------------------
145-- local government areas
146--------------------------------------------------------------------------------------
147
148DROP TABLE IF EXISTS admin_bdys.local_government_areas CASCADE;
149CREATE TABLE admin_bdys.local_government_areas AS
150SELECT bdy.gid,
151 tab.lga_pid,
152 tab.abb_name AS name,
153 tab.lga_name AS full_name,
154 ste.st_abbrev AS state,
155 bdy.geom
156 FROM raw_admin_bdys.aus_lga AS tab
157 INNER JOIN raw_admin_bdys.aus_lga_polygon AS bdy ON tab.lga_pid = bdy.lga_pid
158 INNER JOIN raw_admin_bdys.aus_state AS ste ON tab.state_pid = ste.state_pid;
159
160ALTER TABLE admin_bdys.local_government_areas ADD CONSTRAINT local_government_areas_pk PRIMARY KEY (gid);
161CREATE INDEX local_government_areas_geom_idx ON admin_bdys.local_government_areas USING gist(geom);
162ALTER TABLE admin_bdys.local_government_areas CLUSTER ON local_government_areas_geom_idx;
163
164
165 : relation "raw_admin_bdys.aus_lga" does not exist
166LINE 13: FROM raw_admin_bdys.aus_lga AS tab
167 ^
168
169root : INFO SQL FAILED! : -------------------------------------------------------------------------------
170-- ABS census 2011 - statistical area 1's
171--------------------------------------------------------------------------------------
172
173DROP TABLE IF EXISTS admin_bdys.abs_2011_sa1 CASCADE;
174CREATE TABLE admin_bdys.abs_2011_sa1 AS
175SELECT bdy.gid,
176 tab.sa1_11main,
177 tab.sa1_11_7cd,
178 tab.sa2_11main,
179 tab.sa2_11_5cd,
180 tab.sa2_11name::text,
181 tab.sa3_11code,
182 tab.sa3_11name::text,
183 tab.sa4_11code,
184 tab.sa4_11name::text,
185 tab.gcc_11code::text,
186 tab.gcc_11name::text,
187 ste.st_abbrev::text AS state,
188 tab.area_sqm,
189 bdy.geom
190 FROM raw_admin_bdys.aus_sa1_2011 AS tab
191 INNER JOIN raw_admin_bdys.aus_sa1_2011_polygon AS bdy ON tab.sa1_11pid = bdy.sa1_11pid
192 INNER JOIN raw_admin_bdys.aus_state AS ste ON tab.state_pid = ste.state_pid;
193
194ALTER TABLE admin_bdys.abs_2011_sa1 ADD CONSTRAINT abs_2011_sa1_pk PRIMARY KEY (gid);
195CREATE INDEX abs_2011_sa1_geom_idx ON admin_bdys.abs_2011_sa1 USING gist(geom);
196ALTER TABLE admin_bdys.abs_2011_sa1 CLUSTER ON abs_2011_sa1_geom_idx;
197
198
199 : relation "raw_admin_bdys.aus_sa1_2011" does not exist
200LINE 22: FROM raw_admin_bdys.aus_sa1_2011 AS tab
201 ^
202
203root : INFO SQL FAILED! : -------------------------------------------------------------------------------
204-- local government wards
205--------------------------------------------------------------------------------------
206
207DROP TABLE IF EXISTS admin_bdys.local_government_wards CASCADE;
208CREATE TABLE admin_bdys.local_government_wards AS
209SELECT bdy.gid,
210 tab.ward_pid,
211 lga.lga_pid,
212 tab.name,
213 tab.name AS lga_name,
214 ste.st_abbrev AS state,
215 bdy.geom
216 FROM raw_admin_bdys.aus_ward AS tab
217 INNER JOIN raw_admin_bdys.aus_lga AS lga ON tab.lga_pid = lga.lga_pid
218 INNER JOIN raw_admin_bdys.aus_ward_polygon AS bdy ON tab.ward_pid = bdy.ward_pid
219 INNER JOIN raw_admin_bdys.aus_state AS ste ON tab.state_pid = ste.state_pid;
220
221ALTER TABLE admin_bdys.local_government_wards ADD CONSTRAINT local_government_wards_pk PRIMARY KEY (gid);
222CREATE INDEX local_government_wards_geom_idx ON admin_bdys.local_government_wards USING gist(geom);
223ALTER TABLE admin_bdys.local_government_wards CLUSTER ON local_government_wards_geom_idx;
224 : relation "raw_admin_bdys.aus_lga" does not exist
225LINE 15: INNER JOIN raw_admin_bdys.aus_lga AS lga ON tab.lga_pid = ...
226 ^
227
228root : INFO SQL FAILED! : -------------------------------------------------------------------------------
229-- ABS census 2011 - statistical area 2's
230--------------------------------------------------------------------------------------
231
232DROP TABLE IF EXISTS admin_bdys.abs_2011_sa2 CASCADE;
233CREATE TABLE admin_bdys.abs_2011_sa2 AS
234SELECT bdy.gid,
235 tab.sa2_11main,
236 tab.sa2_11_5cd,
237 tab.sa2_11name::text,
238 tab.sa3_11code,
239 tab.sa3_11name::text,
240 tab.sa4_11code,
241 tab.sa4_11name::text,
242 tab.gcc_11code::text,
243 tab.gcc_11name::text,
244 ste.st_abbrev::text AS state,
245 tab.area_sqm,
246 bdy.geom
247 FROM raw_admin_bdys.aus_sa2_2011 AS tab
248 INNER JOIN raw_admin_bdys.aus_sa2_2011_polygon AS bdy ON tab.sa2_11pid = bdy.sa2_11pid
249 INNER JOIN raw_admin_bdys.aus_state AS ste ON tab.state_pid = ste.state_pid;
250
251ALTER TABLE admin_bdys.abs_2011_sa2 ADD CONSTRAINT abs_2011_sa2_pk PRIMARY KEY (gid);
252CREATE INDEX abs_2011_sa2_geom_idx ON admin_bdys.abs_2011_sa2 USING gist(geom);
253ALTER TABLE admin_bdys.abs_2011_sa2 CLUSTER ON abs_2011_sa2_geom_idx;
254
255
256 : relation "raw_admin_bdys.aus_sa2_2011" does not exist
257LINE 20: FROM raw_admin_bdys.aus_sa2_2011 AS tab
258 ^
259
260root : INFO SQL FAILED! :
261--------------------------------------------------------------------------------------
262-- ABS census 2011 - meshblocks
263--------------------------------------------------------------------------------------
264
265DROP TABLE IF EXISTS admin_bdys.abs_2011_mb CASCADE;
266CREATE TABLE admin_bdys.abs_2011_mb AS
267SELECT bdy.gid,
268 tab.mb_11code::text,
269 aut.name::text AS mb_category,
270 tab.sa1_11main,
271 tab.sa1_11_7cd,
272 tab.sa2_11main,
273 tab.sa2_11_5cd,
274 tab.sa2_11name::text,
275 tab.sa3_11code,
276 tab.sa3_11name::text,
277 tab.sa4_11code,
278 tab.sa4_11name::text,
279 tab.gcc_11code::text,
280 tab.gcc_11name::text,
281 ste.st_abbrev::text AS state,
282 tab.area_sqm,
283 tab.mb11_pop,
284 tab.mb11_dwell,
285 bdy.geom
286 FROM raw_admin_bdys.aus_mb_2011 AS tab
287 INNER JOIN raw_admin_bdys.aus_mb_2011_polygon AS bdy ON tab.mb_11pid = bdy.mb_11pid
288 INNER JOIN (SELECT DISTINCT code, name FROM raw_admin_bdys.aus_mb_category_class_aut) AS aut ON tab.mb_cat_cd = aut.code
289 INNER JOIN raw_admin_bdys.aus_state AS ste ON tab.state_pid = ste.state_pid;
290
291ALTER TABLE admin_bdys.abs_2011_mb ADD CONSTRAINT abs_2011_mb_pk PRIMARY KEY (gid);
292CREATE INDEX abs_2011_mb_geom_idx ON admin_bdys.abs_2011_mb USING gist(geom);
293ALTER TABLE admin_bdys.abs_2011_mb CLUSTER ON abs_2011_mb_geom_idx;
294
295
296 : relation "raw_admin_bdys.aus_mb_2011" does not exist
297LINE 27: FROM raw_admin_bdys.aus_mb_2011 AS tab
298 ^
299
300root : INFO SQL FAILED! : -------------------------------------------------------------------------------
301-- ABS census 2011 - statistical area 3's
302--------------------------------------------------------------------------------------
303
304DROP TABLE IF EXISTS admin_bdys.abs_2011_sa3 CASCADE;
305CREATE TABLE admin_bdys.abs_2011_sa3 AS
306SELECT bdy.gid,
307 tab.sa3_11code,
308 tab.sa3_11name::text,
309 tab.sa4_11code,
310 tab.sa4_11name::text,
311 tab.gcc_11code::text,
312 tab.gcc_11name::text,
313 ste.st_abbrev::text AS state,
314 tab.area_sqm,
315 bdy.geom
316 FROM raw_admin_bdys.aus_sa3_2011 AS tab
317 INNER JOIN raw_admin_bdys.aus_sa3_2011_polygon AS bdy ON tab.sa3_11pid = bdy.sa3_11pid
318 INNER JOIN raw_admin_bdys.aus_state AS ste ON tab.state_pid = ste.state_pid;
319
320ALTER TABLE admin_bdys.abs_2011_sa3 ADD CONSTRAINT abs_2011_sa3_pk PRIMARY KEY (gid);
321CREATE INDEX abs_2011_sa3_geom_idx ON admin_bdys.abs_2011_sa3 USING gist(geom);
322ALTER TABLE admin_bdys.abs_2011_sa3 CLUSTER ON abs_2011_sa3_geom_idx;
323
324
325 : relation "raw_admin_bdys.aus_sa3_2011" does not exist
326LINE 17: FROM raw_admin_bdys.aus_sa3_2011 AS tab
327 ^
328
329root : INFO SQL FAILED! : -------------------------------------------------------------------------------
330-- ABS census 2011 - greater capital city statistical areas
331--------------------------------------------------------------------------------------
332
333DROP TABLE IF EXISTS admin_bdys.abs_2011_gccsa CASCADE;
334CREATE TABLE admin_bdys.abs_2011_gccsa AS
335SELECT bdy.gid,
336 tab.gcc_11code::text,
337 tab.gcc_11name::text,
338 ste.st_abbrev::text AS state,
339 tab.area_sqm,
340 bdy.geom
341 FROM raw_admin_bdys.aus_gccsa_2011 AS tab
342 INNER JOIN raw_admin_bdys.aus_gccsa_2011_polygon AS bdy ON tab.gcc_11pid = bdy.gcc_11pid
343 INNER JOIN raw_admin_bdys.aus_state AS ste ON tab.state_pid = ste.state_pid;
344
345ALTER TABLE admin_bdys.abs_2011_gccsa ADD CONSTRAINT abs_2011_gccsa_pk PRIMARY KEY (gid);
346CREATE INDEX abs_2011_gccsa_geom_idx ON admin_bdys.abs_2011_gccsa USING gist(geom);
347ALTER TABLE admin_bdys.abs_2011_gccsa CLUSTER ON abs_2011_gccsa_geom_idx;
348 : relation "raw_admin_bdys.aus_state" does not exist
349LINE 15: INNER JOIN raw_admin_bdys.aus_state AS ste ON tab.state_pi...
350 ^
351
352root : INFO SQL FAILED! :
353--------------------------------------------------------------------------------------
354-- ABS census 2016 - meshblocks
355--------------------------------------------------------------------------------------
356
357DROP TABLE IF EXISTS admin_bdys.abs_2016_mb CASCADE;
358CREATE TABLE admin_bdys.abs_2016_mb AS
359SELECT bdy.gid,
360 tab.mb_16code::text,
361 aut.name::text AS mb_category,
362 tab.sa1_16main,
363 tab.sa1_16_7cd,
364 tab.sa2_16main,
365 tab.sa2_16_5cd,
366 tab.sa2_16name::text,
367 tab.sa3_16code,
368 tab.sa3_16name::text,
369 tab.sa4_16code,
370 tab.sa4_16name::text,
371 tab.gcc_16code::text,
372 tab.gcc_16name::text,
373 ste.st_abbrev::text AS state,
374 tab.area_sqm,
375 tab.mb16_pop,
376 tab.mb16_dwell,
377 bdy.geom
378 FROM raw_admin_bdys.aus_mb_2016 AS tab
379 INNER JOIN raw_admin_bdys.aus_mb_2016_polygon AS bdy ON tab.mb_16pid = bdy.mb_16pid
380 INNER JOIN (SELECT DISTINCT code, name FROM raw_admin_bdys.aus_mb_category_class_aut) AS aut ON tab.mb_cat_cd = aut.code
381 INNER JOIN raw_admin_bdys.aus_state AS ste ON tab.state_pid = ste.state_pid;
382
383ALTER TABLE admin_bdys.abs_2016_mb ADD CONSTRAINT abs_2016_mb_pk PRIMARY KEY (gid);
384CREATE INDEX abs_2016_mb_geom_idx ON admin_bdys.abs_2016_mb USING gist(geom);
385ALTER TABLE admin_bdys.abs_2016_mb CLUSTER ON abs_2016_mb_geom_idx;
386
387
388 : relation "raw_admin_bdys.aus_mb_2016" does not exist
389LINE 27: FROM raw_admin_bdys.aus_mb_2016 AS tab
390 ^
391
392root : INFO SQL FAILED! : -------------------------------------------------------------------------------
393-- ABS census 2011 - statistical area 4's
394--------------------------------------------------------------------------------------
395
396DROP TABLE IF EXISTS admin_bdys.abs_2011_sa4 CASCADE;
397CREATE TABLE admin_bdys.abs_2011_sa4 AS
398SELECT bdy.gid,
399 tab.sa4_11code,
400 tab.sa4_11name::text,
401 tab.gcc_11code::text,
402 tab.gcc_11name::text,
403 ste.st_abbrev::text AS state,
404 tab.area_sqm,
405 bdy.geom
406 FROM raw_admin_bdys.aus_sa4_2011 AS tab
407 INNER JOIN raw_admin_bdys.aus_sa4_2011_polygon AS bdy ON tab.sa4_11pid = bdy.sa4_11pid
408 INNER JOIN raw_admin_bdys.aus_state AS ste ON tab.state_pid = ste.state_pid;
409
410ALTER TABLE admin_bdys.abs_2011_sa4 ADD CONSTRAINT abs_2011_sa4_pk PRIMARY KEY (gid);
411CREATE INDEX abs_2011_sa4_geom_idx ON admin_bdys.abs_2011_sa4 USING gist(geom);
412ALTER TABLE admin_bdys.abs_2011_sa4 CLUSTER ON abs_2011_sa4_geom_idx;
413
414
415 : relation "raw_admin_bdys.aus_sa4_2011" does not exist
416LINE 15: FROM raw_admin_bdys.aus_sa4_2011 AS tab
417 ^
418
419root : INFO SQL FAILED! : -------------------------------------------------------------------------------
420-- ABS census 2016 - statistical area 2's
421--------------------------------------------------------------------------------------
422
423DROP TABLE IF EXISTS admin_bdys.abs_2016_sa2 CASCADE;
424CREATE TABLE admin_bdys.abs_2016_sa2 AS
425SELECT bdy.gid,
426 tab.sa2_16main,
427 tab.sa2_16_5cd,
428 tab.sa2_16name::text,
429 tab.sa3_16code,
430 tab.sa3_16name::text,
431 tab.sa4_16code,
432 tab.sa4_16name::text,
433 tab.gcc_16code::text,
434 tab.gcc_16name::text,
435 ste.st_abbrev::text AS state,
436 tab.area_sqm,
437 bdy.geom
438 FROM raw_admin_bdys.aus_sa2_2016 AS tab
439 INNER JOIN raw_admin_bdys.aus_sa2_2016_polygon AS bdy ON tab.sa2_16pid = bdy.sa2_16pid
440 INNER JOIN raw_admin_bdys.aus_state AS ste ON tab.state_pid = ste.state_pid;
441
442ALTER TABLE admin_bdys.abs_2016_sa2 ADD CONSTRAINT abs_2016_sa2_pk PRIMARY KEY (gid);
443CREATE INDEX abs_2016_sa2_geom_idx ON admin_bdys.abs_2016_sa2 USING gist(geom);
444ALTER TABLE admin_bdys.abs_2016_sa2 CLUSTER ON abs_2016_sa2_geom_idx;
445
446
447 : relation "raw_admin_bdys.aus_sa2_2016" does not exist
448LINE 20: FROM raw_admin_bdys.aus_sa2_2016 AS tab
449 ^
450
451root : INFO SQL FAILED! : -------------------------------------------------------------------------------
452-- ABS census 2016 - statistical area 1's
453--------------------------------------------------------------------------------------
454
455DROP TABLE IF EXISTS admin_bdys.abs_2016_sa1 CASCADE;
456CREATE TABLE admin_bdys.abs_2016_sa1 AS
457SELECT bdy.gid,
458 tab.sa1_16main,
459 tab.sa1_16_7cd,
460 tab.sa2_16main,
461 tab.sa2_16_5cd,
462 tab.sa2_16name::text,
463 tab.sa3_16code,
464 tab.sa3_16name::text,
465 tab.sa4_16code,
466 tab.sa4_16name::text,
467 tab.gcc_16code::text,
468 tab.gcc_16name::text,
469 ste.st_abbrev::text AS state,
470 tab.area_sqm,
471 bdy.geom
472 FROM raw_admin_bdys.aus_sa1_2016 AS tab
473 INNER JOIN raw_admin_bdys.aus_sa1_2016_polygon AS bdy ON tab.sa1_16pid = bdy.sa1_16pid
474 INNER JOIN raw_admin_bdys.aus_state AS ste ON tab.state_pid = ste.state_pid;
475
476ALTER TABLE admin_bdys.abs_2016_sa1 ADD CONSTRAINT abs_2016_sa1_pk PRIMARY KEY (gid);
477CREATE INDEX abs_2016_sa1_geom_idx ON admin_bdys.abs_2016_sa1 USING gist(geom);
478ALTER TABLE admin_bdys.abs_2016_sa1 CLUSTER ON abs_2016_sa1_geom_idx;
479
480
481 : relation "raw_admin_bdys.aus_sa1_2016" does not exist
482LINE 22: FROM raw_admin_bdys.aus_sa1_2016 AS tab
483 ^
484
485root : INFO SQL FAILED! : -------------------------------------------------------------------------------
486-- ABS census 2016 - greater capital city statistical areas
487--------------------------------------------------------------------------------------
488
489DROP TABLE IF EXISTS admin_bdys.abs_2016_gccsa CASCADE;
490CREATE TABLE admin_bdys.abs_2016_gccsa AS
491SELECT bdy.gid,
492 tab.gcc_16code::text,
493 tab.gcc_16name::text,
494 ste.st_abbrev::text AS state,
495 tab.area_sqm,
496 bdy.geom
497 FROM raw_admin_bdys.aus_gccsa_2016 AS tab
498 INNER JOIN raw_admin_bdys.aus_gccsa_2016_polygon AS bdy ON tab.gcc_16pid = bdy.gcc_16pid
499 INNER JOIN raw_admin_bdys.aus_state AS ste ON tab.state_pid = ste.state_pid;
500
501ALTER TABLE admin_bdys.abs_2016_gccsa ADD CONSTRAINT abs_2016_gccsa_pk PRIMARY KEY (gid);
502CREATE INDEX abs_2016_gccsa_geom_idx ON admin_bdys.abs_2016_gccsa USING gist(geom);
503ALTER TABLE admin_bdys.abs_2016_gccsa CLUSTER ON abs_2016_gccsa_geom_idx;
504 : relation "raw_admin_bdys.aus_gccsa_2016" does not exist
505LINE 13: FROM raw_admin_bdys.aus_gccsa_2016 AS tab
506 ^
507
508root : INFO SQL FAILED! : -------------------------------------------------------------------------------
509-- ABS census 2016 - statistical area 4's
510--------------------------------------------------------------------------------------
511
512DROP TABLE IF EXISTS admin_bdys.abs_2016_sa4 CASCADE;
513CREATE TABLE admin_bdys.abs_2016_sa4 AS
514SELECT bdy.gid,
515 tab.sa4_16code,
516 tab.sa4_16name::text,
517 tab.gcc_16code::text,
518 tab.gcc_16name::text,
519 ste.st_abbrev::text AS state,
520 tab.area_sqm,
521 bdy.geom
522 FROM raw_admin_bdys.aus_sa4_2016 AS tab
523 INNER JOIN raw_admin_bdys.aus_sa4_2016_polygon AS bdy ON tab.sa4_16pid = bdy.sa4_16pid
524 INNER JOIN raw_admin_bdys.aus_state AS ste ON tab.state_pid = ste.state_pid;
525
526ALTER TABLE admin_bdys.abs_2016_sa4 ADD CONSTRAINT abs_2016_sa4_pk PRIMARY KEY (gid);
527CREATE INDEX abs_2016_sa4_geom_idx ON admin_bdys.abs_2016_sa4 USING gist(geom);
528ALTER TABLE admin_bdys.abs_2016_sa4 CLUSTER ON abs_2016_sa4_geom_idx;
529
530
531 : relation "raw_admin_bdys.aus_sa4_2016" does not exist
532LINE 15: FROM raw_admin_bdys.aus_sa4_2016 AS tab
533 ^
534
535root : INFO SQL FAILED! : -------------------------------------------------------------------------------
536-- ABS census 2016 - statistical area 3's
537--------------------------------------------------------------------------------------
538
539DROP TABLE IF EXISTS admin_bdys.abs_2016_sa3 CASCADE;
540CREATE TABLE admin_bdys.abs_2016_sa3 AS
541SELECT bdy.gid,
542 tab.sa3_16code,
543 tab.sa3_16name::text,
544 tab.sa4_16code,
545 tab.sa4_16name::text,
546 tab.gcc_16code::text,
547 tab.gcc_16name::text,
548 ste.st_abbrev::text AS state,
549 tab.area_sqm,
550 bdy.geom
551 FROM raw_admin_bdys.aus_sa3_2016 AS tab
552 INNER JOIN raw_admin_bdys.aus_sa3_2016_polygon AS bdy ON tab.sa3_16pid = bdy.sa3_16pid
553 INNER JOIN raw_admin_bdys.aus_state AS ste ON tab.state_pid = ste.state_pid;
554
555ALTER TABLE admin_bdys.abs_2016_sa3 ADD CONSTRAINT abs_2016_sa3_pk PRIMARY KEY (gid);
556CREATE INDEX abs_2016_sa3_geom_idx ON admin_bdys.abs_2016_sa3 USING gist(geom);
557ALTER TABLE admin_bdys.abs_2016_sa3 CLUSTER ON abs_2016_sa3_geom_idx;
558
559
560 : relation "raw_admin_bdys.aus_sa3_2016" does not exist
561LINE 17: FROM raw_admin_bdys.aus_sa3_2016 AS tab
562 ^
563
564root : INFO SQL FAILED! :
565--------------------------------------------------------------------------------------
566-- locality boundaries
567--------------------------------------------------------------------------------------
568
569DROP TABLE IF EXISTS admin_bdys.locality_bdys CASCADE;
570CREATE TABLE admin_bdys.locality_bdys(
571 gid SERIAL NOT NULL,
572 locality_pid text NOT NULL,
573 locality_name text NOT NULL,
574 postcode text NULL,
575 state text NOT NULL,
576 locality_class text NOT NULL,
577 address_count integer NOT NULL DEFAULT 0,
578 street_count integer NOT NULL DEFAULT 0,
579 geom geometry(Multipolygon, 4283, 2) NOT NULL
580) WITH (OIDS=FALSE);
581ALTER TABLE admin_bdys.locality_bdys OWNER TO moth;
582
583INSERT INTO admin_bdys.locality_bdys (locality_pid, locality_name, postcode, state, locality_class, geom)
584SELECT dat.loc_pid,
585 dat.name,
586 dat.postcode,
587 ste.st_abbrev,
588 aut.name_aut,
589 st_multi(st_union(st_buffer(bdy.geom, 0.0)))
590 FROM raw_admin_bdys.aus_locality AS dat
591 INNER JOIN raw_admin_bdys.aus_locality_polygon AS bdy ON dat.loc_pid = bdy.loc_pid
592 INNER JOIN raw_admin_bdys.aus_state AS ste ON dat.state_pid = ste.state_pid
593 INNER JOIN raw_admin_bdys.aus_locality_class_aut AS aut ON dat.loccl_code = aut.code_aut
594 WHERE dat.loccl_code = 'G'
595 GROUP BY dat.loc_pid,
596 dat.name,
597 dat.postcode,
598 ste.st_abbrev,
599 aut.name_aut;
600
601ANALYZE admin_bdys.locality_bdys;
602
603
604-- cookie cut ACT districts to areas without a gazetted locality; and add to locality bdys table
605
606-- create temp table of ACT districts
607DROP TABLE IF EXISTS temp_districts;
608CREATE TEMPORARY TABLE temp_districts (
609 locality_pid text NOT NULL PRIMARY KEY,
610 locality_name text NOT NULL,
611 postcode text NULL,
612 state text NOT NULL,
613 locality_class text NOT NULL,
614 geom geometry(Multipolygon, 4283, 2) NULL
615) WITH (OIDS=FALSE);
616ALTER TABLE temp_districts OWNER TO moth;
617
618CREATE INDEX temp_districts_geom_idx ON temp_districts USING gist(geom);
619ALTER TABLE temp_districts CLUSTER ON temp_districts_geom_idx;
620
621INSERT INTO temp_districts
622SELECT dat.loc_pid,
623 dat.name,
624 dat.postcode,
625 ste.st_abbrev,
626 aut.name_aut,
627 ST_Multi(ST_Union(ST_Buffer(bdy.geom, 0.0)))
628 FROM raw_admin_bdys.aus_locality AS dat
629 INNER JOIN raw_admin_bdys.aus_locality_polygon AS bdy ON dat.loc_pid = bdy.loc_pid
630 INNER JOIN raw_admin_bdys.aus_state AS ste ON dat.state_pid = ste.state_pid
631 INNER JOIN raw_admin_bdys.aus_locality_class_aut AS aut ON dat.loccl_code = aut.code_aut
632 WHERE dat.loccl_code = 'D'
633 AND ste.st_abbrev = 'ACT'
634 GROUP BY dat.loc_pid,
635 dat.name,
636 dat.postcode,
637 ste.st_abbrev,
638 aut.name_aut;
639
640ANALYZE temp_districts;
641
642-- Insert the ACT localities merged into a single multipolygon as the cookie cutter
643INSERT INTO temp_districts
644 SELECT 'DUMMY',
645 'DUMMY',
646 NULL,
647 'XYZ',
648 'DUMMY',
649 ST_Multi(ST_Union(geom)) AS geom
650 FROM admin_bdys.locality_bdys
651 WHERE state = 'ACT';
652
653-- cookie cut the districts up by the merged localities (buffer required to remove slivers)
654UPDATE temp_districts AS dist
655 SET geom = ST_Multi(ST_Buffer(ST_Buffer(ST_Difference(dist.geom, (SELECT geom FROM temp_districts WHERE locality_pid = 'DUMMY')), 0.00000001), -0.00000001))
656 WHERE locality_pid <> 'DUMMY';
657
658-- delete the cookie cutter
659DELETE FROM temp_districts WHERE locality_pid = 'DUMMY';
660
661
662-- while we're at it - fill the big gap in SA with an unincorporated area
663INSERT INTO temp_districts
664SELECT 'SA999999',
665 'UNINCORPORATED',
666 NULL,
667 'SA',
668 'UNOFFICIAL SUBURB',
669 ST_Multi(ST_Buffer(ST_Difference(ST_SetSRID(ST_GeomFromText('POLYGON((128.96007125417 -25.9721745610671,133.1115 -25.9598957395068,133.12 -26.6761603305237,133.797926948924 -26.6925320926041,133.724254019562 -27.5888860665053,133.867506937766 -28.0513883452762,133.892064580886 -29.5739622187522,133.138963525189 -29.5125681109508,133.110312941548 -30.6094761703367,131.645040235353 -30.494873835774,128.98053595677 -30.789565553221,128.96007125417 -25.9721745610671))'), 4283), ST_Union(geom)), 0.0))
670 FROM admin_bdys.locality_bdys
671 WHERE ST_Intersects(geom, ST_SetSRID(ST_GeomFromText('POLYGON((128.96007125417 -25.9721745610671,133.1115 -25.9598957395068,133.12 -26.6761603305237,133.797926948924 -26.6925320926041,133.724254019562 -27.5888860665053,133.867506937766 -28.0513883452762,133.892064580886 -29.5739622187522,133.138963525189 -29.5125681109508,133.110312941548 -30.6094761703367,131.645040235353 -30.494873835774,128.98053595677 -30.789565553221,128.96007125417 -25.9721745610671))'), 4283));
672
673
674-- insert the districts into the gazetted localities, whilst ignoring the remaining slivers (Admin boundary topology is not perfect)
675INSERT INTO admin_bdys.locality_bdys (locality_pid, locality_name, postcode, state, locality_class, geom)
676SELECT locality_pid,
677 locality_name,
678 postcode,
679 state,
680 locality_class,
681 ST_Multi(ST_Union(geom))
682 FROM (
683 SELECT locality_pid,
684 locality_name,
685 postcode,
686 state,
687 locality_class,
688 ST_Area((ST_Dump(geom)).geom) AS area,
689 (ST_Dump(geom)).geom as geom
690 FROM temp_districts
691 ) AS sqt
692 WHERE area > 0.000001
693 GROUP BY locality_pid,
694 locality_name,
695 postcode,
696 state,
697 locality_class;
698
699DROP TABLE temp_districts;
700
701
702-- insert the missing boundary for Thistle Island, SA - from a polygon in the raw state boundaries
703INSERT INTO admin_bdys.locality_bdys (locality_pid, locality_name, postcode, state, locality_class, geom)
704SELECT '250190776' AS locality_pid,
705 'THISTLE ISLAND' AS locality_name,
706 null AS postcode,
707 'SA' AS state,
708 'TOPOGRAPHIC LOCALITY' AS locality_class,
709 ST_Multi(ST_Buffer(geom, 0.0)) AS geom
710 --ST_Multi(ST_Buffer((SELECT geom FROM raw_admin_bdys.aus_state_polygon WHERE ST_Intersects(ST_SetSRID(ST_MakePoint(136.1757, -35.0310), 4283), geom)), 0.0)) as geom;
711 FROM raw_admin_bdys.aus_state_polygon
712 WHERE ST_Intersects(ST_SetSRID(ST_MakePoint(136.1757, -35.0310), 4283), geom);
713
714
715-- split Melbourne into its 2 postcode areas: 3000 (north of the Yarra River) and 3004 (south)
716DROP TABLE IF EXISTS temp_bdys;
717CREATE UNLOGGED TABLE temp_bdys
718(
719 locality_pid text NOT NULL,
720 locality_name text NOT NULL,
721 postcode text NULL,
722 state text NOT NULL,
723 locality_class text NOT NULL,
724 geom geometry(Multipolygon, 4283, 2) NOT NULL
725)
726WITH (OIDS=FALSE);
727ALTER TABLE temp_bdys OWNER TO moth;
728
729insert into temp_bdys
730select locality_pid,
731 locality_name,
732 '3000' AS postcode,
733 state,
734 locality_class,
735 ST_Multi((ST_Dump(ST_Split(geom, ST_GeomFromText('LINESTRING(144.96691 -37.82135,144.96826 -37.81924,144.97045 -37.81911,144.97235 -37.81921,144.97345 -37.81955,144.97465 -37.82049,144.97734 -37.82321,144.97997 -37.82602,144.98154 -37.82696,144.98299 -37.82735,144.98499 -37.82766,144.9866 -37.82985)', 4283)))).geom) AS geom
736 from admin_bdys.locality_bdys
737 where locality_pid = 'VIC1634';
738
739-- update the locality_pids of the 2 new boundaries
740UPDATE temp_bdys
741 SET locality_pid = locality_pid || '_2',
742 postcode = '3004'
743 WHERE ST_Intersects(ST_SetSRID(ST_MakePoint(144.9781, -37.8275), 4283), geom);
744
745UPDATE temp_bdys
746 SET locality_pid = locality_pid || '_1'
747 WHERE postcode = '3000';
748
749-- insert the new boundaries into the main table, the old record doesn't get deleted yet!
750INSERT INTO admin_bdys.locality_bdys (locality_pid, locality_name, postcode, state, locality_class, geom)
751SELECT locality_pid,
752 locality_name,
753 postcode,
754 state,
755 locality_class,
756 geom
757 FROM temp_bdys;
758
759DROP TABLE temp_bdys;
760
761-- delete the replaced Melbourne locality
762DELETE FROM admin_bdys.locality_bdys WHERE locality_pid = 'VIC1634';
763
764
765-- update stats
766ANALYZE admin_bdys.locality_bdys;
767
768-- create indexes for later use
769ALTER TABLE admin_bdys.locality_bdys ADD CONSTRAINT locality_bdys_pk PRIMARY KEY (locality_pid);
770CREATE UNIQUE INDEX locality_bdys_gid_idx ON admin_bdys.locality_bdys USING btree(gid);
771CREATE INDEX locality_bdys_geom_idx ON admin_bdys.locality_bdys USING gist(geom);
772ALTER TABLE admin_bdys.locality_bdys CLUSTER ON locality_bdys_geom_idx;
773
774
775-- uncomment this if your want an SA Hundreds boundary table (historical bdys)
776
777-- -- create South Australian Hundreds table -- 1s
778-- DROP TABLE IF EXISTS admin_bdys.hundreds_sa_only;
779-- CREATE UNLOGGED TABLE admin_bdys.hundreds_sa_only
780-- (
781-- gid SERIAL NOT NULL,
782-- locality_pid text NOT NULL PRIMARY KEY,
783-- locality_name text NOT NULL,
784-- postcode text NULL,
785-- state text NOT NULL,
786-- locality_class text NOT NULL,
787-- geom geometry(Multipolygon, 4283, 2) NOT NULL
788-- )
789-- WITH (OIDS=FALSE);
790-- ALTER TABLE admin_bdys.hundreds_sa_only OWNER TO moth;
791--
792-- INSERT INTO admin_bdys.hundreds_sa_only (locality_pid, locality_name, postcode, state, locality_class, geom)
793-- SELECT dat.loc_pid,
794-- dat.name,
795-- dat.postcode,
796-- ste.st_abbrev,
797-- aut.name,
798-- ST_Multi(ST_Buffer(ST_Union(ST_Buffer(bdy.geom, 0.0000001)), -0.0000001))
799-- FROM raw_admin_bdys.aus_locality AS dat
800-- INNER JOIN raw_admin_bdys.aus_locality_polygon AS bdy ON dat.loc_pid = bdy.loc_pid
801-- INNER JOIN raw_admin_bdys.aus_state AS ste ON dat.state_pid = ste.state_pid
802-- INNER JOIN locality_class_aut AS aut ON dat.loccl_code = aut.code
803-- WHERE dat.loccl_code = 'H'
804-- AND ste.st_abbrev = 'SA'
805-- GROUP BY dat.loc_pid,
806-- dat.name,
807-- dat.postcode,
808-- ste.st_abbrev,
809-- aut.name;
810--
811-- -- update stats
812-- ANALYZE admin_bdys.hundreds_sa_only;
813--
814-- CREATE UNIQUE INDEX hundreds_sa_only_gid_idx ON admin_bdys.hundreds_sa_only USING btree(gid);
815-- CREATE INDEX hundreds_sa_only_geom_idx ON admin_bdys.hundreds_sa_only USING gist(geom);
816-- ALTER TABLE admin_bdys.hundreds_sa_only CLUSTER ON hundreds_sa_only_geom_idx;
817
818
819 : relation "raw_admin_bdys.aus_locality" does not exist
820LINE 27: FROM raw_admin_bdys.aus_locality AS dat
821 ^
822
823root : INFO - Step 2 of 3 : admin boundaries prepped : 0:00:00.430014
824root : INFO SQL FAILED! :
825DROP TABLE IF EXISTS admin_bdys.local_government_wards_analysis CASCADE;
826CREATE TABLE admin_bdys.local_government_wards_analysis (
827 gid SERIAL NOT NULL PRIMARY KEY,
828 ward_pid text NOT NULL,
829 name text NOT NULL,
830 state text NOT NULL,
831 geom geometry(Polygon, 4283, 2) NOT NULL
832) WITH (OIDS=FALSE);
833ALTER TABLE admin_bdys.local_government_wards_analysis OWNER TO moth;
834
835INSERT INTO admin_bdys.local_government_wards_analysis (ward_pid, name, state, geom)
836SELECT ward_pid,
837 name,
838 state,
839 ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
840 FROM admin_bdys.local_government_wards;
841
842CREATE INDEX local_government_wards_analysis_geom_idx ON admin_bdys.local_government_wards_analysis USING gist(geom);
843ALTER TABLE admin_bdys.local_government_wards_analysis CLUSTER ON local_government_wards_analysis_geom_idx;
844
845ANALYZE admin_bdys.local_government_wards_analysis;
846 : relation "admin_bdys.local_government_wards" does not exist
847LINE 17: FROM admin_bdys.local_government_wards;
848 ^
849
850root : INFO SQL FAILED! :
851DROP TABLE IF EXISTS admin_bdys.locality_bdys_analysis CASCADE;
852CREATE TABLE admin_bdys.locality_bdys_analysis (
853 gid SERIAL NOT NULL PRIMARY KEY,
854 locality_pid text NOT NULL,
855 locality_name text NOT NULL,
856 state text NOT NULL,
857 geom geometry(Polygon, 4283, 2) NOT NULL
858) WITH (OIDS=FALSE);
859ALTER TABLE admin_bdys.locality_bdys_analysis OWNER TO moth;
860
861INSERT INTO admin_bdys.locality_bdys_analysis (locality_pid, locality_name, state, geom)
862SELECT locality_pid,
863 locality_name,
864 state,
865 ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
866 FROM admin_bdys.locality_bdys;
867
868CREATE INDEX locality_bdys_analysis_geom_idx ON admin_bdys.locality_bdys_analysis USING gist(geom);
869ALTER TABLE admin_bdys.locality_bdys_analysis CLUSTER ON locality_bdys_analysis_geom_idx;
870
871ANALYZE admin_bdys.locality_bdys_analysis;
872 : relation "admin_bdys.locality_bdys" does not exist
873LINE 17: FROM admin_bdys.locality_bdys;
874 ^
875
876root : INFO SQL FAILED! :
877DROP TABLE IF EXISTS admin_bdys.state_bdys_analysis CASCADE;
878CREATE TABLE admin_bdys.state_bdys_analysis (
879 gid SERIAL NOT NULL PRIMARY KEY,
880 state_pid text NOT NULL,
881 name text NOT NULL,
882 state text NOT NULL,
883 geom geometry(Polygon, 4283, 2) NOT NULL
884) WITH (OIDS=FALSE);
885ALTER TABLE admin_bdys.state_bdys_analysis OWNER TO moth;
886
887INSERT INTO admin_bdys.state_bdys_analysis (state_pid, name, state, geom)
888SELECT state_pid,
889 name,
890 state,
891 ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
892 FROM admin_bdys.state_bdys;
893
894CREATE INDEX state_bdys_analysis_geom_idx ON admin_bdys.state_bdys_analysis USING gist(geom);
895ALTER TABLE admin_bdys.state_bdys_analysis CLUSTER ON state_bdys_analysis_geom_idx;
896
897ANALYZE admin_bdys.state_bdys_analysis;
898 : relation "admin_bdys.state_bdys" does not exist
899LINE 17: FROM admin_bdys.state_bdys;
900 ^
901
902root : INFO SQL FAILED! :
903DROP TABLE IF EXISTS admin_bdys.commonwealth_electorates_analysis CASCADE;
904CREATE TABLE admin_bdys.commonwealth_electorates_analysis (
905 gid SERIAL NOT NULL PRIMARY KEY,
906 ce_pid text NOT NULL,
907 name text NOT NULL,
908 state text NOT NULL,
909 geom geometry(Polygon, 4283, 2) NOT NULL
910) WITH (OIDS=FALSE);
911ALTER TABLE admin_bdys.commonwealth_electorates_analysis OWNER TO moth;
912
913INSERT INTO admin_bdys.commonwealth_electorates_analysis (ce_pid, name, state, geom)
914SELECT ce_pid,
915 name,
916 state,
917 ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
918 FROM admin_bdys.commonwealth_electorates;
919
920CREATE INDEX commonwealth_electorates_analysis_geom_idx ON admin_bdys.commonwealth_electorates_analysis USING gist(geom);
921ALTER TABLE admin_bdys.commonwealth_electorates_analysis CLUSTER ON commonwealth_electorates_analysis_geom_idx;
922
923ANALYZE admin_bdys.commonwealth_electorates_analysis;
924 : relation "admin_bdys.commonwealth_electorates" does not exist
925LINE 17: FROM admin_bdys.commonwealth_electorates;
926 ^
927
928root : INFO SQL FAILED! :
929DROP TABLE IF EXISTS admin_bdys.local_government_areas_analysis CASCADE;
930CREATE TABLE admin_bdys.local_government_areas_analysis (
931 gid SERIAL NOT NULL PRIMARY KEY,
932 lga_pid text NOT NULL,
933 name text NOT NULL,
934 state text NOT NULL,
935 geom geometry(Polygon, 4283, 2) NOT NULL
936) WITH (OIDS=FALSE);
937ALTER TABLE admin_bdys.local_government_areas_analysis OWNER TO moth;
938
939INSERT INTO admin_bdys.local_government_areas_analysis (lga_pid, name, state, geom)
940SELECT lga_pid,
941 name,
942 state,
943 ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
944 FROM admin_bdys.local_government_areas;
945
946CREATE INDEX local_government_areas_analysis_geom_idx ON admin_bdys.local_government_areas_analysis USING gist(geom);
947ALTER TABLE admin_bdys.local_government_areas_analysis CLUSTER ON local_government_areas_analysis_geom_idx;
948
949ANALYZE admin_bdys.local_government_areas_analysis;
950 : relation "admin_bdys.local_government_areas" does not exist
951LINE 17: FROM admin_bdys.local_government_areas;
952 ^
953
954root : INFO SQL FAILED! :
955DROP TABLE IF EXISTS admin_bdys.state_lower_house_electorates_analysis CASCADE;
956CREATE TABLE admin_bdys.state_lower_house_electorates_analysis (
957 gid SERIAL NOT NULL PRIMARY KEY,
958 se_lower_pid text NOT NULL,
959 name text NOT NULL,
960 state text NOT NULL,
961 geom geometry(Polygon, 4283, 2) NOT NULL
962) WITH (OIDS=FALSE);
963ALTER TABLE admin_bdys.state_lower_house_electorates_analysis OWNER TO moth;
964
965INSERT INTO admin_bdys.state_lower_house_electorates_analysis (se_lower_pid, name, state, geom)
966SELECT se_lower_pid,
967 name,
968 state,
969 ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
970 FROM admin_bdys.state_lower_house_electorates;
971
972CREATE INDEX state_lower_house_electorates_analysis_geom_idx ON admin_bdys.state_lower_house_electorates_analysis USING gist(geom);
973ALTER TABLE admin_bdys.state_lower_house_electorates_analysis CLUSTER ON state_lower_house_electorates_analysis_geom_idx;
974
975ANALYZE admin_bdys.state_lower_house_electorates_analysis;
976 : relation "admin_bdys.state_lower_house_electorates" does not exist
977LINE 17: FROM admin_bdys.state_lower_house_electorates;
978 ^
979
980root : INFO SQL FAILED! :
981DROP TABLE IF EXISTS admin_bdys.state_upper_house_electorates_analysis CASCADE;
982CREATE TABLE admin_bdys.state_upper_house_electorates_analysis (
983 gid SERIAL NOT NULL PRIMARY KEY,
984 se_upper_pid text NOT NULL,
985 name text NOT NULL,
986 state text NOT NULL,
987 geom geometry(Polygon, 4283, 2) NOT NULL
988) WITH (OIDS=FALSE);
989ALTER TABLE admin_bdys.state_upper_house_electorates_analysis OWNER TO moth;
990
991INSERT INTO admin_bdys.state_upper_house_electorates_analysis (se_upper_pid, name, state, geom)
992SELECT se_upper_pid,
993 name,
994 state,
995 ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
996 FROM admin_bdys.state_upper_house_electorates;
997
998CREATE INDEX state_upper_house_electorates_analysis_geom_idx ON admin_bdys.state_upper_house_electorates_analysis USING gist(geom);
999ALTER TABLE admin_bdys.state_upper_house_electorates_analysis CLUSTER ON state_upper_house_electorates_analysis_geom_idx;
1000
1001ANALYZE admin_bdys.state_upper_house_electorates_analysis;
1002 : relation "admin_bdys.state_upper_house_electorates" does not exist
1003LINE 17: FROM admin_bdys.state_upper_house_electorates;
1004 ^
1005
1006root : INFO - Step 3 of 3 : admin boundaries for analysis created : 0:00:00.344880
1007root : INFO Part 3 of 6 : Raw admin boundaries loaded! : 0:00:02.635921
1008root : INFO
1009root : INFO Part 4 of 6 : Start create reference tables : 2018-05-15 15:30:34.317254
1010root : INFO - Step 1 of 14 : create reference tables : 0:00:00.055335
1011root : INFO - Step 2 of 14 : localities populated : 0:00:00.491115
1012root : INFO - Step 3 of 14 : locality aliases populated : 0:00:00.709998
1013root : INFO - Step 4 of 14 : locality neighbours populated : 0:00:00.183920
1014root : INFO - Step 5 of 14 : streets populated : 0:00:17.675546
1015root : INFO - Step 6 of 14 : street aliases populated : 0:00:00.721336
1016root : INFO - Step 7 of 14 : addresses populated : 0:20:23.390106
1017root : INFO - Step 8 of 14 : principal alias lookup populated : 0:01:30.150836
1018root : INFO - Step 9 of 14 : primary secondary lookup populated : 0:00:35.206367
1019Traceback (most recent call last):
1020 File "./gnaf-loader/load-gnaf.py", line 951, in <module>
1021 if main():
1022 File "./gnaf-loader/load-gnaf.py", line 118, in main
1023 create_reference_tables(pg_cur, settings)
1024 File "./gnaf-loader/load-gnaf.py", line 640, in create_reference_tables
1025 pg_cur.execute(psma.open_sql_file("03-10-reference-split-melbourne.sql", settings))
1026psycopg2.ProgrammingError: relation "admin_bdys.locality_bdys" does not exist
1027LINE 14: FROM admin_bdys.locality_bdys AS bdy
1028 ^