Files
Introduction
Relating database tables in FME is fast and easy using the FeatureJoiner transformer. The FeatureJoiner combines features based on common attribute values, similar to a SQL join. 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).
Sources
The first source dataset is the AddressPoints table in the Vancouver SpatiaLite database, which 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.
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 on the Address ID to create a dataset with complete mailing addresses.
To follow along with the tutorial, please download the data from the Files section of this article.
1. Create a New Workspace
Open FME Workbench and create a blank workspace.
2. Add a SpatiaLite Reader
Add a 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, set the following:
- Format: SpatiaLite
-
Dataset: Vancouver.sqlite
- Click on the ellipses to navigate to the file location on your computer
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 intend to join. Click on the AddressPoints reader feature type to open the mini toolbar, 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, set the following:
- Action: Change Case
- Case Change Type: lowercase
Click OK.
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. Set the following in the Add Writer dialog:
- Format: SpatiaLite
-
Dataset: Vancouver.sqlite
- Click on the ellipses to navigate to a location on your computer
- Table Definition: Automatic
Change the Table Definition to Click OK.
In the Writer Feature Type dialog, set the following:
- Table Name: JoinedAddresses
- Geometry: db_point
Click OK twice 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 the Run button on the top toolbar, or by selecting Run > Run Workspace from 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 mini toolbar and clicking on the View Written Data button.
In Data 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
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.