Previous: Calculating Values for New Fields
Overview
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).
Downloads
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.
Exercise 1 - Relate Database Tables using the FeatureJoiner
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.
Instructions
1. Start FME Workbench
- If it isn’t open already, launch FME Workbench.
2. Create a New Workspace
- In the Create Workspace part of the Start page, select the option to Create a New Workspace.
3. Add a PostGIS Reader
- Start typing “PostGIS” without anything selected on the workspace, then select the PostGIS format from the list of Readers by double-clicking or by using the arrow keys and the Enter key to add it to the workspace.
- Next, select the PostGIS Training Database from the Reader Connection list. If you have not already set up the database connection select Add Database Connection and enter the following parameters:
- Host: postgis.train.safe.com
- Port: 5432
- Database: fmedata
- Username: fmedata
- Password: fmedata
- Next, open the Reader Parameters and select the AddressPoints and VanHomes tables from the Table List.
4. Add a PostGIS Writer
- Similar to adding a reader, type “PostGIS” then select the PostGIS format from the list of Writers.
- Set the writer format to PostGIS and connect the writer dataset to the same dataset as the reader (PostGIS Training Database). Set the Table Definition to Automatic and click OK.
5. Set the Table Name, Feature Operation, and Table Handling
- After the Writer Feature Type is added it will open the Writer parameters. Set the Table Name to “JoinedAddresses”, by default the table will be placed in the “public” schema.
- Next, ensure the Feature Operation is set to Insert and set the Table Handling to Drop and Create.
Note: if the table name matches an existing table the data will be overwritten.
6. Add a BulkAttributeRenamer
- Since you will be relating fields, you need to make sure the cases match on the joining fields. Add a BulkAttributeRenamer and connect it to the AddressPoints table.
7. Convert to Attributes to Lowercase
- Open the BulkAttributeRenamer Parameters and set the Action to Change Case and change the Case Change Type to lowercase. This will ensure the field names in the AddressPoints table match the field names in the VanHomes table.
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.
8. Add a FeatureJoiner
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).
- Connect the output of the BulkAttributeRenamer to the Left parameter and connect the VanHomes table to the Right parameter of the FeatureJoiner. Once you have made the connections, open the FeatureJoiner Parameters.
9. Select the Attributes to Join On
- Leave the default parameters for the Join Mode and set the Join On Left and Right to “addressid”. In this case, you are only interested in fields that are common in both tables. To learn more about database joins using the FeatureJoiner, see The FeatureJoiner Transformer article.
- Finally, connect the FeatureJoiner:Joined port to the PostGIS writer to complete the workspace.
10. Run the Workspace
- Run the workspace by clicking the run button on the toolbar, or by using Run > Run Translation on the menu bar.
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.
Results
Input
Output
Exercise 2 - Relate Data from a CSV File
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).
Instructions
1. Start FME Workbench
- If it isn’t open already, launch FME Workbench.
2. Select Generate Workspace
- In the Create Workspace part of the Start page select the option to Generate (Workspace). Alternatively, you can use the shortcut Ctrl+G.
For more information on Creating a Translation, see the Desktop Basic Course Manual.
3. Set the Reader Format and Connect to the PostGIS Training Database
- In the Generate Workspace dialog, set the Reader Format to PostGIS.
- Next, select the PostGIS Training Database from the Reader Connection list. If you have not already set up the database connection select Add Database Connection and enter the following parameters:
- Host: postgis.train.safe.com
- Port: 5432
- Database: fmedata
- Username: fmedata
- Password: fmedata
- Next, open the Reader Parameters and select the Downtown_PublicArt table from the Table List.
4. Set the Writer Format and Connect to the PostGIS Training Database
- Set the Writer Format to PostGIS and the Writer Connection to the same connection as the reader (PostGIS Training Database) and click OK to generate the workspace. The Generate Workspace dialog should look like the screenshot provided below:
5. Set the Feature Operation and Table Handling
- After the workspace is generated, double-click the Writer Feature Type to open the Writer Feature Type Parameters dialog.
- In the General section, set the Table Name to PublicArtJoin.
- In the Table section, ensure the Feature Operation is set to Insert and set the Table Handling to Drop and Create.
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.
6. Update Attributes
- Switch to the User Attributes Tab in the Writer Parameters dialog, set the Attribute Definition to Automatic.
- Click OK to accept the changes and close the Writer Parameters dialog.
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.
7. Add a CSV Reader
- Add a CSV reader to the canvas by typing “CSV” to bring up the list of FME Reader in the Quick Add Search. Select the CSV format from the list of Readers by double-clicking or by using the arrow keys and the Enter key to add it to the workspace.
- Once selected, the Add Reader box will open. Click on the Dataset ellipsis and open the 3531-downtown CSV file from the sample data set then click OK.
8. Add a FeatureMerger
- Connect the PostGIS table to the Requestor port and the CSV to the Supplier port. The FeatureMerger is used to combine attributes and/or geometry from two different streams of features, based on a common key attribute value or expression.
To learn more about using Key-Based Transformers in FME, see the Desktop Basic Course Manual.
9. Set the FeatureMerger Parameters
- Open the FeatureMerger Parameters dialog and set the Join On Requestor to “name” and “title” and the Supplier to “Name” and “Title”. Leave all other parameters as default and click OK. This will match features based on both the name and title attributes.
10. Add a BulkAttributeRenamer
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).
- Add a BulkAttributeRenamer to the canvas to automatically change the attributes to lowercase from Titlecase. This will also remove the duplicate column names (“Name” → “name”).
- Connect the Merged port from the FeatureMerger to the BulkAttributeRenamer, then connect the output to the PublicArtJoin writer.
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.
11. Set the BulkAttributeRenamer Parameters
- Open the BulkAttributeRenamer Parameters dialog and set the action to Change Case and change the Case Change Type to lowercase.
12. Set the Workspace Parameters
- Before you can run the workspace, you have to set the Workspace Parameters (found in the Navigator window) > Translation > Rejected Feature Handling to Continue Translation.
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.
13. Run the Translation
- Run the workspace by clicking the run button on the toolbar, or by using Run > Run Translation on the menu bar.
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.
Results
Input
Output
Transformers
- BulkAttributeRenamer - Renames attributes by adding or removing prefixes or suffixes, or replacing text using regular expressions or character strings.
- FeatureJoiner - Joins features by combining the attributes/geometry of features based on common attribute values, like a SQL join operation.
- FeatureMerger - Copies and merges the attributes/geometry from one feature (or multiple features) onto another feature (or multiple features).
Data Attribution
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.
