The DatabaseJoiner Transformer

Introduction

The DatabaseJoiner transformer is similar to the FeatureJoiner or FeatureMerger, but instead of merging two streams of features and an in-memory join, it merges one stream of features with data read from an external database.

Still trying to decide which is the most appropriate join transformer to use? See the article Merging or Joining Spreadsheet or Database Data

For example, a dataset of AddressPoints has an AddressId number, but no postal address. The DatabaseJoiner transformer is being used to join data from an address table onto the AddressPoints data. In this case the address point features are obtaining address data directly from an address table in a PostGIS database.

The parameters dialog for the DatabaseJoiner looks like this:

Again, AddressId is being used as the join key.

Incoming features (AddressPoints) that do not find a match in the join table (PostalAddress) are output via the Unjoined port and also have the attribute _matched_records = 0

Example workspace with DatabaseJoiner.

The DatabaseJoiner has a number of advantages over other data joining transformers such as the FeatureMerger:

• DatabaseJoiner has parameters to control cardinality and how to handle 1:M joins,
• Parameters for optimizing the database query - refer to the user documentation
• DatabaseJoiner allows features to be joined without having to read the entire dataset into a workspace. FME can just query the database and select the individual records it needs. This can improve performance greatly. It does, of course, require the queried records to be stored in an appropriate database format.

Articles

DatabaseJoiner Example 1

The article Join CSV Data to a Database Table with the DatabaseJoiner matches CSV crime data to a database table of road information, so that we can determine which roads show the most crime activity.

DatabaseJoiner Example 2

The article Build Road Lines from GPS Points (CSV to Shape)  takes a set of GPS points and converts them into line work showing a road network. The attributes relating to the roads is held in an Access database. These attributes are joined to the GPS line work using the DatabaseJoiner, adding value to the output.