Updating Databases: Filtering Features to set Database Update Operation Type

Introduction

Information stored in a database is very likely to change over time. When the source of these changes is a dataset elsewhere, incremental updates can be used to keep the database up-to-date.
This example illustrates how to use the fme_db_operation attribute to drive a mixture of database changes - firstly an Insert, then both Update and Delete operations - filtering data to determine which action to carry out.

Note: Although this example uses a PostGIS database, the same techniques are also applicable to other databases, including Geodatabases.

The fme_db_operation is a special FME format attribute that can be set to either INSERT, UPDATE or DELETE to control incremental updates on a target database. The value can be set 'manually' using transformers like the AttributeManager (as illustrated in the simple examples below). Some FME database formats (ArcSDE Geodatabase and Smallworld) can read differences and automatically set the value of fme_db_operation.  Some transformers (ChangeDetector) will also set the fme_db_operation attribute to the appropriate value. See the article Geodatabase Behavior: Updating a File Geodatabase to see how to use the ChangeDetector to set fme_db_operation and use incremental updates on a Geodatabase writer. 

Step-by-step Instructions

Part 1: Seed Training Database

1. Create the Training Database
Before you begin, please ensure you have created your personal training database by following the directions in Creating a PostgreSQL/PostGIS Training Database with FME.  
 
2. Read Parks
Start FME Workbench and create a blank workspace. Add a MapInfo TAB (MITAB) reader to the canvas and browse to the Parks.tab dataset that is available to download from the Files section of this article. 

 
3. Add Tester Transformer
This dataset contains all of the parks in the City of Vancouver, but we only require parks in the Downtown area. Add a Tester to the canvas and connect it to the Parks reader feature type. 

 
In the Tester parameters, set the Test Clause to NeighborhoodName = Downtown.

 
4. Add PostGIS Writer
Let's create a new table in the FMETraining database for only the Downtown Parks. Add a PostGIS writer to the canvas and connect to the FMETraining database. Leave the Table Definition to Copy from Reader, then click OK. 


Connect the writer feature type to the Tester Passed output port and then open the writer feature type parameters. 
In the PostGIS writer parameters, set the Table Name to DowntownParks, then the Table Qualifier to public. 
Since this is a new table, we’ll leave the Feature Operation to Insert and the Table Handling to Create if Needed. Click Ok. 

 
5. Map Attributes 
Since PostGIS defaults to lowercase attributes, we need to map our attributes since the MapInfo TAB dataset has the attribute names in CamelCase. You can tell that these attributes are not mapped correctly, because the arrow next to public.DowntownParks is red. If you click on this arrow to expand the attribute names, you can see that all of them have a red arrow. Expand the Tester attributes to see the CamelCase names. 


To fix this, right-click on the connection line between the Tester and writer feature type and select Auto Connect Attributes. 


All of the attributes should now have a green arrow beside them, indicating they were mapped correctly. 

 
6. Run the Workspace
Now save the workspace and then run it. 19 features will pass the test and be written to the database table.

 

Part 2: Filter and Update Data

1. Filter and Update Data
Now let's imagine it is six months later. Various real-world updates have been made to the parks. Portal Park is temporarily closed for redevelopment and should be removed from the table. All other parks have had 100% more trees added as part of a tree-planting initiative.
We need to edit our workspace to update the data, and to define what actions should be carried out.
Firstly disconnect the writer feature type from the workflow, and then add a second Tester transformer. Connect the Tester_2 to the Passed output port on the Tester. 


In the Tester_2 parameters, set the test to be ParkName = Portal Park. 

 
This separates the data into records that should be deleted (Tester_2 Passed) and records requiring updates (Tester_2 Failed).
 
2. Update Tree Counts
Connect an ExpressionEvaluator transformer to the Tester_2 Failed output port. Set the Evaluation Mode parameter to Overwrite Existing Attributes and select TreeCount as the attribute to update.
Set the Arithmetic Expression to: 

@Value(TreeCount)*2 

 
3. Set Update Type
Because there are multiple, mixed operations here (some Updates, some Deletes) we must tag each feature individually with its operation.
Add an AttributeCreator to the canvas and connect it to the Tester_2 Passed output port. In the parameters, create a new attribute called fme_db_operation and give it a value of DELETE. 

 
Duplicate the AttributeCreator (ctrl-d/cmd-d) and connect the AttributeCreator_2 to the ExpressionEvaluator. In the parameters, change the Attribute Value to UPDATE. 

  
4. Set fme_db_operation
Connect the public.DowntownParks writer feature type to both of the AttributeCreators. 


We've now set what operations to carry out. The final step is to tell FME which features in the database the operations should be applied to.
Open the parameters for the writer feature type once more. Set Feature Operation to fme_db_operation and ensure Table Handling is set to Use Existing (FME should set that automatically when you change the feature operation). For the Match Columns parameter, select the field parkid.

 
This means that parkid is used to match the incoming features to the records in the table. Where there is a match then the action defined by fme_db_operation will be carried out.
 
5. Check Attribute Mapping and Run Workspace
One final check before we run the workspace. Ensure that all attributes are being mapped correctly from the AttributeCreators. You’ll need to Auto Connect Attributes for both of the connection lines. Note this can be avoided by correctly mapping the attributes earlier in the workspace using an AttributeManager.  Once correctly mapped, run the workspace. 

This time there should be 18 park features. All of the TreeCount attributes will be double what they were previously, and Portal Park should not exist.

 

Non-Harmonized Formats

The procedure outlined above was demonstrated on PostGIS. However, the exact same process applies to most major database formats, which have been harmonized.
Should your database format have a non-harmonized interface, then the following must be applied.

• There may or may not be a Feature Operation parameter to set. Generally, if there is it should be changed to fme_db_operation, or UPDATE if there is no option for fme_db_operation
• There may or may not be a Match Columns parameter to set, and it may have a different name.
  • If there is such a parameter, then set it to parkid
  • If there is no such parameter, then in the AttributeManager transformers, as well as creating fme_db_operation, also create fme_where
    • Set the value of fme_where to parkid = @Value(ParkId)

 
The fme_where attribute is the equivalent of setting the Match Columns parameter in a harmonized GUI. It should be of the format [database field] [operator] [attribute] (i.e. the database field should come first). The operator would usually be an equals operation (=).

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.