Converting from SQL Server

Introduction

With FME, reading from a database is comparable to reading from a file-based dataset. Various FME parameters exist to provide control when reading a spatial database. These parameters can control the Reader (Database) and the Feature Types (Tables), to improve the performance of database reading. When reading from a database, frequently not every feature in every table is required, the fewer features that are read from the database, the quicker the read will be, the less system resources are used, and the faster the overall translation will be.

The following may improve Microsoft SQL Server database reading performance:

• Command Timeout: The length of time, in seconds, for the database to return a result before termination. If set to 0, there will be no timeout. The default value is 30 seconds.
• WHERE Clause: Specifies a query, so that only features that pass the query will be returned to FME. This employs database query tools, which in turn make use of database indices, and is a lot more efficient than reading an entire table and then filtering it with a Tester transformer.
• Number of Records to Fetch At A Time: Defines how many records will be fetched at a time from the database. Too low a number and FME will spend excessive time making read requests. Too high a number and database performance might be slowed down for other users. The number of records is set in the Navigator window under the Reader parameters, the default value is 10.
• Search Envelope: Set a spatial query; only features that fall inside the specified extents will be returned to FME. Again, this employs native database functionality, and is more efficient than reading the entire table and then clipping it with a Clipper transformer. 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. The limitation here is that the four parameters only define a rectangular envelope. Note that to use a search envelope requires a spatial index to exist for that table; FME can’t do a spatial query without one. If a format doesn’t support a spatial index being created on a view, then FME will not be able to do spatial queries on that view.
• FeatureReader transformer: The FeatureReader will read data, from a database, and use incoming features as the basis for a spatial or non-spatial query.

Note: The Search Envelope is only available for Microsoft SQL Server Spatial Readers, the Non-Spatial Readers will not have this option since this format does not support a spatial index.
 

Video

Step-by-Step Instructions

In this demonstration, a WHERE clause will be used to select only the park features in a chosen neighborhood of interest. The Parks table already exists in the SQL Server database and will be written to a File Geodatabase.
 

1. Add SQL Server Reader

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

From the Readers menu, select Add Reader. Select Microsoft SQL Server Spatial as the format. The dataset will be the SQL Training Database connection created in Viewing and Inspecting SQL Server data. In the Reader parameters, set the fmedata2016.Parks as the Table List.

If you cannot choose the named connection, select Embed Connection. Enter the parameters manually:

• Server: sql.fmetraining.ca
• Database: fmedata
• Authentication: SQL Server Authentication
• Username: fmedata
• Password: SQLtraining2016
• Table List: fmedata2016.Parks

2. Set the WHERE Clause

The WHERE Clause can be set in the Navigator or the workspace. It can be found in the Navigator under the SQL Training Database reader Feature Type (Parameters > WHERE Clause:). Right click and select Edit Parameter Value.


Type the following Clause into the area provided in the SQL Server Parameters dialog box.

"NeighborhoodName" = 'Fairview'

In standard SQL, double quotation marks indicate an attribute/field name (NeighborhoodName), while single quotation marks indicate an attribute/string value (Fairview).



When we inspect the data we can see in the Visual Preview that there are only parks in the Fairview neighborhood.

3. Add the File Geodatabase Writer

From the Writers menu, select Add Writer. Select Esri Geodatabase (File Geodb Open API) as the format. Browse to a location and set a file geodatabase name, such as FairviewParks.gdb, for the dataset.


Open the Feature Type Parameters of the Writer. Change the Table Name to Parks and the Geometry to geodb_polygon since Parks consists of polygon features.


Connect the Reader and Writer.

4. Run the Workspace

Maintaining the attributes from the SQL Server Reader, the File Geodatabase Writer writes 5 parks in the Fairview neighborhood to FairviewParks.gdb.
 

Advanced Option

Using Concatenated Published Parameters

An issue with the WHERE clause parameter (and similar parameters) is that it can be difficult to get user input and apply it to the clause. Publishing the parameters is not helpful since the user would have to enter the full clause (<field> = <value>). Often only the <value> part is required as input. A concatenated parameter is useful in these scenarios. It is a parameter that is built of a constant string (<field>) and a user-defined value (<value>).

1. Add a Published Parameter

From the Navigator pane, right-click on "User Parameters" and choose Create User Parameter.

In the Add/Edit User Parameter window, set the Type to Text. Enter NeighborhoodName as the name and NeighborhoodName: as the prompt. Set the Default Value as desired, single quotation marks are required for the value (i.e.- 'Fairview').

2. Convert the WHERE clause to a published parameter

From the Navigator pane, expand the MSSQL_SPATIAL reader Parameters. Right-click on the WHERE Clause, and 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 published parameter previously created 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.

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.