KB10765 - Support for SQL database map layer formats.

The TatukGIS DK, GIS Editor, and Internet Server products support the following SQL database layer formats: Microsoft SQL Spatial Server (Katmai), Oracle Spatial/Locator & Oracle GeoRaster, PostGIS, ESRI ArcSDE & ESRI SDE Raster, ESRI Personal Geodatabase (PGDB), Geomedia Access Warehouse & Geomedia SQL Server, OpenGIS (OGC) Simple Features Implementation for SQL, TatukGIS SQL binary, SQLite Spatial, and TatukGIS PixelStore. A matrix presenting each supported SQL based format with its compatible database engine(s) is presented in knowledge base item KB10639.

The support is read/write for the vector and PixelStore formats and read-only for the ESRI ArcSDE Raster and Oracle GeoRaster formats. The free TatukGIS Viewer product (user support not provided) can also display each supported SQL layer type.

Due to the SQL nature of these formats, the connection of a TatukGIS product to a SQL database vector map layer is performed using a stub file that contains 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 SQL vector 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 also possible to connect to an SQL 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.

While this knowledge base item provides general instructions and examples for connecting a TatukGIS product to SQL database layers, the knowledge base items listed below provide instructions for specific SQL based formats:

• ESRI ArcSDE and ArcSDE Raster:  KB10783
• Oracle Spatial and Oracle GeoRaster:  KB10779
• MSSQL Spatial (Katmai):  KB10811
• PostGIS: KB10810
• PixelStore:  KB10649

Some TTKLS examples are provided below. These examples assume that the database connection uses the ADO interface. (Connecting via the DBX interface requires the skills of a specialist.)

Example 1
 

Connecting 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 similiar to create *.ttkls connector files to other supported SQL based layer types held in a compatible database product. Replace the Storage value with the token corresponding to the layer type and the Dialect value with the token corresponding to the database product. The following is a partial list of supported SQL 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 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 SQL database product must be compatible. The TatukGISbinary format is compatible with all TatukGIS supported database products, but other formats are not. For example, the OpenGIS SQL format is not compatible with PostgreSQL database, the Geomedia Access Warehouse format works only with MS JET (Access), the Geomedia SQL Server format works only with MSSQL Server, ArcSDE works only with MSSQL Server or Oracle, etc.  

Notes for SQLite

• SQLite database is appropriate for the TatukGIS 'Native' and 'OpenGIS' SQL vector layer formats and for the TatukGIS 'PixelStore' raster layer format 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.