span8
span4
span8
span4
Intro: Tutorial: Getting Started with Microsoft SQL Server|Previous Section: Converting from SQL Server|Next Section: Converting to SQL Server: Write to an Existing Table
Writing data into a new table in a database is not much more complex than writing out to a new file.There are a number of Feature Type writer parameters which allow users to control database writing.
Spatial Type
Microsoft SQL Server Spatial offers the ability to set a Spatial Type.Geometrysupports planar or Cartesian coordinates such as UTM,whileGeographysupports ellipsoidal data such as lat/long.More information on Spatial Type and other SQL Server Spatial Writer parameters can be found in theMSSQL Spatial Writer documentation.
Feature Operation
Updates and deletions to a database can be primarily controlled through the Writer parameter: Feature Operation.This parameter informs FME which action to implement onto the database.Its three values are: Insert,Update,and Delete.
Table Handling
Several Table Handling parameters exist to help create or update existing database tables:
Bulk Insert
Additionally,SQL Server writers have the option to setBulk Insert(from the Navigator pane).If Yes is selected,the insertion mode will be changed to batch from feature-by-feature,resulting in a greater insertion speed.However,there is reduced granularity in errors.Should a large translation fail,there will be more rows to insert when the batch fails as opposed to feature-by-feature insertion.
Downtown Vancouver public art data,read in as points.The CSV reader can be set up to create points from the x & y columns as it reads the data.
This demonstration will load the public art data in the CSV file into a new table in the database.
1.Add the CSV Reader
Start FME Workbench.On the Start Page,select New Workspace to create a blank canvas.
Drag the Downtown.csv file onto the canvas from Windows Explorer,generating a CSV reader.In the Add Reader dialog,if not correct,set CSV for the source format and Downtown.csv for the dataset.
Click the Parameters button on the Add Reader window.In the Schema Attributes,set the columns Longitude and Latitude to type x_coordinate and y_coordinate respectively.This informs the reader to create points from the CSV data as it reads the file.Click OK to close the parameters window.
In the Add Reader window,set the coordinate system to LL84.Each point will be tagged with this coordinate system as it's read.Click OK to add the Reader to the workspace.
2a.Add the SQL Server Writer
From the Writers menu,Add Writer.
Set the format to Microsoft SQL Server Spatial,and the dataset to the SQL Training Database connection established inViewing and Inspecting SQL Server data.
Open the Parameters dialog for the Reader and set the Spatial Type to Geography.Clear the contents of the Spatial Column.
If you do not have this named database connection,select the option to Embed Connection Parameters and after selecting Parameters,enter the parameters manually:
Set the Table Definition to Copy From Reader,so that the attributes from our source are copied into the SQL Server database table.
2b.Format the Writer
After adding the Writer to the canvas,open the Feature Type Properties.In the General tab,change the Table Name to Downtown_PublicArt,and enter the Table Qualifier as fmedata2016.
The Table Qualifier is the same as the Schema for the Table Listing in previous Reader parameters.It is specified per database table as one could write to multiple schemas using the same database writer.
In the Format Parameters tab,check the Table Settings parameters.As we are creating a new table in SQL Server and adding data into it,the Feature Operation should be left as Insert,and Table Handling as Create If Needed.The Spatial Type can be left as Inherit From Writer.
Click OK to close the parameter window,then connect the Reader to the Writer.
3.Run the Workspace
The SQL Server database will now have the Downtown_PublicArt table.Right-click on the Writer and select Inspect to see the geometry and attributes of the table displayed in FME Data Inspector.
To replace the entire contents of a table,use the Table Handling parameters "Drop and Create" and "Truncate Existing".
"Truncate Existing" is used when the table needs to be emptied of existing data but does not otherwise need an update to its schema or table structure."Drop and Create" is used when the table needs to be emptied and an update is made to the database schema.For example,this is used when you wish to update a table with new content and require a new column to be added to the table.
When using either of these,set the Feature Operation parameter to Insert.Setting it as Update or Delete will be of no use once the existing table has been emptied.
Continue to Part 4: Converting to SQL Server: Write to an Existing Table
Converting to SQL Server: Write to an Existing 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
Viewing and Inspecting SQL Server Data
Writing to Database Tables that contain Multiple Geometry Columns
Handling Data that contains Multiple Geometry columns
© 2019 亚搏在线Safe Software Inc |Legal