Files
-
- 70 KB
- Download
Introduction
FME offers multiple ways to join data. This example will look at using the FeatureMerger transformer. The FeatureMerger copies and merges attributes/geometry from one feature with another feature by performing a non-spatial join using key values. Please read the FeatureMerger documentation for a more detailed description of what this transformer does, as well as details about each parameter.
If you are used to SQL, try the FeatureJoiner transformer instead. FeatureMerger handles joins differently: by default, it creates only a single match per source feature. The FeatureJoiner supports multiple matches.
Using the FeatureMerger
The FeatureMerger receives two streams of features via its input port:
- 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.
Key things to remember:
- 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.
Many Requestors to One Supplier:
The People table is the Requestor, and the Jobs table is the Supplier. Since each person is a unique value, the Jobs table is merged successfully onto each record in the Person table. They merged on the Job key.
One Requestor to Many Suppliers (Process Duplicate Suppliers Disabled):
However, now we have switched the tables. The Jobs table is now the Requestor, and the People Table is the Supplier. Only Joe is merged with the IT job since it is the first occurrence of an IT job; the remaining people with IT jobs were sent to the rejected port. This is because the suppliers are duplicates. To process duplicate suppliers, we need to create a list:
One Requestor to Many Suppliers (Process Duplicate Suppliers Enabled):
To handle duplicate suppliers coming from the People Table, we need to create a list. We do this by enabling the Generate List parameter in the FeatureMerger. For this example, we created a list named SameJob and included only the Name attribute. So now, when we run the translation and inspect the features in the Feature Information Window, we can see a list for each person with the same job. Alternatively, you can explode the list using a ListExploder after the FeatureMerger to see each list item in a table.
How to Use the FeatureMerger
Now that we have discussed some theory and hypothetical FeatureMerger scenarios, let's look at an example using the FeatureMerger.
Scenario: We want to merge voting divisions and locations data with the voter results for mayor, but we want to keep the geometry. To do this, we will use a FeatureMerger transformer.
1. Open FME Workbench
In a blank workspace, add a GML (Geography Markup Language) reader and browse to the ElectionVoting.gml dataset; add both the VotingDivisions and VotingPlaces Feature Types.
Add a second reader to the canvas. This time, choose Microsoft Excel as the Format and browse to the ElectionResults.xlsx dataset. Only add the Mayor sheet.
2. Merge Features
Add a FeatureMerger transformer to the canvas. Connect the VotingDivisions to the Requestor input port. Then connect the Mayor Feature Type to the Supplier input port on the FeatureMerger.
VotingDivisions connected to the Requestor port, Mayor connected to the Supplier port
In the FeatureMerger parameters, set the Join On to Division for both the Requestor and the Supplier.
FeatureMerger Join On parameter: Requestor and Supplier set to Division
Then, under Merge Parameters, set the Reject Null and Missing Keys to Yes because we don’t want any values without a Division associated with them. This will send any features to the <Rejected> port that have no Division value in any of the tables we are trying to merge.
We want to leave Process Duplicate Suppliers unchecked because each division should have only one record. When we run the translation, we should double-check the UnusedSuppliers output. This is a QA test to see whether there are duplicates or differing values we should investigate. The rest of the parameters can just be left at the defaults.
Reject Null and Missing Keys set to Yes in the FeatureMerger
3. Run the Translation
Turn on Run with Feature Caching, then run the translation. You should get 55 Merged features.
The FeatureMerger outputs 55 features through the Merged port.
4. Inspect Output
Inspect the Merged feature output; we should now have the geometry of the Divisions as well as the attributes from Mayor.
FeatureMerger Merged output port inspected in the Data Inspector.
5. Merge Voting Places
Next, merge Voting Places with the Merged output from the FeatureMerger to add all Voting Places attributes to each Division.
To do this, add a second FeatureMerger to the canvas. Connect the Merged output port on the first FeatureMerger to the Requestor input port on the second FeatureMerger. Then connect VotingPlaces to the Supplier input port on the second FeatureMerger. To prevent your connections from crossing, right-click on the Requestor port on the second FeatureMerger and select Move Down. This moves the port down and cleans up the connections.
Second FeatureMerger connected to the Voting Places and the Merged output port on the first FeatureMerger
In the parameters of FeatureMerger_2, set the Join On to Division for both the Requestor and the Supplier and set the Comparison Mode to String. Leave the remaining parameters at their defaults.
Run the translation with Feature Caching or Run with Full Inspection. It looks like five features are going to the <Rejected> port. Let’s inspect them and find out why they were rejected.
If you add a base map, these five features are definitely within our area of interest, but the fme_rejection_code is EXTRA_REFERENCE_FEATURE. This means that we have multiple VotingPlaces within a single VotingDivision. We want all the VotingPlaces, so we will create a list.
In the parameters of FeatureMerger_2, under the Merge Parameters, enable Generate List. Then, for List Name, call it VotingLocations, then change Add To List to All Attributes.
Generate List parameter in FeatureMerger_2
6. Run the Translation
Run the translation again. Now there won’t be any <Rejected> features. Inspect the Merged output port on the FeatureMerger_2 to see the list we just created.
Click on the Division at the bottom of the top half of the peninsula, Division 17. This one contains two VotingPlaces. In the Feature Information Window, you can see that a list has been created for these two features.
Inspect the Merge output port on FeatureMerger_2 with the features as a list
7. Explode List
We want to read the list in a table format, not only inside the Feature Information Window.
Back in FME Workbench, add a ListExploder transformer to the canvas and connect it to the Merged output port on the FeatureMerger_2. In the ListExploder parameters, set the List Attribute to VotingLocations{} and the Element Index Attribute to List_Index.
ListExploder attributes set to VotingLocations{}
8. Remove Attributes
We have a bunch of GML attributes that we don’t need. Add an AttributeRemover transformer to the Elements output port on the ListExploder. Remove all attributes that start with gml_, then click OK.
ListExploder and AttributeRemover parameters
9. Inspect Final Output
Re-run the translation to see the changes. AttributeRemover outputs 55 features. The extra five features were the features that were sent to the rejected features, which we then added by creating a list. We then exploded the list to create a feature for each.
When you inspect the output, each Division with more than one VotingPlace will be a darker color. Also, in the Table View for Division 17, each VotingPlace now includes the Mayor results as well.
Final output showing the geometry of the Divisions and the exploded list as a table
Additional Resources
Merging or Joining Spreadsheet or Database Data
Merging or Joining Spatial Data
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.