Tutorial: Updating Databases with FME

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.
These updates may be as simple as reloading an entire dataset and replacing the existing content. In some cases the table is restructured before reloading, in other cases, the table is truncated (emptied of its contents) but the structure is left as before.
More complex updates involve processing individual records (leaving others untouched). In some cases, the entire record is replaced, in other cases only individual fields of a record are updated.

Database Update Functionality

FME functionality to define updates depends on the format used. Many formats have been harmonized to provide a consistent user experience, while others have not. This means there are two basic sets of functionality available.

• Simplified, Harmonized Dialogs: Cover most of the major formats
• FME Format Attributes: For formats without a harmonized interface

For more information on basic update functionality, including the differences between harmonized dialogs and format attributes, see the article An Introduction to Incremental Database Updates.

Database Update Methodology

The methods used within an FME workspace depend not just on the database format, but also on the source of the updates and what type of update is required.

FME can update all data in a database or all data in a specific table; it can also carry out updates on single features while inserting or deleting others.

FME can also be used to detect changes between two source datasets, writing the differences as updates to the database.

The following examples illustrate different combinations of functionality and methodology.

Database Access

Safe Software has provided a PostgreSQL training base to read data from within FME. However, this database is read-only. This entire tutorial series needs the ability to write to PostgreSQL (PostGIS). Please follow the instructions in Creating a PostgreSQL/PostGIS Training Database with FME on how to create your own training database. 
Note: These tutorials can be completed with any database or your own personal PostgreSQL database. 
 

Articles

These examples each include a series of steps by which to learn the technique being described. Although each example is for a specific database format, the same techniques apply to any database updates carried out with FME.

Example 1: Updating a Whole Table

Converting to PostGIS: Create, Drop or Truncate a Table: Updating the entire database table is very simple; particularly when the database format is one with a simplified and harmonized interface in FME.

Example 2: Carrying out Mixed Operations

Updating Databases: Filtering Features to set Database Update Operation Type: Mixed operations are a combination of Insert, Update, and Delete commands inside a single workspace; for example, Record A needs to be deleted, while Record B needs to be modified.

Mixed operations require the ability to identify which features need which operation. This example demonstrates how to filter data depending on the operation type, and how to set the operation. It is illustrated using a harmonized format, but includes instructions on how to apply the same technique using the format attribute fme_where

Example 3: Identifying Updates with Change Detection

Updating Databases: Using Change Detection to Set Operation Type: Expanding on the workspace from Example 2, this example carries out mixed operations, but identifies the type of operation to apply using change detection methods. 

Example 4: Updating Individual Fields of a Record

Updating Databases: Updating Individual Fields of a Record: On occasion, it is necessary only to update individual fields in a particular record, not the entire record. This example illustrates how to update specific fields only. 
 

Example 5: UPSERTing Changes 

Updating a PostgreSQL Database Using UPSERT: If the format allows for it, UPSERTs can be done within FME using the ChangeDetector.