search

minus34 / gnaf-loader

A quick way to get started with Geoscape's open GNAF & Admin Boundaries
Apache License 2.0
182 stars 66 forks source link

Issues with May 2023 #80

Closed DigitalCyberSoft closed 1 year ago

DigitalCyberSoft commented 1 year ago

Getting some unexpected errors. I've tried both GDA94 and GDA2020

root        : INFO     
root        : INFO     Start gnaf-loader
root        : INFO      - running Python 3.11.4 with psycopg 3.1.8
root        : INFO      - on Linux #1 SMP PREEMPT_DYNAMIC Thu Jul  6 04:05:18 UTC 2023
root        : INFO      - using Postgres 15.1 on x86_64-redhat-linux-gnu and PostGIS 3.3.3 (with GEOS 3.11.1-CAPI-1.17.1)
root        : INFO     
root        : INFO     Arguments
root        : INFO      - prevacuum : False
root        : INFO      - raw_fk : False
root        : INFO      - raw_unlogged : False
root        : INFO      - max_processes : 4
root        : INFO      - no_boundary_tag : False
root        : INFO      - srid : 4283
root        : INFO      - pghost : 127.0.0.1
root        : INFO      - pgport : 5432
root        : INFO      - pgdb : gnaf
root        : INFO      - pguser : postgres
root        : INFO      - pgpassword : ************
root        : INFO      - geoscape_version : 202305
root        : INFO      - previous_geoscape_version : 202302
root        : INFO      - gnaf_tables_path : /domain/gnaf-data/GNAF
root        : INFO      - admin_bdys_path : /domain/gnaf-data/AdminBoundaries/
root        : INFO      - states : ['ACT', 'NSW', 'NT', 'OT', 'QLD', 'SA', 'TAS', 'VIC', 'WA']
root        : INFO     
root        : INFO     Part 1 of 6 : Create schemas : 2023-07-29 06:18:55.362376
root        : INFO     Part 1 of 6 : Schemas created! : 0:00:00.001482
root        : INFO     
root        : INFO     Part 2 of 6 : Start raw GNAF load : 2023-07-29 06:18:55.364225
root        : INFO      - Step 1 of 7 : tables dropped : 0:00:00.848947
root        : INFO      - Step 2 of 7 : database NOT vacuumed
root        : INFO      - Step 3 of 7 : tables created : 0:00:00.034085
root        : INFO          - Loading state ACT
root        : INFO          - Loading state NSW
root        : INFO          - Loading state NT
root        : INFO          - Loading state OT
root        : INFO          - Loading state QLD
root        : INFO          - Loading state SA
root        : INFO          - Loading state TAS
root        : INFO          - Loading state VIC
root        : INFO          - Loading state WA
root        : INFO      - Step 4 of 7 : tables populated : 0:02:11.208403
root        : INFO          - fixed missing geocodes
root        : INFO          - authority tables deduplicated
root        : INFO      - Step 5 of 7 : indexes created : 0:01:09.787365
root        : INFO      - Step 6 of 7 : primary & foreign keys NOT created
root        : INFO      - Step 7 of 7 : tables analysed : 0:00:00.020806
root        : INFO     Part 2 of 6 : Raw GNAF loaded! : 0:03:22.019740
root        : INFO     
root        : INFO     Part 3 of 6 : Start raw admin boundary load : 2023-07-29 06:22:17.384107
root        : INFO      Importing aus_town_point - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_town_point" does not exist
root        : INFO      Importing aus_remoteness_2016_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_remoteness_2016_polygon" does not exist
root        : INFO      Importing aus_sa1_2016_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sa1_2016_polygon" does not exist
root        : INFO      Importing aus_gccsa_2016_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_gccsa_2016_polygon" does not exist
root        : INFO      Importing aus_mb_2016_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_mb_2016_polygon" does not exist
root        : INFO      Importing aus_sa3_2016_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sa3_2016_polygon" does not exist
root        : INFO      Importing aus_sa2_2016_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sa2_2016_polygon" does not exist
root        : INFO      Importing aus_sa4_2016_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sa4_2016_polygon" does not exist
root        : INFO      Importing aus_sa2_2011_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sa2_2011_polygon" does not exist
root        : INFO      Importing aus_sa4_2011_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sa4_2011_polygon" does not exist
root        : INFO      Importing aus_mb_2011_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_mb_2011_polygon" does not exist
root        : INFO      Importing aus_sa1_2011_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sa1_2011_polygon" does not exist
root        : INFO      Importing aus_sa3_2011_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sa3_2011_polygon" does not exist
root        : INFO      Importing aus_gccsa_2011_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_gccsa_2011_polygon" does not exist
root        : INFO      Importing aus_iloc_2011_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_iloc_2011_polygon" does not exist
root        : INFO      Importing aus_remoteness_2021 - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_remoteness_2021" does not exist
root        : INFO      Importing aus_sosr_2016_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sosr_2016_polygon" does not exist
root        : INFO      Importing aus_sua_2016_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sua_2016_polygon" does not exist
root        : INFO      Importing aus_sos_2016_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sos_2016_polygon" does not exist
root        : INFO      Importing aus_ucl_2016_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_ucl_2016_polygon" does not exist
root        : INFO      Importing aus_ireg_2011_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_ireg_2011_polygon" does not exist
root        : INFO      Importing aus_sosr_2021 - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sosr_2021" does not exist
root        : INFO      Importing aus_sua_2021 - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sua_2021" does not exist
root        : INFO      Importing aus_ucl_2021 - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_ucl_2021" does not exist
root        : INFO      Importing aus_sos_2021 - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sos_2021" does not exist
root        : INFO      Importing aus_sua_2011_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sua_2011_polygon" does not exist
root        : INFO      Importing aus_iare_2011_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_iare_2011_polygon" does not exist
root        : INFO      Importing aus_sa3_2021 - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sa3_2021" does not exist
root        : INFO      Importing aus_sa1_2021 - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sa1_2021" does not exist
root        : INFO      Importing aus_mb_2021 - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_mb_2021" does not exist
root        : INFO      Importing aus_sa2_2021 - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sa2_2021" does not exist
root        : INFO      Importing aus_sa4_2021 - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sa4_2021" does not exist
root        : INFO      Importing aus_gccsa_2021 - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_gccsa_2021" does not exist
root        : INFO      Importing aus_ucl_2011_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_ucl_2011_polygon" does not exist
root        : INFO      Importing aus_sos_2011_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sos_2011_polygon" does not exist
root        : INFO      Importing aus_sosr_2011_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_sosr_2011_polygon" does not exist
root        : INFO      Importing aus_remoteness_2011_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_remoteness_2011_polygon" does not exist
root        : INFO      Importing aus_ireg_2016_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_ireg_2016_polygon" does not exist
root        : INFO      Importing aus_iare_2016_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_iare_2016_polygon" does not exist
root        : INFO      Importing aus_iloc_2016_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_iloc_2016_polygon" does not exist
root        : INFO      Importing aus_iare_2021 - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_iare_2021" does not exist
root        : INFO      Importing aus_ireg_2021 - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_ireg_2021" does not exist
root        : INFO      Importing aus_iloc_2021 - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_iloc_2021" does not exist
root        : INFO      Importing aus_comm_electoral_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_comm_electoral_polygon" does not exist
root        : INFO      Importing aus_state_electoral_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_state_electoral_polygon" does not exist
root        : INFO      Importing aus_localities - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_localities" does not exist
root        : INFO      Importing aus_state_polygon - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_state_polygon" does not exist
root        : INFO      Importing aus_lga - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_lga" does not exist
root        : INFO      Importing aus_wards - Couldn't cluster on spatial index : relation "raw_admin_bdys_202305.aus_wards" does not exist
root        : INFO      - Step 1 of 3 : raw admin boundaries loaded : 0:00:14.042708
root        : INFO          - authority tables deduplicated
root        : INFO     SQL FAILED! : -------------------------------------------------------------------------------
-- states
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys_202305.state_bdys CASCADE;
CREATE TABLE admin_bdys_202305.state_bdys AS
SELECT bdy.gid,
       tab.state_pid,
       tab.state_name AS name,
       tab.st_abbrev AS state,
       bdy.geom
  FROM raw_admin_bdys_202305.aus_state AS tab
  INNER JOIN raw_admin_bdys_202305.aus_state_polygon AS bdy ON tab.state_pid = bdy.state_pid;

