The Australian Government coat of Arms

Communities of practice

Communities of practice

Mapping postcode data to local government areas

Hello,

I am scoping out how to map postcode data to different statistical geographic area like local government area. I was wondering if anyone has come across a guide to construct these geography conversion tables on excel or can give me some pointers on how to go about doing it.

Cheers,

Joe

Hey Joe,

What precisely do you want to do?

If you’re looking to map data to LGAs I might be able to help.

-Giles

The ABS has some LGA data at
https://www.abs.gov.au/AUSSTATS/abs@.nsf/DetailsPage/1270.0.55.003July%202019?OpenDocument

older version at https://www.abs.gov.au/AUSSTATS/abs@.nsf/DetailsPage/1270.0.55.003July%202016?OpenDocument

There’s also this but it’s for 2011 data https://greenash.net.au/thoughts/2014/07/australian-lga-to-postcode-mappings-with-postgis-and-intersects/

cheers
Rosie

Hello Giles,

Thanks for the reply, It is much appreciated!

Essentially, II would like to create a choropleth map that visually describes the ‘reach’ of a program. i.e. the number of people that have participated in a program in geographic space. At the moment
I am scoping out how to go about doing this so your advice would be greatly appreciated.

The data to be represented will come from registration forms that participants will complete prior to undertaking a webinar. As part of this registration form they will include details of their postcodes.
Because there might not be enough participants to warrant so much granularity, I thought that it would be best to aggregate this postcode data to its corresponding LGA’s.

So far, I have been able to assign mock postcode data to its concomitant LGA but I have not managed to find a way to map it.

To concord the postcode data to its LGA equivalent I have:

  • Downloaded the ABS AGS2016 grid correspondences

  • Added a row with the mock postcode data

  • Used vlookup and sum if to show how many times a postcode appears in an LGA.

  • The end result is a two column excel sheet with 1) the LGA (and its ABS assigned number) and 2) the count of postcodes within each LGA.

I have also found on the ABS site the most recent shapefile of a map of Australia with the necessary LGA layer but I have not gone further. I tried to use ESRI online (free trial) to see if I can
load that layer and play around with it (I.e. link the excel file to the shapefile) but I could not even go through that initial step of loading the layer.

I was wondering if this is the approach that one should take given my stated goal. Also do you have any advice on how to go about the last step of mapping the data without having to spend money on
a mapping program? I have been reading up on QGIS for example. Would you recommend it?

Thanks in advance for your response.

Cheers,

Joe Lasco.

Hey Joe,

What you’ve developed is a good start. Essentially what you’ll need to do is add an additional column with the count of participants to your two excel sheet and merge this to a map file via something like QGIS.

This isn’t hard to do but is a little bit finicky when you’re just learning QGIS here’s a good summary of how to do it in the US: https://www.youtube.com/watch?v=rG6UphZGmg4

Happy to do skype/lunch (if you’re in Sydney) or somesuch to run through how to do it if it’s helpful.

Edit: BTW the map file for Australia (by LGA) is here: https://www.abs.gov.au/AUSSTATS/abs@.nsf/DetailsPage/1270.0.55.003July%202016?OpenDocument
(download the file "Local Government Areas ASGS Ed 2016 Digital Boundaries in MapInfo Interchange Format ").

Once you’ve downloaded the .zip file you can just drag it onto QGIS and it will show you a map of Australia. You can then merge the excel data (via a csv) and create a map - like the video.

-Giles

Essentially what you need to do to map the data is to merge your LGA participant numbers with the map’s ‘attribute table’. This is essentially what they’re doing in the video I shared: joining the data with the map file.

Once they’re joined you can then just create heatmap via QGIS etc.

Thanks Giles,

Happy to know I am not too far off. I’ll have a look at the links you have provided and see if I can get it done.

Cheers from Melbourne.

Joe