FeatureMerger

Categories

Filters and Joins

Related Transformers

DatabaseJoiner

FeatureJoiner

InlineQuerier

ListBasedFeatureMerger

SQLCreator

SQLExecutor

Matcher

Merges the attributes and/or geometry of one set of features onto another set of features, based on matching key attribute values and expressions.

Jump to Configuration

Typical Uses

  • Combining attributes and/or geometry from two different streams of features, based on a common key attribute value or expression.

How does it work?

The FeatureMerger receives two streams of features via its input ports.

Requestor: Requestors are the features that will receive new attributes and/or geometry.

Supplier: Suppliers provide attributes and/or geometry to be merged onto the Requestors.

Matches between Requestor and Supplier are identified according to the Join On configuration in the parameters dialog. The Join conditions can be simple or complex, using attribute values, constants, functions, or a combination of any of these in expression form. Multiple join conditions can be defined (features must meet all conditions to match).

When a Requestor finds a matching Supplier, the attributes and/or geometry from the Supplier are merged onto the Requestor.

  • If the Requestor already has an attribute that the Supplier also has, the Requestor's original value for that attribute can be preserved or overridden.
  • A single Supplier may be used by many Requestors.
  • Many Suppliers can be merged onto a single Requestor.
  • When attribute names conflict, you can choose whether Requestor or Supplier attribute values are maintained by using the Conflict Resolution parameter, and whether null values follow the same behavior by using the Ignore Nulls parameter.

Requestor features match Supplier features when every pair of keys (which support expressions) specified in the Join On table has the same value for both the Requestor and Supplier features.

Examples

In this example, we have two datasets - a CSV text file of historical crime reports, and a shapefile of public streets. Both of the datasets have one piece of information in common, that is an address in the form of “hundred” block.

The FeatureJoiner would likely be more effective to do a simple attribute value join, however, the format of the address is slightly different in each dataset. The CSV data uses the string “XX” in place of zeroes in the address:

And the street data has the more conventional form, with zeroes:

Our goal is to merge the street geometry onto the crime data - producing one output feature for each crime record. And so, The CSV data is routed into the Requestor input port, and the streets are routed into the Supplier port.

In the parameters dialog, we need to create a Join On pair of Requestor and Supplier values. As the format of the addresses is not identical, we will adjust one, constructing a key. With the assistance of the Text Editor, we create an expression that will replace the “XX” of the address with “00”, and so match the format of the street (Supplier) data.

The other half of the pairing is the attribute value HBLOCK from the street data.

Feature Merge Type is set to Attributes and Geometry. The default values for Attribute Accumulation (Merge Supplier) will provide the correct results.

The Merged output contains crime records, now with geometry for the street and associated street attributes. Note that the address format was not actually changed by the constructed expression - it was just used for matching evaluation. In this location, 5 crime records were found. Each one now has (identical) geometry.

To see this example reversed - attaching all crime records to a single street - see the next example.

In this example, we reverse the scenario in the above example. We want to merge all the historical crime records onto the street data, producing one record per street line segment (block), with a list attribute containing the crime data.

In this case, the streets are the Requestor features, and the crime data is the Supplier.

In the parameters dialog, we again create a Join On pair, using an expression to match the two (slightly different) hundred block formats. The Feature Merge Type is Attributes Only, as the Requestors (streets) already have the desired geometry.

Process Duplicate Suppliers is enabled. Without this, the first match would be processed and the remainder discarded. A count of how many Suppliers (crimes) matched will be kept in the Number of Suppliers Attribute, named Incident_Count.

Generate List is enabled, and given the List Name of Crime_Records. We will add only Selected Attributes to the List, and choose TYPE and YEAR.

The Merged output features now have a list attribute, containing all matching records from the CSV. Note the Incident_Count attribute.

