Thursday, July 12, 2012

Reference Map for Fusion Tables

Reference Map for Fusion Tables is a free Web Application Service (WAS) provided by The application enables displaying information stored in Google’s Fusion Tables as a thematic choropleth map. There are a few tricks you will need to know to put it to a good use. This post describes in detail how to configure Version 3 of the application to display custom choropleth maps (the concept of WAS is explained in an earlier post on this blog).

Firstly, some background information. Although Google included simple map configuration functionality within Fusion Tables, it is a manual process allowing formatting only one dataset at a time. If you have multiple columns of data available for mapping in a single table, the task of creating individual thematic maps can be quite a tedious process. Therefore, creating such maps programmatically, via Fusion Tables API, is the only viable option.

My recently released Census 2011 Online Maps application is an example of how to automate choropleth map creation process. There are two separate modules in that application. The first module enables selecting and analysing data stored in Fusion Tables, then formatting it for display as a thematic layer on a Google Map (ie. selecting number of classes, defining range break values and selecting colour scheme for thematic layer). The second module is a free-standing application, built with Google Map API, that displays the information from Fusion Tables as a thematic layer, formatted according to user requirements.

Now specifically about the mapping app. When designing Census 2011 Online Maps application I opted to incorporate Reference Map for Fusion Tables rather than coding mapping functionality from the scratch, but I had to update the application in order to enable proper formatting of numeric data retrieved from Fusion Tables. It also was an opportunity to expand the functionality of the reference map to enable generating thematic choropleth maps with 2, 3, 4 as well as 5 data ranges (five is a limit imposed by Fusion Tables API). All in all, the latest version of Reference Map for Fusion Tables now accepts a number of new parameters which enable quite extensive customisation of thematic layer for presentation on the map.

Below is a full list of accepted parameters and relevant options. Parameters flagged as “minimum” are compulsory in order to display Fusion Table in its “native style” (ie. as styled manually using relevant Fusion Tables functionality). “Optional” parameters can be omitted without degrading presentation of the information on the map. Please note, each parameter must be separated in compiled URL with “&” symbol.

Reference Map for Fusion Tables v3 – Acceptable Parameters:

ftl=  table id number eg: 1234567 [minimum]

q1c=geometry [minimum] - specifies which column contains spatial data

r1= defines the number of ranges for choropleth map: eg. 2, 3, 4 or 5

st1= thematic layer colour scheme selector, eg. b0=blue, pp0=purple, g0=green 2br0= blue-red; it is also possible to specify colours in reverse order as b0r, pp0r, g0r, 2br0r accordingly

qst1= SQL like data ranges specification; must correspond to the number of ranges specified for r1 parameter and be comma separated; column names must be between apostrophes; can  be either in ascending or descending order, eg.: 'Relevant Column Name' > 2030 AND 'Relevant Column Name ' <= 3035,'Relevant Column Name ' >= 0 AND 'Relevant Column Name' <= 2030  

q1=  SQL like WHERE query specification, to limit results to only specific set of data, eg. location: postcode > 1999 AND postcode < 3000  [optional]

n1= map title [optional]

xyz= allows to set map location and zoom level as longitude, latitude and zoom values, eg: 147.971361,-32.801441,6 [optional]

ad= can be used to hide ads on the map when set to 0, default is 1 [optional]

The following 3 parameters go together and are optional; each must contain the same number of variables separated by comma:

iw= allows to specify columns to be used to generate content of information window (“balloon”) that appears on the map when coloured polygons are clicked, eg. PC, D32 - if omitted the content of the window will be as specified manually using relevant Fusion Table functionality [optional]

iwalt= allows to specify alternative names for columns listed in iw parameter, eg. Postcode, Proportions of All Sales; default is column names as named in Fusion Tables [optional]

iwf= allows to specify format of numeric values returned from Fusion Tables, eg. n0, c2, %1, $0, where the first character indicates format option and the second (numeric value) specifies the number of decimal points; eg. if Fusion Tables column contains value 1.548  it will be formatted accordingly as: 1 (ie. “no format” and 0 decimal points),  1.55 (“comma format” for 1,000’s etc. and 2 decimal places), 154.8% (“percent format” with one decimal place) and $1 (“currency format” with 0 decimal places) [optional]. For columns with plain text use t0 code.

This is how fully compiled URL should look like:
&n1=Median total family income weekly
&q1=name > 1999 AND name < 3000
&qst1='B113' > 2030 AND 'B113' <= 3035,'B113' > 1484.5 AND 'B113' <= 2030,'B113' > 1194 AND 'B113' <= 1484.5,'B113' > 986 AND 'B113' <= 1194,'B113' >= 0 AND 'B113' <= 986
&iwalt=Postcode,Median total family income weekly

Finally, a short paragraph about the format of data in Fusion Tables that can be used with the Reference Map. The table must have polygons as a geographic component (eg. postcodes, suburbs, sales territories or other administrative boundaries) and it has to have a secondary descriptive column expressed as a number (eg. postcode number). The attributes to be mapped have to be numbers in order to divide them into meaningful ranges and to assign specific colours to those ranges. Those values could be any numbers representing characteristics of a given area (polygon) such as counts, percentages, densities or similar, or numbers used as “category descriptors” (eg. 1 means “mortgage stress belt”, 2 is “no mortgage burden areas”, etc...).

If it all gets too complicated, I will be happy to assist in setting up your own Fusion Tables account with a custom Reference Map.

1 comment:

LSU Justin Farrow said...

this came in handy today.. thank you for sharing it!