minus34 / gnaf-loader Goto Github PK

A quick way to get started with Geoscape's open GNAF & Admin Boundaries

License: Apache License 2.0

Python 73.50% Shell 26.50%

gnaf g-naf open-data geocoded-data addresses psma-gnaf psma-administrative-boundaries geoscape-gnaf geoscape gnaf-loader

gnaf-loader's Introduction

gnaf-loader

A quick way to load the complete Geocoded National Address File of Australia (GNAF) and Australian Administrative Boundaries into Postgres, simplified and ready to use as reference data for geocoding, analysis, visualisation and aggregation.

What's GNAF?

Have a look at these intro slides (PDF), as well as the data.gov.au page.

There are 4 options for loading the data

Run the load-gnaf Python script and build the database yourself in a single step
Pull the database from Docker Hub and run it in a container
Download the GNAF and/or Admin Bdys Postgres dump files & restore them in your Postgres 14+ database
Use or download Geoparquet and Parquet Files in S3 for your data & analytics workflows; either in AWS or your own platform.

Option 1 - Run load.gnaf.py

Running the Python script takes 30-120 minutes on a Postgres server configured to take advantage of the RAM available.

You can process the GDA94 or GDA2020 version of the data - just ensure that you download the same version for both GNAF and the Administrative Boundaries. If you don't know what GDA94 or GDA2020 is, download the GDA94 versions (FYI - they're different coordinate systems)

Performance

To get a good load time you'll need to configure your Postgres server for performance. There's a good guide here, noting it's a few years old and some of the memory parameters can be beefed up if you have the RAM.

Pre-requisites

Postgres 14.x and above with PostGIS 3.2+
Add the Postgres bin directory to your system PATH
Python 3.6+ with Psycopg 3.x

Process

Download Geoscape GNAF from data.gov.au (GDA94 or GDA2020)
Download Geoscape Administrative Boundaries from data.gov.au (download the ESRI Shapefile (GDA94 or GDA2020) version)
Unzip GNAF to a directory on your Postgres server
Unzip Admin Bdys to a local directory
Alter security on those directories to grant Postgres read access
Create the target database (if required)
Add PostGIS to the database (if required) by running the following SQL: CREATE EXTENSION postgis
Check the available and required arguments by running load-gnaf.py with the -h argument (see command line examples below)
Run the script, come back in 30-120 minutes and enjoy!

Command Line Options

The behaviour of gnaf-loader can be controlled by specifying various command line options to the script. Supported arguments are:

Required Arguments

--gnaf-tables-path specifies the path to the extracted GNAF PSV files. This directory must be accessible by the Postgres server, and the corresponding local path for the server to this directory may need to be set via the local-server-dir argument
--local-server-dir specifies the local path on the Postgres server corresponding to gnaf-tables-path. If the server is running locally this argument can be omitted.
--admin-bdys-path specifies the path to the extracted Shapefile admin boundary files. Unlike gnaf-tables-path, this path does not necessarily have to be accessible to the remote Postgres server.

Postgres Parameters

--pghost the host name for the Postgres server. This defaults to the PGHOST environment variable if set, otherwise defaults to localhost.
--pgport the port number for the Postgres server. This defaults to the PGPORT environment variable if set, otherwise 5432.
--pgdb the database name for Postgres server. This defaults to the PGDATABASE environment variable if set, otherwise geoscape.
--pguser the username for accessing the Postgres server. This defaults to the PGUSER environment variable if set, otherwise postgres.
--pgpassword password for accessing the Postgres server. This defaults to the PGPASSWORD environment variable if set, otherwise password.

Optional Arguments

--srid Sets the coordinate system of the input data. Valid values are 4283 (the default: GDA94 lat/long) and 7844 (GDA2020 lat/long).
--geoscape-version Geoscape version number in YYYYMM format. Defaults to current year and last release month. e.g. 202405.
--previous-geoscape-version Previous Geoscape release version number as YYYYMM; used for QA comparison. e.g. 202402.
--raw-gnaf-schema schema name to store raw GNAF tables in. Defaults to raw_gnaf_<geoscape_version>.
--raw-admin-schema schema name to store raw admin boundary tables in. Defaults to raw_admin_bdys_<geoscape_version>.
--gnaf-schema destination schema name to store final GNAF tables in. Defaults to gnaf_<geoscape_version>.
--admin-schema destination schema name to store final admin boundary tables in. Defaults to admin_bdys_<geoscape_version>.
--previous-gnaf-schema Schema with previous version of GNAF tables in. Defaults to gnaf_<previous_geoscape_version>.
--previous-admin-schema Schema with previous version of admin boundary tables in. Defaults to admin_bdys_<previous_geoscape_version>.
--states space separated list of states to load, eg --states VIC TAS. Defaults to loading all states.
--prevacuum forces the database to be vacuumed after dropping tables. Defaults to off, and specifying this option will slow the import process.
--raw-fk creates both primary & foreign keys for the raw GNAF tables. Defaults to off, and will slow the import process if specified. Use this option if you intend to utilise the raw GNAF tables as anything more then a temporary import step. Note that the final processed tables will always have appropriate primary and foreign keys set.
--raw-unlogged creates unlogged raw GNAF tables, speeding up the import. Defaults to off. Only specify this option if you don't care about the raw data tables after the import - they will be lost if the server crashes!
--max-processes specifies the maximum number of parallel processes to use for the data load. Set this to the number of cores on the Postgres server minus 2, but limit to 12 if 16+ cores - there is minimal benefit beyond 12. Defaults to 4.
--no-boundary-tag DO NOT tag all addresses with some of the key admin boundary IDs for creating aggregates and choropleth maps.

Example Command Line Arguments

Local Postgres server: python load-gnaf.py --gnaf-tables-path="C:\temp\geoscape_202405\G-NAF" --admin-bdys-path="C:\temp\geoscape_202405\Administrative Boundaries" Loads the GNAF tables to a Postgres server running locally. GNAF archives have been extracted to the folder C:\temp\geoscape_202405\G-NAF, and admin boundaries have been extracted to the C:\temp\geoscape_202405\Administrative Boundaries folder.
Remote Postgres server: python load-gnaf.py --gnaf-tables-path="\\svr\shared\gnaf" --local-server-dir="f:\shared\gnaf" --admin-bdys-path="c:\temp\unzipped\AdminBounds_ESRI" Loads the GNAF tables which have been extracted to the shared folder \\svr\shared\gnaf. This shared folder corresponds to the local f:\shared\gnaf folder on the Postgres server. Admin boundaries have been extracted to the c:\temp\unzipped\AdminBounds_ESRI folder.
Loading only selected states: python load-gnaf.py --states VIC TAS NT ... Loads only the data for Victoria, Tasmania and Northern Territory

Advanced

You can load the Admin Boundaries without GNAF. To do this: comment out steps 1, 3 and 4 in def main.

Note: you can't load GNAF without the Admin Bdys due to dependencies required to split Melbourne and to fix non-boundary locality_pids on addresses.

Attribution

When using the resulting data from this process - you will need to adhere to the attribution requirements on the data.gov.au pages for GNAF and the Admin Bdys, as part of the open data licensing requirements.

WARNING:

The scripts will DROP ALL TABLES using CASCADE in the GNAF and Admin Bdy schemas and then recreate them; meaning you'll LOSE YOUR VIEWS if you have created any! If you want to keep the existing data - you'll need to change the schema names in the script or use a different database
All raw GNAF tables can be created UNLOGGED to speed up the data load. This will make them UNRECOVERABLE if your database is corrupted. You can run these scripts again to recreate them. If you think this sounds ok - set the unlogged_tables flag to True for a slightly faster load
Boundary tagging (on by default) will add 15-60 minutes to the process if you have PostGIS 2.2+. If you have PostGIS 2.1 or lower - it can take HOURS as the boundary tables can't be optimised!

IMPORTANT:

Whilst you can choose which 4 schemas to load the data into, I haven't QA'd every permutation. Stick with the defaults if you have limited Postgres experience
If you're not running the Python script on the Postgres server, you'll need to have access to a network path to the GNAF files on the database server (to create the list of files to process). The alternative is to have a local copy of the raw files
The 'create tables' sql script will add the PostGIS extension to the database in the public schema, you don't need to add it to your database
There is an option to VACUUM the database at the start after dropping the existing GNAF/Admin Bdy tables - this doesn't really do anything outside of repeated testing. (I was too lazy to take it out of the code as it meant renumbering all the SQL files and I'd like to go to bed now)

