Local Government Citizen Services and BI Reporting

Live Demo

Introduction

With the FME platform, any local government service can be integrated with business information (BI) tools to create powerful data analysis and visualization. The following demo integrates Esri ArcGIS Survey123 and Tableau Public. It could be adapted to work with any citizen submission or reporting workflow to create a data dashboard. Follow this article to get a high-level overview of this integration.
 

Create Reporting Using Survey123

Esri’s ArcGIS Survey123 is an application that allows users to create forms that can include maps. The Lost and Found Pets Reporting form asks for users’ contact information, pet location, and pet details.

Use the CSV files in this demo to list the answer choices for cat breed, dog breed, and color in Survey123.
 

Build a Pet Matching Workspace

This workspace reads the citizen’s submission from Survey123 and looks for a matching found pet, appending information about the match, including the location of the closest animal shelter. (Workspace screen capture is divided into two parts for ease of readability)

Read Reporting Data

Using FME Workbench, we can read in the Survey123 data through a webhook. Any new submissions will be read in as JSON when the workspace is rerun or triggered by the webhook. 

Find out how to do this from the Automating Workflows from Survey123 to ArcGIS using FME Server article.

Filter Data

Depending on the details submitted, we will manipulate the data differently. In this demo, the submission data is filtered by pet type and whether the user wants to receive an email with potential matches for their lost pet.

Find the Nearest Shelter

After reading shelter data in KML format, we can use the NeighborFinder to locate the nearest shelter to the lost or found pets. The NeighborFinder transformer adds shelter attributes to the survey data based on proximity, such as name, address, phone number, and website.

Buffer Lost Pet Locations

This workspace uses a Bufferer transformer to conduct a proximity analysis, identifying all found pets within a certain location of the lost pet.

Read Local Government Data

If there is pre-existing data on found pets, it can be combined along with the survey data. In this demo, the workspace reads existing found pet data from the City of Austin to look for matches. Found pet submissions could also be stored in a database and used to search for matches. The Reprojector transformer is used to make sure the found pet and survey data use the same coordinate system.

Match Pets

The PointOnAreaOverlayer appends the local government found pets to the lost pets' data if they fall within the buffer. 



In the image below, the blue circle is the buffered lost pet, and the four points inside are the matched found pets. 

Check For Matches

Not all lost pets will have found pet matches, but we still want to send out an email to the user. In the Tester transformer, both the passed and failed ports are used.

Extract Matches

The lost pets that have more than one match will be appended to the feature as a list. To separate them, the ListExploder transformer extracts them from the list. 

Get Image Links and Assign No Image Link

Only some of the matched found pets have images that are stored on Amazon AWS with the same name as their animal ID. The HTTPCaller transformer checks which found pets have images. Those that do not have an image obtain a “No Image” value in the Link attribute.

Create an HTML Table

The HTMLReportGenerator has the ability to create an HTML table. This table includes all of the matched found pets' information, along with embedded images if they exist. 

Merge Table with User Information

To send out only one email, the feature containing user information is merged with the HTML table, creating one HTML document to use in the email body.

Write Data as an FME Server Automation

The FME Server Automations writer lets FME read the information from this workspace. There are three writers, one for matches, no matches, and found pets.

Create a Pet Matching Automation

We want the workspace we built to trigger when a survey is submitted and to send out an email to the user. We will create an FME Server Automation that is triggered by a webhook.

Add a Webhook Trigger

The workspace will run with the webhook trigger when a user completes a Survey123 form. 

Add an Action

When the workspace is added as an action, three different output ports appear below. These correspond to the writers in our workspace, holding the information for each email type.

Add an External Action

An email can be sent to the user with the External Action. In this automation, there are three Email External Actions to format the email depending on the workspace outcome. It is possible to structure the email in the workspace, but it would increase the number of transformers.

Create a Dashboard

Visualizing your data can be as easy as creating a dashboard to show your community live changes. 

Build a Google Sheets Workspace

Some BI platforms like Tableau Public can read data using Google Sheets. 

In FME Workbench, local government and survey found pets data are read in to combine the data in one place. After editing the data, it is then written to Google Sheets. 

Automate Google Sheets Writing

To display live data, an automation is set up to run the workspace on FME Server. Since Tableau Public updates daily, the automation is scheduled to match that. 

Create a Dashboard

Using Tableau Public desktop, a dashboard can be created that displays a map, chart, and a graph. It is then published to the web.

Data Attribution

Original Data provided by: Austin Animal Shelter (2021). Austin Animal Center Found Pets Map. City of Austin, Texas Open Data Portal. Data has been modified for this example.