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

Liz Sanderson
Liz Sanderson
  • Updated

FME Version

  • FME 2021.1

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 contained in a CSV file (comma-separated values). 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

InputShape.png

 

CSV - Vancouver2014ElectionResults.csv

InputCSV.png

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 add the Vancouver2014ElectionResults.csv, the default parameters are ok. This CSV file contains the election results data to populate the pie charts.
CSVReader.png
 
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. 
AttributeExploder.png
 
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.
Tester.png
 
4. Create the Attribute for the HTML
Following the documentation for Google’s Chart API, the data must be transformed into [‘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)]

AttributeCreator.png
 
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.
Aggregator.png
 
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 power of Google Maps API, so we will write out 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 _html3 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>

 AttributeCreator2.png

 
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, then next to each set the String Value to _html1, _html2, and _html3, respectively.
StringConcat.png
 

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 popup 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 is of municipal voting boundaries in the City of Vancouver which provides the coordinates for placing the pins which will contain the pie charts.
ShapefileReader.png
 
9. Create a Larger Polygon of Voting Boundaries
Because the election data from the CSV is organized into voting districts, the Dissolver transformer is used to create one larger polygon for each of the many voting boundaries contained in the shapefile – this ensures only one pie chart for each district is created. 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.
Dissolver.png
 
10. Create a Center Point Inside Each Polygon
We need to create a center point inside each of the voting boundary polygons for a pin to be placed so that an information window will pop up containing the chart. 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.
CoordinateExtractor.png
 
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.
FeatureMergerConnections.png

In the Parameters, set the Requestor to LOCAL_AREA and the Supplier to LOCATION. Then Set the Feature Merge Type to Attributes and Geometry.
FeatureMerger.png
 
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.
Reprojector.png
 

Part 3: Setup 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 the placemark pins and insert the pie chart into the information balloon, visible when the pin is clicked.
KMLPropSetter.png

15. Create Background Polygons
To create background polygons of the voting districts, add an AttributeKeeper transformer to the Area Output port on the Dissolver.
AttributeKeeperConnection.png

In the parameters, set the Attributes to Keep to LOCAL_AREA. 
AttributeKeeper.png
 
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.
PropSetter2.png

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. For this example, the background color of the district boundaries is yellow, and the pins are set to black.
KMLStylers.png
 
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. 
KMLWriter.png
 
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.
WriterConnections.png
 
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. 
Output.png

 

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.

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.