When I was studying geography at university I enjoyed creating my own maps using ArcGIS software. However, I thought my days of creating maps were over when I finished my course and no longer had access to the software. Fortunately I did the boot camp at the THAT Camp in Canberra last year where Paul Hagon showed us how to create maps through Google Fusion Tables. How exciting! In this post I will share with you how I created a basic map of some 2011 Australian census data so hopefully you too can experience the joys of basic map making.
The point of this exercise was to help me learn more about making maps and to explore some of the data available from the 2011 census and the datasets that Australian governments have made available on data.gov.au. I wanted to hone my skills in pulling together data from disparate sources, manipulating the data and mapping it.
The first map that I wanted to create would depict the number of school aged children (those in the 6-18 age group) as a proportion of the total population. I wanted to concentrate on the Melbourne metropolitan area and its’ Local Government Areas (LGAs). I needed the following data:
- a shape file that showed LGA boundaries for Melbourne in .kml format;
- the number of children aged 6-18 for each LGA; and
- the total population in each LGA.
Finding the Data
There are two obvious sources for data such as this in Australia. My first point of call was the data.gov.au website. It is worth browsing this website to see the kind of data that is available that you can mashup. I did a search and quickly found the file that I was looking for, Vicmap Lite Local Government, which was created by the Victorian Department of Sustainability and Environment in January 2010. When I downloaded it I found that it was in a .kmz format whereas I needed it in the .kml format. That is not a problem because .kmz is simply a compressed file format that when extracted converts to .kml.
I then turned to the Australian Bureau of Statistics (ABS) website to find the 2011 census data that I would need. However, the data that they had available for download did not show the population by LGA. I also wanted to find the population of school-aged children as well as the total population.
This is where the fabulous ABS facility, TableBuilder, comes to the rescue. There is no charge to register to use the basic TableBuilder. I suggest that you apply for every free thing that is available through TableBuilder. You never know when you are going to want to access it!
The TableBuilder is an intuitive application where you select the data from the census that you want to use and create your own table. As with all new applications you should give yourself time to play around with it to get to know it.
From the TableBuilder I created two tables – one with the total population for each LGA and one with the school-aged population for each LGA. I downloaded the tables I constructed in Table Builder and opened them in Excel. Then I had to do some cleaning up. Google Fusion Tables only recognises one header line so I deleted unwanted header lines and fixed up the titles for the one header line remaining so that the titles were self-explanatory. I deleted column A which did not have any data and moved the caveats and attribution details into another worksheet.
I knew that I would want to merge the tables I was creating together with the shape file I had downloaded. To do this I needed a common column name through which the files could connect. The column that was common to all three files was the column containing the names of the LGAs. In the spreadsheets I made sure that this column had exactly the same name – “LGA_Name’. I then converted the Excel files into a .csv file ready to upload them to Google Fusion Tables.
It is nice to find data in well formatted spreadsheets, but sometimes you need to look further afield. As I was working on this project I decided to include the number of schools in each LGA. This data is not available in the 2011 census and was not available on data.gov.au. A quick Google search led me to a PDF report issued by the Victorian Department of Education and Early Childhood Development titled ‘Summary Statistics Victorian Schools – February 2011‘. It includes a table, ‘Schools and Students by local Government Area and Sector’ (pp. 22-3) which I copied into an Excel spreadsheet. When I copied the data into the spreadsheet the data was split nicely into distinct rows but there was only one column in which all the data for each row was huddled. In order to separate the data into separate columns I used the data/text to column facility in Excel. However, it was trickier to correctly extract the name of the LGAs into the cells in which I wanted to place it, so I had to use the ‘mid’ and ‘search’ functions. I fiddled around with the spreadsheet and produced the desired results in a reasonable time.
The point is that good data can be found in all sorts of places. Don’t restrict yourself to the few sites that tailor themselves specifically to your needs. The data found on the plethora of sites that populate the web is often not formatted in the way you would wish, but it doesn’t take too much work to knock it into shape. Don’t throw caution to the wind either. Always be wary of the provenance of the data, its’ rigor and the date on which it is produced. It would be a shame to waste your time and skills on dodgy data!
While I was downloading the data I created a word document for each data download where I recorded:
- copyright details;
- the author of the data;
- the URL (hopefully I have a permalink);
- the date the data was published; and
- the date of the download
Attribution is very important and I need to be able to tell others where I have found the data.
Google Fusion Tables
It was time to turn to Google Fusion Tables. Firstly you need to login to your Google Drive account. Click the red ‘Create’ button on the top left, then scroll down the list that pops up, click on ‘More’, then click on ‘Fusion Table (experimental). I uploaded my three spreadsheets and the shape file.
Then I realised that I had not checked the shape file to ensure that the name of the column in which it recorded the names of the LGAs was the same as that I had used in the spreadsheets. Sure enough, when I opened the shape file in Google Fusion Tables I found that this column had a different name. That was quickly dealt with by clicking on the down arrow next to the name of the column in Google Fusion Tables, clicking on ‘Change…’ in the drop down box and then changing the name of the column. I then did a series of merges between the spreadsheets and the shape file to created a new merged table..
I needed to add another column in my merged table in order to calculate the proportion of school-age children in the population. This involved constructing a formula from one column of one of the merged spreadsheets with a column from another of the merged spreadsheets. I could only do this calculation in the merged table but the Edit/Add formula column facility in Google Fusion is only available for the original tables that you have uploaded, not for merged tables. So I had to download the merged spreadsheet, manipulate it in Excel and then upload it again to Google Fusion Tables.
I then merged the shape file with the merged spreadsheets. Voila! A tab called ‘Map of geometry’ appeared in the merged file. I clicked on the ‘Map of geometry’ tab and it produced the map. Oh no! The map is all pink!. To rectify this I clicked on the arrow in the ‘Map of geometry’ tab and adjusted the settings in ‘Change map styles’. To get rid of the pink I clicked on ‘Fill color’ under the ‘Polygons’ heading on the left hand side and complete the settings under the ‘Buckets’ tab.
You can have a look at the map I created by clicking here. If you click on one of the shaded areas in the map a pop-up box appears giving information about the particular LGA. Before publishing the map you can determine what appears in the pop-up box by clicking on ‘Change info window layout..’ under the ‘Map of geometry’ tab.
It was at the end of the process that I hit a number of limitations. You get what you pay for. All the services I had used were free and understandably don’t have all the functionality I would like.
The first thing that you will probably notice when you look at my map is that there is no legend and the title is at the top of the screen separate from the actual map. Google Fusion Tables does not provide a facility to add a legend to maps, yet a legend is essential in a map. I did a Google search and found that there are solutions to this issue such as this one from Mu Lin. But then I found another problem.
This blog is hosted through WordPress.com. WordPress.com does not support the embedding of Google fusion maps. If my blog was a WordPress.org site I would be able to adopt the solution Mu Lin suggests. As my blog is a WordPress.com site I needed to find another solution.
After much searching and tearing my hair out I had to resort to converting my map into a jpeg format and using Photoshop to add the legend. The result is below.
I have explained some basic techniques that those who work in digital humanities use: finding data; tidying, manipulating and formatting data; and visualising it. However, digital humanities is not just about using technology. It is about advancing research in the humanities through the use of technology. There are all sorts of interesting questions that maps such as this could help us explore. There is one thing that I am curious about. The LGAs with the highest proportion of school aged children are all in the outlying suburbs of Melbourne except for one. What is different about Bayside? Why does it attract so many families with school aged children? There is a lot more work that needs to be done here.
I have written this post in the hope that it will help people who are just starting out in digital humanities. If you have any questions about what I did feel free to ask me in the comments below. I would also love to hear about your experience in making maps.
The response to this post reinforced to me how the web is about learning and sharing. People are so generous online and the assistance that I received as a result of this post will help me to go on the next step of my mapping journey.
I have told this story in Learning to Make Maps on Storify.