12
Jul

Australian LGA to postcode mappings with PostGIS and Intersects

For a recent project, I needed to know the LGAs (Local Government Areas) of all postcodes in Australia, and vice versa. As it turns out, there is no definitive Australia-wide list containing this data anywhere. People have been discussing the issue for some time, with no clear outcome. So, I decided to get creative.

To cut a long story short: I've produced my own list! You can download my Australian LGA postcode mappings spreadsheet from Google Docs.

If you want the full story: I imported both the LGA boundaries data and the Postal Area boundaries data from the ABS, into PostGIS, and I did an "Intersects" query on the two datasets. I exported the results of this query to CSV. Done! And all perfectly reproducible, using freely available public data sets, and using free and open-source software tools.

The process

I started by downloading the Geo data that I needed, from the ABS. My source was the page Australian Statistical Geography Standard (ASGS): Volume 3 - Non ABS Structures, July 2011. This was the most recent page that I could find on the ABS, containing all the data that I needed. I downloaded the files "Local Government Areas ASGS Non ABS Structures Ed 2011 Digital Boundaries in MapInfo Interchange Format", and "Postal Areas ASGS Non ABS Structures Ed 2011 Digital Boundaries in MapInfo Interchange Format".

Big disclaimer: I'm not an expert at anything GIS- or spatial-related, I'm a complete n00b at this. I decided to download the data I needed in MapInfo format. It's also available on the ABS web site in ArcGIS Shapefile format. I could have downloaded the Shapefiles instead – they can also be imported into PostGIS, using the same tools that I used. I chose the MapInfo files because I did some quick Googling around, and I got the impression that MapInfo files are less complex and are somewhat more portable. I may have made the wrong choice. Feel free to debate the merits of MapInfo vs ArcGIS files for this task, and to try this out yourself using ArcGIS instead of MapInfo. I'd be interested to see the difference in results (theoretically there should be no difference… in practice, who wants to bet there is?).

I then had to install PostGIS (I already had Postgres installed) and related tools on my local machine (running Ubuntu 12.04). I'm not providing PostGIS installation instructions here, there's plenty of information available elsewhere to help you get set up with all the tools you need, for your specific OS / requirements. Installing PostGIS and related tools can get complicated, so if you do decide to try all this yourself, don't say I didn't warn you. Ubuntu is probably one of the easier platforms on which to install it, but there are plenty of guides out there for Windows and Mac too.

Once I was all set up, I imported the data files into a PostGIS-enabled Postgres database with these commands:

ogr2ogr -a_srs EPSG:4283 -f "PostgreSQL" \
PG:"host=localhost user=lgapost dbname=lgapost password=PASSWORD" \
-lco OVERWRITE=yes -nln lga LGA_2011_AUST.mid

ogr2ogr -a_srs EPSG:4283 -f "PostgreSQL" \
PG:"host=localhost user=lgapost dbname=lgapost password=PASSWORD" \
-lco OVERWRITE=yes -nln postcodes POA_2011_AUST.mid

If you're interested in the OGR Toolkit (ogr2ogr and friends), there are plenty of resources available; in particular, this OGR Toolkit guide was very useful for me.

After playing around with a few different map projections, I decided that EPSG:4283 was probably the correct one to use as an argument to ogr2ogr. I based my decision on seeing the MapInfo projection string "CoordSys Earth Projection 1, 116" in the header of the ABS data files, and then finding this list of common Australian-used map projections. Once again: I am a total n00b at this. I know very little about map projections (except that it's a big and complex topic). Feel free to let me know if I've used completely the wrong projection for this task.

I renamed the imported tables to 'lga' and 'postcodes' respectively, and I then ran this from the psql shell, to find all LGAs that intersect with all postal areas, and to export the result to a CSV:

\copy (SELECT     l.state_name_2011,
                  l.lga_name_2011,
                  p.poa_code_2011
       FROM       lga l
       INNER JOIN postcodes p
       ON         ST_Intersects(
                      l.wkb_geometry,
                      p.wkb_geometry)
       ORDER BY   l.state_name_2011,
                  l.lga_name_2011,
                  p.poa_code_2011)
TO '/path/to/lga_postcodes.csv' WITH CSV HEADER;

Final remarks

That's about it! Also, some notes of mine (mainly based on the trusty Wikipedia page Local Government in Australia):

  • There's no data for the ACT, since the ACT has no LGAs
  • Almost the entire Brisbane and Gold Coast metro areas, respectively, are one LGA
  • Some areas of Australia aren't part of any LGA (although they're all remote areas with very small populations)
  • Quite a large number of valid Australian postcodes are not part of any LGA (because they're for PO boxes, for bulk mail handlers, etc, and they don't cover a geographical area as such, in the way that "normal" postcodes do)

