search

minus34 / gnaf-loader

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

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

Closed funk44 closed 3 years ago

funk44 commented 5 years ago

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 -------------------------------------------------------------------------------
minus34 commented 3 years ago

Better late than never - have added state index on source table to speed up postcode creation. Issue not showing in August 2021 release