TatukGIS menu

Knowledge Base

KB10779 - Connecting to Oracle Spatial and Oracle GeoRaster database layers.

TatukGIS Developer Kernel Enterprise version and desktop GIS Editor products directly support the Oracle Spatial/Locator (vector) and Oracle GeoRaster data formats and their spatial indexes. (The free GIS Viewer provides read-only support.)

The support includes:

  • Reading/writing/editing Oracle Spatial vector coverages (SDO_GEOMETRY)
  • Creation of new Oracle Spatial tables and importing data into the tables
  • Use of Locator spatial operators for server-side spatial queries (based on the DE9IM model)
  • Utilization of Oracle Spatial indexes
  • Read-only support for Oracle Georaster layers

Importing data into the Oracle Spatial based on the layer name will:

  • remove the existing table with this name and unregister the table from the spatial USER_SDO_GEOM_METADATA table
  • create a new table with this name with new columns, e.g., FID as the primary key, GEOMETRY as the geometry, and all fields from the layer structure
  • create and insert spatial data
  • register the new layer in USER_SDO_GEOM_METADATA,
  • create a spatial index ( R-TREE ), if possible

Some examples of connecting to Oracle Spatial layer are presented below. A more detailed and frequently updated description of TatukGIS support for Oracle Spatial layer class and data structures is found in the Developer Kernel product documentation web site at: http://docs.tatukgis.com/DK11

Opening an Oracle Spatial layer

The Oracle Spatial layer can be opened by opening a *.TTKLS file with the name_of_layer.ttkls. TTKLS is a TatukGIS stub file containing the connection settings to a database map layer. The TTKLS file for any database layer 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 the help file for menu item: Layer/Add SQL Layer). The connection to the database layer can also be established without the use of 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.

The structure of the *.ttkls file for an Oracle Spatial layer must be:

  [TatukGIS Layer]
  Layer=layer table name 
  Features=TABLE/VIEW NAME; if provided, will be joined with standard table
  GeometryColumn=geometry column name (MDSYS.SDO_GEOMETRY)
  IndexColumn=unique ID column name of the UID (must be numeric type)
  Srid=Custom protection ID, use for writing, must exist in MDSYS.CS_SRS
  GeometryRelationships=Server|Client - default is server if parameter is omitted
  ValidateSettings=True|False - default is True, validates the above parameters
  MetadataTable=Native|Internal - default is Native
  Database=database name (TNS alias or SERVER:port/Service)
  User_Name=user name
  Password=user password

For example:

  [TatukGIS Layer]


  • The Oracle client driver must be installed on the local computer. The Oracle client can be downloaded from the Oracle web site. However, TatukGIS recommends using Oracle Instant Client from: http://www.oracle.com/technology/tech/oci/instantclient/index.html.
  • When importing a new layer into Oracle Spatial, ensure that the TableName and layer name are valid (first char is a letter, etc., as per the Oracle requirements).
  • If the GeometryColumn is empty, then the layer will try to detect a valid column from USER_SDO_GEOM_METADATA.
  • If IndexColumn is empty, then the layer will try to detect a valid column with the index assigned.
  • Ensure that a spatial index for any given geometry table column exists and is valid.
  • Ensure that a user table is registered in USER_SDO_GEOM_METADATA.
  • Ensure that table columns allow inserting null values that are used upon inserting a reserved UID record for a new shape.
  • If you check DE9-IM coincidence between geometry objects, take into consideration the setting of the GeometryRelationships property. Default value is Server (decreases the number of returned rows).
  • To use server-side spatial topological operators, execute the layer FindFirst method with proper _de9im<.i> parameter, e.g., shp := lv.FindFirst( Extent, Scope, buffer, RELATE_INTERSECT). Try to use predefined topological operators from the GisTopology module.
  • ValidateSettings=True forces validation of the IndexColumn ,GeometryColumn, and the spatial index. Otherwise parameters are fully trusted and the layer will open faster.
  • Srid allows for setting a column projection ID, defined in MDSYS.CS_SRS, that will be used upon writing instead of a layer EPSG.
  • User can modify the amount of returned shapes by setting a layer SmartSize property, which controls min_resolution value in SDO_FILTER (if a shape minimum bounding box size in current zoom is smaller, then the shape will not be returned).
  • MetadataTable property instructs a layer where to look for metadata info required to set up basic parameters such as geometry column, srid, and extent. The Native option will use a format specific table(USER_SDO_GEOM_METADATA). The Internal option will use use the internal table (TTKGIS_GEOMETRY_COLUMNS). The same rules will be applied for writing, except when importing a new layer which requires a registration in USER_SDO_GEOM_METADATA to create a spatial index.
  • The Scope property, e.g., Scope=country_code='POL', can be used to limit the amount of data read from big datasets. This can be set in a layer configuration .ini file or via the code.
  • To join a view (external attributes data) to a layer, set the Features property in the .ttkls file, e.g. Features=view_my_table. The join requires that a column with the name 'IndexColumn' exists in the view.
  • If a layer is based on a view, it cannot check if the spatial index exists and disables spatial queries. ForceSpatialIndex=True forces the layer to use the spatial index.

