Knowledge Base

The TatukGIS Developer Kernel, desktop GIS Editor, and free GIS Viewer (read-only) support the many SQL layer and spatial database formats listed in knowledge base item KB10639. Detailed and more regularly updated information about support for each format is provided in the documentation wiki.

Support for each database layer format is direct, without use of 3rd party software or middleware, and includes:

• Reading/writing/editing vector map layer geometry and attributes.
• Creation of new tables and importing data into the tables.
• Use of spatial operators for server-side spatial queries based on the DE-9IM model (spatial formats only).
• Utilization of server side spatial indexes for fast queries (spatial formats only).

Support for ArcSDE includes:

• Reading/writing/editing ArcSDE Geodatabase SQL vector map layer geometry and attributes.
• Direct ArcSDE SQL layer access, bypassing ArcSDE services and independent of any ESRI middleware.
• Utilization of ArcSDE spatial indexes for fast spatial queries.
• Supported databases: MSSQL Server, Oracle, and other SQL databases compatible with the ArcSDE standard.

Due to the SQL nature of these formats, the connection of a TatukGIS product to a database layer requires a stub file with the connection settings, e.g., database engine type, SQL data storage type (format), ADO driver, layer name. This stub file for TatukGIS products is called a *.TTKLS file. A *.TTKLS file for any supported layer can be created i) manually using any text editor (such as Note Pad) or ii) using the SQL connection Wizard feature in the TatukGIS Editor product (refer to the help file for the Editor Layer/Add SQL Layer menu item and to the on-line Editor/Viewer tutorials). It is particularly easy using the Editor Layer/Export or Layer/New menu commands to create a new OpenGIS or TatukGIS native SQL layer in a local SQLite or MS Access database (refer to the help files for these menu items).

It is also possible to connect to an database layer without using a *.ttkls file by i) embedding all the *.ttkls file parameters into the Path property as a CRLF or '\n' string (as a new line) or ii) assigning the *.ttkls file parameters to the SQLParameters property using SQLParameters[‘KEY’] = VALUE.

This knowledge base item provides general instructions and examples for connecting a TatukGIS product to database layers. The knowledge base items listed below provide additional instructions for specific database layer formats:

• ESRI ArcSDE and ESRI ArcSDE Raster: KB10783
• Oracle Spatial and Oracle GeoRaster: KB10779
• Microsoft SQL Spatial (Katmai): KB10811
• OpenGIS PostGIS: KB10810
• TatukGIS PixelStore (raster): KB10649

Some TTKLS examples are provided below. These examples assume database connection using the ADO interface. (Connection using the DBX interface requires skills of a specialist.)

Example 1: Connect to a Geomedia SQL Server file on MSSQL database

1. Create an ODBC connection (within the Microsoft Windows O/S) to your data
2. Create a yourname.ttkls text file
     [TatukGIS Layer]
  Storage=Geomedia
  LAYER= name_of_the_layer
  DIALECT=MSSQL
  ADO=YOUR_DSN_NAME
3. Open yourname.ttkls 

Example 2: Connect to a Geomedia Access (MDB) Warehouse file on MS Access database

1. Create a yourname.ttkls text file
     [TatukGIS Layer]
  Storage=Geomedia
  LAYER=name_of_the_layer
  DIALECT=MSJET
  ADO=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_database.mdb
2. Open yourname.ttkls

Example 3: Connect to an ESRI Personal Geodatabase (PGDB) file on MS Access database

1. Create a yourname.ttkls text file
     [TatukGIS Layer]
  Storage=PersonalGdb
  LAYER=name_of_the_layer
  DIALECT=MSJET
  ADO=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_database.mdb
2. Open yourname.ttkls

Example 4: Connect to a TatukGIS Native (binary) SQL file on SQLite database

1. Create a yourname.ttkls text file
     [TatukGIS Layer]
  Storage=Native
  Layer=Rivers
  Dialect=SQLITE
  Sqlite=gistest.sqlite
2. Open yourname.ttkls

Example 5: Connect to a TatukGIS PixelStore file on SQLite database

1. Create a yourname.ttkls text file
     [TatukGIS Layer]
  Storage= PixelStore2
  Layer=ORTO
  Dialect=SQLITE
  Sqlite=pixelstore.sqlite
2. Open yourname.ttkls 

TTKLS Configuration Settings

The procedure is very similar to create *.ttkls connector files to other supported SQL layer types held in a compatible database engine. Replace the Storage value with the token corresponding to the layer type and the Dialect value with the token corresponding to the database engine. The following is a partial list of supported database layer types and database engines. Refer to the help file for the full list and complete instructions.

  Storage=Native|OpenGisBlob|OpenGisNormalized|OpenGisWkt|PostGIS|GeoMedia|Katmai|OracleSpatial|
          OracleGeoraster|PersonalGdb|SdeBinary|SdeOgcWkkb|SdeRaster|PixelStore2|SpatialWare|
          DB2SpatialExtender|IfxSpatialDataBlade

  Dialect=MSJET|MSSQL|INTERBASE|MYSQL|DB2|SYBASE|ORACLE|PROGRESS|INFORMIX|
          ADVANTAGE|SAPDB|POSTGRESQL|FLASHFILER|NEXUSDB|BLACKFISH|SQLITE

• Native
  corresponds to the TatukGIS binary proprietary format.
• OpenGisBlob
  OpenGIS (OGC) WKB format.
• OpenGisNormalized
  OpenGIS WKT format (but with the x and y coordinates organized into separate database columns).
• OpenGisWkt
  OpenGIS WKT format (but with the x and y coordinates in one column).
• OpenGisKatmai
  OpenGIS tables structure with geometry column kept as MSSQL 2008 spatial geometry type.
• Katmai
  MSSQL 2008 Spatial format.
• PersonalGdb
  ESRI Personal Geodatabase format for Access.
• SdeBinary
  ArcSDE binary geometry type.
• SdeOgcWkb
  ArcSDE WKB geometry type.
• DB2SpatialExtender
  IBM DB2 Spatial Extender data type.
• IfxSpatialDataBlade
  IBM Informix Spatial DataBlade data type.

To create a new database layer:

1. Create a *.ttkls file (in the Windows O/S) to connect to the data, or
2. manipulate layer.SQLParams directly; refer to knowledge item KB10418 for more about SQLParams and about how to bypass the *.ttkls file.
3. Keep in mind that the SQL layer format and the database engine (product) must be compatible. The TatukGISbinary format is compatible with all TatukGIS supported database products, but other formats are not. Knowledge base item KB10639 presents a matrix of supported SQL and spatial database layer formats with compatible database engines.

Notes for SQLite

• SQLite database is appropriate for the TatukGIS 'Native' and 'OpenGIS' SQL vector layer formats and the TatukGIS 'PixelStore' raster layer format when stored on the local computer.
• Try to use an upper case layer name (in the meaning of Layer=xxxx), because the layer name can be case insensitive on different database engines.
• To use a global transaction during the import, add ENGINEOPTIONS=16 parameter to ttkls file. This should speed up the process a lot by limiting journaling operations.
• If a SQLite client supports R-tree extension, the layer will try to build and use the rtree index.
• If a SQLite client supports encryption, a layer will pass a key from Password to a database in the 'PRAGMA key' format command.
• Use the PRAGMA statement in a *.ttkls file to modify the operation of the SQLite library. Statements such as 'synchronous' or 'journal_mode' may improve the performance.