Knowledge Base

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]
  Storage=OracleSpatial
  Dialect=ORACLE
  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
  ForceSpatialIndex=True|False
  ...
  Database=database name (TNS alias or SERVER:port/Service)
  User_Name=user name
  Password=user password
  ...

For example:

  [TatukGIS Layer]
  Storage=OracleSpatial
  Dialect=ORACLE
  Layer=DROGI
  GeometryColumn=GEOMETRY
  IndexColumn=FID
  ValidateSettings=False
  Database=SERVER/SPATIAL
  User_Name=SCOTT
  Password=TIGER

Notes:

• 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