Converting from Oracle

Introduction

A number of FME parameters exist to provide control when reading from a spatial database. Parameters can be used to control the Reader (Database), the Feature Types (Tables), and improve the performance of database reading.

Performance

In most cases not every feature is required to be read from a database, and performance is largely dependant on 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 first placing the reader object 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 that will be returned to FME. Using a WHERE clause will make use of database indices, and 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. This will again improve performance, and is more efficient than reading the entire table and clipping it with a Clipper transformer. The use of a search envelop 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 feature are read from the database. This can help limit the time spent reading in 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 uses the same constraints outlined above (WHERE clause, Search Envelope, Rows to Read At A Time) to improve performance.

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 1.

1. Add an Oracle Spatial Object reader

Start FME Workbench. From the Start page select the New Workspace option.

From the Readers menu, select Add Reader. Select ‘Oracle Spatial Object’ as the format. For the dataset, select ‘add database connection’, and Connect to your Oracle database instance. Refer to the Viewing and Inspecting Oracle Data article if you need additional details for connecting to Oracle.

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

From the Writers menu, select Add Writer. Select Esri Geodatabase (File Geodb Open API) as the format, and 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. By using the WHERE Clause we have constrained the parks being read from the Oracle database to the 5 parks that appear 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 be using the same ‘Parks' table from the previous exercise. Remember to clear the previous WHERE clause from the reader feature type.

1. Add a Published Parameter

Right-click on “User Parameters” in the Navigator window and choose 'Create User Parameter'. When prompted, choose a parameter of type ‘Text’. Set the name to ‘NeighborhoodName’ and the prompt to ‘NeighborhoodName:’. Set the Default Value if desired (e.g.) ‘Downtown’ with single quotes. Click OK to save the published parameter.

2. Convert the WHERE clause to a published parameter

From the Navigator pane, expand the Oracle Spatial reader feature type Parameters, and right-click on the ‘WHERE Clause:’ Choose Create User Parameter. Uncheck the Published box so the user is not prompted, setting it as a private parameter. Set the Value to:

"NEIGHBORHOODNAME" = $(NeighborhoodName)

This parameter makes reference to the previously published parameter called NeighborhoodName.

3. Run the Workspace using Run with Prompt

Set the workspace to ‘Run with Prompt’, and Redirect to FME Data Inspector from the Run and Writer menus respectively. This will prompt the user to enter a neighborhood name and then display the output in the Inspector. Selecting the redirect option is useful to 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.