Files
Introduction
FME includes a number of transformers specifically designed for database use. These fall under the workflows category in the Transformer Gallery. Such transformers are commonly used to query a database, but can also be used to dispatch updates and insertions or perform spatial relationships with data residing in a database.
For our purposes, a geodatabase is an example of such a database.
Requirements
There are no Esri licensing requirements for this tutorial. Although the demonstration shows the Esri Geodatabase (File Geodb) reader, you can use the Esri Geodatabase (File Geodb Open API) reader/writer instead. For more information on required ArcGIS license levels, please see Required ArcGIS License Types for FME Geodatabase Formats.
Video
Why use a Transformer?
Transformers are sometimes preferable to using a writer to carry out updates because you may wish to only apply a change to a small subset of data, or you may wish to use a special WHERE clause that isn’t available when you choose a writer's UPDATE mode. However, unless you need these functions for a specific reason, you should use a writer instead.
FeatureReader
The FeatureReader transformer can read any FME-supported data format. The first use of this transformer is to simply read a dataset, just like a reader. The transformer is initiated by an incoming feature to read an existing dataset. It then returns the dataset's contents as features. In other words, it is effectively acting as a Workbench reader during the workspace's transformation phase. The initiator feature(s) can come from a reader or from a Creator transformer.
The second role of this transformer is to carry out spatial and non-spatial queries on the data being read. In this way, any data format can be treated as a database. For example, if the initiator feature is a polygon, the FeatureReader can be configured to read point features from a selected dataset whose points fall within the incoming polygon.
The example below demonstrates this functionality.
Source Data
Grid (Esri File Geodatabase Feature Class)
Address Points (Esri File Geodatabase Feature Class)
In the above images, we see a visualization of the Grid and Address Points feature classes, along with their accompanying attributes.
Step-by-step Instructions
The task here is to read all Addresses within a specified city grid square. Because the addresses do not have a city grid cross-reference, this will have to be done with a spatial rather than a non-spatial query.
One method would be to read the entire address dataset, then filter it against the chosen Grid square. However, as shown below, a more efficient approach is to use a FeatureReader transformer.
1. Read Grid Data
Open FME Workbench and start a blank workspace. Add an Esri Geodatabase (File Geodb) reader to the canvas and browse to the Database_Transformers.gdb dataset, which you can download from the Files section of this article. Then open the Parameters.
The Grid feature provides the spatial constraint used when querying the Address Points dataset. Instead of reading the entire Grid dataset, we will use the WHERE clause in the File Geodatabase reader to select a specific Grid square as our spatial constraint for the Address Points feature class. Such functionality can improve the workflow's efficiency and performance.
In the parameters, click the ellipsis next to Tables, then select Grid. Then, for the WHERE Clause, type in the following (remember to include the single quotes):
FacetText = 'L13' Click OK twice to finish adding the reader. The WHERE clause will query the underlying File Geodatabase and return only the Grid square that matches the query. It should be noted that the WHERE clause can be adjusted after adding the reader via the Navigation window.
2. Read Address Points
Address feature geometry is read from the source feature class using the FeatureReader transformer. The Grid square selected in Step 1 will act as the initiating feature and the spatial query for the FeatureReader.
Add a FeatureReader transformer to the canvas and connect the Grid reader feature type to the Initiator input port of the FeatureReader. In the FeatureReader parameters, set the Format to File Geodatabase (File Geodb) and the dataset to the Database_Transformers.gdb.
Next, click the ellipsis next to Feature Types to Read and select the AddressPoints feature class. While the WHERE Clause can be left empty in this instance (since we already selected AddressPoints), we still need to select a spatial interaction.
Select Initiator OGC - Contains Result from the Spatial Filter drop-down menu. For an example of what each of the Spatial Filters does, see the documentation.
3. Visualize Result
Let’s view the data to confirm that we selected the correct spatial filter. Connect an Inspector to the AddressPoints output port on the FeatureReader. Let’s also add an Inspector to the Grid reader feature type so we can overlay the two outputs.
Run the workspace. In either Data Preview or the Data Inspector, you’ll see all of the AddressPoints contained within the selected L13 Grid square.
Advanced Task
Some addresses in the dataset have a Status field with the value ‘Retired’, indicating an address that is no longer valid. You can see this in the Visual Preview table view:
Use the FeatureReader WHERE clause to filter out addresses with this status.
Make a note of feature counts for your chosen grid square with and without the WHERE clause. This will help confirm the query is operating correctly. For example, square L13 should have 307 addresses in total or 278 when excluding retired addresses. See the attached FeatureReaderToQueryAGeodatabase_Advanced.fmwt for the correct answer. For more information on writing WHERE clauses, see the W3Schools tutorial.
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.