The FeatureJoiner Transformer

Introduction

The FeatureJoiner is a new transformer for FME 2018. It is similar to the FeatureMerger transformer but has a more simplistic process as well as better performance. If you are familiar with SQL you already know how to use the FeatureJoiner. The following article will explain the basics of how to use the FeatureJoiner; for a more in-depth explanation please see the FeatureJoiner documentation.

Join Types Using the FeatureJoiner

There are three join modes available in the FeatureJoiner: an Inner, Left and Full Join. Compared to the FeatureMerger, the FeatureJoiner can more easily handle features with multiple matches (ie. 1:M, M:N and M:1). Each set of features that match will create a joined output, so if you are used to the FeatureMerger the output might be different than you expect, but it is correct.

To set the type of join in the FeatureJoiner, go into the parameters and change Join Mode.

Inner Join

An Inner Join is joining two tables together using a common identifier and only joining the data that the identifier appears on both tables, this will be output through the Joined output port. All other data where the identifier doesn’t match is ignored and will be output through the UnjoinedRight/Left port.

For the Inner Join, only five records are joined based on their Jobs common identifier.

Left Join

A Left Join joins two tables together using a common identifier and joins all the records with the same identifier as well as keeps all the records from the left table even where the identifier doesn’t match the right table, and then ignores all the records from the right that doesn’t have a matching identifier.

Five records are joined based on their Jobs common identifier as well as the other three records from the left table. Since there is no matching common identifier on the right table, those attributes are <null>. If we wanted to get all the records from the right table instead of the left to do a Right Join, we would just need to switch the input ports on the FeatureJoiner so that the right table is read into the Left input port and the left table is read into the Right input port.

Full Join

A Full Join joins together both left and right tables in their entirety using their common identifier. If there are records without a common identifier they will be output through the Rejected output port on the FeatureJoiner.

Five records are joined completely, but the remaining three records from the left table are included as well as the remaining four records from the right table. All features will be output through the Joined output port unless there is no value for the common identifier, these values will be output through the Rejected output port.

FeatureMerger Output Ports

How to use the FeatureJoiner

1. Open FME Workbench

Download and open the ParksFeatureJoinerDemo.fmwt workspace in FME Workbench

Initial workspace reading in Parks.tab and the Parks table from CommunityMapping.gdb

This workspace joins a Parks.tab dataset with a Parks table from the Community Mapping Geodatabase. The Parks Geodatabase table contains addresses and a URL for each park which we want to join to the Parks.tab dataset. We will use the ParkName to join the two tables.

Parks MapInfo Tab table [MITAB]

Parks table from CommunityMapping File Geodatabase [FileGDB]

2. Connect the FeatureJoiner

Connect the Parks [MITAB] Feature Type to the Left input port on the FeatureJoiner and then connect the Parks [FILEGDB] to the Right input port.

Both Park datasets connected to the FeatureJoiner transformer

Then in the FeatureJoiner parameters set the Join Mode to Left. For the Attribute Conflict Resolution and Geometry Handling parameters, set those to Use Left.

FeatureJoiner Join Mode Parameter, all set to Left

Next for Join On set the Left to ParkName and the Right to ParkName then change the Comparison Mode to String. You could leave the Comparison Mode to Automatic if you wish.

Join On parameter set to ParkName for both Left and Right and the Comparison Mode set to String

3. Run Translation and Inspect

Run the translation with Feature Caching Enabled

65 Joined features, 15 UnjoinedRight features and 15 <Rejected> features

It looks like we have 65 features that were joined, 15 features that were unjoined from the right table and 15 features that were rejected. Let’s inspect the features that were rejected. Click on the Inspection icon on the Rejected port to inspect the features in the Data Inspector.

Looking at these features, it looks like they are tiny unnamed parks that were built to create traffic calming areas. Since they are unnamed they can’t be joined but we can still keep them even though they were rejected.

4. Append Features

Add an AttributeManager to the canvas and connect it to both the Joined and Rejected output ports on the FeatureJoiner. The only purpose of adding the AttributeManager is to show that both the joined and rejected features will continue through the rest of the workspace..

Run the translation again with Feature Caching Enabled still and then inspect the AttributeManager output data. We will have 80 features and when we inspect the data both the named and unnamed parks are included in the table.

AttributeManager connected to the Joined and <Rejected> output ports on the FeatureJoiner

The Joined and <Rejected> features appended in the Data Inspector

Additional Resources

The FeatureMerger Transformer

Merging or Joining Spreadsheet or Database Data

Merging or Joining Spatial Data

How to Join Data with FME and (Maybe) Land Your Dream Job! - Blog Post

FeatureJoiner Documentation

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.