Using Google's Chart API to Create Pie Charts from a CSV and Shapefile

Files

UsingGoogleChartAPI.zip
- 300 KB
- Download

Introduction

Charts can be generated dynamically using FME and Google’s Chart API. In the following example, coordinate data is read from an Esri Shapefile and joined to election results in a CSV (comma-separated values) file. A Pie Chart is generated using HTML code, and the results are exported to KML (viewable in Google Earth).

Source Data

Esri Shapefile - voting_boundary.shp

CSV - Vancouver2014ElectionResults.csv

Step-by-step Instructions

Part 1: Read in CSV data and prepare the data for the chart

1. Read Source CSV Data

In a blank workspace, add a CSV Reader and load Vancouver2014ElectionResults.csv. The default parameters are ok. This CSV file contains the election results data to populate the pie charts.

2. Explode Attributes

Notice in the source data that all the voting districts are stored as column headers. The best practice when designing databases is to store values that share a common theme (e.g., location and vote count) in separate tables – or for FME, as separate attributes. To do this, we will use an AttributeExploder transformer. In the parameters, set the Attribute Name Label to LOCATION and the Attribute Value Label to VOTE_COUNT. We still want to keep all our attributes after we’ve made new ones, so set Keep Attributes to Yes.

3. Remove FME Format Attributes

Behind the scenes, FME creates attributes for certain properties; usually, these attributes are ignored and don’t affect the final output. Viewing the output from the AttributeExploder, you can see that there are various features that were added in the VOTE_COUNT column, like CSV2_1 and fme_no_geom. Using a Tester transformer, set the Left Value to VOTE_COUNT, the Operator to Contains Regex, and then set the Right Value to [0-9]. This Regular Expression checks the data to ensure it is numeric.

4. Create the Attribute for the HTML

According to the documentation for Google’s Chart API, the data must be transformed into the [‘Data’, Value] format using the AttributeCreator. Connect an AttributeCreator transformer to the Passed Output port on the Tester. In the parameters, create a new attribute called _format_candidate and set the Value to: ['@Value(CANDIDATE)',@Value(VOTE_COUNT)]

5. Aggregate Attributes by Location

The goal is to have a pie chart for each voting district in Vancouver, so we will need to aggregate our _format_candidate by LOCATION. Add an Aggregator transformer to the canvas, enable Group Processing, then set the Group By to LOCATION, and the Mode to Attributes Only. Set Keep Input Attributes to Yes and set the Attributes to Concatenate to _format_candidate.

6. Create Attributes to Contain the HTML for the Pie Chart

Now we’ll create the pie chart, this can also be done using the ChartGenerator transformer, if you would like to use that method you can check out the Creating Charts with FME tutorial. For this example, we want to leverage the Google Maps API, so we will write our own HTML and JavaScript. Using an AttributeCreator, create three attributes, _html1, _html2, and _html3.

For _html1, enter the following as the value:

<head>  
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>;  
    <script type="text/javascript">  
        google.load("visualization", "1", {packages:["corechart"]});  
        google.setOnLoadCallback(drawChart);  
        function drawChart() 
        {  <!-- Define the Label and Data titles -->  
            var data = google.visualization.arrayToDataTable([  ['Candidate', 'Votes'],

For _html2, set the Value to the _format_candiadate attribute we created with the Aggregator

For _html,3 enter the following as the value:

<!-- Close data tag -->]);

<!-- Set the chart type to Pie Chart --> 
    var chart = new google.visualization.PieChart(document.getElementById('piechart'));   
    var options = {  
    <!-- Set Chart Title -->  
        title: 'Vancouver 2014 Municipal Election',  
    <!-- Generates 3D Pie Charts -->  
        is3D: true,  
    <!-- Aggregates Candidates with less than 10% total votes into 'Other' category -->  
        sliceVisibilityThreshold: .1,  
    <!-- Set Pie Chart size -->  
        'width':500,  'height':300  };  
        chart.draw(data, options);}
</script>
</head>
<body>  
<!-- Set window size -->  
<div id="piechart" style="width: 450px; height: 300px;"></div>  
</body>

7. Concatenate _html Attributes to Form One Attribute

