span8
span4
span8
span4
Intro: Getting Started with Oracle|Previous Section: Viewing and Inspecting Oracle Data|Next Section: Converting to Oracle: Create, Drop or Truncate a Table
Note: The above video has been filmed using FME 2016.1
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.
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.
Note: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.
In addition, FME also includes a transformer that can be used to improve database performance:
为this demonstration we will use a WHERE Clause with the Oracle Spatial Object reader to select a neighbourhood of interest from the Parks dataset, and improve performance by constraining the number of parks being read from the database.
Note: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.
为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.
A concatenated parameter is a parameter that is built of a constant string (
(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.
Continue to Part 3: Converting to Oracle: Create, Drop or Truncate a Table
Let the Database Do the Work: Reading
Performing spatial queries on database tables using the FeatureReader
Writing to an Oracle table with Foreign Keys
Converting to Oracle: Write to an Existing Table
Error truncating geodatabase table (Oracle errors ORA-29859 ORA-06512)
ORA-20092: Maximum number of grids per feature exceeded
© 2019 Safe Software Inc |Legal