Usage Notes

  • For simple joins, the FeatureJoiner may provide better performance than the FeatureMerger. However, the FeatureJoiner only accepts attribute values as keys and not constructed expressions, and does not support list attributes. Additionally, the FeatureMerger is able to (optionally) restrict output to one feature in the case of multiple matching Suppliers, whereas the FeatureJoiner will create multiple features for all matches.
  • For complex joins using SQL syntax, or more than two input feature streams, consider using the InlineQuerier.
  • Where multiple FeatureMergers are required, consider using the InlineQuerier instead.
  • If all the data to be queried already exists in a SQL-capable data source, it will be more efficient to use the SQLCreator or SQLExecutor, which allows the queries and filtering of the data to be executed directly by the database before it enters the FME environment.
  • To perform a join between features already in the workspace and data residing in an external database, consider the DatabaseJoiner.
  • To perform a join where the Requestor key is a list attribute, consider using the ListBasedFeatureMerger.
  • To join features on matching geometry, consider the Matcher. The FeatureMerger does not accept geometry as a key.
  • To join all Suppliers to all Requestors when there is no common join value, setting Join On > Requestor and Join On > Supplier to the same constant value (for example, 1 and 1) will merge all to all. Enable Generate List to store multiple matches per feature.

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).

Transformer

Match By

Uses SQL Statements

Can Create List

Input Type

Notable

Description
FeatureJoiner Attributes No No Features
  • Best performance for simple joins on attribute values
  • Cannot create lists - represents multiple joins as multiple output features
  • Cannot use constructed keys or expressions
Joins features by combining the attributes and/or geometry of features based on common key attribute values. Performs the equivalent of Inner, Left, and Full SQL joins.
FeatureMerger Attributes No Yes Features
  • Can use expressions as keys (constructed keys)
  • Can create lists – represents multiple joins as a list on a single feature
  • Cannot match on geometry
Merges the attributes and/or geometry of one set of features onto another set of features, based on matching key attribute values and expressions.
ListBasedFeatureMerger List Attribute to Single Attribute No Yes Features
  • Only joining transformer to use all list attribute contents as keys (others may use one specific value in a list, but not the entire list)
 Merges the attributes and/or geometry of one set of features onto another set of features, based on matching list attribute values with key attribute values and expressions.
InlineQuerier SQL query Yes No Features
  • Use SQL on non-SQL data
  • Allows multiple join/SQL statements, and so can replace multiple transformers with one
Creates a set of SQLite database tables from incoming features, executes SQL queries against them, and outputs the results as features.
SQLCreator SQL query Yes No External DB
  • Best if all features already in SQL-capable source
  • Read an external DB table mid-translation
Generates FME features from the results of a SQL query executed once against a database. One FME feature is created for each row of the results of the SQL query.
SQLExecutor SQL query Yes No External DB
  • Best for complex joins between features and SQL-capable source
  • Read an external DB table mid-translation
Executes SQL queries against a database. One query is issued to the database for each initiating feature that enters the transformer. Both the initiating features and the results of the query may be output as features.
DatabaseJoiner Attributes No Yes External DB and Features
  • Best for simple joins between features and a database table
  • Very efficient if keys indexed, and caches data locally
  • Read an external DB table mid-translation
  • Non-blocking
Joins attributes from an external table to features already in a workspace, based on a common key or keys. SQL knowledge not required. Non-blocking transformer.
Matcher Geometry and/or Attributes No Yes Features
  • The only one of these methods that can use geometry as key for matching
  • Can look for attribute differences
  • Uses geometry, not spatial relationships

Detects features that are matches of each other. Features are declared to match when they have matching geometry, matching attribute values, or both. A list of attributes which must differ between the features may also be specified.

If matching on attributes only (not geometry), using the FeatureMerger or another method will give better performance.

Configuration

Input Ports

Features that will receive new attributes and/or geometry from the features connected to the Supplier port.

The source of new attributes and/or geometry for features that enter through the Requestor port.

Note that by setting Process Duplicate Suppliers to Yes, more than one Supplier may be merged to each Requestor. These Suppliers potentially could come from different sources (such as different database tables).

Output Ports

Requestors that match a Supplier (or possible multiple Suppliers, if Process Duplicate Suppliers is set to Yes).

Requestors that do not match any Suppliers. If a Join On key is null or missing on a Requestor feature, that feature is output onto this port.

Suppliers that are found by at least one Requestor.

Note: A Supplier may still be output through this port even though none of its attributes were merged. This can occur because all of the attributes were already present in the Requestor or were provided by earlier Suppliers.

Suppliers that do not match any Requestors. If a Join On key is null or missing on a Supplier feature, that feature is output onto this port.

