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

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 parks in the City of Vancouver, but we only require those 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:

• Left Value: NeighborhoodName
  • Operator: =
  • Right Value: Downtown

Click OK.

4. Add PostGIS Writer

Let's create a new table in the FMETraining database for the Downtown Parks only. 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 attribute names, we need to map our attributes to match the MapInfo TAB dataset's CamelCase attribute names. You can tell that these attributes are not mapped correctly because the arrow is 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 through a tree-planting initiative.

We need to update our workspace and define the actions to 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:

• Left Value: ParkName
  • Operator: =
  • Right Value: Portal Park

Click OK.

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:

• New Attribute: fme_db_operation
  • Attribute Value: DELETE

Click OK.

Duplicate the AttributeCreator (ctrl-d/cmd-d) and connect the AttributeCreator_2 to the ExpressionEvaluator. In the parameters, set:

• New Attribute: fme_db_operation
  • Attribute Value: UPDATE

Click OK.

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 database features 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.

If your database format has a non-harmonized interface, 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 is usually an equals sign (=).

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.