ALTER TABLE admin_bdys_202305.state_bdys ADD CONSTRAINT state_bdys_pk PRIMARY KEY (gid);
CREATE INDEX state_bdys_geom_idx ON admin_bdys_202305.state_bdys USING gist(geom);
ALTER TABLE admin_bdys_202305.state_bdys CLUSTER ON state_bdys_geom_idx;

 : relation "raw_admin_bdys_202305.aus_state" does not exist
LINE 12:   FROM raw_admin_bdys_202305.aus_state AS tab
                ^
root        : INFO     SQL FAILED! : -------------------------------------------------------------------------------
-- commonwealth electoral boundaries
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys_202305.commonwealth_electorates CASCADE;
CREATE TABLE admin_bdys_202305.commonwealth_electorates AS
SELECT bdy.gid,
       tab.ce_pid,
       tab.name,
       tab.dt_gazetd,
       ste.st_abbrev AS state,
       tab.redistyear,
       bdy.geom
  FROM raw_admin_bdys_202305.aus_comm_electoral AS tab
  INNER JOIN raw_admin_bdys_202305.aus_comm_electoral_polygon AS bdy ON tab.ce_pid = bdy.ce_pid
  INNER JOIN raw_admin_bdys_202305.aus_state AS ste ON tab.state_pid = ste.state_pid;

ALTER TABLE admin_bdys_202305.commonwealth_electorates ADD CONSTRAINT commonwealth_electorates_pk PRIMARY KEY (gid);
CREATE INDEX commonwealth_electorates_geom_idx ON admin_bdys_202305.commonwealth_electorates USING gist(geom);
ALTER TABLE admin_bdys_202305.commonwealth_electorates CLUSTER ON commonwealth_electorates_geom_idx;

 : relation "raw_admin_bdys_202305.aus_comm_electoral" does not exist
LINE 14:   FROM raw_admin_bdys_202305.aus_comm_electoral AS tab
                ^
root        : INFO     SQL FAILED! : --------------------------------------------------------------------------------------------
-- state electoral boundaries - choose bdys that will be current until at least 3 months from now
---------------------------------------------------------------------------------------------------

-- create lower house table
DROP TABLE IF EXISTS admin_bdys_202305.state_lower_house_electorates CASCADE;
CREATE TABLE admin_bdys_202305.state_lower_house_electorates AS
SELECT bdy.gid,
       tab.se_pid AS se_lower_pid,
       tab.name,
       tab.dt_gazetd,
       tab.eff_start,
       tab.eff_end,
       aut.name AS electorate_class,
       ste.st_abbrev AS state,
       bdy.geom
  FROM raw_admin_bdys_202305.aus_state_electoral AS tab
  INNER JOIN raw_admin_bdys_202305.aus_state_electoral_polygon AS bdy ON tab.se_pid = bdy.se_pid
  INNER JOIN raw_admin_bdys_202305.aus_state AS ste ON tab.state_pid = ste.state_pid
  INNER JOIN raw_admin_bdys_202305.aus_state_electoral_class_aut AS aut ON tab.secl_code = aut.code
  WHERE (tab.eff_end > now() + interval '3 months'
    OR (tab.eff_start <= now() + interval '3 months' AND tab.eff_end IS NULL))
  AND tab.secl_code <> '3';

ALTER TABLE admin_bdys_202305.state_lower_house_electorates ADD CONSTRAINT state_lower_house_electorates_pk PRIMARY KEY (gid);
CREATE INDEX state_lower_house_electorates_geom_idx ON admin_bdys_202305.state_lower_house_electorates USING gist(geom);
ALTER TABLE admin_bdys_202305.state_lower_house_electorates CLUSTER ON state_lower_house_electorates_geom_idx;

 : relation "raw_admin_bdys_202305.aus_state_electoral" does not exist
LINE 17:   FROM raw_admin_bdys_202305.aus_state_electoral AS tab
                ^
root        : INFO     SQL FAILED! : 
-- create upper house table
DROP TABLE IF EXISTS admin_bdys_202305.state_upper_house_electorates CASCADE;
CREATE TABLE admin_bdys_202305.state_upper_house_electorates AS
SELECT bdy.gid,
       tab.se_pid AS se_upper_pid,
       tab.name,
       tab.dt_gazetd,
       tab.eff_start,
       tab.eff_end,
       aut.name AS electorate_class,
       ste.st_abbrev AS state,
       bdy.geom
  FROM raw_admin_bdys_202305.aus_state_electoral AS tab
  INNER JOIN raw_admin_bdys_202305.aus_state_electoral_polygon AS bdy ON tab.se_pid = bdy.se_pid
  INNER JOIN raw_admin_bdys_202305.aus_state AS ste ON tab.state_pid = ste.state_pid
  INNER JOIN raw_admin_bdys_202305.aus_state_electoral_class_aut AS aut ON tab.secl_code = aut.code
  WHERE (tab.eff_end > now() + interval '3 months'
    OR (tab.eff_start <= now() AND tab.eff_end IS NULL))
  AND tab.secl_code = '3'
  AND ste.st_abbrev NOT IN ('NSW', 'SA');

ALTER TABLE admin_bdys_202305.state_upper_house_electorates ADD CONSTRAINT state_upper_house_electorates_pk PRIMARY KEY (gid);
CREATE INDEX state_upper_house_electorates_geom_idx ON admin_bdys_202305.state_upper_house_electorates USING gist(geom);
ALTER TABLE admin_bdys_202305.state_upper_house_electorates CLUSTER ON state_upper_house_electorates_geom_idx;

 : relation "raw_admin_bdys_202305.aus_state_electoral" does not exist
