CA1 – Data merge in Google Fusion Tables and graphical representation in Google Maps

fusion_topsThis post demonstrates functionality of Google Fusion Tables in conjunction with Google Maps on example of Irish population by county heat map.

— Fusion Tables are experimental, SaaS product to gather, merge and share tables to enable visualization of data stored in them. It does not support calculations – use Google Sheets for these. —

So this and many other maps can be easily created by uploading correctly structured data in Fusion Tables.                                                       Everyone with Google account can upload data, create their visualization, never look at this again and add their bit to cyber landfill in the world wide web.

To create graphical representation of each county population we would need information on the population as well as data on countries borders – KML file. KLM stands for Keyhole Markup Language. KLM (or KMZ) files contain list of coordinates readable by Google Maps and most data analytic applications offering geoanalytic function. They are widely available on the internet.

First one and many other data sets can be easily found on the Central Statistics Office page. Get KML file here.

To merge the information from both files (population- county-county location) we have to upload data to Fusion Tables and merge it on the unique identifier occurring in both sets – county name in this case. Just choose “New” and then “Fusion Table” on the Drive.fusion1

With all necessary data uploaded into the cloud, it can be merged now.fusion2

Popup wizard will ask us in which column are our identifiers, then it will ask which columns from original files we want to keep in the new one. It is up to creator how much information they want their new file to contain. For this map it is essential there is county name, population and geographical information.                                                                         After choosing desired columns and hitting next button, the merged file is created and default map visualization can be found by clicking on “Map of geometry” tab.                                                                                  Colours, buckets, transparency, legend can be adjusted in “Change feature styles” option. Or left to Google’s calculations.fusion3

Final result can be shared by clicking on the arrow on the “Map of geometry” tab, choosing “Publish” and option how we want to share it. It can be embedded and interactive on the website as below:

At first glance it is easy to spot that counties Dublin, Galway, Cork have over 550 000 inhabitants each with Dublin fitting into 1 300 000 to over 2 500 000 citizens bucket.

Some of the least populated counties are Leitrim, Monaghan or Longford. Reasons for that could be: no 3rd level education establishments in the area, so young people emigrate beside county borders, distance to big cities and UK market, poor road infrastructure or mountainous terrain and barren soil and no jobs opportunities.

To practice use of fusion tables and google maps related features I decided to see if there is any clear visual correlation between the amount of Starbucks and left wing/liberal party “Razem” voters per province in Poland basing on data a friend of mine posted recently on Facebook.

This map shows amount of Starbucks coffee shops per province in Poland:

mapa-starbucks

While this one shows % of voters of “Razem” left wing, liberal party

mapa-support

The slight correlation is noticeable: The biggest amount of coffee shops and liberal voters are in 3 provinces: Pomorskie, Dolnoslaskie and Mazowieckie. All 3 have in common big cities with major polish universities and cultural centers.                                                       Starbucks company is fairly successful in creating their look of hip, cool place, luring people inside to spend their money on overpriced burnt coffee in aesthetically pleasing cup. An equivalent of pop music for not demanding listeners.  Their clientele are students and young professionals.                                                                                            Social -liberal (usually) young voters believe in democracy and globalization yet are anti privatization and pro local markets protection (IMHO, supporting a foreign company in smothering local business is not very social democratic). Demand strengthening of workers rights and fair redistribution of income, support rights of  ethnic and sexual orientation minorities, yet are wearing t shirts with Ernesto G. commonly know as Che.

Leave a Reply

Your email address will not be published. Required fields are marked *