ondrejivanic/g-naf

Geocoded National Address File is Australia’s authoritative, geocoded address file. This repo has few tools / scripts to create derived datasets

G-NAF

Geocoded National Address File is Australia’s authoritative, geocoded address file. This repo has a few tools / scripts to create derived datasets

Prerequisites

Working PostgreSQL instalation

Import data

• Create source directory

mkdir source

• Unzip FEB16_GNAF+EULA_PipeSeparatedValue_20160222170142.zip into source directory

• Import *.psv

./gnaf2pgsql | psql

• Merge state data and create flat table

psql -f g-naf.sql

Last step creates address_detail_flat table in public schema and staging schema has all merged tables and authority code tables

Address Flat Table

This table has all addreses (as in address_detail table) and contains addtional information. Appendix B in G-NAF documentation decribes G-NAF data model in detail.

            Table "public.address_detail_flat"
          Column           |       Type       | Modifiers 
---------------------------+------------------+-----------
 address_detail_pid        | text             | 
 building_name             | text             | 
 lot_number_prefix         | text             | 
 lot_number                | text             | 
 lot_number_suffix         | text             | 
 flat_type_code            | text             | 
 flat_number_prefix        | text             | 
 flat_number               | integer          | 
 flat_number_suffix        | text             | 
 level_type_code           | text             | 
 level_number_prefix       | text             | 
 level_number              | integer          | 
 level_number_suffix       | text             | 
 number_first_prefix       | text             | 
 number_first              | integer          | 
 number_first_suffix       | text             | 
 number_last_prefix        | text             | 
 number_last               | integer          | 
 number_last_suffix        | text             | 
 location_description      | text             | 
 alias_principal           | text             | 
 postcode                  | text             | 
 private_street            | text             | 
 legal_parcel_id           | text             | 
 confidence                | integer          | 
 level_geocoded_code       | integer          | 
 property_pid              | text             | 
 gnaf_property_pid         | text             | 
 primary_secondary         | text             | 
 m2_mb_2011_code           | text             | 
 amb2_mb_match_code        | text             | 
 sl_street_class_code      | text             | 
 sl_street_name            | text             | 
 sl_street_type_code       | text             | 
 sl_street_suffix_code     | text             | 
 sl_gnaf_street_pid        | text             | 
 sl_gnaf_street_confidence | integer          | 
 sl_gnaf_reliability_code  | integer          | 
 l_locality_name           | text             | 
 l_primary_postcode        | text             | 
 l_locality_class_code     | text             | 
 l_gnaf_locality_pid       | text             | 
 l_gnaf_reliability_code   | integer          | 
 s_state_abbreviation      | text             | 
 lp_planimetric_accuracy   | integer          | 
 lp_longitude              | double precision | 
 lp_latitude               | double precision | 
 as_address_type           | text             | 
 as_address_site_name      | text             | 
 adg_geocode_type_code     | text             | 
 adg_longitude             | double precision | 
 adg_latitude              | double precision | 
 slp_boundary_extent       | integer          | 
 slp_planimetric_accuracy  | integer          | 
 slp_longitude             | double precision | 
 slp_latitude              | double precision | 

The following tables are included:

• address_mesh_block_2011 (amb2_)
• mb_2011 (mb_)
• address_site (as_)
• locality (l_)
• street_locality (sl_)
• state (s_)
• locality_point (lp_)
• address_default_geocode (adg_)
• street_locality_point (slp_)

g-naf's Issues

address_detail_flat creation taking many hours to create

Hello, wondering what's a rough estimate of time it should take for the creation of this table. It's been about 24 hours so far, so i'm wondering if it's my problem or is it expected to take so long.

I'm using probably "average" hardware, a laptop with 8G ram with Intel i7-6600U cpu.

Path for PSV file in Python code

Hi. I downloaded the AUG 2016 GNAF file.
your ./source/////*.psv

should now be changed to:

./source////.psv

The path is changed.
Thanks

Incorrect Join

Line 613 of your current g-naf.sql uses the line left outer join staging.locality l on l.locality_pid = ad.locality_pid you may wish to update this to left outer join staging.locality l on l.street_locality_pid = ad.street_locality_pid

At present your join will create extra records as the locality_pid is a 1:n join.