span8
span4
Intro: Getting Started with Microsoft SQL Server|Previous Section: Viewing and Inspecting SQL Server Data|Next Section: Converting to SQL Server: Create,Drop or Truncate a Table
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:
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.
Downloads
Final Advanced Workspace as a Template
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 inViewing 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:
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 > Format).To it access through the workspace canvas,open the Feature Type Properties and select the Format Parameters tab.Set the WHERE clause to:
"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 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.Under the General tab,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.
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 (
1.Add a Published Parameter
From the Navigator pane,right-click on "User Parameters" and choose Add 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.
Continue to Part 3: Converting to SQL Server: Create,Drop or Truncate a Table
Converting from SQL Server to MapInfo (Non-Spatial to Spatial)
Performing spatial queries on database tables using the FeatureReader
Tutorial: Getting Started with Microsoft SQL Server
Converting to SQL Server: Create,Drop or Truncate a Table
Viewing and Inspecting SQL Server Data
Converting to SQL Server: Write to an Existing Table
FME is loading features with invalid geometries into my Database
© 2019 亚搏在线Safe Software Inc |Legal