span8
span4
span8
span4
Previous: Calculating Values for New Fields
Relating database tables in FME 2018.0 is fast and easy using the new FeatureJoiner transformer. The FeatureJoiner combines the attributes of features based on common attribute values, similar to a SQL join operation. In this tutorial, you will learn how to join features from two database tables based on a primary key, but this method can be modified to combine features from multiple data types (for example, shapefile and database table).
Note: Since you are working on a public facing database, tables can occasionally be overwritten and/or modified. If your translation produces unexpected results, see the Resetting the PostGIS Training Database article. Alternatively, you can download the provided file(s) and replace the PostGIS reader with a reader of the appropriate format.
In this exercise, you will learn how to join two tables based on a primary key. In this instance, you are interested in joining two tables based on the Address ID because you want to have a dataset with complete mailing addresses.
Note: if the table name matches an existing table the data will be overwritten.
Note: by clicking on the arrow on the reader, you will expose a list of attributes that have been read in. If you expand the attributes on both the AddressPoints and VanHomes reader you will notice that the AddressPoints table uses TitleCase whereas the VanHomes table uses lowercase.
You will be performing an inner join so the position (left and right) of the inputs is not important since the output will only contain matched features; however, if you were performing a left join (matched features and all unmatched left features) the position is important otherwise you will have an unexpected result. For a more detailed explanation on how to use joining transformers, see the Working with Merging/Joining Transformers (How to choose the right one).
After running the workspace, your database will have a new table (public.JoinedAddresses) with the joined fields. You can view the new table by selecting the writer and clicking the Inspect button in the shortcut menu that appears when the writer is selected.
In exercise 2, you will be adding new columns to your table using the FeatureMerger. Rather than creating a column and assigning values, you will be appending longitude and latitude data from another file into your database table. You will be performing a join on fields that are common in both your database table and the CSV file that you are reading in (i.e. an inner join).
For more information on Creating a Translation, see the Desktop Basic Course Manual.
Note: Drop and Create is used when the table needs to be emptied and an update is made to the database schema. For example, this is used when you wish to update a table with new content and require a new column to be added to the table.
Note: An Automatic Attribute Definition is when Workbench automatically defines the list of attributes, depending on which Reader feature types are connected. Further, the list of attributes on the Writer feature type will update automatically whenever attributes are changed (i.e. renamed, removed, etc.) in the workspace. For more information on User Attributes and Attribute Definitions, see the Documentation.
To learn more about using Key-Based Transformers in FME, see the Desktop Basic Course Manual.
Only matched features pass the FeatureMerger which will result in duplicate rows (i.e. Name - from the CSV file, and name from the PostGIS table).
Note: you can also remove unwanted attributes using transformers such as the AttributeManager, AttributeRemover, or BulkAttributeRemover or by setting the Writer Attribute Definition to Manual and removing attributes. For more information on managing/removing attributes, see the Desktop Basic Course Manual.
By default, the FME Workspace Parameter is set to Terminate Translation which will cause the Workspace to stop if any features are rejected by the FeatureMerger. You can learn more about rejected feature handling in the Desktop Basic Course Manual.
After running the translation, your table will have latitude and longitude columns. You can view the new table by selecting the writer feature type and clicking the Inspect button in the shortcut menu that appears when the writer is selected.
Data used in this tutorial originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.
© 2019 Safe Software Inc | Legal