search

minus34 / gnaf-loader

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

--raw-fk flag causes SQL errors #73

Open internoot opened 1 year ago

internoot commented 1 year ago

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     -------------------------------------------------------------------------------
minus34 commented 1 year ago

The logic used to split the raw GNAF PK & FK SQL into individual statements didn't support the commenting out of the MB 2011 statements - they've now been removed. Should be fine now.