ArcGIS Help 10.1 - ST_Geometry storage in Oracle

The Esri ST_Geometry spatial data type can be used in Oracle databases that contain a geodatabase and those that do not. It allows you to integrate spatial data with other types of business data, so your multiuser database gains the advantage of adding a geographic component to your analyses and data products. Keeping your spatial data together with other business objects also simplifies multiuser access, management, and security of your data, because you will have to manage fewer data storage resources.

The Esri ST_Geometry spatial data type is the default geometry storage type for geodatabases in Oracle. You can also install the ST_Geometry type in Oracle databases using the Create Spatial Type.

Note:

The ST_Geometry type is not supported with Oracle XA transactions.

To create a geodatabase and use the ST_Geometry type and domain index in the Oracle DBMS, the geodatabase administrator user (sde) must be granted the proper system privileges to instantiate types, operators, and stored procedures. See User permissions for geodatabases in Oracle for information on permissions needed. To install the ST_Geometry type in an Oracle database, there must also be an sde user present, and it must be granted specific privileges to instantiate types, operators, and stored procedures. See Adding the ST_Geometry type to an Oracle database for more information.

Using the Esri ST_Geometry spatial type in a geodatabase in Oracle or an Oracle database, you can access your spatial data through SQL functions that implement the ISO SQL/MM Spatial Standard and to the Simple Feature Specification of the OGC. You can use SQL commands to store, retrieve, and manipulate spatial features as you would any other type of data. You can use a long list of standards-based functions with SQL commands and stored procedures to retrieve and analyze spatial data. Having SQL access to the data makes it possible for you to use other applications to access data that was created in Oracle.

To access spatial features with SQL, the ST_Geometry libraries must be installed on the same server as the Oracle database management system (DBMS). If you plan to run your Oracle DBMS on a server separate from an ArcSDE service, be sure the operating system of your Oracle server is supported for ArcSDE as well and that the ST_Geometry libraries are installed there.

To use SQL to access tables in Oracle that use the spatial type, you must configure the Oracle listener. See Configuring the Oracle listener to use SQL for details.

How ST_Geometry stores spatial data

The following is a description of ST_Geometry in Oracle:

Name	Type
ENTITY	NUMBER(38)
NUMPTS	NUMBER(38)
MINX	FLOAT(64)
MINY	FLOAT(64)
MAXX	FLOAT(64)
MAXY	FLOAT(64)
MINZ	FLOAT(64)
MAXZ	FLOAT(64)
MINM	FLOAT(64)
MAXM	FLOAT(64)
AREA	FLOAT(64)
LEN	FLOAT(64)
SRID	NUMBER(38)
POINTS	BLOB

The attributes of the spatial type represent the following information:

Entity: The type of geometric feature stored in the spatial column (linestring, multilinestring, multipoint, multipolygon, point, or polygon), the value of which is a bit mask derived from the st_geom_util stored procedure.
Numpts: The number of points defining the geometry; for multipart geometries, this includes the separators between each part, one point for each separator.
Minx, miny, maxx, maxy: The spatial envelope of the geometry
Area: The area of the geometry
Len: The perimeter length of the geometry
SRID: Contains the identifier for the geometry that links it to its associated spatial reference (coordinate system) record in the ST_Spatial_References table
Points: Contains the byte stream of the point coordinates that define the geometry

Like other object types, the ST_Geometry data type contains a constructor method and functions. A constructor method is a function that returns a new instance (object) of the data type and sets up the values of its attributes.

The name of the constructor is the same as the type (ST_Geometry). When you instantiate an object of the ST_Geometry type, you invoke the constructor method. For example:

CREATE TABLE hazardous_sites (name        varchar2(128),
                              location    st_geometry);

The following ST_Geometry accessor functions take a single ST_Geometry as input and return the requested property value as a number.

The ST_Area member function returns the area of a geometry.
ST_Len returns the length of a geometry.
ST_Entity returns a number containing a bit mask that describes the entity type.
ST_NumPoints returns the number of points (vertices) that define a geometry.
ST_MinM, ST_MinX, ST_MinY, and ST_MinZ return the minimum desired coordinate of a geometry.
ST_MaxM, ST_MaxX, ST_MaxY, and ST_MaxZ return the maximum desired coordinate of a geometry.
ST_SRID returns the spatial reference identifier for a geometry.
Get_release is a static member function used internally for spatial type administration (that is, upgrades and patches).

For example, the following query returns the name and area of the individual states in the United States.

SELECT name, st_area(geometry)
FROM us_states
ORDER BY name;

ST_LineString, ST_MultiLineString, ST_MultiPoint, ST_MultiPolygon, ST_Point, and ST_Polygon are all subtypes (or subclasses) of ST_Geometry. ST_Geometry and its subtypes share common attributes and functions. The constructor definition for ST_LineString, ST_MultiLineString, ST_MultiPoint, ST_MultiPolygon, ST_Point, and ST_Polygon is the same. The name of the constructor is the same as the type it constructs.