LINE 14:   FROM raw_admin_bdys_202305.aus_state_electoral AS tab
                ^
root        : INFO     SQL FAILED! : 
--------------------------------------------------------------------------------------
-- locality boundaries
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys_202305.locality_bdys CASCADE;
CREATE TABLE admin_bdys_202305.locality_bdys(
  gid SERIAL NOT NULL,
  locality_pid text NOT NULL,
--   old_locality_pid text NULL,
  locality_name text NOT NULL,
  postcode text NULL,
  state text NOT NULL,
  locality_class text NOT NULL,
  address_count integer NOT NULL DEFAULT 0,
  street_count integer NOT NULL DEFAULT 0,
  geom geometry(Multipolygon, 4283, 2) NOT NULL
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys_202305.locality_bdys OWNER TO postgres;

INSERT INTO admin_bdys_202305.locality_bdys (locality_pid, locality_name, state, locality_class, geom)
SELECT loc_pid,
       loc_name,
       state,
       loc_class,
       st_multi(st_union(st_buffer(geom, 0.0))) AS geom
  FROM raw_admin_bdys_202305.aus_localities
  WHERE loc_class = 'Gazetted Locality'
  GROUP BY loc_pid,
       loc_name,
       state,
       loc_class;

ANALYZE admin_bdys_202305.locality_bdys;

-- cookie cut ACT districts to areas without a gazetted locality; and add to locality bdys table

-- create temp table of ACT districts
DROP TABLE IF EXISTS temp_districts;
CREATE TEMPORARY TABLE temp_districts (
  locality_pid text NOT NULL PRIMARY KEY,
--   old_locality_pid text NULL,
  locality_name text NOT NULL,
  state text NOT NULL,
  locality_class text NOT NULL,
  geom geometry(Multipolygon, 4283, 2) NULL
) WITH (OIDS=FALSE);
ALTER TABLE temp_districts OWNER TO postgres;

CREATE INDEX temp_districts_geom_idx ON temp_districts USING gist(geom);
ALTER TABLE temp_districts CLUSTER ON temp_districts_geom_idx;

INSERT INTO temp_districts
SELECT dat.loc_pid,
--        NULL,
       dat.loc_name,
       dat.state,
       dat.loc_class,
       st_multi(st_union(st_buffer(dat.geom, 0.0))) AS geom
  FROM raw_admin_bdys_202305.aus_localities AS dat
  WHERE dat.loc_class = 'District'
    AND dat.state = 'ACT'
  GROUP BY dat.loc_pid,
           dat.loc_name,
           dat.state,
           dat.loc_class;
ANALYZE temp_districts;

-- Insert the ACT localities merged into a single multipolygon as the cookie cutter
INSERT INTO temp_districts
  SELECT 'DUMMY',
--          'DUMMY',
         'DUMMY',
         'XYZ',
         'DUMMY',
         ST_Multi(ST_Union(geom)) AS geom
  FROM admin_bdys_202305.locality_bdys
  WHERE state = 'ACT';

-- cookie cut the districts up by the merged localities (buffer required to remove slivers)
UPDATE temp_districts AS dist
  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))
  WHERE locality_pid <> 'DUMMY';

-- delete the cookie cutter
DELETE FROM temp_districts WHERE locality_pid = 'DUMMY';

-- while we're at it - fill the big gap in SA with an unincorporated area
INSERT INTO temp_districts
SELECT 'locsa999999',
--        'SA999999',
       'UNINCORPORATED',
       'SA',
       'UNOFFICIAL SUBURB',
       ST_Multi(ST_Buffer(ST_Difference(ST_Transform(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), 4283), ST_Union(geom)), 0.0))
  FROM admin_bdys_202305.locality_bdys
  WHERE ST_Intersects(geom, ST_Transform(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), 4283));

-- insert the districts into the gazetted localities, whilst ignoring the remaining slivers (Admin boundary topology is not perfect)
INSERT INTO admin_bdys_202305.locality_bdys (locality_pid, locality_name, state, locality_class, geom)
SELECT locality_pid,
--        old_locality_pid,
       locality_name,
       state,
       locality_class,
       ST_Multi(ST_Union(geom))
  FROM (
    SELECT locality_pid,
--            old_locality_pid,
           locality_name,
           state,
           locality_class,
           ST_Area((ST_Dump(geom)).geom) AS area,
           (ST_Dump(geom)).geom as geom
      FROM temp_districts
  ) AS sqt
  WHERE area > 0.000001
  GROUP BY locality_pid,
--            old_locality_pid,
           locality_name,
           state,
           locality_class;

DROP TABLE temp_districts;

-- insert the missing boundary for Thistle Island, SA - from a polygon in the raw state boundaries
INSERT INTO admin_bdys_202305.locality_bdys (locality_pid, locality_name, state, locality_class, geom)
SELECT '250190776' AS locality_pid,
--        '250190776' AS old_locality_pid,
       'THISTLE ISLAND' AS locality_name,
       'SA' AS state,
       'TOPOGRAPHIC LOCALITY' AS locality_class,
       ST_Multi(ST_Buffer(geom, 0.0)) AS geom
       --ST_Multi(ST_Buffer((SELECT geom FROM raw_admin_bdys_202305.aus_state_polygon WHERE ST_Intersects(ST_Transform(ST_SetSRID(ST_MakePoint(136.1757, -35.0310), 4283), 4283), geom)), 0.0)) as geom;
  FROM raw_admin_bdys_202305.aus_state_polygon
  WHERE ST_Intersects(ST_Transform(ST_SetSRID(ST_MakePoint(136.1757, -35.0310), 4283), 4283), geom);

-- split Melbourne into its 2 postcode areas: 3000 (north of the Yarra River) and 3004 (south)
DROP TABLE IF EXISTS temp_bdys;
CREATE UNLOGGED TABLE temp_bdys
(
  locality_pid text NOT NULL,
--   old_locality_pid text NULL,
  locality_name text NOT NULL,
  postcode text NULL,
  state text NOT NULL,
    locality_class text NOT NULL,
  geom geometry(Multipolygon, 4283, 2) NOT NULL
)
WITH (OIDS=FALSE);
ALTER TABLE temp_bdys OWNER TO postgres;

insert into temp_bdys
select locality_pid,
--        'VIC1634',
       locality_name,
       '3000' AS postcode,
       state,
       locality_class,
       ST_Multi((ST_Dump(ST_Split(geom, ST_Transform(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), 4283)))).geom) AS geom
  from admin_bdys_202305.locality_bdys
  where locality_pid = 'loc9901d119afda';

-- update the locality_pids of the 2 new boundaries
UPDATE temp_bdys
  SET locality_pid = locality_pid || '_2',
