Updating Databases: Using Change Detection to Set Operation Type

Introduction

Information stored in a database is unlikely to remain unchanged. Depending on how these changes occur, bulk updates may be done on the database.

This example carries out a mixture of updates - firstly an Insert, then both Update and Delete operations - using change detection to determine which action to carry out.

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

Step-by-step Instructions

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. Seed Database
If you have completed the Updating Databases: Filtering Features to set Database Update Operation Type article, skip to step 3. Your training database will already have the DowntownParks table. 

If you have not completed the Updating Databases: Filtering Features to set Database Update Operation Type article, either do so now or download the CreateDowntownParks.fmwt workspace from the Files section of this article. 

Open the CreateDowntownParks.fmwt workspace in FME Workbench and update the Database Connection. To do this, expand Database Connections in the Navigator pane and right-click on FMETraining. Select Edit connection, then update the credentials to your credentials. If you are using the default FMETraining database, you will only need to update your password. 


Once the connection is updated, run the workspace to seed the database. 
 
3. Read Data
Open a blank workspace in FME Workbench, then add in a PostGIS reader. Set the Connection to FMETraining and then open the parameters. 

 
In the parameters, click on the ellipsis next to Tables and then select the DowntownParks table. Click OK three times to finish adding the reader to the canvas. 

 
Next, add a MapInfo TAB (MITAB) reader to the canvas and browse to the ParkUpdates.tab dataset. Click OK to add the reader to the canvas. 


There should now be two readers, the public.DowntownParks is the original dataset, and the ParkUpdates is the updated dataset. 


 
4. Add ChangeDetector Transformer
We will use the ChangeDetector transformer to determine the changes between the two datasets. The ChangeDetector creates an attribute called fme_db_operation and based on the output port assigns it a value of either UPDATE, INSERT, DELETE or if the feature is unchanged, no attribute is created. 
Add a ChangeDetector to the canvas and connect the Original input port to the public.DowntownParks reader feature type and the Revised input port to the ParksUpdates reader feature type. 

 
In the parameters, set the Update Detection Key Attributes to parkid. Then set the Selected Attributes to visitorcount and treecount. Finally, expand Check Geometry and set Match Geometry to 2D. For this example, the vistorcount and treecount numbers have been increased. 
 
 
5. Add a PostGIS Writer
Add a PostGIS writer to the canvas and set the connection to FMETraining. Set the Table Definition to Copy from Reader. 

 
In the Select Feature Type dialog, select public.DowntownParks [POSTGIS] as the reader to copy from. Since we are writing to the same table we are reading from, this method works. If you were writing to a different table that was not already in the workspace, use the Import from Dataset Table Definition when adding the reader. 

 
6. Connect PostGIS Writer
Connect the public.DowntownParks writer feature type to the Updated, Inserted, and Deleted output ports on the ChangeDetector. Do not connect it to the Unchanged output port, otherwise, duplicate features will be added into the database. 

 
7. Update PostGIS Writer Parameters
Open the public.DowntownParks writer feature type parameters. Set the Feature Operation to fme_db_operation and ensure that Table Handling is set to Use Existing. Set parkid as the Match Column, then click OK. 

 
8. Inspect Data and Run Workspace
Before running the workspace, inspect the current contents of the DowntownParks table. Make a note of the visitorcount and treecount fields for several records. This will ensure that we can compare the results to prove that the update process was successful. Note, if you completed Updating Databases: Filtering Features to set Database Update Operation Type, your numbers will be slightly different as that tutorial removed Portal Park and doubled the tree count. 

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.