How to Write Spatial Data into Tableau with FME

Introduction

In this exercise, we will work through setting up a simple FME Workspace to convert a MapInfo dataset containing Parks information to Tableau's Hyper format.

Step-by-Step Instructions

Part 1: Simple Format Conversion

1. Generate a simple FME Workspace

Start FME Workbench and click on the Generate option from the Start page.

Set up the Reader and Writer as follows:

Click OK to generate the workspace.

Generate workspace with an MITAB Reader and a Tableau Hyper Writer

2. Inspect data

It's probably a good idea to know what we are working with here to know what to do with our data. We could install MapInfo (a GIS application) on our machines and open the file directly in it, but all we really want to do is inspect our data source here. Let's use feature caching to look at this MapInfo dataset. To do this click on the "Parks" Feature Type in the workspace. Then click the run to this play button and click on the green feature cache to inspect the dataset.

Inspect the Parks Reader

The Visual Preview window will open and show us the geometries and data inside the file we are working with.

3. Clean up the workspace

Since we only have area features in our data source, let's remove the GeometryFilter and three non-polygon feature types that were automatically added to our workspace.

• Delete the Geometry Filter transformer

4. Modify the schema on the Tableau writer

Our writer is currently set up to create a file called Parks_polygon, let's change that to something nicer. Click on the cog-wheel icon (or just double click) on the writer feature type to open the properties dialog. Change the Table name parameter. Let's set it to just 'Parks' instead.

Workspace cleaned up with Reader and Writer renamed

5. Save the Workspace

Click File > Save As to save a copy of the workflow we have built so far.

6. Run the Workspace

Click the green play button in the Workbench toolbar to run our data conversion to actually create our Hyper file from the MapInfo dataset.

Part 2: Spatial Data Blending

We have been given a CSV file containing information about the locations of drinking fountains in the city and would like to find out how many fountains are in each park. The drinking fountain dataset does have a Location column, but on closer inspection, the location names there don't match up with the park names from our MapInfo dataset. The CSV file does have x, y coordinates in it, maybe we can join these datasets spatially.

1. Add CSV Reader into our workspace

Click on Readers > Add Reader and set the following:

Now we will setup the parameters for the Reader. Click on Parameters... For Feature Type Name(s) select From File Name(s).If you are using FME 2020 or newer, confirm that the Attribute Definition has x and y, set to x_coord and y_coord, respectively. By default, FME will map X/Y or Latitude/Longitude attributes to the x_coord and y_coord data types. If you are using FME 2019.2 or older, set the attribute definition to Manual and manually set the data type accordingly. This will tell FME to display each row in the CSV as a point - for more information, see Using the Reader parameters in the Converting from CSV Data article. This will tell FME to display each row in the CSV as a point. Click Ok. Then set the Coord. System to UTM83-10. Click Ok to add the new reader to the workspace

Add a CSV Reader to the workspace, change Coord. System to UTM83-10

In the parameters of the CSV Reader, confirm the x_coord and y_coord attributes use the x_coordinate and y_coordinate data type and manually adjust if needed.

2. Inspect the two datasets

Disable the Tableau writer feature type for now by locating it on the canvas, right-clicking on it, and selecting "Disable". Connect an Inspector transformer to the output ports on each of the readers inside the workspace. Click Run to see both datasets inside the Data Inspector Application.

Workspace with the inspectors and the Parks Writer disabled

3. Use FME to spatially relate the two datasets

Let's use a transformer to find the water fountain points that overlap with the park areas.

Add the PointOnAreaOverlayer transformer into the workspace.

Remove Inspectors from previous step and connect the Parks feature type to the Area input port and the CSV feature type to the Point input port. Then connect the Area output port to the Hyper writer.

Access the PointOnAreaOverlayer transformer Parameters. It's currently set to create an attribute called _overlaps with a count of the number of features that overlap with each other. We'll leave that as it is for now. Click OK to close the parameters dialog.

4. Modify the writer feature type properties

We want to add a new column to the Hyper file we create with the count of the number of fountains that were found inside each park. Open the writer feature type properties dialog. Then open the User Attributes tab to look at the columns that are currently defined on the writer.

Add the _overlaps attribute to the list of columns there. You can do this either by switching the Attribute Definition to Automatic (to automatically pick up any new attributes added to the workspace) or by setting the Attribute Definition to Manual and manually typing in the name of the attribute we want to add.

User Attributes in the Writer parameters, add the attribute _overlaps

Next, go to the Parameters table, under Table > General change Table Handling: to Drop and Create. This will tell FME to delete the existing Hyper file and re-create it with the new column added.

Writer parameters, change the Table Handling to Drop and Create

5. Save and Run the Workspace

Enable the Tableau writer feature type by locating it on the canvas, right-clicking on it, and selecting 'Enable'. Save the modified workspace, then Run it.

6. Optional: Inspect the data in Tableau

Open up your data in Tableau and experiment with how you would like to display your data.

Parks.hyper opened in Tableau. Each circle is a single park. Stanley Park has the most drinking fountains, with 29.