Since ST_Point is a finite object (a single point value), it can also be created using one of the following methods.

This method utilizes coordinate points and an SRID.

METHOD

 FINAL CONSTRUCTOR FUNCTION ST_POINT RETURNS SELF AS RESULT
 Argument Name                  Type                    In/Out Default?

 PT_X                           NUMBER                  IN
 PT_Y                           NUMBER                  IN
 SRID                           NUMBER                  IN

SQL> INSERT INTO sample_pt VALUES (ST_Point (10, 20, 1) );

This method allows you to specify coordinate points and an elevation value for each point.

METHOD

 FINAL CONSTRUCTOR FUNCTION ST_POINT RETURNS SELF AS RESULT
 Argument Name                  Type                    In/Out Default?
 
 PT_X                           NUMBER                  IN
 PT_Y                           NUMBER                  IN
 PT_Z                           NUMBER                  IN
 SRID                           NUMBER                  IN

SQL> INSERT INTO sample_pt VALUES (ST_Point (10, 20, 5, 1) );

This last method for ST_Point additionally allows a measure value to be specified as part of the point object created.

METHOD

 FINAL CONSTRUCTOR FUNCTION ST_POINT RETURNS SELF AS RESULT
 Argument Name                  Type                    In/Out Default?

 PT_X                           NUMBER                  IN
 PT_Y                           NUMBER                  IN
 PT_Z                           NUMBER                  IN
 MEASURE                        NUMBER                  IN
 SRID                           NUMBER                  IN

SQL> INSERT INTO sample_pt VALUES (ST_Point (10, 20, 5, 401, 1) );

Metadata schema

The ST_Geometry type for Oracle and metadata tables are owned by the SDE schema. The schema definition is the base table description for metadata tables used to define and describe the type column/table, spatial index (the ST_Spatial_Index domain index), and spatial references information. All type and domain index type definitions, packages, and metadata tables are created in the SDE schema.

Because the definitions for ST_Geometry are owned by the SDE user, never delete the SDE user from the database if you have tables in the database that contain ST_Geometry columns. Doing so causes those tables to be inaccessible.

As mentioned in the Oracle Application Developer's Guide, when a user is dropped from the database, one of the drop statements executed is DROP TYPE with the FORCE option. This statement removes all types owned by that user, so that user can be removed from the database. DROP TYPE FORCE causes types to be dropped even if they have dependent types or tables associated with them. Once that happens, the associated tables are marked invalid, rendering the data in the tables inaccessible.

See System tables of a geodatabase stored in Oracle for a description of the following ST_Geometry metadata tables:

ST_COORDINATE_SYSTEMS
ST_GEOMETRY_COLUMNS
ST_GEOMETRY_INDEX
ST_SPATIAL_REFERENCES

Dive-in:

In addition, the following database objects are created to maintain ST_Geometry metadata:

An SDE database user
A default tablespace for the SDE user
ST_Geometry functions
ST_Geometry views
- ALL_ST_GEOMETRY_COLUMNS_V
- USER_ST_GEOMETRY_COLUMNS_V
- USER_ST_GEOM_INDEX_V
ST_Geometry triggers
- DB_EV_DROP_ST_METADATA
- DB_EV_ALTER_ST_METADATA
- DB_EV_RENAME_ST_METADATA
- TG_ST_SPATIAL_REF_SRID
- TG_ST_CREF_SYS
- TG_GCOL_NAME
- TG_ST_GEOMINDEX_NAME
An SPX_UTIL package and package body
An SDEXMLTOTEXT operator
ST_Geometry types
- BLOB_ARRAY_TAB
- INT_ARRAY_TAB
- FLT_ARRAY_TAB
- BND_ROWID_TAB
- SP_GRID_INFO
  SP_Grid_Info is used as the data type for the field GRID in the table ST_Geometry_Index. It contains the grid-level information for spatial indexes.

Creating feature classes in Oracle with ST_Geometry storage

In databases that have the ST_Geometry spatial data type installed, you specify the ST_Geometry as the storage type when you create a feature class.

In geodatabases, the geometry storage type used for a feature class is determined by the GEOMETRY_STORAGE setting in the configuration keyword you specify when you create the feature class.

Setting ST_Geometry to be the default storage type for new geodatabase feature classes

Starting with ArcGIS 9.3, the ST_Geometry is the default storage type for feature classes in a new geodatabase in Oracle. That means the GEOMETRY_STORAGE parameter of the DEFAULTS keyword in the DBTUNE table is set to ST_GEOMETRY.

