PLEASE NOTE: Google decided it will downturn Fusion Tables in December 2019.
This is a follow-up from my earlier post on using Google Fusion Tables to analyse the embarkation roll of the 114th Bn CEF. Fusion Tables allows you to examine a spreadsheet using some of the apps in the Google Suite of online tools, such as Google Maps. If a column in your spreadsheet contains locations, for example, all of these locations can be mapped. Without really explaining anything, I ended my post with an intensity map showing the countries of birth for the soldiers in the 114th. This post will provide more information for creating an intensity map with Google Fusion Tables to visualize numerical data in relation to geographical categories.
When Fusion Tables maps locations, it marks each location with a feature (a dot). As a result, this is best suited for locations such as addresses which can be pinpointed on a map. Locations covering a greater area, such as a province or country, are marked with a dot in the geographical centre of that area. This makes for bad visualizations because the whole of Canada, for example, is represented by a dot in the north of Saskatchewan. This also makes for bad visualizations because an infinite number of values are represented by on single dot.In the case of the 114th Bn’s embarkation roll, the countries of birth of some 650 soldiers are reduced to five dots, because most of the soldiers were born in either Canada, the US, or the UK. Mapping such values is relatively easy to remedy but it requires importing polygons into fusion tables so that values can be assigned to shapes, rather than just appearing as dots. For this post, I thought it would be interesting to map enlistment rates by province. I filled an Excel spreadsheet with the enlistment rates from each province, based on Brown and Loveridge’s article on recruitment in Canada.
The next step is finding the right set of polygons so that we can assign a numerical value to the shape of a each province. Because we are working in Google, these files will need to be imported as .kml files. Google has made Keyhole Markup Language the standard for geocoding in its mapping apps, so we need to find a .kml file with the shapes of the Canadian provinces. There are a number of places to find .kml files online, but a colleague recommended this website. I found the relevant file containing Canadian provinces and downloaded it in .kmz format. A .kmz file is a compressed .kml file and needs to be unzipped with WinZip or 7zip. Because I was dealing with statistics from the First World War which did not include enlistment rates for the territories or Newfoundland (which was its own dominion at the time) I edited the .kml file in QGIS to exclude these provinces. Once that was done, I opened Fusion Tables and uploaded the .kml file.
So at this point, I had a spreadsheet with the name of the nine provinces and the rates of enlistments open in one Fusion Table and another Fusion Table with a polygon for each province. These two tables need to be merged. To merge tables, simple click on the ‘file’ menu in Fusion Tables and select ‘merge.’ Fusion Tables will prompt you to select the other table with which you would like to merge, then ask you which columns you would like to be matched up between the two tables. Because both tables included the names of the provinces, these two columns should be selected and all the corresponding values in the two tables will be matched accordingly.
Once this is selected, click ‘next,’ follow the prompts, and the two tables will be merged into one. The new Fusion Table will have a tab ready for ‘Map Geometry,’ and clicking on it will show that the polygons are indeed in this table, but they appear in a uniform red colour without any of the numerical values assigned. To assign values, click on ‘Change Feature Style’ on the left-hand menu, select ‘Fill Colour’ on the left-hand menu of the new window, and ‘Gradient’ in the menu at the top of the window, then select ‘Show a gradient.’ Below ‘Show a gradient’ is a drop-down menu to select which column should be represented in the polygons, and below that is a prompt for ‘use this range’ that automatically assigns the lowest and highest values in the column to the lightest and darkest shades of the gradient.
In this case, Fusion Tales will assign values based on the column ‘Enlisted,’ which contains the total number of men who enlisted from each province over the duration of the war.
Ontario appears as the darkest shade of green, with the total enlistments from Ontario (242,655), being more than double the next province (88,052 enlistments in Quebec). The trouble is that Ontario’s adult male population was not more than double that of Quebec, so clearly we need to look at enlistments as a percentage of the total adult male population. Luckily, there is a column with these values, so it is just a matter of clicking on ‘Change feature style’ and selecting ‘percentage’ from the drop-down menu. It is important to remember to click on ‘use this range’ again to ensure that the values from the new column are matched to the appropriate shades of green. The result provides a much different picture.
When we map enlistments as a percentage of the total population, Manitoba is revealed to have the highest proportion of its adult male population that enlisted. It is difficult to see which province follows Manitoba, with British Columbia, Alberta, Ontario, New Brunswick, and Nova Scotia all appearing as relatively similar shades of green, but it is clear that Saskatchewan, Quebec, and Prince Edward Island (if you look closely) are a shade lighter. While this map does not give specific numbers, it illustrates very concisely the disparities between enlistments by province. While this is of little help in analysing the statistics, it certainly creates a more pleasing visualization than a table or even a pie chart or bar graph.
Certainly, Fusion Tables provides an easy tool to create visualizations of geospatial data. There are still limitations. The most challenging limitation is the need to find .kml files for the appropriate polygons. These files are usually produced with contemporary boundaries in mind and might not reflect the historical boundaries of smaller geographical categories, such as counties or electoral districts, which are redrawn fairly regularly. Changing or altering polygons is possible in QGIS, and I imagine it there must be a way to edit these in Google Earth.
In any case, that’s how to map intensity in Fusion Tables.