You are here: Filters and Joins > DatabaseJoiner

DatabaseJoiner

Joins attributes from an external table to features already in a workspace, based on a common key or keys.

Jump to Configuration

Typical Uses

  • Joining attributes from an external database table to features already in a workspace.

How does it work?

The DatabaseJoiner queries an external table to retrieve attributes associated with a feature. One or more feature attributes (primary keys) are matched to one or more columns (foreign keys) in a table in the database, and the values from the matching table row(s) are added to the feature as attributes.

A number of matching methods (Cardinality) are available - Match All (1:M), First (1:0..1+), Exactly One (1:1), or Zero or One (1:0..1). Features that do not fulfill the matching conditions are output via the <Rejected> port.

The _matched_records attribute specifies how many records in the database the feature matched to. Multiple matches can create multiple features or add a list attribute to a single feature.

The DatabaseJoiner allows simple join relationships based on multiple attribute keys and requires no knowledge of SQL – this is often very effective for simple lookup tables.

Usage Notes

  • For optimal performance, ensure that the keys (attributes to be joined on) are indexed in the database. If keys are indexed, the DatabaseJoiner can be considerably more efficient than other joining transformers.
  • To join two feature streams that are already in a workspace, consider using the FeatureMerger, which also provides geometry merging options.
  • If all data resides in a SQL-capable source, consider using the SQLCreator or SQLExecutor, which can also execute more complex join queries which are processed in the source database. For a simple join, the DatabaseJoiner may be the most efficient approach.
  • The DatabaseJoiner does not require knowledge of SQL.
  • To perform SQL join queries (simple or complex) on non-SQL data sources, consider using the InlineQuerier.

Choosing a Feature Joining Method

Many transformers can perform data joining based on matching attributes, expressions and/or geometry. When choosing one for a specific joining task, considerations include the complexity of the join, data format, indexing, conflict handling, and desired results. Some transformers use SQL syntax, and some access external databases directly. They may or may not support list attribute reading and creation.

Generally, choosing the one that is most specific to the task you need to accomplish will provide the optimal performance results. If there is more than one way to do it (which is frequently the case), time spent on performance testing alternate methods may be worthwhile. Performance may vary greatly depending on the existence of key indexes when reading external tables (as opposed to features already in the workspace).

Configuration

Input Ports

Output Ports

Parameters

The DatabaseJoiner is a very powerful transformer with many performance-related settings.

Editing Transformer Parameters

Using a set of menu options, transformer parameters can be assigned by referencing other elements in the workspace. More advanced functions, such as an advanced editor and an arithmetic editor, are also available in some transformers. To access a menu of these options, click beside the applicable parameter. For more information, see Transformer Parameter Menu Options.

Defining Values

There are several ways to define a value for use in a Transformer. The simplest is to simply type in a value or string, which can include functions of various types such as attribute references, math and string functions, and workspace parameters. There are a number of tools and shortcuts that can assist in constructing values, generally available from the drop-down context menu adjacent to the value field.

Reference

Processing Behavior

Feature-Based

Feature Holding

No

Dependencies Format-dependent - may require third-party drivers for some formats
FME Licensing Level FME Base Edition and above
Aliases Joiner
History  
Categories

Attributes

Filters and Joins

FME Knowledge Center

The FME Knowledge Center is the place for demos, how-tos, articles, FAQs, and more. Get answers to your questions, learn from other users, and suggest, vote, and comment on new features.

Search for all results about the DatabaseJoiner on the FME Knowledge Center.

 

Examples may contain information licensed under the Open Government Licence – Vancouver