Introduction
Many FME parameters exist to provide control when reading from a spatial database. These parameters can be used to control the Reader (Database) and the Feature Types (Tables) and improve the performance of database reading.
Performance
In most cases, not every feature must be read from a database; performance largely depends on the number of features being read. The fewer features that are read, the quicker the read will be, the less system resources will be used, and the faster the final translation will be. FME has a number of parameters that can be used to improve database reading performance, including the WHERE Clause, Search Envelope, and Rows to Read At A Time.
Each of these parameters can be set when the reader object is first placed through its parameters dialog or at any point afterwards by interacting with the parameters through the Navigator pane.
- WHERE Clause: Enter an SQL WHERE clause (query) to constrain the number of features returned to FME. Using a WHERE clause will use database indices, which will be much more efficient than reading an entire table into an FME Workspace and then filtering with a Tester transformer.
- Search Envelope: (Only available when using the “Oracle Spatial Object” reader) using the Set a spatial query to define a rectangular bounding box, and only the features that fall within its extents will be returned to FME. Again, this will improve performance and is more efficient than reading the table and clipping it with a Clipper transformer. Using a search envelope requires that a spatial index exists for the table, or for the view; FME can’t perform a spatial query without one. Note: An optional “Clip to Search Envelope” parameter defines whether features will be clipped where they cross the defined extents, or be allowed to pass completely where at least a part of them falls inside the extents.
- Features to Read: Improve the performance by specifying how many features are read from the database. This can help limit the time spent reading features while developing a workspace. You can also set the number of features to read per feature type.
In addition, FME also includes a transformer that can be used to improve database performance:
- FeatureReader transformer: The FeatureReader transformer will read any FME-supported format and improve performance by using the same constraints outlined above (WHERE clause, Search Envelope, Rows to Read At A Time).
Video
Step-by-step Instructions
Using a WHERE Clause
For this demonstration, we will use a WHERE Clause with the Oracle Spatial Object reader to select a neighborhood of interest from the Parks dataset and improve performance by constraining the number of parks being read from the database.
Accompanying data for Parks has been provided in the downloads section in a MapInfo MIF/MID format. This exercise will assume that the Parks data is loaded into your Oracle database prior to beginning step one.
1. Add an Oracle Spatial Object Reader
Start FME Workbench. From the Start page, select the New Workspace option.
Click on the Add Reader icon. Select ‘Oracle Spatial Object’ as the format. Select ‘add database connection’ for the dataset and connect to your Oracle database instance. If you need additional details for connecting to Oracle, refer to the Viewing and Inspecting Oracle Data article.
For our example, we will be making use of the Parks dataset. If you are following along with the FME sample datasets, then select ‘Parameters...’, and from Table List choose ‘Parks’
2. Set the WHERE Clause
The WHERE Clause can also be set from within the reader’s parameters. Set ‘WHERE Clause’:
“NEIGHBORHOODNAME” = 'Fairview'
In standard SQL, double quotation marks indicate an attribute/field name (NeighborhoodName), while single quotation marks indicate an attribute/string value (Fairview).
3. Add a File Geodatabase Writer
Click on the Add Writer icon. Select Esri Geodatabase (File Geodb Open API) as the format, browse to a location, and set a name for the dataset. Open the Feature Parameters for the Writer. Under the General tab, change the Table Name to ‘Parks’ and the Geometry to ‘geodb_polygon’. Connect the Reader and Writer.
4. Run the Workspace
Run the workspace and view the results in FME Data Inspector. Using the WHERE clause, we have constrained the parks being read from the Oracle database to the five parks appearing in the Fairview neighborhood.
Concatenated Published Parameters
A concatenated parameter is a parameter that is built of a constant string (<field>), and a user-defined value (<value>). A concatenated string can be useful in cases where you would like to limit the need for user input. This exercise will demonstrate how to use concatenated published parameters in FME, and will use the same ‘Parks' table from the previous exercise. Remember to clear the last WHERE clause from the reader feature type.
1. Add a Published Parameter
Right-click “User Parameters” in the Navigator window and choose "Manage User Parameter". When prompted, click the green plus sign and select ‘Text’. Set the name to ‘NeighborhoodName’ and the prompt to ‘NeighborhoodName:’. Uncheck the Published box so the user is not prompted, setting it as a private parameter. Set the Default Value if desired (e.g., ‘Downtown’ with single quotes. Click OK to save the user parameter.
2. Reference the WHERE Clause to a User Parameter
From the Navigator pane, expand the Oracle Spatial reader feature type Parameters, and right-click on the ‘WHERE Clause:’ Choose Create "Edit User Parameter". Set the Value of ‘WHERE Clause’ to:
"NEIGHBORHOODNAME" = $(NeighborhoodName)
Be sure to use all uppercase for column names in Oracle.
3. Run the Workspace using Run with Prompt
Set the workspace to ‘Prompt for Parameters’ under the Run menu, and publish the Neighborhood user parameter by right-clicking on “User Parameters” in the Navigator window, choosing ‘Manage User Parameter', selecting NeighborhoodName, and checking the Published box. This will prompt the user to enter a neighborhood name. Selecting the redirect option helps display outputs before writing to the Writer, especially when writing to a database.
Run the workspace. When prompted, enter a valid NeighborhoodName ('Downtown', 'West End', 'Kitsilano', 'Mount Pleasant', 'Strathcona', or 'Fairview') into the field provided. Note that single quotation marks indicate an attribute/string value and are required for attribute values.