Fusion your Data and Map using Google Fusion Tables
Lot of us use Google or other Map implementation for either finding a location, getting directions from one place to another or similar other purposes quite regularly. On a different note but very similar in terms of frequency, we often work with data, in-fact lots of data in our day-to-day work and would like to share and present it to people. The data is generally available to us in form of XLS spreadsheets, CSV files and back-end data-bases. Presentation of this data to a varied group of people may become tedious who may want to look and analyze it in different ways. Would it not be nice, if there is a way to store and present our data online in different formats. Even better (provided the data has geographical association), if we can over-lay it on a Map.
Google has come up with a solution to these issues in form of Google Fusion Tables. Here are some of its offerings:
- Upload location aware data on Fusion Tables and publish on Google Maps
- Represent in different Chart forms (Bar, Line etc)
- Represent in form of Motion and TimeLines
- Represent in form of HeatMap and Intensity Maps
Hello World on Google Fusion Tables Let’s start with a simple scenario where a Company is having multiple offices around the World, with information about the employee strength and revenue details from each of these regional offices. We would like this information to be displayed on Google Map.
Preparing the Data Typically this information is available in the form of XLS/CSV files. Let’s look at the sample contents for the purpose of this tutorial:
|City||Business Divisions||Employees||Revenue in Millions (USD)|
|New York||Additives & Modifiers, Chemicals||25000||10000|
|San Francisco||Lubricants, Consulting Services||24000||9000|
For your testing purpose, you can download the complete file from here.
Uploading the Data For uploading the content to Fusion Tables, one must have a Google account. After signing in to Google, we need to go to Fusion Tables Upload page. On the top left corner, we will see a button New Table with 2 options : Import Table and New Empty Table. In our scenario, we will click on Import Table option which will take us to File Upload option from either A file on our Computer or Google SpreadSheet. Again keeping things simple, we will upload the file we have created in previous step.
Here we will be presented with some information about how Fusion Tables is going to handle our table information. Fusion Tables will automatically try to assign column names from the first row of the table. Another interesting point to be noted here is that we can choose a sub-set of columns only to be uploaded to Fusion Tables. This option can be helpful when we are having a table with lot of data but we would like to share only a sub-set of this information with our audience. Again, we can go to the next step with default options. Next window allows us to modify newly created table properties. Couple of pointers for information here:
- Table Name By default the newly created table gets the name of the uploaded file. But we can modify it for a more descriptive name here. You can of-course change the name later as well.
- Allow Export As Fusion Tables is Data Storage on Cloud, it gives us the option and control for sharing our data with outside world. If we allow our table to be exportable, then others can download a copy of the data and use it for their own purposes. There are other sharing options as well which we will discuss later. For the time being, we can leave with default settings
Once we click next, we see our data in the tabular format. We can do multiple things here :
- Sort the contents on different columns
- Modify the contents of a particular column
- Remove one / multiple rows
- Filter results based upon SQL like queries
I have pointed some of these things in the attached Screen shot.
Presenting the Data on Map Now we come to our main goal of the exercise. For that we need to click on Visualize –> Map option in the menu bar. I mentioned in the beginning that we need to have location aware data to be displayed on a Google Map. In normal scenarios, we would provide Latitude & Longitude information about each row in our table for Fusion Tables to plot them appropriately on the map. But for our simplistic example, we have mentioned names of the well-known cities in the World. Fusion Tables takes “City” column in our table for location and automatically geo-codes them for getting corresponding Latitude and Longitude information before displaying them on World Map. So if everything works out well, we should see marker points at each of the cities in our table on a Google Map. Generating a publicly available map from our data is all this simple :-).
A sample generated map can be accessed here. If you click on any of the marker points in the map, you will be getting information from the corresponding row in the table. You can customize the same through Configure Info Window link. You can also choose which type of markers, you would like to be displayed by going through the options in Configure Styles Window
Sharing Map It’s again very easy. Click on Get embeddable link and you will get an HTML code snippet which you can use in any HTML page, insert it in your web-site or blog and it will be accessible to all the people. Like many of other Google’s Cloud based collaboration product suites Fusion tables also allows you to share and control access to your tables and thus corresponding maps with outside world. If you click on Share button on the top right side corner of the table browser window, you will see a list of sharing options:
- Invite Options You can invite people to view/edit table data. For people who have worked on Google docs, this feature may sound very familiar where a group of people can collaborate on a Table in different ways.
- Visibility Options This is related with who all will be able to view the Map generated through underlying Table. As you can make out from the options, if you want to publish map on a publicly accessible location e.g. Web-site or blog, then you need to make your table Public or Unlisted. There might be scenarios where you are uploading a confidential information on Fusion Tables and would prefer an authenticated access, you can do so by making the Table private. In that case, users need to sign-in to access your Map. In specific scenarios, you would like the underlying table data to be private but still be able to generate publicly accessible Maps. For that purpose, you need to be a Google Maps Premier customer (a paid service) and then achieve this using Fusion Tables protection Layer.
Customizing Map Presentation Now the basic Map is done, let’s go a bit further and try to change its Look & Feel a bit. We would like to present the markers on the map with different colors based upon the revenues of the offices. This can be done through following simple steps:
- When you are visualizing the table as Map, Click on “Configure Styles” link
- Go to Pointers –> Marker Icons
- On the right side menu, click on Buckets Tab
- Select Radio button “Divide into Buckets”
- Choose 4 Buckets and choose “Revenue in Millions” Column. Here, we are telling Fusion Tables to use the values in this column to apply our styles.
- In the final value at the bottom, put 10000. Here, we are putting the highest revenue in any of the business units and rest of the categories get automatically updated.
- We can now choose marker colors for each of the revenue brackets
- Clicking on save will automatically apply these settings on our Map
You can have a look at the modified map here
That brings us to an end of our Getting Started with Fusion Tables. We have used Fusion Tables in Industrial Projects in last couple of months. Though it’s still in beta stage and so obviously has some rough edges but we were quite happy with the approach and usability in different scenarios. In my opinion, here are some of the advantages of Fusion Tables:
- Ease in Getting started and presenting information in variety of formats
- Little or no programming effort required if we are having simple use cases
- Access of latest data to all concerned people
- Ease of sharing and collaboration on the Underlying Data. We can work in team and also use other’s data (Merging and creating views)
- Querying and Filtering possibilities
Furthermore if you want to achieve fine-grained control and interactivity, we have Fusion Tables API at our disposal as well which allows us to programmatically manage the underlying data, styling and all other details on the map. More on that in a separate post. Till then, happy fusionning
PS : For all curious people, who are wondering why I didn’t embed my generated Fusion Table Maps in this blog-post as we can do for any Google Maps. Unfortunately it’s not yet possible in WordPress, though they are working on it. You can get more information here.