FME contains a number of Workbench 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, Geodatabase is one example of such a database.
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.
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.
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.
A 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.
Grid (ESRI File Geodatabase Feature Class)
Address Points (ESRI File Geodatabase Feature Class)
In the above images, we see a visualizations of the Grid and Address Points feature classes as well as their accompanying attributes.
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
Grid feature geometry is read from the source feature class using the Esri Geodatabase (File Geodb ArcObjects) reader. 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.
Add the Esri Geodatabase (File Geodatabase ArcObjects) reader to the workspace and click on the parameters button. Choose Grid as the table to read and in the WHERE clause section of the parameters dialogue type FacetText = 'L13' (remember to include the single quotes).
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 tree in Workbench.
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 workspace and connect the Grid square reader to the Initiator port of the FeatureReader. Open the FeatureReader parameters dialogue. The Reader portion of the dialogue asks for the location of the data to query. This is the location of the Address Point data.
Next, under the Feature Types to Read section of the dialogue, click on the [...] button and select the Address Points 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 Contains from the Spatial Filter drop down menu. When complete, your FeatureReader parameters dialog should look as follows:
3. Visualize Result
Connect an Inspector transformer to the AddressPoints output port on the Feature Reader. Run the workspace. A tile of addresses that correspond to the selected Grid square should appear in the Data Inspector. Overlay the original grid data to prove the query worked correctly.
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 Data Inspector 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.
3 People are following this .