Option 2 - Run the database in a docker container

GNAF and the Admin Boundaries are ready to use in Postgres in an image on Docker Hub.

Process

In your docker environment pull the image using docker pull minus34/gnafloader:latest
Run using docker run --publish=5433:5432 minus34/gnafloader:latest
Access Postgres in the container via port 5433. Default login is - user: postgres, password: password

Note: the compressed Docker image is 8Gb, uncompressed is 25Gb

WARNING: The default postgres superuser password is insecure and should be changed using:

ALTER USER postgres PASSWORD '<something a lot more secure>'

Option 3 - Load PG_DUMP Files

Download Postgres dump files and restore them in your database.

Should take 15-60 minutes.

Pre-requisites

Postgres 14+ with PostGIS 3.0+
A knowledge of Postgres pg_restore parameters

Process

Download the GNAF dump file or GNAF GDA2020 dump file (~2.0Gb)
Download the Admin Bdys dump file or Admin Bdys GDA2020 dump file (~2.8Gb)
Edit the restore-gnaf-admin-bdys.bat or .sh script in the supporting-files folder for your dump file names, database parameters and for the location of pg_restore
Run the script, come back in 15-60 minutes and enjoy!

Option 4 - Geoparquet Files in S3

Geoparquet versions of the spatial tables, as well as parquet versions of the non-spatial tables, are in a public S3 bucket for use directly in an application or service. They can also be downloaded using the AWS CLI.

Geometries have WGS84 lat/long coordinates (SRID/EPSG:4326). A sample query for analysing the data using Apache Sedona, the spatial extension to Apache Spark is in the spark folder.

The files are here: s3://minus34.com/opendata/geoscape-202405/geoparquet/

AWS CLI Examples:

List all datasets: aws s3 ls s3://minus34.com/opendata/geoscape-202405/geoparquet/
Copy all datasets: aws s3 sync s3://minus34.com/opendata/geoscape-202405/geoparquet/ <my-local-folder>

DATA LICENSES

Incorporates or developed using G-NAF © Geoscape Australia licensed by the Commonwealth of Australia under the Open Geo-coded National Address File (G-NAF) End User Licence Agreement.

Incorporates or developed using Administrative Boundaries © Geoscape Australia licensed by the Commonwealth of Australia under Creative Commons Attribution 4.0 International licence (CC BY 4.0).

DATA CUSTOMISATION

GNAF and the Admin Bdys have been customised to remove some of the known, minor limitations with the data. The most notable are:

All addresses link to a gazetted locality that has a boundary. Those small number of addresses that don't in raw GNAF have had their locality_pid changed to a gazetted equivalent
Localities have had address and street counts added to them
Suburb-Locality bdys have been flattened into a single continuous layer of localities - South Australian Hundreds have been removed and ACT districts have been added where there are no gazetted localities
The Melbourne, VIC locality has been split into Melbourne, 3000 and Melbourne 3004 localities (the new locality PIDs are loc9901d119afda_1 & loc9901d119afda_2). The split occurs at the Yarra River (based on the postcodes in the Melbourne addresses)
A postcode boundaries layer has been created using the postcodes in the address tables. Whilst this closely emulates the official Geoscape postcode boundaries, there are several hundred addresses that are in the wrong postcode bdy. Do not treat this data as authoritative

gnaf-loader's People

Contributors

Stargazers

Watchers

gnaf-loader's Issues

Top level Administrative Boundaries subfolder no longer exists

The latest zipfile no longer has a single top level "Administrative Boundaries" subfolder - there are now state subfolders, each with their own "Administrative Boundaries".

Pointing to the folder containing AdminBounds_MMMYY_STATE_GDA2020_SHP folders seems to be working.

Error at File "gnaf-loader/load-gnaf.py", line 513, in prep_admin_bdys

I'm trying to update openaddresses with the FEB18 release but when I run from a clean install of Debian 9 with the following setup:

apt update && apt install byobu git curl zip unzip parallel python-psycopg2 postgresql-9.6 postgresql-9.6-postgis-2.3 postgis

git clone https://github.com/minus34/gnaf-loader.git

TMP=/tmp/work
mkdir $TMP
mkdir $TMP/gnaf $TMP/gnaf-admin $TMP/tablespace
chown postgres:postgres $TMP/tablespace

sudo -u postgres psql -c "CREATE USER gnafun WITH SUPERUSER PASSWORD 'gnafpw'"
sudo -u postgres psql -c "CREATE TABLESPACE gnafts OWNER gnafun LOCATION '$TMP/tablespace'"
sudo -u postgres psql -c 'CREATE DATABASE gnafdb OWNER gnafun TABLESPACE gnafts'
sudo -u postgres psql -c 'CREATE EXTENSION postgis'

curl -s --retry 10 --location 'https://data.gov.au/dataset/bdcf5b09-89bc-47ec-9281-6b8e9ee147aa/resource/53c24b8e-4f55-4eed-a189-2fc0dcca6381/download/feb18adminboundsesrishapefileordbffile20180219141148.zip' -o $TMP/gnaf-admin.zip &
curl -s --retry 10 --location 'https://data.gov.au/dataset/19432f89-dc3a-4ef3-b943-5326ef1dbecc/resource/4b084096-65e4-4c8e-abbe-5e54ff85f42f/download/feb18gnafpipeseparatedvalue20180219141901.zip' -o $TMP/gnaf.zip &
wait
parallel "unzip -d $TMP/{} $TMP/{}.zip" ::: gnaf gnaf-admin

GNAF_DIR="$(find $TMP -type d | grep 'G-NAF' | grep 'Authority Code' | xargs -I {} dirname {} | head -n1)"
BOUNDARY_DIR="$(find $TMP -type d | grep 'Administrative Boundaries' | head -n1 | xargs -I {} dirname {})"

# /tmp/work/gnaf/FEB18_GNAF_PipeSeparatedValue_20180219141901/G-NAF/G-NAF FEBRUARY 2018
# /tmp/work/gnaf-admin/FEB18_AdminBounds_ESRIShapefileorDBFfile_20180219141148

# change local all all connection to md5 to accept password connections locally
service postgresql restart

python gnaf-loader/load-gnaf.py --pguser gnafun --pgdb gnafdb --pgpassword gnafpw --gnaf-schema gnaf --gnaf-tables-path "$GNAF_DIR" --admin-bdys-path "$BOUNDARY_DIR" --raw-unlogged --no-boundary-tag

I get the following output:

root        : INFO
root        : INFO     Start gnaf-loader
root        : INFO      - running Python 2.7.13 with Psycopg2 2.6.2
root        : INFO      - on Linux #1 SMP Debian 4.9.30-2+deb9u1 (2017-06-18)
root        : INFO      - using Postgres 9.6.6 on x86_64-pc-linux-gnu and PostGIS 2.3.1 (with GEOS 3.5.1-CAPI-1.9.1)
root        : INFO
root        : INFO     Part 1 of 4 : Start raw GNAF load : 2018-02-28 18:08:09.408937
root        : INFO      - Step 1 of 7 : tables dropped : 0:00:00.069420
root        : INFO      - Step 2 of 7 : database NOT vacuumed
root        : INFO      - Step 3 of 7 : UNLOGGED tables created : 0:00:00.019004
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:01:28.195092
root        : INFO      - Step 5 of 7 : indexes created: 0:03:00.082667
root        : INFO      - Step 6 of 7 : primary & foreign keys NOT created
root        : INFO      - Step 7 of 7 : tables analysed : 0:00:04.827586
root        : INFO     Part 1 of 4 : Raw GNAF loaded! : 0:04:33.199275
root        : INFO
root        : INFO     Part 2 of 4 : Start raw admin boundary load : 2018-02-28 18:12:42.608401
root        : INFO      - Step 1 of 3 : raw admin boundaries loaded : 0:06:51.062249
Traceback (most recent call last):
  File "gnaf-loader/load-gnaf.py", line 940, in <module>
    if main():
  File "gnaf-loader/load-gnaf.py", line 90, in main
    prep_admin_bdys(pg_cur, settings)
  File "gnaf-loader/load-gnaf.py", line 513, in prep_admin_bdys
    .format(settings['admin_bdys_schema'], settings['pg_user']))
psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block

Looking at the database the raw_ schema and tables are all there, but the non-raw schema doesn't exist and I can't work out why it's not being created. Any ideas?

This is the postgresql log:

2018-02-28 18:19:33.668 AEDT [5607] gnafun@gnafdb ERROR:  current transaction is aborted, commands ignored until end of transaction block
2018-02-28 18:19:33.668 AEDT [5607] gnafun@gnafdb STATEMENT:  SET CLIENT_ENCODING TO UTF8;
        SET STANDARD_CONFORMING_STRINGS TO ON;
        BEGIN;
        INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('1','AGRICULTURAL',NULL);
        INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('2','COMMERCIAL',NULL);
        INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('3','EDUCATION',NULL);
        INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('4','HOSPITAL/MEDICAL',NULL);
        INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('5','INDUSTRIAL',NULL);
        INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('6','NOUSUALRESIDENCE',NULL);
        INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('7','PARKLAND',NULL);
        INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('8','RESIDENTIAL',NULL);
        INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('9','SHIPPING',NULL);
        INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('10','TRANSPORT',NULL);
        INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('11','WATER',NULL);
        INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('12','OTHER',NULL);
        INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('13','ANTARCTICA',NULL);
        INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('14','MIGRATORY',NULL);
        INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('15','OFFSHORE',NULL);
        COMMIT;
        ANALYZE "raw_admin_bdys_201802"."aus_mb_category_class_aut";

2018-02-28 18:19:33.679 AEDT [5607] gnafun@gnafdb ERROR:  current transaction is aborted, commands ignored until end of transaction block
2018-02-28 18:19:33.679 AEDT [5607] gnafun@gnafdb STATEMENT:  CREATE SCHEMA IF NOT EXISTS admin_bdys_201802 AUTHORIZATION gnafun

Output reference tables to Shapefile

Postcode Bdy Table is Empty

Fix!

Option to use `\copy` instead of `COPY FROM`

This way one utilise data that exists on the client machine instead.

Administrative boundaries dataset is missing state boundaries files

Thought I would share this to save others some time -

At the time of writing, the administrative boundaries dataset linked from this project readme is missing the state boundary definitions.

This manifests as many SQL errors on import, the script eventually crashes.

I was able to work around this by downloading an older version of the administrative boundaries set (Aug 22) and copying the state boundaries into my Nov 22 admin boundaries folder tree.

I've submitted feedback on the dataset in the hope that this can be amended!

As a side note - @minus34: could you confirm if the use of GDA94 admin boundaries is required when using GDA2020 everywhere else (including --srid 7844)? I was able to successfully run the script using both GDA94 and GDA2020 admin boundaries once the missing files were replaced.

SQL FAILED errors during load

I'm trying to run an import with load.gnaf.py on the latest master with commit dc2be64c61970e99a1217cf4a5f36910438f81b2 and encountering these errors each time which cause the script to eventually fail completely.

Kind of at a loss about what to do... Any suggestions?

Full log here - https://pastebin.com/kRHkfXTq (or see excerpt below)

root        : INFO
root        : INFO     Start gnaf-loader
root        : INFO      - running Python 3.6.4 with Psycopg2 2.7.1
root        : INFO      - on Linux #136-Ubuntu SMP Tue Feb 16 22:44:38 UTC 2021
root        : 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)
root        : INFO
root        : INFO     Arguments
root        : INFO      - prevacuum : False
root        : INFO      - raw_fk : False
root        : INFO      - raw_unlogged : True
root        : INFO      - max_processes : 6
root        : INFO      - no_boundary_tag : False
root        : INFO      - pghost : localhost
root        : INFO      - pgport : 5469
root        : INFO      - pgdb :
root        : INFO      - pguser : ubuntu
root        : INFO      - psma_version : 201802
root        : INFO      - raw_gnaf_schema : raw_gnaf
root        : INFO      - raw_admin_schema : raw_admin_bdys
root        : INFO      - gnaf_schema : gnaf
root        : INFO      - admin_schema : admin_bdys
root        : INFO      - gnaf_tables_path : /home/ubuntu/GEO_21_AUG/FILES/G-NAF/G-NAF AUGUST 2021
root        : INFO      - admin_bdys_path : /home/ubuntu/GEO_21_AUG/FILES/AUG21_Admin_Boundaries_ESRIShapefileorDBFfile
root        : INFO      - states : ['ACT', 'NSW', 'NT', 'OT', 'QLD', 'SA', 'TAS', 'VIC', 'WA']
root        : INFO
root        : INFO     Part 1 of 6 : Create schemas : 2021-10-05 02:30:59.216284
root        : INFO     Part 1 of 6 : Schemas created! : 0:00:00.003572
root        : INFO
root        : INFO     Part 2 of 6 : Start raw GNAF load : 2021-10-05 02:30:59.220131
root        : INFO      - Step 1 of 7 : tables dropped : 0:00:00.004193
root        : INFO      - Step 2 of 7 : database NOT vacuumed
root        : INFO      - Step 3 of 7 : UNLOGGED tables created : 0:00:00.085270
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     SQL FAILED! : COPY raw_gnaf.mb_2021 FROM '/home/ubuntu/GEO_21_AUG/FILES/G-NAF/G-NAF AUGUST 2021/Standard/ACT_MB_2021_psv.psv' DELIMITER '|' CSV HEADER; : relation "raw_gnaf.mb_2021" does not exist

root        : INFO     SQL FAILED! : COPY raw_gnaf.locality_pid_linkage.psv FROM '/home/ubuntu/GEO_21_AUG/FILES/G-NAF/G-NAF AUGUST 2021/Standard/act_locality_pid_linkage.psv' DELIMITER '|' CSV HEADER; : cross-database references are not implemented: "raw_gnaf.locality_pi
d_linkage.psv"

root        : INFO     SQL FAILED! : COPY raw_gnaf.address_mesh_block_2021 FROM '/home/ubuntu/GEO_21_AUG/FILES/G-NAF/G-NAF AUGUST 2021/Standard/ACT_ADDRESS_MESH_BLOCK_2021_psv.psv' DELIMITER '|' CSV HEADER; : relation "raw_gnaf.address_mesh_block_2021" does not exist

root        : INFO     SQL FAILED! : COPY raw_gnaf.mb_2021 FROM '/home/ubuntu/GEO_21_AUG/FILES/G-NAF/G-NAF AUGUST 2021/Standard/NSW_MB_2021_psv.psv' DELIMITER '|' CSV HEADER; : relation "raw_gnaf.mb_2021" does not exist

root        : INFO     SQL FAILED! : COPY raw_gnaf.locality_pid_linkage.psv FROM '/home/ubuntu/GEO_21_AUG/FILES/G-NAF/G-NAF AUGUST 2021/Standard/nsw_locality_pid_linkage.psv' DELIMITER '|' CSV HEADER; : cross-database references are not implemented: "raw_gnaf.locality_pi
d_linkage.psv"

root        : INFO     SQL FAILED! : COPY raw_gnaf.address_mesh_block_2021 FROM '/home/ubuntu/GEO_21_AUG/FILES/G-NAF/G-NAF AUGUST 2021/Standard/NSW_ADDRESS_MESH_BLOCK_2021_psv.psv' DELIMITER '|' CSV HEADER; : relation "raw_gnaf.address_mesh_block_2021" does not exist

root        : INFO     SQL FAILED! : COPY raw_gnaf.address_mesh_block_2021 FROM '/home/ubuntu/GEO_21_AUG/FILES/G-NAF/G-NAF AUGUST 2021/Standard/NT_ADDRESS_MESH_BLOCK_2021_psv.psv' DELIMITER '|' CSV HEADER; : relation "raw_gnaf.address_mesh_block_2021" does not exist

root        : INFO     SQL FAILED! : COPY raw_gnaf.locality_pid_linkage.psv FROM '/home/ubuntu/GEO_21_AUG/FILES/G-NAF/G-NAF AUGUST 2021/Standard/nt_locality_pid_linkage.psv' DELIMITER '|' CSV HEADER; : cross-database references are not implemented: "raw_gnaf.locality_pid
_linkage.psv"

root        : INFO     SQL FAILED! : COPY raw_gnaf.mb_2021 FROM '/home/ubuntu/GEO_21_AUG/FILES/G-NAF/G-NAF AUGUST 2021/Standard/NT_MB_2021_psv.psv' DELIMITER '|' CSV HEADER; : relation "raw_gnaf.mb_2021" does not exist

Docker Install Errors "raw_admin_bdys_201708.*** does not exist