--       old_locality_pid = old_locality_pid || '_2',
      postcode = '3004'
  WHERE ST_Intersects(ST_Transform(ST_SetSRID(ST_MakePoint(144.9781, -37.8275), 4283), 4283), geom);

UPDATE temp_bdys
  SET locality_pid = locality_pid || '_1'
--       old_locality_pid = old_locality_pid || '_1'
  WHERE postcode = '3000';

-- insert the new boundaries into the main table, the old record doesn't get deleted yet!
INSERT INTO admin_bdys_202305.locality_bdys (locality_pid, locality_name, postcode, state, locality_class, geom)
SELECT locality_pid,
--        old_locality_pid,
       locality_name,
       postcode,
       state,
       locality_class,
       geom
  FROM temp_bdys;

DROP TABLE temp_bdys;

-- delete the replaced Melbourne locality
DELETE FROM admin_bdys_202305.locality_bdys WHERE locality_pid = 'loc9901d119afda';

-- upper case name and class
UPDATE admin_bdys_202305.locality_bdys
    SET locality_name = upper(locality_name),
        locality_class = upper(locality_class)
;

-- -- add old locality_pids to unedited localities -- need to rollover old locality pids from GNAF 202305 release - not supplied in 202305 release
-- UPDATE admin_bdys_202305.locality_bdys as new
--     SET old_locality_pid = old.old_locality_pid
-- FROM admin_bdys_202305.locality_bdys AS old
-- WHERE new.locality_pid = old.locality_pid;

-- update stats
ANALYZE admin_bdys_202305.locality_bdys;

-- create indexes for later use
ALTER TABLE admin_bdys_202305.locality_bdys ADD CONSTRAINT locality_bdys_pk PRIMARY KEY (locality_pid);
CREATE UNIQUE INDEX locality_bdys_gid_idx ON admin_bdys_202305.locality_bdys USING btree(gid);
CREATE INDEX locality_bdys_state_idx ON admin_bdys_202305.locality_bdys USING btree(state);
CREATE INDEX locality_bdys_geom_idx ON admin_bdys_202305.locality_bdys USING gist(geom);
ALTER TABLE admin_bdys_202305.locality_bdys CLUSTER ON locality_bdys_geom_idx;

 : relation "raw_admin_bdys_202305.aus_localities" does not exist
LINE 27:   FROM raw_admin_bdys_202305.aus_localities
                ^
root        : INFO     SQL FAILED! : -------------------------------------------------------------------------------
-- local government areas
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys_202305.local_government_areas CASCADE;
CREATE TABLE admin_bdys_202305.local_government_areas AS
SELECT gid,
       lga_pid,
       abb_name AS name,
       lga_name AS full_name,
       state,
       st_multi(st_union(st_buffer(geom, 0.0)))::geometry(Multipolygon, 4283, 2) AS geom
  FROM raw_admin_bdys_202305.aus_lga
  GROUP BY
       gid,
       lga_pid,
       abb_name,
       lga_name,
       state
  ;

ALTER TABLE admin_bdys_202305.local_government_areas ADD CONSTRAINT local_government_areas_pk PRIMARY KEY (gid);
CREATE INDEX local_government_areas_geom_idx ON admin_bdys_202305.local_government_areas USING gist(geom);
ALTER TABLE admin_bdys_202305.local_government_areas CLUSTER ON local_government_areas_geom_idx;

 : relation "raw_admin_bdys_202305.aus_lga" does not exist
LINE 13:   FROM raw_admin_bdys_202305.aus_lga
                ^
root        : INFO     SQL FAILED! : -------------------------------------------------------------------------------
-- local government wards
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys_202305.local_government_wards CASCADE;
CREATE TABLE admin_bdys_202305.local_government_wards AS
SELECT bdy.gid,
       bdy.ward_pid,
       bdy.lga_pid,
       bdy.ward_name AS name,
       lga.lga_name AS lga_name,
       bdy.state,
       st_multi(st_union(st_buffer(bdy.geom, 0.0)))::geometry(Multipolygon, 4283, 2) AS geom
  FROM raw_admin_bdys_202305.aus_wards AS bdy
  INNER JOIN raw_admin_bdys_202305.aus_lga AS lga ON bdy.lga_pid = lga.lga_pid
  GROUP BY bdy.gid,
           bdy.ward_pid,
           bdy.lga_pid,
           bdy.ward_name,
           lga.lga_name,
           bdy.state
;

ALTER TABLE admin_bdys_202305.local_government_wards ADD CONSTRAINT local_government_wards_pk PRIMARY KEY (gid);
CREATE INDEX local_government_wards_geom_idx ON admin_bdys_202305.local_government_wards USING gist(geom);
ALTER TABLE admin_bdys_202305.local_government_wards CLUSTER ON local_government_wards_geom_idx;
 : relation "raw_admin_bdys_202305.aus_wards" does not exist
LINE 14:   FROM raw_admin_bdys_202305.aus_wards AS bdy
                ^
root        : INFO     SQL FAILED! : 
--------------------------------------------------------------------------------------
-- ABS census 2016 - meshblocks
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys_202305.abs_2016_mb CASCADE;
CREATE TABLE admin_bdys_202305.abs_2016_mb AS
SELECT bdy.gid,
       tab.mb_16code::bigint,
       aut.name::text AS mb_category,
       tab.sa1_16main,
       tab.sa1_16_7cd,
       tab.sa2_16main,
       tab.sa2_16_5cd,
       tab.sa2_16name::text,
       tab.sa3_16code,
       tab.sa3_16name::text,
       tab.sa4_16code,
       tab.sa4_16name::text,
       tab.gcc_16code::text,
       tab.gcc_16name::text,
       ste.st_abbrev::text AS state,
       tab.area_sqm,
       tab.mb16_pop,
       tab.mb16_dwell,
       bdy.geom
  FROM raw_admin_bdys_202305.aus_mb_2016 AS tab
  INNER JOIN raw_admin_bdys_202305.aus_mb_2016_polygon AS bdy ON tab.mb_16pid = bdy.mb_16pid
  INNER JOIN raw_admin_bdys_202305.aus_mb_category_class_aut AS aut ON tab.mb_cat_cd = aut.code
  INNER JOIN raw_admin_bdys_202305.aus_state AS ste ON tab.state_pid = ste.state_pid;

ALTER TABLE admin_bdys_202305.abs_2016_mb ADD CONSTRAINT abs_2016_mb_pk PRIMARY KEY (gid);
CREATE INDEX abs_2016_mb_geom_idx ON admin_bdys_202305.abs_2016_mb USING gist(geom);
ALTER TABLE admin_bdys_202305.abs_2016_mb CLUSTER ON abs_2016_mb_geom_idx;

 : relation "raw_admin_bdys_202305.aus_mb_2016" does not exist
LINE 27:   FROM raw_admin_bdys_202305.aus_mb_2016 AS tab
                ^
