KB10765 - Support for SQL database map layer formats.  How to connect to the database.

The TatukGIS DK (9.0 and later versions), GIS Editor (2.0 and later versions), and Internet Server (9.0 and layer versions) support the following SQL database layer formats: MSSQL Spatial Server, 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, and TatukGIS PixelStore. Except for the ArcSDE format, the support for each vector format is for both reading and writing data. With ArcSDE, the writing is limited to editing attribute information (so editing ArcSDE map geometry is not supported). Support for the ESRI ArcSDE Raster and Oracle GeoRaster formats is read-only.

The free TatukGIS Viewer product can also connect to and display each of these SQL layer types (but, of course, TatukGIS does not provide user support for the free Viewer).

The free TatukGIS Viewer product can connect to and display SQL database layers residing on the local computer (so not SQL layers residing on a separate server). Therefore, the free Viewer supports: OpenGIS (OGC) Simple Features Implementation for SQL, TatukGIS SQL binary, Geomedia Access Warehouse, and TatukGIS PixelStore.

Due to the SQL nature of these formats, the connection of a TatukGIS product to a SQL database vector map layer is performed using 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 menu item: Layer/Add SQL Layer).

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' sting (as a new line) or ii) assigning the *.ttkls file parameters to the SQLParameters property using SQLParameters[‘KEY’] = VALUE.

This FAQ item provides general instructions and examples for connecting a TatukGIS product to an SQL database layer. Specific instructions for the formats listed below are provided in separate knowledge base items:


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

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

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 Geodatase (PGDB) file

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


The procedure to create *.ttkls connector files to any of the other supported SQL layer types is very similar. Just replace the storage value with the token corresponding to the format, such as:

Storage= 'Native', 'OpenGisBlob', 'OpenGisNormalized', 'GeoMedia', 'PersonalGdb', 'PostGIS', 'OracleSpatial', 'OracleGeoraster', 'SdeBinary', or 'Katmai', and so forth, whereas:

'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


TTKLS Configuration Settings

The correct Dialect and Storage type settings must be considered when configuring a *.ttkls file.

• Dialect: Specifies the SQL database product.
• Storage type: Specifies the format of the data.
  
  The Dialect options are:
  
    GIS_SQL_DIALECT_NAME_MSJET = 'MSJET' ;
    GIS_SQL_DIALECT_NAME_MSSQL = 'MSSQL' ;
    GIS_SQL_DIALECT_NAME_INTERBASE = 'INTERBASE' ;
    GIS_SQL_DIALECT_NAME_MYSQL = 'MYSQL' ;
    GIS_SQL_DIALECT_NAME_DB2 = 'DB2' ;
    GIS_SQL_DIALECT_NAME_SYBASE = 'SYBASE' ;
    GIS_SQL_DIALECT_NAME_ORACLE = 'ORACLE' ;
    GIS_SQL_DIALECT_NAME_PROGRESS = 'PROGRESS' ;
    GIS_SQL_DIALECT_NAME_INFORMIX = 'INFORMIX' ;
    GIS_SQL_DIALECT_NAME_ADVANTAGE = 'ADVANTAGE' ;
    GIS_SQL_DIALECT_NAME_SAPDB = 'SAPDB' ;
    GIS_SQL_DIALECT_NAME_POSTGRESQL = 'POSTGRESQL' ;
    GIS_SQL_DIALECT_NAME_FLASHFILER = 'FLASHFILER' ;
    GIS_SQL_DIALECT_NAME_NEXUSDB = 'NEXUSDB' ;
    GIS_SQL_DIALECT_NAME_BLACKFISHSQL = 'BLACKFISHSQL' ;
  
  
  The storage type options are:
  
    GIS_INI_LAYERSQL_OPENGIS = 'OpenGIS' ;
    GIS_INI_LAYERSQL_NATIVE = 'Native' ;
    GIS_INI_LAYERSQL_OPENGISBLOB = 'OpenGisBlob' ;
    GIS_INI_LAYERSQL_OPENGISBLOB2 = 'OpenGisBlob2' ;
    GIS_INI_LAYERSQL_OPENGISNORMALIZED = 'OpenGisNormalized' ;
    GIS_INI_LAYERSQL_OPENGISNORMALIZED2 = 'OpenGisNormalized2' ;
    GIS_INI_LAYERSQL_OPENGISWKT = 'OpenGisWkt' ;
    GIS_INI_LAYERSQL_OPENGISKATMAI = 'OpenGisKatmai' ;
    GIS_INI_LAYERSQL_POSTGIS = 'PostGis' ;
    GIS_INI_LAYERSQL_KATMAI = 'Katmai' ;
    GIS_INI_LAYERSQL_GEOMEDIA = 'Geomedia' ;
    GIS_INI_LAYERSQL_PGDB = 'PersonalGdb' ;
    GIS_INI_LAYERSQL_SDEBINARY = 'SdeBinary' ;
    GIS_INI_LAYERSQL_SDEOGCWKB = 'SdeOgcWkb' ;
    GIS_INI_LAYERSQL_ORACLESPATIAL = 'OracleSpatial' ;
    GIS_INI_LAYERSQL_ORACLEGEORASTER = 'OracleGeoraster' ;
    GIS_INI_LAYERSQL_SDERASTER = 'SdeRaster' ;
    GIS_INI_LAYERSQL_PIXELSTORE2 = 'PixelStore2' ;
  
  
  To create a new database layer:
  
   
• create a *.ttkls file (in the Windows O/S) to connect to the data, or
• manipulate layer.SQLParams directly
  
  Refer to knowledge item item KB10418 for more about SQLParams and about how to bypass the *.ttkls file.
  
  Keep in mind that the SQL layer format and the SQL database product must be compatible. The TatukGIS binary 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.