How To Import Data and Make Maps with Google Fusion Tables
Share this article:
Related Content:
This GIS tutorial by Naveen Sidda looks at Google Fusions Tables, how to import geographic data from a web page, and how to create map out geographic data within Google Maps.
What is Google Fusion Tables?
Google Fusion Tables is a data web service which is built on the concept of cloud computing for data integration from various data sources, visualization and collaborative management. Essentially, it stores and computes on the fly without any installation and maintenance of applications on the users machines as the providers manage the infrastructure required to accomplish the task.
Working with Fusion Tables:
Fusion Table (FT) allows the import of various data formats like the comma-separated text (.csv), other text-delimited files (.tsv), KML , Google spreadsheets, and other spreadsheet formats (.xls, ods, etc.). In this exercise, the data is prepared in a Google spreadsheet. There is one advantage to using online spreadsheets especially when we pull data from the websites which is the user gets updated information automatically into the imported spreadsheets without the need to intervene manually to update and check the information. This section will explain how to import data from a web page into Google Fusion Tables.
Preparation of the data
The table in the Google docs below shows the data of top touristic countries along with rankings, statistics on the arrivals and yearly percent change.
Table 1. Top touristic countries
The data and other attribute informations are pulled from the website www.infoplease.com (randomly selected for demonstration purpose) by using below simple formula: =ImportHtml (URL, query, index). For more functions visit https://support.google.com/docs/bin/static.py?hl=en&topic=25273&page=table.cs.
ImportHtml is the functions along with the parameters:webpage URL, format or the query structure-(Table/List) to pull from the URL, position or index of the information located on the web page. For this GIS tutorial, the following line of code is entered as shown in Table 2 below:
=ImportHtml(“http://www.infoplease.com/ipa/A0198352.html”, “Table”, 4)
The above function pulls and dynamically updates all the data into the docs as below in Table 2 that is stored in the Table format from the website.
Table 2: Formula to import the data into online spreadsheets.
One more example, using the same above function shows the recent tourism statistics which have been pulled from the Tourism entry on Wikipedia and imported into Google docs as shown in Table 3, which further can be uploaded into Google Fusion Tables for various computing.
Table 3. Showing data extracted from an entry on Wikipedia that is then saved in an online spreadsheet.
Loading Web Site Data into Google Docs
The data that is pulled from the external web sources (Table 1) to the Google docs is now loaded in Google Fusion tables (Classic view) for further analysis and to add the spatial component. As Fusion Tables allows the import of data from .ods or.xls format, Google spreadsheets and creating an empty table directly in the Fusion Tables as shown in below figure 1.
Figure 1. Fusion table Interface to import data.
Once we upload our spreadsheet as above instructed, we have the below Menu option (figure 2) along with our Table name to proceed further. The below section will start from a very basic operations i.e. Visualizing our data in the form of tables, charts and maps, subsequent sections will brief on the Functionality in the FT.
Figure 2. Menu of Fusion Tables
Visualizing Data in Fusion Tables
Pictorial representations of any data especially the statistical data in the form of charts, tables and maps can make people quickly understand and helps to take apt decision for business making, research studies, policy making etc.
As shown above in the Visualize menu, it provides options to view our data in the form of tables, charts and Maps as below:
Table:
All data tabular data along with column names in the spreadsheet are saved in the FT as shown in the Table 4 below. Like any other databases, FT allows to manipulate the schema (add/delete/modify) without any SQL statements. More on the use of the functionality of FT are explained in the next section. FT allows permitted users to comment on the each entity in the table, view the location on of each entity on maps. This facilities the users at different locations to discuss and plan collectively by the click of a mouse.
Table 4. Fusion Table imported from online spreadsheets.
Charts:
The data that is visualized in the form of a table can be also visualized in the form of various types of charts. The figure below shows pie chart on based on the data Country and Arrivals column. This option is available in the same menu under the Visualize menu. Likewise one can avail the other options like the line, bar, scatter plot, timeline depending on the selected attributes and the analysis required. For example, if a study requires yearly, quarterly, monthly and daily data on an iterative intuitive chart Timeline option will be the best option.
Figure 3. Chart display of the Top tourism table.
As 80 percent data created by organizations has a spatial component and this can be quick and easily comprehended when a geographical element is added to the problem. Spatial background is being added to the non spatial data (the attribute data in the form of tables as shown above) by selecting the Visualize –> Map option from the menu as shown in the figure below.
All the tabular information is shown on a map by a process called Geocoding. The concept behind Geocoding is Fusion tables understands and associates all the names of the countries (Country column in the table) to the ground co-ordinates on a geo referenced map. On the other hand, if the table contains latitude and longitude instead of the textual information, it understands and map all these points to the correct location on map and this process is called Reverse Geocoding. As evident from the map below, it shows all the other information such as Arrivals, Percent change etc by click on the push pins on the map.
Figure 4. Google Map interface to the Top tourism data.
Intensity Map
The map below displays distinctive darker and lighter tone maps based on two parameters: spatial information (in our case, it is the Country column) and numeric information (Arrivals (millions) column). This visual effects gives a very quick insight on the information needed from the data. This option is only available in the Classic version of the FT (This is the reason, why I chose Classic version for the demonstration).
Figure 5: Intensity Map for Top tourism data.
Fusion Tables Functions
Conventional databases work on SQL statements, need to memorize the right syntax to execute the basic operations whereas most of the basic operation can be done graphically especially for novice users. Though FT does not do aid to solve complex problems but FT functionality could be sufficient enough to visualize and understand the common day to day problems.
Filter
This option is used when we would like to filter the data based on the objectives. For example, I would like to see the countries whose touristic Arrivals more than 60 millions for economic study or setting for a business etc. Essentially, using the below SQL statement is used to answer my query as France in databases like the MqSQL etc.
SELECT * FROM Top toursium WHERE Arrival(millions)>= 60
The query can be performed as shown in the below figure by simple selection the options in the drop-down menu and the results can be viewed in any form as discussed in the above section. This could be useful for more complex tables and analysis.
Figure 6. Filter function on Top tourism data.
Aggregate
Aggregate functions: Sum, Average, Maximum, Minimum on the columns of a database allows to do various analytical studies and reports.
SELECT SUM (International tourist arrivals (2011)) FROM Untitled Spreadsheet GROUPBY UNWTO Region
View
The view is used for selective choosing of the columns based on the requirements and saving it as a separate database. This functionality can be used on a large table, multiple tables. In FT it is just done by checking on the selected columns that is necessary for the project objective, without any syntax requirement as below:
CREATE VIEW Trail AS
SELECT Name, Length, End point1 , End point 2 FROM Tourism
Wikipedia (http://en.wikipedia.org/wiki/Long distance_trails_in_the_United_States) has the list of long distance trails in the USA. All the data can be pulled onto spreadsheets and can make a table to query and see all the hiking trails of our interest on the map in a very easy and efficient way.
Figure 7. Fusion Tables map interface display of the longest trails in the USA.
Conclusion
A spatial background to the non spatial data can be a visualize treat for the users to comprehend easy and quickly. This GIS tutorial is intended to aim for the novice users to be able to make use of the power of mapping available existing spatial data using Google Fusion Tables in a simple way without much knowledge of Geoinformatics nor database expertise or programming skills. Depending on the data attributes, requirements, data size and so forth, Fusion Tables can be used for basic mapping needs which can be instrumental as a freely available resource.
Related Google Fusion Tables Resources:
Tags: GIS tutorial, Google Docs, google fusion tables, Google Maps, making maps