root        : INFO     SQL FAILED! : -------------------------------------------------------------------------------
-- ABS census 2016 - statistical area 2's
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys_202305.abs_2016_sa2 CASCADE;
CREATE TABLE admin_bdys_202305.abs_2016_sa2 AS
SELECT bdy.gid,
       tab.sa2_16main,
       tab.sa2_16_5cd,
       tab.sa2_16name::text,
       tab.sa3_16code,
       tab.sa3_16name::text,
       tab.sa4_16code,
       tab.sa4_16name::text,
       tab.gcc_16code::text,
       tab.gcc_16name::text,
       ste.st_abbrev::text AS state,
       tab.area_sqm,
       bdy.geom
  FROM raw_admin_bdys_202305.aus_sa2_2016 AS tab
  INNER JOIN raw_admin_bdys_202305.aus_sa2_2016_polygon AS bdy ON tab.sa2_16pid = bdy.sa2_16pid
  INNER JOIN raw_admin_bdys_202305.aus_state AS ste ON tab.state_pid = ste.state_pid;

ALTER TABLE admin_bdys_202305.abs_2016_sa2 ADD CONSTRAINT abs_2016_sa2_pk PRIMARY KEY (gid);
CREATE INDEX abs_2016_sa2_geom_idx ON admin_bdys_202305.abs_2016_sa2 USING gist(geom);
ALTER TABLE admin_bdys_202305.abs_2016_sa2 CLUSTER ON abs_2016_sa2_geom_idx;

 : relation "raw_admin_bdys_202305.aus_sa2_2016" does not exist
LINE 20:   FROM raw_admin_bdys_202305.aus_sa2_2016 AS tab
                ^
root        : INFO     SQL FAILED! : -------------------------------------------------------------------------------
-- ABS census 2016 - statistical area 3's
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys_202305.abs_2016_sa3 CASCADE;
CREATE TABLE admin_bdys_202305.abs_2016_sa3 AS
SELECT bdy.gid,
       tab.sa3_16code,
       tab.sa3_16name::text,
       tab.sa4_16code,
       tab.sa4_16name::text,
       tab.gcc_16code::text,
       tab.gcc_16name::text,
       ste.st_abbrev::text AS state,
       tab.area_sqm,
       bdy.geom
  FROM raw_admin_bdys_202305.aus_sa3_2016 AS tab
  INNER JOIN raw_admin_bdys_202305.aus_sa3_2016_polygon AS bdy ON tab.sa3_16pid = bdy.sa3_16pid
  INNER JOIN raw_admin_bdys_202305.aus_state AS ste ON tab.state_pid = ste.state_pid;

ALTER TABLE admin_bdys_202305.abs_2016_sa3 ADD CONSTRAINT abs_2016_sa3_pk PRIMARY KEY (gid);
CREATE INDEX abs_2016_sa3_geom_idx ON admin_bdys_202305.abs_2016_sa3 USING gist(geom);
ALTER TABLE admin_bdys_202305.abs_2016_sa3 CLUSTER ON abs_2016_sa3_geom_idx;

 : relation "raw_admin_bdys_202305.aus_sa3_2016" does not exist
LINE 17:   FROM raw_admin_bdys_202305.aus_sa3_2016 AS tab
                ^
root        : INFO     SQL FAILED! : -------------------------------------------------------------------------------
-- ABS census 2016 - statistical area 1's
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys_202305.abs_2016_sa1 CASCADE;
CREATE TABLE admin_bdys_202305.abs_2016_sa1 AS
SELECT bdy.gid,
       tab.sa1_16main,
       tab.sa1_16_7cd,
       tab.sa2_16main,
       tab.sa2_16_5cd,
       tab.sa2_16name::text,
       tab.sa3_16code,
       tab.sa3_16name::text,
       tab.sa4_16code,
       tab.sa4_16name::text,
       tab.gcc_16code::text,
       tab.gcc_16name::text,
       ste.st_abbrev::text AS state,
       tab.area_sqm,
       bdy.geom
  FROM raw_admin_bdys_202305.aus_sa1_2016 AS tab
  INNER JOIN raw_admin_bdys_202305.aus_sa1_2016_polygon AS bdy ON tab.sa1_16pid = bdy.sa1_16pid
  INNER JOIN raw_admin_bdys_202305.aus_state AS ste ON tab.state_pid = ste.state_pid;

ALTER TABLE admin_bdys_202305.abs_2016_sa1 ADD CONSTRAINT abs_2016_sa1_pk PRIMARY KEY (gid);
CREATE INDEX abs_2016_sa1_geom_idx ON admin_bdys_202305.abs_2016_sa1 USING gist(geom);
ALTER TABLE admin_bdys_202305.abs_2016_sa1 CLUSTER ON abs_2016_sa1_geom_idx;

 : relation "raw_admin_bdys_202305.aus_sa1_2016" does not exist
LINE 22:   FROM raw_admin_bdys_202305.aus_sa1_2016 AS tab
                ^
root        : INFO     SQL FAILED! : -------------------------------------------------------------------------------
-- ABS census 2016 - statistical area 4's
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys_202305.abs_2016_sa4 CASCADE;
CREATE TABLE admin_bdys_202305.abs_2016_sa4 AS
SELECT bdy.gid,
       tab.sa4_16code,
       tab.sa4_16name::text,
       tab.gcc_16code::text,
       tab.gcc_16name::text,
       ste.st_abbrev::text AS state,
       tab.area_sqm,
       bdy.geom
  FROM raw_admin_bdys_202305.aus_sa4_2016 AS tab
  INNER JOIN raw_admin_bdys_202305.aus_sa4_2016_polygon AS bdy ON tab.sa4_16pid = bdy.sa4_16pid
  INNER JOIN raw_admin_bdys_202305.aus_state AS ste ON tab.state_pid = ste.state_pid;

ALTER TABLE admin_bdys_202305.abs_2016_sa4 ADD CONSTRAINT abs_2016_sa4_pk PRIMARY KEY (gid);
CREATE INDEX abs_2016_sa4_geom_idx ON admin_bdys_202305.abs_2016_sa4 USING gist(geom);
ALTER TABLE admin_bdys_202305.abs_2016_sa4 CLUSTER ON abs_2016_sa4_geom_idx;

 : relation "raw_admin_bdys_202305.aus_sa4_2016" does not exist
LINE 15:   FROM raw_admin_bdys_202305.aus_sa4_2016 AS tab
                ^
root        : INFO     SQL FAILED! : -------------------------------------------------------------------------------
-- ABS census 2016 - greater capital city statistical areas
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys_202305.abs_2016_gccsa CASCADE;
CREATE TABLE admin_bdys_202305.abs_2016_gccsa AS
SELECT bdy.gid,
       tab.gcc_16code::text,
       tab.gcc_16name::text,
       ste.st_abbrev::text AS state,
       tab.area_sqm,
       bdy.geom
  FROM raw_admin_bdys_202305.aus_gccsa_2016 AS tab
  INNER JOIN raw_admin_bdys_202305.aus_gccsa_2016_polygon AS bdy ON tab.gcc_16pid = bdy.gcc_16pid
  INNER JOIN raw_admin_bdys_202305.aus_state AS ste ON tab.state_pid = ste.state_pid;