If you initially created your geodatabase prior to ArcGIS 9.3 and want all new feature classes to be created using ST_Geometry storage by default, you must alter the GEOMETRY_STORAGE parameter under the DEFAULTS keyword in your DBTUNE table. Set the GEOMETRY_STORAGE parameter to ST_GEOMETRY.

Note:

Use the sdedbtune administration command to alter DBTUNE settings. See Altering the contents of the DBTUNE table and the ArcSDE Administration Command Reference provided with the ArcSDE application server for Oracle for details on using the sdedbtune command.

Using ST_Geometry storage for some of your geodatabase feature classes

Geodatabases in Oracle support a number of different geometry storage types—these different types can all be used together in the same database. While there can be only one default geometry storage type, individual tables can be created using different data types.

If you want only a few of your feature classes to use the ST_Geometry spatial type storage, you can set your DEFAULTS GEOMETRY_STORAGE to a different storage type and create a separate keyword for ST_Geometry storage. For example, you could use the sdedbtune command to export the DBTUNE table to a text file, add a keyword to the file similar to the following, and import the file back to the DBTUNE table:

##ST_GEOMETRY
GEOMETRY_STORAGE    "ST_GEOMETRY"
ATTRIBUTE_BINARY    "BLOB"
RASTER_STORAGE	    "BLOB"
ST_GEOM_LOB_STORAGE  " STORE AS (
#               TABLESPACE <tablespace_name>
                ENABLE STORAGE IN ROW CHUNK 8K RETENTION CACHE)"

UI_TEXT             "User Interface text description for ST_GEOMETRY"

COMMENT             "Any general comment for ST_GEOMETRY keyword"

END

Once the keyword has been added, you can use it when you create feature classes for which you want to use ST_Geometry data type storage. For example, if you have a small subset of data that must be accessed using SQL, you could create just that subset of feature classes to use the ST_Geometry spatial data type for geometry storage.

What database objects are created for user data?

There are three database objects created when you use ArcGIS to create a feature class that uses ST_Geometry storage. These objects and the DBTUNE parameters used to control their storage are listed in the following table:

Database objects	Storage parameters
A table with an ST_Geometry column	The B_STORAGE parameter defines the storage for the table. The ST_GEOM_LOB_STORAGE parameter defines storage for LOB segments in the table.
A spatial index	The S_STORAGE parameter defines spatial index storage.
An index on the ObjectID column	The B_INDEX_ROWID parameter defines storage for this index.

Database objects

Storage parameters

A table with an ST_Geometry column

The B_STORAGE parameter defines the storage for the table.

The ST_GEOM_LOB_STORAGE parameter defines storage for LOB segments in the table.

A spatial index

The S_STORAGE parameter defines spatial index storage.

An index on the ObjectID column

The B_INDEX_ROWID parameter defines storage for this index.

For more information on configuration parameters, see Oracle DBTUNE configuration parameters and the following section on LOB storage.

Setting storage for the LOB segment

You should alter the ST_GEOM_LOB_STORAGE parameter under the DEFAULTS keyword list. When added to the DEFAULTS keyword, however, the LOB segment name should not be included in this parameter's definition. If it is included, unless you alter the value for the name, when you create a second feature it fails because each LOB segment name must be unique for a given schema. The following ST_GEOM_LOB_STORAGE parameter example contains no object names, thereby avoiding name collisions within the same schema:

ST_GEOM_LOB_STORAGE  " STORE AS (
  ENABLE STORAGE IN ROW CHUNK 8K RETENTION CACHE)"

Examples of valid values for the ST_GEOM_LOB_STORAGE parameter include the following:

##ST_GEOMETRY
GEOMETRY_STORAGE    "ST_GEOMETRY"
ATTRIBUTE_BINARY    "BLOB"
RASTER_STORAGE	    "BLOB"
ST_GEOM_LOB_STORAGE  " STORE AS (TABLESPACE TERRA_NDX ENABLE STORAGE IN ROW CHUNK 8K
 RETENTION CACHE)"

UI_TEXT             "User Interface text description for ST_GEOMETRY"

COMMENT             "Any general comment for ST_GEOMETRY keyword"

END

##ST_GEOMETRY
GEOMETRY_STORAGE    "ST_GEOMETRY"
ATTRIBUTE_BINARY    "BLOB"
RASTER_STORAGE	    "BLOB"
ST_GEOM_LOB_STORAGE  " STORE AS (ENABLE STORAGE IN ROW CHUNK 8K RETENTION CACHE)"

UI_TEXT             "User Interface text description for ST_GEOMETRY"

COMMENT             "Any general comment for ST_GEOMETRY keyword"

END

As mentioned earlier in this section, if you define the LOB and LOB index tablespace names, you must alter these values prior to each feature class creation. If you do not, subsequent feature class creations fail because each segment name must be unique.

4/2/2015