Relating Data Files and Fields

Introduction

Relating database tables in FME is fast and easy using the FeatureJoiner transformer. The FeatureJoiner combines the attributes of features based on common attribute values, similar to a SQL join operation. In this tutorial, you will learn how to join features from two database tables based on a primary key, but this method can be modified to combine features from multiple data types (for example, shapefile and database table).
 

Step-by-step Instructions

In this exercise, you will learn how to join two tables based on a primary key. In this instance, you are interested in joining two tables based on the Address ID because you want to have a dataset with complete mailing addresses.

Sources

The first source dataset is the AddressPoints table from the Vancouver SpatiaLite database and contains address points in the City of Vancouver. 


The second source is from the same SpatiaLite database and contains data about homes in the City of Vancouver. 

1. Create a New Workspace
Open FME Workbench and create a blank workspace. 

 
2. Add a SpatiaLite Reader
Add a SpatiaLite reader to the canvas by clicking on the Reader button on the top menu bar or by going to Readers > Add Reader. In the Add Reader dialog, select SpatiaLite as the Format, then for Dataset browse to the Vancouver.sqlite dataset which is available for download from the Files section on this article. Then open the parameters. 

 
In the parameters, click on the ellipsis next to Tables, then in the Select Tables dialog, select both the AddressPoints and VanHomes tables. Click OK three times to add the reader to the canvas. 


3. Inspect Source Data
Before we continue, we should inspect the structure of the data we are looking to join together. Click on the AddressPoints reader feature type to open the popup menu, then click on the View Source Data button. Then after inspecting AddressPoints, inspect VanHomes. 


Notice that the AddressPoints attributes (columns) are in TitleCase, but the VanHomes attributes are in lowercase. To join this data correctly, we need to change the AddressPoints to lowercase. 


4. Change AddressPoints to Lowercase 
Click on the AddressPoints reader feature type to select it.  Then add a BulkAttributeRenamer to the canvas by typing “BulkAttributeRenamer” to bring up the list of FME Transformers in the Quick Add Search. Select the BulkAttributeRenamer from the list of Transformers by double-clicking or by using the arrow keys and the Enter key to add it. 


 
Double-click on the BulkAttributeRenamer to open the parameters. In the parameters, change the Action to Case Change, then set the Case Change Type to lowercase. 

 
5. Join Features
Add a FeatureJoiner transformer to the canvas. Connect the Left input port to the BulkAttributeRenamer, then connect the Right input port to the VanHomes reader feature type. 


Since we are performing an inner join, the position (left and right) of the inputs is not important since the output will only contain matched features; however, if we were performing a left join (matched features and all unmatched left features) the position is important otherwise you will have an unexpected result. For a more detailed explanation of how to use joining transformers, see Merging or Joining Spreadsheet or Database Data.
 
In the FeatureJoiner parameters, confirm that the Join Mode is set to Inner. Then set both the Left and Right to addressid. 

 
6. Add a SpatiaLite Table
Now we want to create a new table in the Vancouver.sqlite database. Add a SpatiaLite writer to the canvas, the same way a reader is added. Then for Dataset, browse to the same Vancouver.sqlite database we read from. Change the Table Definition to Automatic, then click OK. 


In the Writer Feature Type dialog, set the Table Name to JoinedAddresses then set the Geometry to db_point, since the data we read in was also point data. Click OK to finish adding the writer. 

 
7. Run the Workspace
Connect the JoinedAddresses writer feature type to the FeatureJoiner Joined output port.  


Run the workspace by clicking on the Run button on the top toolbar, or by using Run > Run Workspace on the top menu bar. 

 
After running the workspace, the features will be joined based on the addressid primary key.  You can view the table by selecting the writer feature type to open the popup menu and clicking on the View Written Data button.  

 
In Visual Preview, you can see the two tables joined. In the screenshot below, the AddressPoints data is highlighted in orange, and the VanHomes data is in blue. 

Data Attribution

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