ALTER TABLE admin_bdys_202305.abs_2016_gccsa ADD CONSTRAINT abs_2016_gccsa_pk PRIMARY KEY (gid);
CREATE INDEX abs_2016_gccsa_geom_idx ON admin_bdys_202305.abs_2016_gccsa USING gist(geom);
ALTER TABLE admin_bdys_202305.abs_2016_gccsa CLUSTER ON abs_2016_gccsa_geom_idx;
 : relation "raw_admin_bdys_202305.aus_gccsa_2016" does not exist
LINE 13:   FROM raw_admin_bdys_202305.aus_gccsa_2016 AS tab
                ^
root        : INFO     SQL FAILED! : 
--------------------------------------------------------------------------------------
-- ABS census 2021 - meshblocks
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys_202305.abs_2021_mb CASCADE;
CREATE TABLE admin_bdys_202305.abs_2021_mb AS
SELECT tab.gid,
       mb_21ppid,
       tab.dt_create,
       mb_21pid::text,
       mb21_code::bigint,
       mb_cat::text,
       chng_flag::integer,
       chng_label::text,
       sa1_21pid::text,
       sa1_21code,
       sa2_21code,
       sa2_21name::text,
       sa3_21code,
       sa3_21name::text,
       sa4_21code,
       sa4_21name::text,
       gcc_21code::text,
       gcc_21name::text,
       ste.st_abbrev::text AS state,
       area_sqm,
       mb21_dwell,
       mb21_pop,
       loci21_uri::text,
       geom
  FROM raw_admin_bdys_202305.aus_mb_2021 AS tab
  INNER JOIN raw_admin_bdys_202305.aus_state AS ste ON tab.state_pid = ste.state_pid;

ALTER TABLE admin_bdys_202305.abs_2021_mb ADD CONSTRAINT abs_2021_mb_pk PRIMARY KEY (gid);
CREATE INDEX abs_2021_mb_geom_idx ON admin_bdys_202305.abs_2021_mb USING gist(geom);
ALTER TABLE admin_bdys_202305.abs_2021_mb CLUSTER ON abs_2021_mb_geom_idx;

 : relation "raw_admin_bdys_202305.aus_mb_2021" does not exist
LINE 32:   FROM raw_admin_bdys_202305.aus_mb_2021 AS tab
                ^
root        : INFO     SQL FAILED! : -------------------------------------------------------------------------------
-- ABS census 2021 - statistical area 1's
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys_202305.abs_2021_sa1 CASCADE;
CREATE TABLE admin_bdys_202305.abs_2021_sa1 AS
SELECT tab.gid,
       sa1_21ppid,
       tab.dt_create,
       sa1_21pid,
       sa1_21code,
       chng_flag,
       chng_label::text,
       sa2_21pid,
       sa2_21code,
       sa2_21name::text,
       sa3_21code,
       sa3_21name::text,
       sa4_21code,
       sa4_21name::text,
       gcc_21code,
       gcc_21name::text,
       ste.st_abbrev::text AS state,
       area_sqm,
       loci21_uri::text,
       geom
  FROM raw_admin_bdys_202305.aus_sa1_2021 AS tab
  INNER JOIN raw_admin_bdys_202305.aus_state AS ste ON tab.state_pid = ste.state_pid;

ALTER TABLE admin_bdys_202305.abs_2021_sa1 ADD CONSTRAINT abs_2021_sa1_pk PRIMARY KEY (gid);
CREATE INDEX abs_2021_sa1_geom_idx ON admin_bdys_202305.abs_2021_sa1 USING gist(geom);
ALTER TABLE admin_bdys_202305.abs_2021_sa1 CLUSTER ON abs_2021_sa1_geom_idx;

 : relation "raw_admin_bdys_202305.aus_sa1_2021" does not exist
LINE 27:   FROM raw_admin_bdys_202305.aus_sa1_2021 AS tab
                ^
root        : INFO     SQL FAILED! : -------------------------------------------------------------------------------
-- ABS census 2021 - statistical area 2's
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys_202305.abs_2021_sa2 CASCADE;
CREATE TABLE admin_bdys_202305.abs_2021_sa2 AS
SELECT tab.gid,
       sa2_21ppid,
       tab.dt_create,
       sa2_21pid,
       sa2_21code,
       sa2_21name::text,
       chng_flag,
       chng_label::text,
       sa3_21pid,
       sa3_21code,
       sa3_21name::text,
       sa4_21code,
       sa4_21name::text,
       gcc_21code,
       gcc_21name::text,
       ste.st_abbrev::text AS state,
       area_sqm,
       loci21_uri::text,
       geom
  FROM raw_admin_bdys_202305.aus_sa2_2021 AS tab
  INNER JOIN raw_admin_bdys_202305.aus_state AS ste ON tab.state_pid = ste.state_pid;

ALTER TABLE admin_bdys_202305.abs_2021_sa2 ADD CONSTRAINT abs_2021_sa2_pk PRIMARY KEY (gid);
CREATE INDEX abs_2021_sa2_geom_idx ON admin_bdys_202305.abs_2021_sa2 USING gist(geom);
ALTER TABLE admin_bdys_202305.abs_2021_sa2 CLUSTER ON abs_2021_sa2_geom_idx;

 : relation "raw_admin_bdys_202305.aus_sa2_2021" does not exist
LINE 26:   FROM raw_admin_bdys_202305.aus_sa2_2021 AS tab
                ^
root        : INFO     SQL FAILED! : -------------------------------------------------------------------------------
-- ABS census 2021 - statistical area 3's
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys_202305.abs_2021_sa3 CASCADE;
CREATE TABLE admin_bdys_202305.abs_2021_sa3 AS
SELECT tab.gid,
       sa3_21ppid,
       tab.dt_create,
       sa3_21pid,
       sa3_21code,
       sa3_21name::text,
       chng_flag,
       chng_label::text,
       sa4_21pid,
       sa4_21code,
       sa4_21name::text,
       gcc_21code,
       gcc_21name::text,
       ste.st_abbrev::text AS state,
       area_sqm,
       loci21_uri::text,
       geom
  FROM raw_admin_bdys_202305.aus_sa3_2021 AS tab
  INNER JOIN raw_admin_bdys_202305.aus_state AS ste ON tab.state_pid = ste.state_pid;

ALTER TABLE admin_bdys_202305.abs_2021_sa3 ADD CONSTRAINT abs_2021_sa3_pk PRIMARY KEY (gid);
CREATE INDEX abs_2021_sa3_geom_idx ON admin_bdys_202305.abs_2021_sa3 USING gist(geom);
ALTER TABLE admin_bdys_202305.abs_2021_sa3 CLUSTER ON abs_2021_sa3_geom_idx;

 : relation "raw_admin_bdys_202305.aus_sa3_2021" does not exist