I hope that this information is of use, to anyone else who needs to link up LGAs and postcodes in a database or in a GIS project.

Comments are closed

Comments

23
Sep
2014

Amazing work thank you so much.

I have a list of suburbs that I am using together with your table. I can send it to you. Let me know.

Cheers- Juan

11
Oct
2014

Thanks so much for this. Very clear explanation - which helps to qualify the data. And great explanation on how to replicate the process if needed. Thanks again.

17
Oct
2014
Thank you, Jeremy,
I was having a dreadful time trying to get some LGA data into Tableau as a personal exercise, but I have not programming skills at all and the python script I was given was all messed up.

This file has helped me a great deal.

12
Nov
2014

Life saver thankyou! Just what I needed for a tender:)

08
Jan
2015

You are the best.

24
Feb
2015
vealmince
Some pivot table goodness:
40% of postcodes are contained in a single LGA, the rest cross multiple LGAs.
The average LGA covers 12 postcodes (the median is 10).
Brisbane LGA covers 90 postcodes.
Postcode 4871 is included in 19 LGAs: Aurukun, Cairns, Carpentaria, Cassowary Coast, Charters Towers, Cook, Croydon, Etheridge, Flinders, Hope Vale, Kowanyama, Lockhart River, McKinlay, Mornington, Napranum, Pormpuraaw, Richmond, Tablelands, Yarrabah.

Only 10 LGAs comprise a single postcode: Palm Island, Tiwi Islands, Weipa, Mornington, Pormpuraaw, Mapoon, Torres Strait Island, Deniliquin, Broken Hill, King Island.

21
Apr
2015

Hi,

This is brilliant, just what I was looking for.

Just one question though some postcodes are in more than one LGA. How do you select the appropriate one?

21
Apr
2015

@G. Re: "some postcodes are in more than one LGA. How do you select the appropriate one?" Well, that's the whole point - many of those postcodes really do belong to multiple LGAs. Officially and on the ground. So, for those postcodes, there is no "appropriate one", there's only an "appropriate many".

If you have more information, e.g. an exact street address, which yields a geographical point (not an area), then you can of course always map that to a single LGA. But with just a postcode, all you have is a (possibly large) geographical area, which may overlap with the areas of multiple LGAs.

18
Jun
2015

Thanks so much, this is brilliant! I wonder why there is no official doc of this info available??

17
Feb
2017

UPDATE: due to a request that I received, I've created a new spreadsheet based on the latest ABS data sets (released in 2016). You can access the new file "Australian LGA postcode mappings (2016 data)" at https://docs.google.com/spr... . Main changes, as far as I can see: areas that have no LGA are now included (listed as "Unincorporated"), including all of the ACT; the controversial NSW council amalgamations have happened, and this is now reflected in the data sets.

Also, when trying to generate the new mapping with PostGIS, I got an "ERROR: GEOSIntersects: TopologyException: side location conflict" for the polygons for the Circular Head LGA / the 7330 postcode in Tasmania. So, I excluded these problem areas from the auto-generated mapping, and per the info at http://www.geopostcodes.com... (and a few other similar web sites), I manually added "Tasmania,Circular Head,7321", "Tasmania,Circular Head,7330", and "Tasmania,Circular Head,7331" to the spreadsheet.