Using the FeatureReader to Query a Geodatabase

Introduction

FME contains a number of transformers specifically designed for use with databases. These come 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.
 

ArcSDEQuerier

The ArcSDEQuerier is a transformer for issuing commands to an ArcSDE database. This transformer can issue update and delete commands, but – as mentioned – it’s better to use the SDE writer where possible. In query mode a full set of spatial interactions is available.

FeatureReader

The FeatureReader transformer can be used to read any FME-supported format of data. 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 contents of the dataset as features. In other words, it is really doing the job of a Workbench reader, but in the transformation phase of the workspace. 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 format of data may be treated as if it were a database. For example, if the initiator feature is a polygon, the FeatureReader can be made to read point features from a selected dataset, where those points fall inside the incoming polygon.

The example below will demonstrate such 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 as well as 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 non-spatial, query.
One method would be to read the entire address dataset, then filter it against the chosen Grid square. However, as demonstrated below, a more efficient way to perform the task will be 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 will provide the spatial constraint that will be used when querying against the Address Points dataset. Instead of reading in the entire Grid dataset, we will use the WHERE clause functionality on the File Geodatabase reader to select a specific Grid square to act as our spatial constraint for our Address Points feature class. Such functionality can provide efficiency and performance to the workflow.


In the parameters, click on the ellipsis next to Tables and 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 (we already selected AddressPoints), we do 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 do, 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 Visual 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 whose value is ‘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 data made available by the City of Vancouver, British Columbia, as well as fictional data created for this article.  The City of Vancouver data contains information licensed under the Open Government License - Vancouver.