LINE 24:   FROM raw_admin_bdys_202305.aus_sa3_2021 AS tab
                ^
root        : INFO     SQL FAILED! : -------------------------------------------------------------------------------
-- ABS census 2021 - statistical area 4's
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys_202305.abs_2021_sa4 CASCADE;
CREATE TABLE admin_bdys_202305.abs_2021_sa4 AS
SELECT tab.gid,
       sa4_21ppid,
       tab.dt_create,
       sa4_21pid,
       sa4_21code,
       sa4_21name::text,
       chng_flag,
       chng_label::text,
       gcc_21pid,
       gcc_21code,
       gcc_21name::text,
       ste.st_abbrev::text AS state,
       area_sqm,
       loci21_uri::text,
       geom
  FROM raw_admin_bdys_202305.aus_sa4_2021 AS tab
  INNER JOIN raw_admin_bdys_202305.aus_state AS ste ON tab.state_pid = ste.state_pid;

ALTER TABLE admin_bdys_202305.abs_2021_sa4 ADD CONSTRAINT abs_2021_sa4_pk PRIMARY KEY (gid);
CREATE INDEX abs_2021_sa4_geom_idx ON admin_bdys_202305.abs_2021_sa4 USING gist(geom);
ALTER TABLE admin_bdys_202305.abs_2021_sa4 CLUSTER ON abs_2021_sa4_geom_idx;

 : relation "raw_admin_bdys_202305.aus_sa4_2021" does not exist
LINE 22:   FROM raw_admin_bdys_202305.aus_sa4_2021 AS tab
                ^
root        : INFO     SQL FAILED! : -------------------------------------------------------------------------------
-- ABS census 2021 - greater capital city statistical areas
--------------------------------------------------------------------------------------

DROP TABLE IF EXISTS admin_bdys_202305.abs_2021_gccsa CASCADE;
CREATE TABLE admin_bdys_202305.abs_2021_gccsa AS
SELECT tab.gid,
       gcc_21ppid,
       tab.dt_create,
       gcc_21pid,
       gcc_21code,
       gcc_21name::text,
       chng_flag,
       chng_label::text,
       ste.st_abbrev::text AS state,
       area_sqm,
       loci21_uri::text,
       geom
  FROM raw_admin_bdys_202305.aus_gccsa_2021 AS tab
  INNER JOIN raw_admin_bdys_202305.aus_state AS ste ON tab.state_pid = ste.state_pid;

ALTER TABLE admin_bdys_202305.abs_2021_gccsa ADD CONSTRAINT abs_2021_gccsa_pk PRIMARY KEY (gid);
CREATE INDEX abs_2021_gccsa_geom_idx ON admin_bdys_202305.abs_2021_gccsa USING gist(geom);
ALTER TABLE admin_bdys_202305.abs_2021_gccsa CLUSTER ON abs_2021_gccsa_geom_idx;
 : relation "raw_admin_bdys_202305.aus_gccsa_2021" does not exist
LINE 19:   FROM raw_admin_bdys_202305.aus_gccsa_2021 AS tab
                ^
root        : INFO      - Step 2 of 3 : admin boundaries prepped : 0:00:00.107211
root        : INFO     SQL FAILED! : 
DROP TABLE IF EXISTS admin_bdys_202305.locality_bdys_analysis CASCADE;
CREATE TABLE admin_bdys_202305.locality_bdys_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  locality_pid text NOT NULL,
  locality_name text NOT NULL, postcode text NULL,
  state text NOT NULL,
  geom geometry(Polygon, 4283, 2) NOT NULL
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys_202305.locality_bdys_analysis OWNER TO postgres;

INSERT INTO admin_bdys_202305.locality_bdys_analysis (locality_pid, locality_name, postcode, state, geom)
SELECT locality_pid,
       locality_name, postcode,
       state, 
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM admin_bdys_202305.locality_bdys;

CREATE INDEX locality_bdys_analysis_geom_idx ON admin_bdys_202305.locality_bdys_analysis USING gist(geom);
ALTER TABLE admin_bdys_202305.locality_bdys_analysis CLUSTER ON locality_bdys_analysis_geom_idx;

ANALYZE admin_bdys_202305.locality_bdys_analysis;
 : relation "admin_bdys_202305.locality_bdys" does not exist
LINE 17:   FROM admin_bdys_202305.locality_bdys;
                ^
root        : INFO     SQL FAILED! : 
DROP TABLE IF EXISTS admin_bdys_202305.commonwealth_electorates_analysis CASCADE;
CREATE TABLE admin_bdys_202305.commonwealth_electorates_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  ce_pid text NOT NULL,
  name text NOT NULL,
  state text NOT NULL,
  geom geometry(Polygon, 4283, 2) NOT NULL
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys_202305.commonwealth_electorates_analysis OWNER TO postgres;

INSERT INTO admin_bdys_202305.commonwealth_electorates_analysis (ce_pid, name, state, geom)
SELECT ce_pid,
       name,
       state, 
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM admin_bdys_202305.commonwealth_electorates;

CREATE INDEX commonwealth_electorates_analysis_geom_idx ON admin_bdys_202305.commonwealth_electorates_analysis USING gist(geom);
ALTER TABLE admin_bdys_202305.commonwealth_electorates_analysis CLUSTER ON commonwealth_electorates_analysis_geom_idx;

ANALYZE admin_bdys_202305.commonwealth_electorates_analysis;
 : relation "admin_bdys_202305.commonwealth_electorates" does not exist
LINE 17:   FROM admin_bdys_202305.commonwealth_electorates;
                ^
root        : INFO     SQL FAILED! : 
DROP TABLE IF EXISTS admin_bdys_202305.local_government_areas_analysis CASCADE;
CREATE TABLE admin_bdys_202305.local_government_areas_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  lga_pid text NOT NULL,
  name text NOT NULL,
  state text NOT NULL,
  geom geometry(Polygon, 4283, 2) NOT NULL
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys_202305.local_government_areas_analysis OWNER TO postgres;

INSERT INTO admin_bdys_202305.local_government_areas_analysis (lga_pid, name, state, geom)
SELECT lga_pid,
       name,
       state, 
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM admin_bdys_202305.local_government_areas;

CREATE INDEX local_government_areas_analysis_geom_idx ON admin_bdys_202305.local_government_areas_analysis USING gist(geom);
ALTER TABLE admin_bdys_202305.local_government_areas_analysis CLUSTER ON local_government_areas_analysis_geom_idx;

ANALYZE admin_bdys_202305.local_government_areas_analysis;
 : relation "admin_bdys_202305.local_government_areas" does not exist
LINE 17:   FROM admin_bdys_202305.local_government_areas;
                ^
root        : INFO     SQL FAILED! : 
DROP TABLE IF EXISTS admin_bdys_202305.state_bdys_analysis CASCADE;
CREATE TABLE admin_bdys_202305.state_bdys_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  state_pid text NOT NULL,
  name text NOT NULL,
  state text NOT NULL,
  geom geometry(Polygon, 4283, 2) NOT NULL
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys_202305.state_bdys_analysis OWNER TO postgres;

