I had a few hours spare and so thought I’d have a play with the “G-NAF” data set provided free of charge by the Australian government

What is G-NAF?

G-NAF (Geocoded National Address File) is a trusted index of Australian address information. It contains the state, suburb, street, number and coordinate reference (or “geocode”) for street addresses in Australia.

It’s worth noting that it doesn’t contain personal or business related data - just data on physical addresses in Australia.

It comes as a ZIP file containing SQL scripts to define the database schema as well as pipe-delimited data files. More information can be found here

Where can I get it?

Head to https://data.gov.au/dataset/geocoded-national-address-file-g-naf

Click the link “Go to resource” as shown above.

How do I get it into SQL server?

These are the steps I followed - there’s more than one way to skin a cat, as they say.

1. Unzip the archive e.g. aug18_gnaf_pipeseparatedvalue_20180827115521.zip into a suitable location (e.g. c:\source)
2. Install SQL Server (I used the “Developer” edition 2017; “Express” probably would have been fine also.
3. Create a new database called GNAF. I used the default collation (SQL_Latin1_General_CP1_CI_AS) and that seems to have worked fine.
4. Put your database in simple recovery to speed things up: ALTER DATABASE GNAF SET RECOVERY SIMPLE;
5. Execute the script G-NAF\Extras\create_tables_sqlserver.sql against the GNAF database (I ran the script via SQL Server Management Studio)
6. Import data - I made use of a couple of PowerShell scripts to load the data in via BCP (hosted on gist.github.com)

• load_authority_codes.ps1

PS .\load_authority_codes.ps1 -serverName eddie-pc -databaseName GNAF -authorityCodeFilesPath '.\AUG18_GNAF_PipeSeparatedValue_20180827115521\G-NAF\G-NAF AUGUST 2018\Authority Code'

• load_jurisdiction_codes.ps1

PS .\load_jurisdiction_data.ps1 -serverName eddie-pc -databaseName GNAF -jurisdictionFilesPath '.\AUG18_GNAF_PipeSeparatedValue_20180827115521\G-NAF\G-NAF AUGUST 2018\Standard' -jurisdictionName QLD

7. Create the “sample” view by executing G-NAF\Extras\GNAF_View_Scripts\address_view.sql (I ran the script using SQL Server Management Studio)
8. Optionally, create foreign key constraints by executing G-NAF\Extras\GNAF_TableCreation_Scripts\add_fk_constraints.sql (I didn’t bother)

It only took a few minutes to import the data for Queensland and roughly the same for Western Australia (inside a VM).

What can I do with it?

Well I’m still figuring that part out. You can run simple “geocoding” type queries as below:

Have fun with it!