Updating Databases: Updating Individual Fields of a Record

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.
In some scenarios it is necessary to update only certain fields in the database; even if the columns are changed in the source of the updates, they should not all be applied to the database.
The way to avoid updating a field is to simply remove it from the features written to the database, as the following example proves.

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. Remove Attributes
To avoid updating specific attributes, we can simply remove them from the data that will be written. This can either be done with an AttributeManager or an AttributeRemover. 
 
Add an AttributeRemover to the canvas and connect it to all of the output ports on the ChangeDetector except the Unchanged port then connect the AttributeRemover output port to the public.DowntownParks writer feature type. 

 
In the parameters, set the Attributes to Remove to visitorcount and treecount. 

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

 
7. Connect PostGIS Writer
Connect the public.DowntownParks writer feature type to the AttributeRemover. Click on the arrow to on the writer feature type to expand the attributes. Notice that the visitorcount and treecount attributes have a red arrow indicating they are no longer receiving a value. This means those attributes will not be updated. 

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

 
Note: in some workflows, you may only want to update attributes and not make any changes to the geometry. If this is the case, then set the parameter Update Spatial Columns to No. 
 
9. Inspect Data and Run Workspace
Before running the workspace, inspect the current contents of the DowntownParks table. Take note of Helmcken Park, it has 46 vertices, as well as 9 trees and 14253 visitors. 

Note that the treecount and visitorcount attributes may have different numbers if you have completed other tutorials in their series. Just take note of the numbers in your original and updated databases. 


If you inspect the ParksUpdate dataset, note that Helmcken Park has 9 trees and 14680 visitors. 


After running the workspace, Helmcken Park now has only 45 vertices but still has only 9 trees and 14253 visitors. This means the geometry was updated, but the treecount and visitorcount attributes were skipped. 

 

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.