INSERT INTO admin_bdys_202305.state_bdys_analysis (state_pid, name, state, geom)
SELECT state_pid,
       name,
       state, 
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM admin_bdys_202305.state_bdys;

CREATE INDEX state_bdys_analysis_geom_idx ON admin_bdys_202305.state_bdys_analysis USING gist(geom);
ALTER TABLE admin_bdys_202305.state_bdys_analysis CLUSTER ON state_bdys_analysis_geom_idx;

ANALYZE admin_bdys_202305.state_bdys_analysis;
 : relation "admin_bdys_202305.state_bdys" does not exist
LINE 17:   FROM admin_bdys_202305.state_bdys;
                ^
root        : INFO     SQL FAILED! : 
DROP TABLE IF EXISTS admin_bdys_202305.local_government_wards_analysis CASCADE;
CREATE TABLE admin_bdys_202305.local_government_wards_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  ward_pid text NOT NULL,
  name text NOT NULL,
  state text NOT NULL,
  geom geometry(Polygon, 4283, 2) NOT NULL
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys_202305.local_government_wards_analysis OWNER TO postgres;

INSERT INTO admin_bdys_202305.local_government_wards_analysis (ward_pid, name, state, geom)
SELECT ward_pid,
       name,
       state, 
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM admin_bdys_202305.local_government_wards;

CREATE INDEX local_government_wards_analysis_geom_idx ON admin_bdys_202305.local_government_wards_analysis USING gist(geom);
ALTER TABLE admin_bdys_202305.local_government_wards_analysis CLUSTER ON local_government_wards_analysis_geom_idx;

ANALYZE admin_bdys_202305.local_government_wards_analysis;
 : relation "admin_bdys_202305.local_government_wards" does not exist
LINE 17:   FROM admin_bdys_202305.local_government_wards;
                ^
root        : INFO     SQL FAILED! : 
DROP TABLE IF EXISTS admin_bdys_202305.state_upper_house_electorates_analysis CASCADE;
CREATE TABLE admin_bdys_202305.state_upper_house_electorates_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  se_upper_pid text NOT NULL,
  name text NOT NULL,
  state text NOT NULL,
  geom geometry(Polygon, 4283, 2) NOT NULL
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys_202305.state_upper_house_electorates_analysis OWNER TO postgres;

INSERT INTO admin_bdys_202305.state_upper_house_electorates_analysis (se_upper_pid, name, state, geom)
SELECT se_upper_pid,
       name,
       state, 
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM admin_bdys_202305.state_upper_house_electorates;

CREATE INDEX state_upper_house_electorates_analysis_geom_idx ON admin_bdys_202305.state_upper_house_electorates_analysis USING gist(geom);
ALTER TABLE admin_bdys_202305.state_upper_house_electorates_analysis CLUSTER ON state_upper_house_electorates_analysis_geom_idx;

ANALYZE admin_bdys_202305.state_upper_house_electorates_analysis;
 : relation "admin_bdys_202305.state_upper_house_electorates" does not exist
LINE 17:   FROM admin_bdys_202305.state_upper_house_electorates;
                ^
root        : INFO     SQL FAILED! : 
DROP TABLE IF EXISTS admin_bdys_202305.state_lower_house_electorates_analysis CASCADE;
CREATE TABLE admin_bdys_202305.state_lower_house_electorates_analysis (
  gid SERIAL NOT NULL PRIMARY KEY,
  se_lower_pid text NOT NULL,
  name text NOT NULL,
  state text NOT NULL,
  geom geometry(Polygon, 4283, 2) NOT NULL
) WITH (OIDS=FALSE);
ALTER TABLE admin_bdys_202305.state_lower_house_electorates_analysis OWNER TO postgres;

INSERT INTO admin_bdys_202305.state_lower_house_electorates_analysis (se_lower_pid, name, state, geom)
SELECT se_lower_pid,
       name,
       state, 
       ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 512)
  FROM admin_bdys_202305.state_lower_house_electorates;

CREATE INDEX state_lower_house_electorates_analysis_geom_idx ON admin_bdys_202305.state_lower_house_electorates_analysis USING gist(geom);
ALTER TABLE admin_bdys_202305.state_lower_house_electorates_analysis CLUSTER ON state_lower_house_electorates_analysis_geom_idx;

ANALYZE admin_bdys_202305.state_lower_house_electorates_analysis;
 : relation "admin_bdys_202305.state_lower_house_electorates" does not exist
LINE 17:   FROM admin_bdys_202305.state_lower_house_electorates;
                ^
root        : INFO      - Step 3 of 3 : admin boundaries for analysis created : 0:00:00.079434
root        : INFO     Part 3 of 6 : Raw admin boundaries loaded! : 0:00:14.233923
root        : INFO     
root        : INFO     Part 4 of 6 : Start create reference tables : 2023-07-29 06:22:31.618203
root        : INFO      - Step  1 of 14 : create reference tables : 0:00:00.037779
root        : INFO      - Step  2 of 14 : localities populated : 0:00:00.452101
root        : INFO      - Step  3 of 14 : locality aliases populated : 0:00:00.395617
root        : INFO      - Step  4 of 14 : locality neighbours populated : 0:00:00.158574
root        : INFO      - Step  5 of 14 : streets populated : 0:00:11.398965
root        : INFO      - Step  6 of 14 : street aliases populated : 0:00:00.317903
root        : INFO      - Step  7 of 14 : addresses populated : 0:02:58.946919
root        : INFO      - Step  8 of 14 : principal alias lookup populated : 0:00:15.489838
root        : INFO      - Step  9 of 14 : primary secondary lookup populated : 0:00:21.874416
Traceback (most recent call last):
  File "/domain/gnaf-loader/load-gnaf.py", line 994, in <module>
    if main():
       ^^^^^^
  File "/domain/gnaf-loader/load-gnaf.py", line 123, in main
    create_reference_tables(pg_cur)
  File "/domain/gnaf-loader/load-gnaf.py", line 629, in create_reference_tables
    pg_cur.execute(geoscape.open_sql_file("03-10-reference-split-melbourne.sql").format(settings.srid))
  File "/usr/lib/python3.11/site-packages/psycopg/cursor.py", line 723, in execute
    raise ex.with_traceback(None)
psycopg.errors.UndefinedTable: relation "admin_bdys_202305.locality_bdys" does not exist
LINE 14:   FROM admin_bdys_202305.locality_bdys AS bdy
                ^
minus34 commented 1 year ago

The script wasn't able to import the Admin Boundary Shapefiles which caused the cascade of errors.

2 things to try:

DigitalCyberSoft commented 1 year ago

You beauty!

I'm guessing an older version of this script used to not require shp2pgsql or something similar as the script was previously working on the same machine. PostGIS was installed, but not postgis-client.