You are here: Filters and Joins > DatabaseJoiner

DatabaseJoiner

DatabaseJoiner: Joins attributes from an external table to incoming features as they are being processed through a translation.

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

The _matched_records attribute specifies how many records in the database the feature matched to.

Output Ports

Parameters

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

Reader

Join

Merge Attributes

Optimize

Tip: Where DatabaseJoiner transformers will match only on a known subset of records within a table, it will often be more efficient to prefetch that subset of records before matching takes place.

Usage Notes

Relationship to FeatureMerger

The FeatureMerger joins two datasets being read in a workspace. FeatureMerger is also able to perform certain geometric operations on incoming features using its Merge Type parameter. FeatureMerger performs all joins in memory, so it can be faster than the DatabaseJoiner if you have more than one relationship on the same data. DatabaseJoiner joins are performed by the database using SQL. The article FME2011 Use Case: Joiner vs FeatureMerger contains a more detailed comparison of these transformers.

Relationship to InlineQuerier/SQLCreator/SQLExecutor

The DatabaseJoiner is very useful and efficient when there exists a one-to-one or one-to-many relationship between data flowing through FME and data held within a database. If it can be used, the DatabaseJoiner can be more efficient than using either the InlineQuerier or SQLCreator/SQLExecutor, provided that the DatabaseJoiner key fields have indexes in the source database. 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. SQLCreator/SQLExecutor allow more complex joins and these are executed by the source database.

The InlineQuerier is useful in cases where data sources have no SQL ability, or for more complex queries. The InlineQuerier allows you to ask more sophisticated questions about the data than the DatabaseJoiner.

Example

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.

Transformer Categories

Attributes

Filters and Joins

Search FME Knowledge Center

Search for samples and information about this transformer on the FME Knowledge Center.