Now that we have created our HTML attributes, we need to concatenate them into one attribute to read into our chart. With the StringConcatenator, name the New Attribute _html. Then, for the String Parts, set the String Type to Attribute Value three times, and next to each set the String Value to _html1, _html2, and _html3, respectively.

Part 2: Create Pins for the Map

8. Read in the voting_boundary Shapefile

Now we need to create the pins for our map to contain the pie chart pop-up when clicked. Add an Ersi Shapefile reader to the canvas and read in the voting_boundary.shp, the default parameters are ok. The polygon shapefile contains the municipal voting boundaries in the City of Vancouver and provides the coordinates for placing the pins that will contain the pie charts.

9. Create a Larger Polygon of Voting Boundaries

Because the election data in the CSV is organized by voting district, the Dissolver transformer is used to create a single larger polygon for each of the many voting boundaries in the shapefile – this ensures only one pie chart is created for each district. Add a Dissolver to the canvas, and connect it to the voting_boundary reader feature type. In the parameters, enable Group Processing, then set the Group By to LOCAL_AREA. Next, set the Accumulation Mode to Merge Incoming Attributes.

10. Create a Center Point Inside Each Polygon

We need to create a center point within each voting boundary polygon for a pin, so that an information window containing the chart pops up. Add a CenterPointReplacer to the canvas and connect it to the Area Output port on the Dissolver.

11. Extract the Coordinates to Store as Attributes

Now that a center point has been established, we need to extract its coordinates to create latitude and longitude attributes. Add a CoordinateExtractor transformer, and connect it to the Point output port on the CenterPointReplacer. In the parameters, set the Mode to Specify Coordinate, then change the X Attribute to LONGITUDE and the Y Attribute to LATITUDE.

12. Merge CSV Data and Center Points

Now that the HTML pie charts and center points have been created, we need to merge them together. Add a FeatureMerger to the canvas, connect the Output port on the CoordinateExtractor to the Requester Input port, and the Output port on the StringConcatenator to the Supplier Input port.

In the Parameters, set the Requestor to LOCAL_AREA and the Supplier to LOCATION. Then, Set the Feature Merge Type to Attributes and Geometry.

13. Reproject the Data

The data needs to be reprojected to a Google Earth-compatible format before we continue. Add a Reprojector to the canvas and connect it to the Merged output port on the FeatureMerger. In the parameters, set the Destination Coordinate System to LL-WGS84.

Part 3: Set up the KML Formatting

14. Create Placemark Pins and Insert the Pie Chart

Connect a KMLPropertySetter transformer to the Reprojected output port on the Reprojector. In the parameters, set the Name to LOCAL_AREA and then set the summary to:

Vancouver 2014 Election Results for @Value(LOCATION) District

For Description Balloon, set the Content Type to HTML and the Content to _html. Then set Include Attribute Table to No. This will create placemark pins and insert a pie chart into the information balloon, which is visible when the pin is clicked.

15. Create Background Polygons

To create background polygons of the voting districts, add an AttributeKeeper transformer to the Area Output port on the Dissolver.

In the parameters, set the Attributes to Keep to LOCAL_AREA.

Next, connect another KMLPropertySetter to the AttributeKeeper output port. Then, in the parameters, set the Name to LOCAL_AREA and the Summary to: @Value(LOCAL_AREA) Voting District

Then, for the Description Balloon, set the Content Type to Text and the Content to Voting District. Set the Include Attribute Table to No.

16. (Optional) Style Pins and Polygons.

If you would like to change the style of your Pins and Voting District polygons, you can add a KMLStyler to each KMLPropertySetter and change the parameters to your style of choice. In this example, the district boundaries are yellow, and the pins are black.

17. Write to OGC/Google Earth KML

Add an OGC/Google KML writer to the canvas, set the Feature Type Definition to Automatic, then click OK. Change the Feature Type Name to Elections and click OK to finish adding the writer.

Connect the Writer to the Output ports of both the KMLPropertySetters, OR if you styled your data with the KMLStyler, connect the writer to the Output ports on the KMLStylers instead.

18. View the Output in Google Earth

Run the workspace, then open up the Elections.kml in Google Earth to view the results. Click on any of the pins to see the pie charts containing 2014 election data. See Updating KML Balloon Contents & Removing "Directions" for information on how to remove the Directions at the bottom of the chart.

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.