KB10779 - How to connect a TatukGIS product to an Oracle Spatial or Oracle GeoRaster map layer? What is supported?

The TatukGIS DK product (DK Enterprise 8.11 and later versions of the VCL, ActiveX, and .NET editions), TatukGIS Internet Server product (8.10.1 and later versions), and GIS Editor product (1.10.1 and later versions) directly support the Oracle Spatial/Locator (vector) and Oracle GeoRaster data formats.

This 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
   

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 SQL vector map layer. The TTKLS file for any 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 Editor product (refer to the menu item: Layer/Add SQL Layer). The connection to the ArcSDE 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
  Layer=name of the registered layer table
  Features=TABLE/VIEW NAME; if provided, will be joined with standard geometry table
  GeometryColumn=name of the column with geometry(MDSYS.SDO_GEOMETRY type)
  IndexColumn=name of the column with unique ID for the UID (ID column must be numeric type)
  UseSDOFilter=if True, SDO filter will be used
  IgnoreExtent=if True, Layer extent will not be calculated from registered data
  SRID=Projection ID used for reading and writing must be valid for SDO filter
  DATABASE=database name (TNS alias or SERVER:port/Service)
  USER_NAME=user name
  PASSWORD=user password
  SCHEMA=user schema

For example:

  [TatukGIS Layer]
  Storage=OracleSpatial
  Layer=LANDUSE
  GeometryColumn=GEOMETRY
  IndexColumn=FID
  UseSDOFilter=True
  IgnoreExtent=False
  SRID=8703
  DATABASE=SERVER:1521/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 IndexColumn is empty, then the shape fields will be read with geometry; otherwise only after request. There can also be a problem with locations.
• 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 the SRID used for export is valid for data coordinates.
• 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 use server-side spatial topological operators, execute the layer FindFirst method with proper _de9im parameter, e.g. shp := lv.FindFirst( Extent, Scope, buffer, RELATE_INTERSECT ) . Try to use predefined topological operators from the GisTopology module.
• 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. 

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 FAQ 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:
   1. Number of objects. For larger layers, a separate tablespace for the index can improve performance.
   2. 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 the 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

• 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.

Problems with reading the data

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. This problem can be resolved by turning off the spatial filter by setting the UseSDOFilter flag to False. Setting this Flag to True forces the layer to try to use a spatial filter.
2. An invalid extent. Information in the USER_SDO_GEOM_METADATA for a given table can be invalid or missing. Therefore the user can force a layer to recalculate its extent by setting IgnoreExtent to True. Setting this to False forces the layer to try to use the extent and SRID information from USER_SDO_GEOM_METADATA.

Opening an Oracle Georaster layer