If Process Duplicate Suppliers is No, then duplicate suppliers will be rejected. If Reject Null and Missing Keys is Yes, then keyless features will be rejected.

Rejected Feature Handling: can be set to either terminate the translation or continue running when it encounters a rejected feature. This setting is available both as a default FME option and as a workspace parameter.

Parameters

Group By

The input features may be partitioned by the Group By parameter. If you choose any Group By attributes, then references between features will only be resolved if they share a common value for the selected attributes.

If you do not choose any Group By attributes, all features are processed together.

If you have more than one Reader, a typical use is to group by reader_id to ensure that references are resolved within the correct set of features.

Complete Groups

Select the point in processing at which groups are processed:

When All Features Received

This is the default behavior. Processing will only occur in this transformer once all input is present.

When Group Changes (Advanced)

This transformer will process input groups in order. Changes of the value of the Group By parameter on the input stream will trigger processing on the currently accumulating group. This may improve overall speed (particularly with multiple, equally-sized groups), but could cause undesired behavior if input groups are not truly ordered.

There are two typical reasons for using When Group Changes (Advanced) . The first is incoming data that is intended to be processed in groups (and is already so ordered). In this case, the structure dictates Group By usage - not performance considerations.

The second possible reason is potential performance gains.

Performance gains are most likely when the data is already sorted (or read using a SQL ORDER BY statement) since less work is required of FME. If the data needs ordering, it can be sorted in the workspace (though the added processing overhead may negate any gains).

Sorting becomes more difficult according to the number of data streams. Multiple streams of data could be almost impossible to sort into the correct order, since all features matching a Group By value need to arrive before any features (of any feature type or dataset) belonging to the next group. In this case, using Group By with When All Features Received may be the equivalent and simpler approach.

Note: Multiple feature types and features from multiple datasets will not generally naturally occur in the correct order.

As with many scenarios, testing different approaches in your workspace with your data is the only definitive way to identify performance gains.

Suppliers First

When this option is enabled, the transformer will assume that all Suppliers will enter the transformer before any Requestors. Once the first Requestor arrives, it will process and output Requestors as they come in. The Suppliers will be output after all the Requestors have been processed.

Note: It is the user’s responsibility to make sure all the Suppliers arrive before the Requestors. The transformer will stop accepting Suppliers once it receives its first Requestor when this option is enabled.

Requestor

Specify an expression from the Requestor feature to match with the Supplier feature expression.

An expression can be a constant, attribute value, function or mixture. Leading and trailing space characters are trimmed from the evaluated expressions. Matches are made when the trimmed values of all the Requestor expressions equal the trimmed values of their corresponding Supplier expressions.

Supplier

Specify an expression from the Supplier feature to match with the Requestor feature expression.

An expression can be a constant, attribute value, function or mixture. Leading and trailing space characters are trimmed from the evaluated expressions. Matches are made when the trimmed values of all the Requestor expressions equal the trimmed values of their corresponding Supplier expressions.

Generally, the Suppliers will have expressions defined that result in a unique match with a Requestor, and any duplicate Suppliers are ignored by the transformer. However, if the Process Duplicate Suppliers parameter is set to Yes, then all Suppliers whose expressions match their corresponding Requestor expressions will be combined onto that Requestor.

Comparison Mode

The Comparison Mode column specifies how to perform the comparison between Requestor and Supplier attribute values. If Automatic or Numeric is specified, an attempt will be made to convert attribute values to numbers before comparing them.

Feature Merge Type

This parameter specifies what to merge onto the completed feature. Choices include:

  • Attributes Only: The Suppliers’ attributes will be joined to the attributes of the Requestor features.
  • Geometry: Supplier geometry will replace Requestor geometry.
  • Attributes and Geometry: Both the geometry and attributes from the Suppliers are joined to the Requestor features. Any geometry on the Requestor will be overwritten.

Reject Null and Missing Keys

Yes: Any Requestor or Supplier feature that contains a null or missing join key is output via the <Rejected> port.

No: Null and missing keys are treated as empty strings.

If more than one Supplier is found for a given Requestor, and Process Duplicate Suppliers is not enabled, then every Supplier after the first is output via the <Rejected> port and only the first of the Suppliers will be matched with a Requestor.

