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 simply to remove it from the features written to the database, as the following example shows.

Although this example uses a PostGIS database, the same techniques apply to other databases as well.

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 tutorial, 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, then right-click 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 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 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 following

• General:
  • Update Detection Key Attributes: parkid
• Check Attributes: enabled
  • Selected Attributes: treecount visitorcount
    • Click on the ellipses to open a window to select the attributes or copy and paste the attributes from above
• Check Geometry: enabled
  • Match Geometry: 2D

Click OK.

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, connect it to all the ChangeDetector's output ports except the Unchanged port, and connect the AttributeRemover's 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. 

In some workflows, you may only want to update attributes without changing 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, 9 trees, and 14253 visitors. 

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

If you inspect the ParksUpdate dataset, you will see 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.