Microsoft SQL Server Spatial

Read and Write Support

Read	Write		FME Required
Read	Commit	Copy To	FME Required

Data Support

Data	Support	Notes
Non-Spatial		For Non-Spatial data, use the Microsoft SQL Server data store.
2D
Measured
Heighted
3D		Note: Support for 3D is in BETA when using this data store. For more information about 3D data in 1Integrate, please refer to 3D Support.

Key Information

This format is only available on Windows.
The Microsoft SQL Server 2012 Native Client must be installed.

Input and Output Parameters

Input Parameters

Parameter	Description
Source Database	The name of the SQL database. The database must exist in the RDBMS.
Coordinate Reference System	This parameter can be used to specify a Coordinate Reference System (CRS) code of the input data, if it cannot be deduced automatically. This will not re-project the data, but allows its current CRS to be defined (e.g to enable multiple data stores to open where one does not have a CRS defined). Note: Coordinate Reference System parameters are required in a session that will write data to Oracle. See Coordinate Reference System for more information on when this is required.
Import FME Log File	Download the FME log file created during import (to help identify any errors).
Allow invalid geometries	Allows features with invalid geometries (e.g. duplicate vertices, self-intersections) to be imported. This is necessary if they need to be imported in order to be validated or corrected. If not ticked then features with invalid geometries are not imported and are logged as feature errors in the open data task.
Enable full 3D Geometries (BETA)	Select to enable full 3D geometries. Note: This parameter will only appear with an additional license enabling 3D support within 1Integrate. For more information on full 3D in 1Integrate, please see 3D Support.
Fix ring direction and inclusion errors	Polygon rings are assumed to be encoded using the right-hand rule, i.e. outer rings are anti-clockwise and inner rings are clockwise. If the service does not guarantee that encoding, then set this flag to have polygon rings automatically oriented on import to avoid later problems when processing the data. Note: This requires a small amount of additional processing so only set if the ring direction is not guaranteed.
Reverse coordinate axis order (y,x)	Reverse the x and y co-ordinate values.
Command timeout	The timeout, in seconds, after which to terminate a query to the database if it has not yet returned a result. If set to zero, there is no timeout.
Retrieve all schemas	Must be checked to allow import schema refresh.
Import host name	The host name of the Microsoft SQL Server.
Import username	The username of the Microsoft SQL Server.
Import password	The password of the Microsoft SQL Server.
WHERE clause	Define a WHERE clause to limit the rows read by the reader from each table (e.g. LENGTH > 2000 would select only the features where the length is greater than 2000).
Clip to envelope	If selected, causes features that overlap the bounding box to be clipped and the outer part discarded. Note: This should only be used when a session is loading a subset of the data by defining an extent as a bounding box or polygon. If set, it causes features that overlap the extent to be clipped and the outer part discarded. If not selected then the whole geometry of features that intersect the extent will be loaded including the parts of their geometry that are outside the extent.
Read cache size	Used to determine the number of rows that are retrieved at once from the data source (10 by default).
Assume one SRID per column	If selected, assume that a single SRID (Spatial Reference ID) is used within a given column (as is common practice).
Exposed attributes	Allows the selection of format attributes to be explicitly added to the reader feature type. Enter a comma-separated list of attribute names and types that will be added to the schema features.
Input spatial type is geography (default: geometry)	If selected, geography will be used as the spatial type. If unselected, the default of geometry will be used instead.
Input spatial column name	Name of the column containing spatial data.
For X,Y,Z,M	If you have data that is recorded as X,Y,Z,M, you must opt to ignore either Z or M values by choosing to use X, Y and M or X, Y and Z only. Note: If the default is left on and X,Y,Z,M is uploaded then an exception will be thrown as 1Integrate only supports X,Y,M or X,Y,Z. When consuming data with 3 ordinates, you must also select one of the two options: Select X,Y,Z to read in heighted data. Select X,Y,M to read in measured data.
Connect using Windows Authentication	If the tick box is selected, 1Integrate will connect to the database using the details of the Windows user running the Application Server, ignoring the SQL Server username and password fields.

Output Parameters

Parameter	Description
Destination Database	The location of the data store.
Coordinate Reference System	Required if the data store will be used in a session that will write data to Oracle. This can be used to specify the CRS of the output data if it cannot be deduced from the input data. This will not re-project the data, but allows it’s current CRS to be defined. See Coordinate Reference System for more information on when this is required.
Export FME Log File	Download the FME log file created during export (to help identify any errors).
Reverse coordinate axis order (y, x)	Reverse the x and y co-ordinate values.
Command timeout	The timeout, in seconds, after which to terminate a query to the database if it has not yet returned a result. If set to zero, there is no timeout.
Export host name	The host name of the Microsoft SQL Server.
Export username	The username of the Microsoft SQL Server.
Export password	The password of the Microsoft SQL Server.
Input spatial type is geography (default: geometry)	If selected, spatial type is geography. If unselected, spatial type is geometry (the default).
Spatial column name	Specify the column to use when writing spatial data to tables.
Orient polygons	When writing geography (geodetic) data, polygons must be oriented according to the left-hand rule: outer boundaries must be counter-clockwise and inner boundaries must be clockwise. If selected, polygons will be automatically re-oriented. You may wish to disable this feature if your input polygons are known to have correct orientation.