If Process Duplicate Suppliers is enabled, duplicate Suppliers are all matched with the corresponding Requestor. Attributes are merged based on the Attribute Accumulation mode, and optionally output as a list attribute of the Requestor, using the specified List Name. The Supplier geometry is merged using the specified Geometry Merge Type.

Note that enabling Generate List also implicitly enables Process Duplicate Suppliers.

Geometry Merge Type

Specifies how to merge duplicate Suppliers onto the Requestor. It is applicable only if Feature Merge Type specifies to merge geometry. Choices include:

  • Build Polygons: If the Suppliers consist exclusively of polygon and donut polygon features, any common border segments will be removed. If the Suppliers contain at least one non-donut or non-polygon feature, then the transformer will form polygons and donuts from the Suppliers and will join connected line segments of the Supplier features before setting the geometry of the Requestor feature. In this case, the geometry may be an aggregate if several disjoint geometries were created.

  • Build Aggregates: The transformer will create an aggregate of the geometries of the Supplier features. (If there is only one Supplier feature, then the Requestor geometry will be an aggregate with one element.)

  • Build Lines from Points: The transformer will connect the points of the Supplier features into lines. Note that any non-point features that are referenced will be ignored when building lines.

Tolerance

The minimum distance between geometries in 2D before they are considered equal, in ground units. If the tolerance is None, the geometries must be exactly identical to be considered equal. If the tolerance is Automatic, a tolerance will be automatically computed based on the location of the input geometries. Additionally, a custom tolerance may be used.

Connect Z Mode

If applicable, select a method for handling z values.

When viewed in 2D (ignoring Z), a path (which may define the border of a polygon) may appear to be closed as shown in the left figure below. This same path, when viewed in 3D, may appear to be open as shown in the right figure below.

To specify how (and if) paths should be closed in 3D, select one of the listed modes.

Mode

Description

Example
Extend The Curve is extended so that all vertices are left at their original location.
Average Subsequent vertices that are not connected, but share an x and a y value are combined into one vertex, whose Z value is the average of the original two.
First Wins Subsequent vertices that are not connected, but share an x and a y value are combined into one vertex, whose Z value is taken from the first encountered vertex.
Last Wins Subsequent vertices that are not connected, but share an x and a y value are combined into one vertex, whose Z value is taken from the last encountered vertex.
Ignore Z values are ignored. No change is made to the way the nodes are connected.

Number of Suppliers Attribute

The name of an attribute which will store a count of the number of Suppliers matched a Requestor.

If attributes on the Supplier and Requestor feature share the same name, but are not geometry attributes that start with fme_, then they are deemed conflicted.

Accumulation Mode

Merge Supplier: The Requestor feature will retain all of its own un-conflicted attributes, and will additionally acquire any un-conflicted attributes that the supplier feature has. This mode will handle conflicted attributes based on the Conflict Resolution parameter.

Prefix Supplier: The Requestor feature will retain all of its own attributes. In addition, the Requestor will acquire attributes reflecting the supplier feature’s attributes, with the name prefixed with the Prefix parameter.

Replace with Single Supplier: The Requestor feature will have all of its attributes removed, except geometry attributes that start with fme_. Then, all of the attributes from one (arbitrary) supplier feature will be placed onto the Requestor.

Conflict Resolution

Use Requestor: If a conflict occurs, the Requestor values will be maintained.

Use Supplier: If a conflict occurs, the values of the supplier will be transferred onto the Requestor.

Ignore Nulls

No: Treat null attribute values like other attribute values.

Yes: Treat null attribute values as less important than other attribute values. Whenever a null value is merged with a non-null value, the non-null value shall prevail, regardless of what Conflict Resolution is set to.

Note: Null and other attribute values are always more dominant than missing attribute values. That is, whenever an attribute value is merged with a missing attribute value, the attribute value shall prevail, regardless of the Ignore Nulls and Conflict Resolution settings.

Prefix

To prevent a Supplier attribute from being ignored because the Requestor attribute already exists, you can optionally specify a prefix that will be applied to each Supplier attribute when it is added to the Requestor.

When there are multiple Supplier features for a Requestor feature, multiple Supplier attribute values are merged into the same prefixed attribute. When multiple Supplier features have the same attribute, generally the resulting attribute value is taken from the last of these features. However, this process is governed by the Ignore Nulls parameter.