Hi,
Trying to install this on docker but getting lots of errors saying things don't exist.
Scripts gets here and stops.
�[32mloader_1 |�[0m Traceback (most recent call last):
�[32mloader_1 |�[0m File "load-gnaf.py", line 940, in
�[32mloader_1 |�[0m if main():
�[32mloader_1 |�[0m File "load-gnaf.py", line 98, in main
�[32mloader_1 |�[0m create_reference_tables(pg_cur, settings)
�[32mloader_1 |�[0m File "load-gnaf.py", line 636, in create_reference_tables
�[32mloader_1 |�[0m pg_cur.execute(psma.open_sql_file("03-10-reference-split-melbourne.sql", settings))
�[32mloader_1 |�[0m psycopg2.ProgrammingError: relation "admin_bdys_201708.locality_bdys" does not exist
�[32mloader_1 |�[0m LINE 14: FROM admin_bdys_201708.locality_bdys AS bdy
�[32mloader_1 |�[0m ^
�[32mloader_1 |�[0m
�[32mdata_loader_1 exited with code 1

Output reference tables to PSV

docker-entrypoint.sh references file load-gnaf-27.py which doesn't exist

Hi, thanks for your work on making the GNAF database easy to use. When building with docker-compose, the script fails trying to execute load-gnaf-27.py, which should be changed to load-gnaf.py.

Fails at Part 2 OF 4 - Not finding/creating boundary layers

Postgres 9.5.3, PostGIS 2.2.2
Anaconda 2.4.1 (32-bit) - Python 2.7.12
psycopg2-win-py27 - 2.6.1
All installed on a Windows 10 local machine.

Created an empty database in postgres called gnaf_May16

Have downloaded the May 2016 GNAF and the Admin boundaries

Only modifications made to load-gnaf.py are to hard-code in my PGDATABASE, PGUSER, PGPASSWORD details.

Running load-gnaf.py with the following parameters:
python load-gnaf.py --gnaf-tables-path="D:\DemoFolder\G-NAF\Downloads\MAY16_GNAF_PipeSeparatedValue_20160523140820\G-NAF\G-NAF MAY 2016" --admin-bdys-path="D:\DemoFolder\G-NAF\Downloads" --states TAS ACT

Fails at Part 2 OF 4.

relation "raw_admin_bdys.aus_state" does NOT exist
LINE 12:   FROM raw_admin_bdys.aus_state AS tab

Full log here.

Database after failing:

So looks like its failing to create any boundary files.

"--admin-bdys-path = This path should contain a subfolder named Administrative Boundaries."
I fed in the argument for this to point to D:\DemoFolder\G-NAF\Downloads, which has a sub folder called Administrative Boundaries, which then has the various subfolders for each type, and these then have subfolders for Authority Code and Standard, as downloaded from data.gov.au.

I am a complete noob with PostGIS and also Python, but im guessing I have made a basic rookie error somewhere. I know I can use the dump files you provide, but would like to get this working so I can get a better understanding of how it all works. Care to put me out of my misery?

restore not working

I am trying to restore the dump file on Postgres 11+ with PostGIS 2.5+.
The bat file is edited for right file location.
But the restore process report a serial of errors like:
"pg_restore: [archiver (db)] could not execute query: Error: ......"

The final database, admin schema has one table, and gnaf has 8 tables.
I think it is wrong. Any ideas? Thanks.

Inconsistent mesh block code type

Hi! The mesh block codes have inconsistent datatypes between the admin boundaries and the gnaf. Please and thank you! :D

Load not completing - hangs at Part 3 - Step 13 for WA only

Load hanging at Part 3 - Step 13 for WA only. All other states complete within 2-3 minutes but WA never completes even after leaving it running for multiple hours. Offending code below

INSERT INTO admin_bdys_201905.postcode_bdys (postcode, state, address_count, street_count, geom)
SELECT postcode,
       state,
       SUM(address_count),
       SUM(street_count),
       ST_Multi(ST_Buffer(ST_Union(ST_Buffer(geom, 0.0000001)), -0.0000001))
  FROM admin_bdys_201905.locality_bdys
  WHERE state = 'WA' GROUP BY postcode,
           state;

PostgreSQL 11.3 64bit
PostGIS 2.5.2
Python 3.6.7 (anaconda)

Windows 10
i7-6600U
32gb RAM

May 19 GNAF file and most up to date shapefiles from data.gov.au

Edit: I left it running overnight and completed in just under 9 hours

06/25/2019 11:19:13 PM Start gnaf-loader
06/25/2019 11:19:13 PM 	- running Python 3.6.7 with Psycopg2 2.8.3
06/25/2019 11:19:13 PM 	- on Windows 6.1.7601
06/25/2019 11:19:13 PM 	- using Postgres 11.3 and PostGIS 2.5.2 (with GEOS 3.7.0-CAPI-1.11.0)
06/25/2019 11:19:13 PM 
06/25/2019 11:19:13 PM Arguments
06/25/2019 11:19:13 PM 	- prevacuum : True
06/25/2019 11:19:13 PM 	- raw_fk : False
06/25/2019 11:19:13 PM 	- raw_unlogged : False
06/25/2019 11:19:13 PM 	- max_processes : 4
06/25/2019 11:19:13 PM 	- no_boundary_tag : False
06/25/2019 11:19:13 PM 	- pghost : 127.0.0.1
06/25/2019 11:19:13 PM 	- pgdb : geodb
06/25/2019 11:19:13 PM 	- pgpassword : ************
06/25/2019 11:19:13 PM 	- psma_version : 201905
06/25/2019 11:19:13 PM 	- raw_gnaf_schema : raw_gnaf_201905
06/25/2019 11:19:13 PM 	- raw_admin_schema : raw_admin_bdys_201905
06/25/2019 11:19:13 PM 	- gnaf_schema : gnaf_201905
06/25/2019 11:19:13 PM 	- admin_schema : admin_bdys_201905
06/25/2019 11:19:13 PM 	- gnaf_tables_path : C:\Apps\GNAF\MAY19_GNAF_PipeSeparatedValue_20190521155815\G-NAF
06/25/2019 11:19:13 PM 	- admin_bdys_path : C:\Apps\GNAF\Administrative Boundaries
06/25/2019 11:19:13 PM 	- states : ['ACT', 'NSW', 'NT', 'OT', 'QLD', 'SA', 'TAS', 'VIC', 'WA']
06/25/2019 11:19:13 PM 
06/25/2019 11:19:13 PM Part 1 of 6 : Create schemas : 2019-06-25 23:19:13.645994
06/25/2019 11:19:13 PM Part 1 of 6 : Schemas created! : 0:00:00.001000
06/25/2019 11:19:13 PM 
06/25/2019 11:19:13 PM Part 2 of 6 : Start raw GNAF load : 2019-06-25 23:19:13.647994
06/25/2019 11:19:16 PM 	- Step 1 of 7 : tables dropped : 0:00:02.391000
06/25/2019 11:19:39 PM 	- Step 2 of 7 : database vacuumed : 0:00:23.556600
06/25/2019 11:19:39 PM 	- Step 3 of 7 : tables created : 0:00:00.109000
06/25/2019 11:19:39 PM 		- Loading state ACT
06/25/2019 11:19:39 PM 		- Loading state NSW
06/25/2019 11:19:39 PM 		- Loading state NT
06/25/2019 11:19:39 PM 		- Loading state OT
06/25/2019 11:19:39 PM 		- Loading state QLD
06/25/2019 11:19:39 PM 		- Loading state SA
06/25/2019 11:19:39 PM 		- Loading state TAS
06/25/2019 11:19:39 PM 		- Loading state VIC
06/25/2019 11:19:39 PM 		- Loading state WA
06/25/2019 11:24:28 PM 	- Step 4 of 7 : tables populated : 0:04:49.052800
06/25/2019 11:27:54 PM 	- Step 5 of 7 : indexes created: 0:03:25.900800
06/25/2019 11:27:54 PM 	- Step 6 of 7 : primary & foreign keys NOT created
06/25/2019 11:28:12 PM 	- Step 7 of 7 : tables analysed : 0:00:18.130400
06/25/2019 11:28:12 PM Part 2 of 6 : Raw GNAF loaded! : 0:08:59.145600
06/25/2019 11:28:12 PM 
06/25/2019 11:28:12 PM Part 3 of 6 : Start raw admin boundary load : 2019-06-25 23:28:12.794594
06/25/2019 11:41:01 PM 	- Step 1 of 3 : raw admin boundaries loaded : 0:12:48.794200
06/25/2019 11:42:10 PM 	- Step 2 of 3 : admin boundaries prepped : 0:01:09.404200
06/25/2019 11:49:44 PM 	- Step 3 of 3 : admin boundaries for analysis created : 0:07:33.059200
06/25/2019 11:49:44 PM Part 3 of 6 : Raw admin boundaries loaded! : 0:21:31.259600
06/25/2019 11:49:44 PM 
06/25/2019 11:49:44 PM Part 4 of 6 : Start create reference tables : 2019-06-25 23:49:44.054194
06/25/2019 11:49:44 PM 	- Step  1 of 14 : create reference tables : 0:00:00.160000
06/25/2019 11:49:44 PM 	- Step  2 of 14 : localities populated : 0:00:00.339000
06/25/2019 11:49:45 PM 	- Step  3 of 14 : locality aliases populated : 0:00:00.523000
06/25/2019 11:49:45 PM 	- Step  4 of 14 : locality neighbours populated : 0:00:00.113000
06/25/2019 11:49:56 PM 	- Step  5 of 14 : streets populated : 0:00:11.141400
06/25/2019 11:49:56 PM 	- Step  6 of 14 : street aliases populated : 0:00:00.360000
06/25/2019 11:54:59 PM 	- Step  7 of 14 : addresses populated : 0:05:03.093600
06/25/2019 11:55:34 PM 	- Step  8 of 14 : principal alias lookup populated : 0:00:34.232200
06/25/2019 11:56:04 PM 	- Step  9 of 14 : primary secondary lookup populated : 0:00:30.528800
06/25/2019 11:56:33 PM 	- Step 10 of 14 : Melbourne split : 0:00:28.477000
06/25/2019 11:59:46 PM 	- Step 11 of 14 : localities finalised : 0:03:13.926600
06/26/2019 12:01:12 AM 	- Step 12 of 14 : addresses finalised : 0:01:25.882200
06/26/2019 07:39:43 AM 	- Step 13 of 14 : postcode boundaries created : 7:38:31.028524
06/26/2019 07:46:13 AM 	- Step 14 of 14 : create primary & foreign keys and indexes : 0:06:29.773600
06/26/2019 07:46:13 AM Part 4 of 6 : Reference tables created! : 7:56:29.587924
06/26/2019 07:46:13 AM 
06/26/2019 07:46:13 AM Part 5 of 6 : Start boundary tagging addresses : 2019-06-26 07:46:13.642118
06/26/2019 07:51:21 AM 	- Step 1 of 6 : principal addresses tagged with admin boundary IDs: 0:05:07.869000
06/26/2019 07:55:12 AM 	- Step 2 of 6 : principal addresses - invalid matches deleted & bdy tag indexes created : 0:03:50.642400
06/26/2019 07:58:38 AM 	- Step 3 of 6 : principal addresses - bdy tags added to output table : 0:03:26.207800
06/26/2019 08:00:17 AM 	- Step 4 of 6 : created index on bdy tagged address table : 0:01:39.226400
06/26/2019 08:00:49 AM 	- Step 5 of 6 : no boundary tag duplicates : 0:00:32.052800
06/26/2019 08:01:15 AM 	- Step 6 of 6 : alias addresses boundary tagged : 0:00:57.949200
06/26/2019 08:01:15 AM 	- Step 6 of 6 : boundary tagged address view created : 0:00:57.961200
06/26/2019 08:01:15 AM Part 5 of 6 : Addresses boundary tagged: 0:15:01.947800
06/26/2019 08:01:15 AM 
06/26/2019 08:01:15 AM Part 6 of 6 : Start row counts : 2019-06-26 08:01:15.590918
06/26/2019 08:04:21 AM 	- Step 1 of 2 : got row counts for gnaf_201905 schema : 0:03:05.843062
06/26/2019 08:04:29 AM 	- Step 2 of 2 : got row counts for admin_bdys_201905 schema : 0:03:13.491120
06/26/2019 08:04:29 AM Part 6 of 6 : Got row counts : 0:03:13.492121
06/26/2019 08:04:29 AM 
06/26/2019 08:04:29 AM Total time : : 8:45:15.654045
06/26/2019 08:04:29 AM Finished successfully!
06/26/2019 08:04:29 AM 
06/26/2019 08:04:29 AM -------------------------------------------------------------------------------

Missing documentation for changed option

We’ve been using the --no-boundary-tag option in OpenAddresses which looks like it was recently removed. Can it be documented in the changelog?

Missing Admin Bdy Shapefiles not being logged correctly

When the admin boundary shapefiles can't be found, a fatal log entry should be output. The code should trap this, but it's not working

Failing to trap this causes a large number of SQL errors when trying to prep the admin bdys. Making it hard for users to determine the source of the errors

Lots of Shapefile SQL Errors

I've been trying to load this datbase today, but I'm getting odd errors that I'm not sure about.

This is running on Centos 7 with Python 3.8.6 with Psycopg2 2.8.4 and Postgres 13.4 on x86_64-pc-linux-gnu and PostGIS 3.1.3 (with GEOS 3.9.1-CAPI-1.14.2).

I've downloaded the Geoscape Admin Boundaries - ESRI Shapefile - GDA2020(ZIP) file, which I'm hoping is the correct one.

The first error is

Importing ACT_TOWN_POINT_shp.shp - Couldn't run Shapefile SQL
shp2pgsql result was: column "geom" of relation "aus_town_point" does not exist

This is true. The "geom" column does not exist in the table.

Then we launch into an awful lot of these errors.

root        : WARNING   Importing WA_TOWN_POINT_shp.dbf - Couldn't run Shapefile SQL
shp2pgsql result was: current transaction is aborted, commands ignored until end of transaction block

Is this because of that first error?

docker-compose up failed dependencies

Attempting to run the docker image and getting this issue:

Reading package lists...
Reading package lists...
Building dependency tree...
Reading state information...
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
postgis : Depends: libgdal1h (>= 1.9.0) but it is not installable
Depends: libgeos-c1 (>= 3.4.2) but it is not installable
Depends: liblwgeom-2.4-0 (>= 2.0.0) but it is not going to be installed
postgresql-client-9.6 : Depends: libpq5 (>= 9.6.9) but 9.6.7-0+deb9u1 is to be installed
E: Unable to correct problems, you have held broken packages.
ERROR: Service 'loader' failed to build: The command '/bin/sh -c apt-get update && apt-get install -y --no-install-recommends postgresql-client-9.6 postgis && apt-get autoremove -y --purge && apt-get clean && rm -rf /var/lib/apt/lists/* /tmp/* /var/tmp/*' returned a non-zero code: 100

Please add hashbang to load-gnaf.py

This will make it easier for me to package NixOS/nixpkgs#46139

You can just add #!/usr/bin/env python or #!/usr/bin/env python3.

invalid byte sequence for encoding "UTF8": 0xc9 CONTEXT: COPY address_site, line 94109

I'll try to investigate but it looks like an issue with the source data, any one else run into the same issue?

root        : INFO     SQL FAILED! : COPY raw_gnaf_202002.address_site FROM '/tmp/work/gnaf/G-NAF/G-NAF FEBRUARY 2020/Standard/NSW_ADDRESS_SITE_psv.psv' DELIMITER '|' CSV HEADER; : invalid byte sequence for encoding "UTF8": 0xc9
CONTEXT:  COPY address_site, line 94109

SSL error: decryption failed or bad record mac

not sure if anyone else has faced this error?

I'm using python3-psycopg2 version 2.8.6-2

root        : INFO     Part 3 of 6 : Start raw admin boundary load : 2021-11-14 03:35:11.941076
root        : INFO      - Step 1 of 3 : raw admin boundaries loaded : 0:06:19.379195
root        : INFO              - 15 duplicates removed from raw_admin_bdys_202111.aus_mb_category_class_aut
root        : INFO              - 7 duplicates removed from raw_admin_bdys_202111.aus_remoteness_category_aut
root        : INFO              - authority tables deduplicated
multiprocessing.pool.RemoteTraceback: 
"""
Traceback (most recent call last):
  File "/usr/lib/python3.9/multiprocessing/pool.py", line 125, in worker
    result = (True, func(*args, **kwds))
  File "/usr/local/gnaf-loader/geoscape.py", line 45, in run_sql_multiprocessing
    pg_cur.execute("SET search_path = {0}, public, pg_catalog".format(settings.raw_gnaf_schema,))
psycopg2.OperationalError: SSL error: decryption failed or bad record mac

"""

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/gnaf-loader/load-gnaf.py", line 1011, in <module>
    if main():
  File "/usr/local/gnaf-loader/load-gnaf.py", line 120, in main
    prep_admin_bdys(pg_cur)
  File "/usr/local/gnaf-loader/load-gnaf.py", line 549, in prep_admin_bdys
    geoscape.multiprocess_list("sql", sql_list, logger)
  File "/usr/local/gnaf-loader/geoscape.py", line 27, in multiprocess_list
    result_list = list(results)
  File "/usr/lib/python3.9/multiprocessing/pool.py", line 870, in next
    raise value
psycopg2.OperationalError: SSL error: decryption failed or bad record mac

Missing Boundaries Due to Inconsistent Authority Files

Geoscape have changed the delivery format to include an authority code file for each state in the Administrative Boundaries dataset.

There are authority files with different authority codes in them (e.g. ACT vs NSW MB 2016 Authority Codes) which cause some boundaries to be missing due to only the first authority code file being used in gnaf-loader to populate the data (to remove duplicates from the process).

Postgres DB without a role named 'postgres' fails

So running the script on a database without the postgres user role fails. (This is default when installing PG using homebrew, I think)

Here's the error:

Part 1 of 3 : Start raw GNAF load : 2016-02-26 14:00:22.543925 - Step 1 of 6 : tables dropped : 0:00:00.003236 - Step 2 of 6 : database NOT vacuumed Traceback (most recent call last): File "load-gnaf.py", line 621, in <module> main() File "load-gnaf.py", line 132, in main create_raw_gnaf_tables(pg_cur) File "load-gnaf.py", line 210, in create_raw_gnaf_tables pg_cur.execute(sql) psycopg2.ProgrammingError: role "postgres" does not exist

I was able to get past this by creating a postgres role.

This is an assumption, which I assume is throughout the .sql files. Perhaps this 'OWNER' should be the database user, and find/replaced at runtime?

README inconsistency with May 2023 GNAF

The README notes:
"--admin-bdys-path specifies the path to the extracted source admin boundary files. This path should contain a subfolder named Administrative Boundaries."
This subfolder doesn't exist in the ABS downloads (any more?) The November 2022 boundaries had a folder called NOV22_AdminBounds_GDA2020_SHP with everything under that, the May 2023 boundary folders are top-level folders within the zip.

I think this lead me down a bit of a rabbithole, I created a folder called "Administrative Boundaries" under my target folder, extracted the May 2023 ABS zip under that, and the script failed to find any of the admin boundary data at all. Extracting the ABS zip directly into my target folder works.

Docker Image: User `postgres' has no password assigned

Hi again, trying out the Nov22 docker image, I ran into another issue.

TLDR; after running the database and trying to connect (from outside the container), I got:

2023-03-20 01:45:44.820 UTC [81] postgres@postgres FATAL:  password authentication failed for user "postgres"
2023-03-20 01:45:44.820 UTC [81] postgres@postgres DETAIL:  User "postgres" has no password assigned.
Connection matched pg_hba.conf line 105: "host all  all    0.0.0.0/0  md5"

Steps

Downloaded the docker image
Run docker run --publish=5433:5432 --name=gnafloader minus34/gnafloader:202211-gda2020
Try to connect from outside via Beekeper Studio or psycopg2
Authentication failure with the error message mentioned above User "postgres" has no password assigned.
Connected to the docker container docker exec -it gnafloader /bin/bash and ran psql --port=5433 and execute ALTER USER postgres PASSWORD "password";
Now the connection is established successful

Potential Issue

It's weird, because setting the password is in the Dockerfile. My best guess would be that maybe the pg_restore command overwrites the previously set password and sets it back to nothing, if your dump contained no password for the postgres user.

Missing table

I just attempted to run the current version of this loader against the Feb 2016 GNAF files, and I’m seeing a lot of instances of this error late in the process:

SQL FAILED! : INSERT INTO gnaf.temp_state_upper_house_electorates_tags (gnaf_pid, gnaf_state, alias_principal, bdy_pid, bdy_name, bdy_state)
SELECT pnts.gnaf_pid,
       pnts.state,   
       'P',
       bdys.se_upper_pid,
       bdys.name,
       bdys.state
  FROM gnaf.address_principals AS pnts
  INNER JOIN admin_bdys.state_upper_house_electorates_analysis AS bdys
  ON ST_Within(pnts.geom, bdys.geom) WHERE bdys.gid > 1440 AND bdys.gid <= 2293;

…

: relation "admin_bdys.state_upper_house_electorates_analysis" does not exist
LINE 9:   INNER JOIN admin_bdys.state_upper_house_electorates_analys...

I’m curious if this is something you’ve encountered, or if it’s a bug? The script completes shortly after.

Latest Docker Image only Available for Mac (linux/arm64/v8)

Hi @minus34,

I just wanted to pull the latest Docker image (Feb23) and noticed that you must have upgraded your build machine to a new Mac. The image now is only available for the architecture linux/arm64/v8, which I cannot run on my Linux machine (linux/amd64).

Is it possible that you can build and push for multiple platforms? Afaik, this is possible by using docker build --platform linux/amd64 <...> in the build process (or could require docker buildx build ... command - I'm not a docker expert).

For now, the old version from Nov22 is still recent enough (and runs on linux/amd64), but eventually this would cause issues.

Here's a log of what pops out when I try to run this on my Fedora 33:

docker run --publish=5433:5432 minus34/gnafloader:latest
WARNING: The requested image's platform (linux/arm64/v8) does not match the detected host platform (linux/amd64) and no specific platform was requested
standard_init_linux.go:228: exec user process caused: exec format error

GDA2020 support

This is a very handy project, thanks for making it available to the world.

I'm curious if support for loading the GDA2020 admin boundaries is on the radar? Obviously maintaining 2 code paths isn't desirable but I imagine a lot of folks have migrated to GDA2020 some time ago.

If not, can you suggest an approach that might achieve the same outcome - ie loading the GDA94 boundaries and performing an in-place translation to the 2020 datum?

script fails with function st_subdivide(geometry, integer) does not exist

Log below.

Sorry I don't have the brainpower to troubleshoot it currently. I'll see if I can make a PR to fix it later on the weekend.

Using these admin boundaries: https://data.gov.au/dataset/psma-administrative-boundaries/resource/53c24b8e-4f55-4eed-a189-2fc0dcca6381

And this G-NAF: https://data.gov.au/dataset/geocoded-national-address-file-g-naf

Part 1 of 3 : Start raw GNAF load : 2016-02-27 09:03:28.022014
    - Step 1 of 6 : tables dropped : 0:00:00.120549
    - Step 2 of 6 : database NOT vacuumed
    - Step 3 of 6 : tables created : 0:00:00.073267
    - Step 4 of 6 : tables populated : 0:00:03.921974
    - Step 5 of 6 : indexes created: 0:00:08.050198
    - Step 6 of 6 : primary & foreign keys NOT created
Part 1 of 4 : Raw GNAF loaded! : 0:00:12.166740

Part 2 of 4 : Start raw admin boundary load : 2016-02-27 09:03:40.188783
    - Step 1 of 2 : raw admin boundaries loaded : 0:00:06.340119
Traceback (most recent call last):
  File "load-gnaf.py", line 628, in <module>
    main()
  File "load-gnaf.py", line 149, in main
    create_admin_bdys_for_analysis(pg_cur)
  File "load-gnaf.py", line 382, in create_admin_bdys_for_analysis
    pg_cur.execute(open_sql_file("02-02-create-admin-bdys-tables.sql"))
psycopg2.ProgrammingError: function st_subdivide(geometry, integer) does not exist
LINE 225:        ST_Subdivide((ST_Dump(ST_Buffer(geom, 0.0))).geom, 51...

'--states' command line argument doesn't take more than one state

I haven't been able to get the '--states' argument to work with more than one state.

e,g. --states=ACT NT fails with invalid argument 'NT', even though a StackExchange article indicates this is the correct syntax.

This is on a Windows 10 machine with Python 2.7.11

NOTE: the default (i.e. not using the --states flag) works fine and loads all states as expected.

docker-compose up failed

Attempting to run the docker-composer up but i am getting error

 => CACHED [3/8] RUN wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -                                                                                       0.0s
 => ERROR [4/8] RUN apt-get update && apt-get install -y --no-install-recommends   postgresql-client-9.6 postgis &&   apt-get autoremove -y --purge &&   apt-get clean && rm -rf /var/lib/apt  41.7s
------                                                                                                                                                                                               
 > [4/8] RUN apt-get update && apt-get install -y --no-install-recommends   postgresql-client-9.6 postgis &&   apt-get autoremove -y --purge &&   apt-get clean && rm -rf /var/lib/apt/lists/* /tmp/* /var/tmp/*:                                                                                                                                                                                         
#6 2.236 Ign http://apt.postgresql.org jessie-pgdg InRelease                                                                                                                                         
#6 2.449 Ign http://apt.postgresql.org jessie-pgdg Release.gpg                                                                                                                                       
#6 2.552 Get:1 http://security.debian.org jessie/updates InRelease [44.9 kB]
#6 2.702 Ign http://apt.postgresql.org jessie-pgdg Release
#6 2.711 Ign http://deb.debian.org jessie InRelease
#6 3.347 Get:2 http://deb.debian.org jessie Release.gpg [1652 B]
#6 3.347 Err http://apt.postgresql.org jessie-pgdg/main amd64 Packages
#6 3.347   
#6 3.474 Get:3 http://deb.debian.org jessie Release [77.3 kB]
#6 3.546 Err http://apt.postgresql.org jessie-pgdg/main amd64 Packages
#6 3.546   
#6 3.958 Err http://apt.postgresql.org jessie-pgdg/main amd64 Packages
#6 3.958   
#6 3.995 Get:4 http://security.debian.org jessie/updates/main amd64 Packages [992 kB]
#6 4.262 Err http://apt.postgresql.org jessie-pgdg/main amd64 Packages
#6 4.262   
#6 4.541 Err http://apt.postgresql.org jessie-pgdg/main amd64 Packages
#6 4.541   404  Not Found [IP: 87.238.57.227 80]
#6 4.711 Get:5 http://deb.debian.org jessie/main amd64 Packages [9098 kB]
#6 41.64 Fetched 10.2 MB in 39s (256 kB/s)
#6 41.64 W: Failed to fetch http://apt.postgresql.org/pub/repos/apt/dists/jessie-pgdg/main/binary-amd64/Packages  404  Not Found [IP: 87.238.57.227 80]
#6 41.64 
#6 41.64 E: Some index files failed to download. They have been ignored, or old ones used instead.
------
executor failed running [/bin/sh -c apt-get update && apt-get install -y --no-install-recommends   postgresql-client-9.6 postgis &&   apt-get autoremove -y --purge &&   apt-get clean && rm -rf /var/lib/apt/lists/* /tmp/* /var/tmp/*]: exit code: 100
ERROR: Service 'loader' failed to build : Build failed

Geocode Level

Is there a way to get the LEVEL_GEOCODED_CODE? I noticed that alot of stuff has been changed to create the tables in PostGIS. The summary at the bottom of the README isn't really comprehensive. It's hard to relate the fields/tables mentioned in the product description with the final database you have created.

Also since I'm using your dump, I don't even have access to the original raw gnaf data.

The presence of the `gid` column

The gid column isn't the primary key. It's only used for serial auto increment of numbers. It doesn't seem useful. Perhaps there's no need to export this gid column?

Compare final record counts between GNAF and Admin Bdy versions for QA

Need to compare the new and previous gnaf.qa and admin_bdy.qa tables to qa the results and get record count differences

Docker container pauses

I just faced an issue with the docker container - it was pausing shortly after a start, about here:

This happened a few times and I started thinking that the problem is caused by some bug in the container. As it turned out later, the issue was related to the lack of disk space on the host. Here's is what VM's log said (I did not even know it existed):

Changing the VM state from 'SUSPENDING' to 'SUSPENDED'
Console: Machine state changed to 'Paused'
Console: VM runtime error: fatal=false, errorID=DevAHCI_DISKFULL message="Host system reported disk full. VM execution is suspended. You can resume after freeing some space"

Just leaving this here to help others with sorting out similar problems. The archives with data as well as the unzipped folders are rather large, so I guess I'm not the only person who has to go through this sort of investigation.

Thanks a lot for the repo, @minus34!

Issues with May 2023

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
                ^

raw_admin_bdys_201702.XYZ does not exist

Hi Minus34,

Are you able to nudge me in the right direction? I keep getting errors like this -

relation "raw_admin_bdys_201702.aus_state" does not exist
relation "raw_admin_bdys_201702.aus_lga" does not exist
relation "raw_admin_bdys_201702.aus_ward" does not exist

The script keeps running but eventually dies completely with

Traceback (most recent call last):
File "load-gnaf.py", line 953, in
if main():
File "load-gnaf.py", line 99, in main
create_reference_tables(pg_cur, settings)
File "load-gnaf.py", line 650, in create_reference_tables
pg_cur.execute(psma.open_sql_file("03-10-reference-split-melbourne.sql", settings))
psycopg2.ProgrammingError: relation "admin_bdys_201702.locality_bdys" does not exist
LINE 14: FROM admin_bdys_201702.locality_bdys AS bdy

After it dies the DB looks like this -

Im running the script like this:
python load-gnaf.py --gnaf-tables-path="C:\GNAF\FEB17 GNAF PipeSeperatedValue\GNAF\G-NAF FEBRUARY 2017" --admin-bdys-path="C:\GNAF\feb17adminboundsesrishapefileordbffile\FEB17_AdminBounds_ESRIShapefileorDBFfile" --pgdb="GNAF" --pguser="postgres" --pgpassword="postgres"

I have added the postgre bin folder to PATH variable. I've confirmed that both shp2pgsql and psql run in command line.

My machine -

Windows 10
Postgres 9.5.0 and PostGIS 2.2.1
Python 3.6.1

Full log file attached.
load-gnaf.log.txt

Much appreciated.

Key (street_locality_pid)=(SAL3622104) is duplicated in Aug20

Probably a data issue, so I'll see if I can manually tweak the source data as a workaround, but just a heads up that

root        : INFO     Part 4 of 6 : Start create reference tables : 2020-08-18 16:45:08.383685
root        : INFO      - Step  1 of 14 : create reference tables : 0:00:00.188041
root        : INFO      - Step  2 of 14 : localities populated : 0:00:00.647852
root        : INFO      - Step  3 of 14 : locality aliases populated : 0:00:00.464266
root        : INFO      - Step  4 of 14 : locality neighbours populated : 0:00:00.112384
Traceback (most recent call last):
  File "/usr/local/gnaf-loader/load-gnaf.py", line 967, in <module>
    if main():
  File "/usr/local/gnaf-loader/load-gnaf.py", line 132, in main
    create_reference_tables(pg_cur, settings)
  File "/usr/local/gnaf-loader/load-gnaf.py", line 626, in create_reference_tables
    pg_cur.execute(psma.open_sql_file("03-05-reference-populate-streets.sql", settings))
psycopg2.IntegrityError: could not create unique index "streets_pk"
DETAIL:  Key (street_locality_pid)=(SAL3622104) is duplicated.

Cawley, WA missing from locality_bdys_display in the Admin Bdys Postgres dump

--raw-fk flag causes SQL errors

Hi @minus34 👋

I've been able to import the latest gnaf without error using your loader and default parameters after resolving an issue with the incomplete administrative boundaries dataset 🥂

However, I've noticed that when the --raw-fk flag is enabled (we are primarily interested in the raw tables) some SQL errors occur on import.

My assumption is that we can ignore these errors given that 2011 mesh blocks are no longer part of recent gnaf/admin boundary releases, I can see that references in the SQL scripts that form part of the loader have been commented out - not sure where these errors are being triggered.

Thanks for your ongoing work on this project and a happy festive period to you!

root        : INFO
root        : INFO     Start gnaf-loader
root        : INFO      - running Python 3.10.6 with psycopg 3.1.5
root        : INFO      - on Linux #62-Ubuntu SMP Tue Nov 22 19:54:14 UTC 2022
root        : INFO      - using Postgres 15.1 (Ubuntu 15.1-1.pgdg22.04+1) on x86_64-pc-linux-gnu and PostGIS 3.3.2 (with GEOS 3.10.2-CAPI-1.16.0)
root        : INFO
root        : INFO     Arguments
root        : INFO      - prevacuum : False
root        : INFO      - raw_fk : True
root        : INFO      - raw_unlogged : False
root        : INFO      - max_processes : 4
root        : INFO      - no_boundary_tag : False
root        : INFO      - srid : 7844
root        : INFO      - pgdb : gnafloader-fk
root        : INFO      - pguser : internoot
root        : INFO      - pgpassword : ************
root        : INFO      - geoscape_version : 202211
root        : INFO      - previous_geoscape_version : 202208
root        : INFO      - gnaf_tables_path : /mnt/data/GNAF-Nov-22/G-NAF/G-NAF NOVEMBER 2022/
root        : INFO      - admin_bdys_path : /mnt/data/GNAF-Nov-22/NOV22_AdminBounds_GDA2020_SHP/
root        : INFO      - states : ['ACT', 'NSW', 'NT', 'OT', 'QLD', 'SA', 'TAS', 'VIC', 'WA']
root        : INFO
root        : INFO     Part 1 of 6 : Create schemas : 2022-12-21 02:45:57.749409
root        : INFO     Part 1 of 6 : Schemas created! : 0:00:00.022754
root        : INFO
root        : INFO     Part 2 of 6 : Start raw GNAF load : 2022-12-21 02:45:57.772235
root        : INFO      - Step 1 of 7 : tables dropped : 0:00:00.001063
root        : INFO      - Step 2 of 7 : database NOT vacuumed
root        : INFO      - Step 3 of 7 : tables created : 0:00:00.073257
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:01:28.724042
root        : INFO              - fixed missing geocodes
root        : INFO              - authority tables deduplicated
root        : INFO      - Step 5 of 7 : indexes created : 0:00:48.128293
root        : INFO     SQL FAILED! : ALTER TABLE ONLY raw_gnaf_202211.address_mesh_block_2011 : syntax error at end of input
LINE 1: ALTER TABLE ONLY raw_gnaf_202211.address_mesh_block_2011
                                                                ^
root        : INFO     SQL FAILED! : ALTER TABLE ONLY raw_gnaf_202211.mb_2011 : syntax error at end of input
LINE 1: ALTER TABLE ONLY raw_gnaf_202211.mb_2011
                                                ^
root        : INFO     SQL FAILED! : ALTER TABLE ONLY raw_gnaf_202211.address_mesh_block_2011 : syntax error at end of input
LINE 1: ALTER TABLE ONLY raw_gnaf_202211.address_mesh_block_2011
                                                                ^
root        : INFO     SQL FAILED! : ALTER TABLE ONLY raw_gnaf_202211.address_mesh_block_2011 : syntax error at end of input
LINE 1: ALTER TABLE ONLY raw_gnaf_202211.address_mesh_block_2011
                                                                ^
root        : INFO     SQL FAILED! : ALTER TABLE ONLY raw_gnaf_202211.address_mesh_block_2011 : syntax error at end of input
LINE 1: ALTER TABLE ONLY raw_gnaf_202211.address_mesh_block_2011
                                                                ^
root        : INFO      - Step 6 of 7 : primary & foreign keys created : 0:02:28.663401
root        : INFO      - Step 7 of 7 : tables analysed : 0:00:00.006380
root        : INFO     Part 2 of 6 : Raw GNAF loaded! : 0:04:45.901886
root        : INFO
root        : INFO     Part 3 of 6 : Start raw admin boundary load : 2022-12-21 02:50:43.674194
root        : INFO      - Step 1 of 3 : raw admin boundaries loaded : 0:05:58.103922
root        : INFO              - 15 duplicates removed from raw_admin_bdys_202211.aus_mb_category_class_aut
root        : INFO              - 7 duplicates removed from raw_admin_bdys_202211.aus_remoteness_category_aut
root        : INFO              - authority tables deduplicated
root        : INFO      - Step 2 of 3 : admin boundaries prepped : 0:01:00.503472
root        : INFO      - Step 3 of 3 : admin boundaries for analysis created : 0:00:39.252281
root        : INFO     Part 3 of 6 : Raw admin boundaries loaded! : 0:07:38.121221
root        : INFO
root        : INFO     Part 4 of 6 : Start create reference tables : 2022-12-21 02:58:21.795476
root        : INFO      - Step  1 of 14 : create reference tables : 0:00:00.242278
root        : INFO      - Step  2 of 14 : localities populated : 0:00:00.405605
root        : INFO      - Step  3 of 14 : locality aliases populated : 0:00:00.400337
root        : INFO      - Step  4 of 14 : locality neighbours populated : 0:00:00.126535
root        : INFO      - Step  5 of 14 : streets populated : 0:00:05.036684
root        : INFO      - Step  6 of 14 : street aliases populated : 0:00:00.218432
root        : INFO      - Step  7 of 14 : addresses populated : 0:01:33.812881
root        : INFO      - Step  8 of 14 : principal alias lookup populated : 0:00:14.012606
root        : INFO      - Step  9 of 14 : primary secondary lookup populated : 0:00:13.512616
root        : INFO      - Step 10 of 14 : Melbourne split : 0:00:13.880089
root        : INFO      - Step 11 of 14 : localities finalised : 0:00:39.709535
root        : INFO      - Step 12 of 14 : addresses finalised : 0:00:57.440604
root        : INFO      - Step 13 of 14 : postcode boundaries created : 0:00:47.840171
root        : INFO      - Step 14 of 14 : create primary & foreign keys and indexes : 0:01:28.306782
root        : INFO     Part 4 of 6 : Reference tables created! : 0:06:14.948283
root        : INFO
root        : INFO     Part 5 of 6 : Start boundary tagging addresses : 2022-12-21 03:04:36.743823
root        : INFO      - Step 1 of 7 : principal addresses tagged with admin boundary IDs: 0:01:39.736330
root        : INFO      - Step 2 of 7 : principal addresses - invalid matches deleted & bdy tag indexes created : 0:00:36.953921
root        : INFO      - Step 3 of 7 : principal addresses - bdy tags added to output table : 0:01:06.816293
root        : INFO      - Step 4 of 7 : created index on bdy tagged address table : 0:00:41.147566
root        : INFO      - Step 5 of 7 : no boundary tag duplicates : 0:00:08.448133
root        : INFO      - Step 6 of 7 : alias addresses boundary tagged : 0:00:19.168150
root        : INFO      - Step 7 of 7 : boundary tagged address view created : 0:00:00.005469
root        : INFO     Part 5 of 6 : Addresses boundary tagged : 0:04:23.907242
root        : INFO
root        : INFO     Part 6 of 6 : Start row counts : 2022-12-21 03:09:00.651223
root        : WARNING           - Previous schema (gnaf_202208) doesn't exist - row count comparison not done
root        : INFO      - Step 1 of 2 : got row counts for gnaf_202211 schema : 0:00:15.717228
root        : WARNING           - Previous schema (admin_bdys_202208) doesn't exist - row count comparison not done
root        : INFO      - Step 2 of 2 : got row counts for admin_bdys_202211 schema : 0:00:16.639916
root        : INFO     Part 6 of 6 : Got row counts : 0:00:16.640013
root        : INFO
root        : INFO     Total time : 0:23:19.551898
root        : INFO     Finished successfully!
root        : INFO
root        : INFO     -------------------------------------------------------------------------------

union all over union

The addresses views in the GNAF files use a UNION which seems to cause Postgres to check for duplicates before displaying data. As the addresses are unique anyway, could you pretty please change the definition to UNION ALL so the data is displayed immediately? 🙏

Totally understand if there's a reason not to.

Thanks again for this wonderful service!

False error when loading certain states by themselves

If you only want to load certain states - you may get an error in Part 2 stating a table doesn't exist - this can be ignored. e.g. loading ACT only will return an error saying "local_government_wards" does not exist.

minus34 / gnaf-loader Goto Github PK

gnaf-loader's Introduction

gnaf-loader

What's GNAF?

There are 4 options for loading the data

Option 1 - Run load.gnaf.py

Performance

Pre-requisites

Process

Command Line Options

Required Arguments

Postgres Parameters

Optional Arguments

Example Command Line Arguments

Advanced

Attribution

WARNING:

IMPORTANT:

Option 2 - Run the database in a docker container

Process

Option 3 - Load PG_DUMP Files

Pre-requisites

Process

Option 4 - Geoparquet Files in S3

AWS CLI Examples:

DATA LICENSES

DATA CUSTOMISATION

gnaf-loader's People

Contributors

Stargazers

Watchers

Forkers

gnaf-loader's Issues

Recommend Projects

Recommend Topics

Recommend Org