Join CSV File to a Database Table with the DatabaseJoiner

Introduction

Here's how to join or merge two sets of data together by a common field -- no coding or scripting necessary. FME's DatabaseJoiner transformer can be used with many of the formats supported by FME. This demo will join CSV and database data based on address values. You'll see how to set up the data for joining using additional transformers, plus get tips for maximizing performance.

The goal is to match CSV data of crime information to a database table of road information so that we can determine which roads show the most criminal activity. The DatabaseJoiner will be used to perform this task, and the output is written to Excel.

Video

Note: This video uses FME 2016, the interface might vary. Here's how to join or merge two sets of data together by a common field -- no coding or scripting necessary. FME's DatabaseJoiner transformer can be used with many of the formats supported by FME. This demo will join CSV and datbase data based on address values. You'll see how to set up the data for joining using additional transformers, plus get tips for maximizing performance.

Step-by-step Instructions

A starting workspace has been provided which reads the CSV data, and formats the Block attribute so it matches the formatting of the HBlock attribute in the Roads table.
 

1. Open starting workspace.

Open StartingWorkspace.fmw, In this workspace, a StringReplacer has been used to replace XX in the Block attribute with 00. A StringCaseChanger changes the Block attribute from uppercase to mixed case.

Starting workspace

2. Add a DatabaseJoiner.

Connect it to the StringCaseChanger. Open the properties of the DatabaseJoiner and connect to the database.

PostGIS connection details:

• Host: postgis.train.safe.com
• Port: 5432
• Database: fmedata
• Username: fmedata
• Password: fmedata
• Table: public

Select the Roads table in the public table

Select the feature attribute from the incoming CSV features, Block, and the corresponding PostGIS table attribute, HBlock, that will be used to find matches. Matches are made when the values of the CSV attributes equal the values of their corresponding PostGIS table fields.

For Fields to Add, select Type.

Open the Optimize panel on the DatabaseJoiner: Add a Prefetch Query to load all of the records from the database table into a

select * from public."Roads"

Because of the prefetch query, the DatabaseJoiner is not required to read from the database for each record. This can improve the performance, and reduce the number of queries back to the database.

Use a DatabaseJoiner and join Block with HBlock. Then Set the Prefect Query to select * from public."Roads"

3. Add an Inspector

Connect and Inspector to the DatabaseJoiner to confirm that the road type attribute has been added.

Output of the dataset with the joined fields in the Data Inspector

Optional

An AttributeFilter can be added to filter by the "type" attribute, and so on.

Or, an Excel writer could be added after the DatabaseJoiner, with the Sheet Name set up to get its value from the "type" attribute. This would result in an Excel file listing crimes with a tab for each road type.

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.