Knowledge Base



KB10811 - Connecting to a Microsoft SQL Spatial (Katmai) layer.

The TatukGIS Developer Kernel Enterprise version and desktop GIS Editor products support reading and writing the Microsoft SQL Spatial (Katmai) vector data structure, its spatial indexes and related features. (The free GIS Viewer provides read-only support.) Further to the information contained in this knowledge base item, detailed and more frequently updated information about TatukGIS support for MS SQL Spatial layer class and data structures is found in the Developer Kernel product documentation web site at: http://docs.tatukgis.com/DK11

A MSSQL Spatial layer can be opened in a TatukGIS product by opening a *.ttkls file with the name_of_layer.ttkls or, without the use of a *.ttkls file, by i) embedding all the *.ttkls file parameters into the Path property as a CRLF or '\n' delimited sting (as a new line) or ii) assigning the *.ttkls file parameters to the SQLParameters property using SQLParameters[‘KEY’] = VALUE.

The TTKLS file is a TatukGIS stub file which contains the connection settings to any given SQL database layer. A TTKLS file can be created i) manually using any text editor (such as Note Pad) or ii) using the SQL connection Wizard feature in the Editor product (refer to menu item: Layer/Add SQL Layer).

The structure of the *.ttkls file for a MSSQL Spatial layer must be:

  [TatukGIS Layer]
  Storage=Katmai
  Layer=table name
  Dialect=MSSQL
  GeometryColumn=geometry column name
  GeometryType=geometry type name (geometry,geography)
  IndexColumn=index column
  SRID=epsg code for geography type
  ForceSpatialIndex=True|False
  GeometryRelationships=Server|Client - default is Client if parameter omitted
  MetadataTable=Native|Internal - default is Native
  ADONET=ADO.NET connection string with MARS enabled
  Provider=.NET Framework data provider (InvariantName from DbProviderFactories)
  ReadOnly=True|False

For example:

  [TatukGIS Layer]
  Storage=Katmai
  Layer=Rivers
  Dialect=MSSQL
  GeometryColumn=geom
  GeometryType=geometry
  IndexColumn=GID
  ForceSpatialIndex=True
  ADONET=Integrated Security=SSPI;Persist Security Info=False;User ID="";
    Initial Catalog=GIS;Data Source=PC\SQLEXPRESS;MultipleActiveResultSets=True
  Provider=System.Data.SqlClient

This layer can also be opened by providing SQLParameters property.

OnPassword event will be fired upon connecting to a database to resolve <#user#>, <#password#> or any other <#token#> embedded into connection options.

Notes:

  • Try to use an uppercase layer name (in the meaning of Layer=xxxx). Layer name can be case sensitive on database engines.
  • The geometry column should allow null values because an empty record is reserved upon shape creation. This layer works faster when the spatial index for the layer exists.
  • If checking DE9-IM coincidence between geometry objects, take into consideration the GeometryRelationships setting.
  • To work with ADO.NET and the new SQL Server 2008 geometry types, the ttkADONET.dll and Microsoft.SqlServer.Types.dll from Microsoft SQL Server System CLR Types libraries are required for client applications.
  • MetadataTable property instructs a layer where to look for metadata info required to set up basic parameters such as the geometry column, srid, and extent. The Native option uses a format specific table, the Internal option uses the internal table (TTKGIS_GEOMETRY_COLUMNS). The same rules are applied for writing data.
  • For compatibility with older versions, the ADO property is equivalent with ADONET.
Created: June 05, 2009, Modified: February 13, 2017
follow us on Facebook  follow us on Twitter  follow us on Google +  follow us on LinkedIn  Embarcadero