Exporting data into an Oracle Spatial layer

  1. Open the subject vector layer in the Editor.
  2. Prepare and create a *.ttkls file based on the information above. Ensure all the restrictions from the Notes section of this KB item are fulfilled.
  3. Use the Editor's Layer\Export... menu to select the Layer Export tool and choose the proper export attributes (extent, query, shape type). Then click the [OK] button.
  4. Change the file type to the extension *.ttkls, choose the already prepared .ttkls export file, and click on the [Save] button. Confirm the file replacement if necessary.
  5. If the configuration is valid, the export will begin and a progress bar notification will be visible. The successful completion of the export operation will be confirmed by an information message box.
  6. To test the exported data, the same configuration file can be used to open the layer with the Editor.

About performance

  1. It is assumed that the database (server) is well configured with regard to software and hardware (with memory allocation for Oracle server and buffer cache).
  2. Speed of layer loading depends on:
    • Number of objects. For larger layers, a separate tablespace for the index can improve performance.
    • Spatial index. If there is no spatial index, operations will be much slower (because all objects are read and the scope filtered internally). The option ‘Use spatial index’ can be used to switch on/off usage of the spatial index for selections. (For use of SDO_FILTER, the spatial index is required!)
  3. Metadata index (primary key). Very important is the 'IndexColumn', which describes the column that will be used as the primary key and unique identifier for shapes and their additional information. If there is no such field, then the application will try to use the ‘rownum’ column instead and will load all metadata into memory. Therefore, reading of data is much slower without this field

Troubleshooting problems with the Oracle Spatial connection

The native support for Oracle Spatial/Locator requires setting some Windows Environment variables. These variables tell the DK where the Oracle client binaries, libraries, and tnsnames.ora information can be found. The more important Environment variables are:

  • ORACLE_HOME - "oracle client home directory"
  • ORACLE_SID - "default database SID from tnsnames.ora"
  • TNS_ADMIN - "folder where the tnsnames.ora exists"

Visit the http://www.ora-code.com/ site for help with problem resolution or contact your Oracle database administrator for assistance.

Troubleshooting problems with functionality

Oracle Spatial is a spatial cartridge for the Oracle database. All Oracle databases include the Oracle Spatial/Locator cartridge, which is a subset of the full Oracle Spatial feature set. The differences between Oracle Spatial/Locator and full Oracle Spatial are described at: http://www.orafaq.com/faq/spatial.

Troubleshooting problems with reading the data

Because the spatial data can come from different vendors and generated by applications, there can appear problems with properly reading the data due to:

  1. No spatial index. The TatukGIS Oracle Spatial support works with or without the presence of a spatial index. The TatukGIS software tries to test if a spatial index is available for the spatial operators which require a spatial index. Because the geometry can sometimes be invalid, the spatial index can be incorrect and return incorrect data to the spatial operators.
  2. An invalid extent. Information in the USER_SDO_GEOM_METADATA for a given table can be invalid or missing.

Opening an Oracle Georaster layer

The DK Spatial Database Extensions (for 9.0 and later versions of the VCL, ActiveX, and .NET editions) includes reading support (for opening and display) for the Oracle Georaster layer format. The TatukGIS Internet Server (9.0 and later versions) and TatukGIS Editor (2.0 and later versions) also support reading Oracle Georaster map layers.

The connection from the TatukGIS product to the Oracle Georaster layer is performed by opening a *.ttkps stub file with the name_of_layer.ttkps. As noted above, the *ttkps file can be created using any text editor (such as Note Pad.)

The structure of the *.ttkps file must be:

  [TatukGIS Layer]

For example:

  [TatukGIS Layer]


Created: November 16, 2007, Modified: February 13, 2017