If there are duplicate Suppliers and Generate List is enabled, then any Suppliers that are combined with a Requestor will have their attributes added to the specified list on the Requestor. Note that enabling Generate List also implicitly enables Process Duplicate Suppliers.

Note: List attributes are not accessible from the output schema in FME Workbench unless they are first processed using a transformer that operates on them, such as ListExploder or ListConcatenator. Alternatively, AttributeExposer can be used.

List Name

Enter a name for the list attribute.

Add To List

All Attributes: Every attribute from a Supplier that combined with a Requestor will be added to the list specified in List Name.

Selected Attributes: Only the attributes specified in the Selected Attributes parameter will be added to the list specified in List Name.

Selected Attributes

The attributes to be added to the list when Add To List is Selected Attributes.

Preserve Feature Order

This parameter controls the order in which features exit a transformer.

When a transformer has more than one output port, features usually exit one port at a time. At times, it may be useful to keep the order that features arrived in, switching from port to port as necessary. This allows feature order to be preserved, though at a potential cost in processing efficiency.

Select a method for feature ordering.

Per Output Port (Default)

Only preserve the input order of features as they occur within the group of features exiting a given output port.

All features exiting an output port retain their ordering relative to each other (within the group), but not relative to features exiting other output ports.

This option is generally the most efficient, where large chunks of features will exit an output port together (taking advantage of bulk mode).

As features exiting different output ports may not be strictly output in the order they arrived, output ordering may be unpredictable.

Across Output Ports

Strictly preserve the input order of features, regardless of which output port they exit. Features will be output singly in the same order they arrived, switching from port to port as necessary. This option is generally less efficient as the processing gains of bulk mode are less likely to apply - however, feature order is predictable.

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.

Using the Text Editor

The Text Editor provides a convenient way to construct text strings (including regular expressions) from various data sources, such as attributes, parameters, and constants, where the result is used directly inside a parameter.

Text Editor

Using the Arithmetic Editor

The Arithmetic Editor provides a convenient way to construct math expressions from various data sources, such as attributes, parameters, and feature functions, where the result is used directly inside a parameter.

Arithmetic Editor

Conditional Values

Set values depending on one or more test conditions that either pass or fail.

Parameter Condition Definition Dialog

Content

Expressions and strings can include a number of functions, characters, parameters, and more.

When setting values - whether entered directly in a parameter or constructed using one of the editors - strings and expressions containing String, Math, Date/Time or FME Feature Functions will have those functions evaluated. Therefore, the names of these functions (in the form @<function_name>) should not be used as literal string values.

String Functions

These functions manipulate and format strings.

Special Characters

 A set of control characters is available in the Text Editor.

Math Functions

Math functions are available in both editors.
Date/Time Functions Date and time functions are available in the Text Editor.

Math Operators

These operators are available in the Arithmetic Editor.

FME Feature Functions

These return primarily feature-specific values.

FME Parameters

FME and workspace-specific parameters may be used.
Creating and Modifying User Parameters Create your own editable parameters.

Dialog Options - Tables

Transformers with table-style parameters have additional tools for populating and manipulating values.

Row Reordering

Enabled once you have clicked on a row item. Choices include:

  • Add a row
  • Remove a row
  • Move current row up one
  • Move current row down one
  • Move current row to top
  • Move current row to bottom

Cut, Copy, and Paste

Enabled once you have clicked on a row item. Choices include:

  • Cut a row - delete and copy to clipboard
  • Copy a row to the clipboard
  • Paste a row from the clipboard

Cut, copy, and paste may be used within a transformer, or between transformers.

Filter

Start typing a string, and the matrix will only display rows matching those characters. Searches all columns. This only affects the display of attributes within the transformer - it does not alter which attributes are output.

Import

Import populates the table with a set of new attributes read from a dataset. Specific application varies between transformers.

Reset/Refresh

Generally resets the table to its initial state, and may provide additional options to remove invalid entries. Behavior varies between transformers.

Note: Not all tools are available in all transformers.

Reference

Processing Behavior

Group-Based

Feature Holding

Yes
Dependencies None
Aliases  
History  

FME Community

The FME Community 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 FeatureMerger on the FME Community.

 

Examples may contain information licensed under the Open Government Licence – Vancouver and/or the Open Government Licence – Canada.