2
The Object-Relational Schema

The object-relational implementation of Oracle8i Spatial consists of a set of object data types, an index method type, and operators on these types. A geometry is stored as an object, in a single row, in a column of type SDO_GEOMETRY. Spatial index creation and maintenance is done using basic DDL (CREATE, ALTER, DROP) and DML (INSERT, UPDATE, DELETE) statements.

2.1 Object-Relational Data Structures

In the Spatial object-relational model, a single SDO_GEOMETRY object replaces the rows and columns in a <layername>_SDOGEOM table of the relational model.

The geometric description of a spatial object is stored in a single row, in a single column of object type SDO_GEOMETRY in a user-defined table. The table does not require the "_SDOGEOM" suffix anymore. Because the SDO_GEOMETRY type does not have an SDO_GID attribute, any table that has a column of type SDO_GEOMETRY must have another column, or set of columns, that defines a unique primary key for that table. Tables of this sort are sometimes referred to as geometry tables.

Oracle8i Spatial defines the object type SDO_GEOMETRY as:

CREATE TYPE sdo_geometry AS OBJECT (
 SDO_GTYPE NUMBER, 
 SDO_SRID NUMBER,
 SDO_POINT SDO_POINT_TYPE,
 SDO_ELEM_INFO MDSYS.SDO_ELEM_INFO_ARRAY,
 SDO_ORDINATES MDSYS.SDO_ORDINATE_ARRAY);

The attributes of the SDO_GEOMETRY object type have the following semantics:

SDO_GTYPE - Indicates the type of the geometry. Valid geometry types correspond to those specified in the Geometry Object Model for the OGIS Simple Features for SQL specification (with the exception of Surfaces.) The numeric values differ from those given in the OGIS specification, but there is a direct correspondence between the names and semantics where applicable. Table 2-1 shows the valid SDO_GTYPE values.

Table 2-1 Valid SDO_GTYPE Values

Value	Geometry Type	Description
0	UNKNOWN_GEOMETRY	Spatial ignores this geometry.
1	POINT	Geometry contains one point.
2	LINESTRING	Geometry contains one line string.
3	POLYGON	Geometry contains one polygon with or without holes¹.
4	Collection	Geometry is a heterogeneous collection of elements.²
5	MULTIPOINT	Geometry has multiple points.
6	MULTILINESTRING	Geometry has multiple line strings.
7	MULTIPOLYGON	Geometry has multiple, disjoint polygons (more than one exterior boundary).

¹ For a polygon with holes, enter the exterior boundary first, followed by any interior boundaries.
² All polygons in the collection must be disjoint.

Values 8-99 are reserved for future use. The enumeration of types shown in Table 2-1 is recommended, however, Spatial does not check or enforce all geometry consistency constraints. Spatial does check the following constraints:

For SDO_GTYPEs 1 and 5, any subelement not of ETYPE 1 is ignored.
For SDO_GTYPEs 2 and 6, any subelement not of ETYPE 2 or 4 is ignored.
For SDO_GTYPEs 3 and 7, any subelement not of ETYPE 3 or 5 is ignored.

The SDO_GEOM.VALIDATE_GEOMETRY( ) function may be used to evaluate the consistency of a single geometry object or all the instances of SDO_GEOMETRY in a specified feature table.

SDO_SRID - Is reserved for future use. This attribute is intended to be a foreign key in a spatial reference system definition table.
SDO_POINT - Is an object type with attributes X, Y, and Z, all of type NUMBER. If the SDO_ELEM_INFO and SDO_ORDINATES arrays are both null, and the SDO_POINT attribute is non-null, then the X and Y values are considered to be the coordinates for a point geometry. Otherwise the SDO_POINT attribute is ignored by Spatial. You should store points in the SDO_POINT attribute for optimal storage.

SDO_ELEM_INFO - Is a varying length array of numbers. This attribute lets you know how to interpret the ordinates stored in the SDO_ORDINATES attribute.

Each triplet set of numbers conveys information about one geometry element, and a geometry may contain many elements. If a geometry has one element, then the SDO_ELEM_INFO array has three numbers; if the geometry has two elements, then the array has six numbers, and so on. Each triplet set is interpreted as follows:

SDO_STARTING_OFFSET -- Indicates the offset within the SDO_ORDINATES array where the first ordinate for this element is stored. Offset values start at 1 and not at 0. Thus the first ordinate for the first element will be at SDO_GEOMETRY.SDO_ORDINATES(1).
SDO_ETYPE - Indicates the type of the element. Valid values are 0 through 5.

SDO_ETYPEs 1, 2, and 3, are considered simple elements. They are defined by a single triplet entry in the SDO_ELEMINFO array. SDO_ETYPEs 4 and 5 are considered compound elements. They contain at least one header triplet with a series of triplet values that belong to the compound element.

The elements of a compound element are contiguous. The last point of a subelement in a compound element is the first point of the next subelement. The point is not repeated.
SDO_INTERPRETATION - Means one of two things, depending on whether or not SDO_ETYPE is a compound element.

If SDO_ETYPE is a compound element (4 or 5), this field specifies how many subsequent triplet values are part of the element.

If the SDO_ETYPE is not a compound element (1, 2, or 3), the interpretation attribute determines how the sequence of ordinates for this element is interpreted. For example, a line string or polygon boundary may be made up of a sequence of connected straight line segments or circular arcs.

A description of valid SDO_ETYPE and SDO_INTERPRETATION value pairs is given in Table 2-2.

If a geometry consists of more than one element, then the last ordinate for an element is always one less than the starting offset for the next element. The last element in the geometry is described by the ordinates from its starting offset to the end of the SDO_ORDINATES varying length array.

For compound elements (SDO_ETYPEs 4 and 5), a set of N triplets (one per subelement) is used to describe the element. It is important to remember that subelements of a compound element are contiguous. The last point of a subelement is the first point of the next subelement. For subelements 1 through N-1, the end point of one subelement is the same as the starting point of the next subelement. The starting point for subelements 2...N-2 is the same as the end point of subelement 1...N-1. The last ordinate of subelement N is either the starting offset-1 of the next element in the geometry, or the last ordinate in the SDO_ORDINATES varying length array.

The current size of a varying length array can be determined by using the function varray_variable.Count( ) in PL/SQL or OCIColSize( ) in Oracle Call Interface (OCI).

The semantics of each SDO_ETYPE element and the relationship between the SDO_ELEM_INFO and SDO_ORDINATES varying length arrays for each of these SDO_ETYPE elements is given in Table 2-2.

Table 2-2 Values and Semantics in SDO_ELEM_INFO

SDO_ETYPE	SDO_INTERPRETATION	Meaning
0	0	Unsupported element type. Ignored by the Spatial functions and procedures.
1	1	Point type.
1	N > 1	Point cluster with N points.
2	1	Line string whose vertices are connected by straight line segments.
2	2	Line string made up of a connected sequence of circular arcs. Each circular arc is described using three coordinates: the arc's starting point, any point on the arc, and the arc's end point. The coordinates for a point designating the end of one arc and the start of the next arc are not repeated. For example, five coordinates are used to describe a line string made up of two connected circular arcs. Points 1, 2, and 3 define the first arc, and points 3, 4, and 5 define the second arc, where point 3 is only stored once.
3	1	Simple polygon whose vertices are connected by straight line segments.
3	2	Polygon made up of a connected sequence of circular arcs that closes on itself. The end point of the last arc is the same as the start point of the first arc. Each circular arc is described using three coordinates: the arc's start point, any point on the arc, and the arc's end point. The coordinates for a point designating the end of one arc and the start of the next arc are not repeated. For example, five coordinates are used to describe a polygon made up of two connected circular arcs. Points 1, 2, and 3 define the first arc, and points 3, 4, and 5 define the second arc. The coordinates for points 1 and 5 must be the same, and point 3 is not repeated.
3	3	Rectangle type. A bounding rectangle such that only two points, the lower-left and the upper-right, are required to describe it.
3	4	Circle type. Described by three points, all on the circumference of the circle.
4	N > 1	Line string with some vertices connected by straight line segments and some by circular arcs. The value, N, in the Interpretation column specifies the number of contiguous subelements that make up the line string. The next N triplets in the SDO_ELEM_INFO array describe each of these subelements. The subelements can only be of SDO_ETYPE 2. The last point of a subelement is the first point of the next subelement, and must not be repeated. See Section 2.2 and Figure 2-2 for an example of a geometry using this type.
5	N > 1	Simple polygon with some vertices connected by straight line segments and some by circular arcs. The value, N, in the Interpretation column specifies the number of contiguous subelements that make up the polygon. The next N triplets in the SDO_ELEM_INFO array describe each of these subelements. The subelements can only be of SDO_ETYPE 2. The end point of a subelement is the start point of the next subelement and must not be repeated. The start and end points of the polygon must be the same. See Section 2.2 and Figure 2-3 for an example of a geometry using this type.

SDO_ORDINATES - Is a varying length array (1048576) of NUMBER type that stores the coordinate values that make up the boundary of a spatial object. This array must always be used in conjunction with the SDO_ELEM_INFO varying length array. The values in the array are ordered by dimension. For example, a polygon, whose boundary has four 2-dimensional points, is stored as {X1, Y1, X2, Y2, X3, Y3, X4, Y4, X1, Y1}. If the points are 3-dimensional, then they are stored as {X1, Y1, Z1, X2, Y2, Z2, X3, Y3, Z3, X4, Y4, Z4, X1, Y1, Z1}. Spatial index creation, operators, and functions ignore the Z values because this release of the product supports only 2-dimensional spatial objects. The number of dimensions associated with each point is stored as metadata in the SDO_GEOM_METADATA table, described in Section 2.3.

The values in the SDO_ORDINATES array must all be valid and non-null. There are no special values used to delimit elements in a multi-element geometry. The start and end points for the sequence describing a specific element are determined by the STARTING_OFFSET values for that element and the next element in the SDO_ELEM_INFO array as explained previously. The offset values start at 1. SDO_ORDINATES(1) is the first ordinate of the first point of the first element.

2.2 Geometry Examples Using the Object-Relational Model

Figure 2-1 illustrates a geometry with two elements. The inner element in this example is treated as a void (a hole).

Figure 2-1 Geometry with a Hole

In Figure 2-2, consider a crescent-shaped object represented as a compound line string made up of one straight line segment and one circular arc. Four points are required to represent this shape. Points 1 and 2 describe the straight line segment and points 2, 3, and 4 describe the circular arc. The SDO_ELEM_INFO array contains 3 triplets for this compound line string. These are {(1,4,2), (1,2,1), (3,2,2)}. The SDO_ORDINATES array contains (X1,Y1, X2, Y2, X3, Y3, X4,Y4).

Figure 2-2

The first triplet indicates that this element is a compound line string made up of two line strings, which are described with the next two triplets.

The second triplet indicates that the line string is made up of straight line segments and that the ordinates for this line string start at offset 1. The end point of this line string is determined by the starting offset of the second line string, 3 in this instance. Assuming the vertices are 2-dimensional, the coordinates for the end point of the first line string are at ordinates 3 and 4.

The third triplet indicates that the second line string is made up of circular arcs with ordinates starting at offset 3. The end point of this line string is determined by the starting offset of the next element or the current length of the SDO_ORDINATES array, if this is the last element.

In Figure 2-3, consider an ice cream cone shaped object represented as a compound polygon made up of one straight line segment and one circular arc. Five points are required to represent this shape. Points 1, 2, and 3 describe one acute angle shaped line string, and points 3, 4, and 5 describe the circular arc. Points 1 and 5 are the same point. The SDO_ELEM_INFO array contains three triplets for this compound line string. These triplets are {(1,5,2), (1,2,1), (5,2,2)}.

The first triplet indicates that this element is a compound line string made up of two line strings, which are described using the next two triplets.

The second triplet indicates that the line string is made up of straight line segments and that the ordinates for this line string start at offset 1. The end point of this line string is determined by the starting offset of the second line string, 5 in this instance. Assuming the vertices are 2-dimensional, the coordinates for the end point of the first line string are at ordinates 5 and 6.

The third triplet indicates that the second line string is made up of circular arcs with ordinates starting at offset 5. The end point of this line string is determined by the starting offset of the next element or the current length of the SDO_ORDINATES array, if this is the last element.

2.3 Geometry Metadata Structure

The geometry metadata describing the dimensions, lower and upper bounds, and tolerance in each dimension must be stored as a single entry in a table named SDO_GEOM_METADATA created in your schema and defined as follows:

Create Table SDO_GEOM_METADATA (
  TABLE_NAME   VARCHAR2(30),
  COLUMN_NAME  VARCHAR2(30),
  DIMINFO      MDSYS.SDO_DIM_ARRAY);

There should be one table with the previous name and layout created in the schema of each user having tables with a column, or columns, of type SDO_GEOMETRY. For example, if user Herman has tables named Roads, Parks, and Rivers, each with a column named theGeometry of type SDO_GEOMETRY, then there must be three entries in the table herman.sdo_geom_metadata. The user, or application, is responsible for populating and maintaining the data in this table.

The SDO_GEOM_METADATA.TABLE_NAME column contains the name of a feature table, such as Roads or Parks, that has a column of type SDO_GEOMETRY. The name of this column, of type SDO_GEOMETRY, is stored in the feature table in the SDO_GEOM_METADATA.COLUMN_NAME column. For the tables Roads and Parks, this column is called theGeometry, and therefore the SDO_GEOM_METADATA table should contain rows (Roads, theGeometry, SomeDimInfo1) and (Parks, theGeometry, SomeDimInfo2). The SDO_GEOM_METADATA.DIMINFO row is a varying length array of an object type, ordered by dimension, and has one entry per dimension. The row is defined as follows:

Create Type SDO_DIM_ARRAY as VARRAY(4) of SDO_DIM_ELEMENT;

where SDO_DIM_ELEMENT is defined as:

Create Type SDO_DIM_ELEMENT as OBJECT (
  SDO_DIMNAME VARCHAR2(64),
  SDO_LB NUMBER NOT NULL,
  SDO_UB NUMBER NOT NULL,
  SDO_TOLERANCE NUMBER NOT NULL);

The SDO_DIM_ARRAY instance is of size N if there are N-dimensions. That is, SDO_GEOM_METADATA.DIMINFO contains 2 SDO_DIM_ELEMENT instances for 2-dimensional geometries, or 3 for 3-dimensional geometries, and so on. Each SDO_DIM_ELEMENT instance in the array must have valid (not NULL) values for the SDO_LB, SDO_UB, and SDO_TOLERANCE attributes.

Spatial assumes that the varying length array is ordered by dimension, and therefore, in the Roads and Parks tables, SomeDimInfo1 is the SDO_DIM_ELEMENT for the first dimension and SomeDimInfo2 is the SDO_DIM_ELEMENT for the second dimension. It is imperative that the DIMINFO varying length array is ordered by dimension in the same way the ordinates for the points in SDO_ORDINATES varying length array are ordered. That is, if the SDO_ORDINATES varying length array contains {X1, Y1, ..., Xn, Yn}, then SomeDimInfo1 must define the X dimension and SomeDimInfo2 must define the Y dimension.

Section 3.1.2 contains examples that show the use of the SDO_GEOMETRY and SDO_DIM_ARRAY types. These examples demonstrate how various geometry objects are represented, and how a feature table and the SDO_GEOM_METADATA table are populated with the data for those objects.

2.4 Spatial Index-Related Structure

This section describes the structure of the tables containing the spatial index data and metadata. Concepts and usage notes for spatial indexing are explained in Section 1.6. Both the spatial index data and metadata are stored in tables created and maintained by the spatial indexing routines. These tables are created in the same schema as the owner of the feature (underlying) table with a spatial index created on a column of type SDO_GEOMETRY.

2.4.1 Spatial Index Tables

There is one metadata view per schema (user), named SDO_INDEX_METADATA, and one index data table per spatially indexed column in that schema. Thus if user Herman has five feature tables, each with a spatial index on their respective SDO_GEOMETRY typed column, then Herman's schema has one SDO_INDEX_METADATA view and five tables containing spatial index data. The index data table names are not created by adding the " _SDOINDEX" suffix to the layer name. Instead, the index data table is named using the user-specified index name and a suffix that indicates the spatial index type (fixed or hybrid, as defined in Section 1.6) and the values of the relevant index parameters.

The SDO_INDEX_METADATA view contains the following columns whose type and purpose are shown in Table 2-3.

Table 2-3 Columns in an SDO_INDEX_METADATA View

Column Name	Data Type	Purpose
SDO_LEVEL	NUMBER	The fixed tiling level at which to tile all objects in the feature table.
SDO_NUMTILES	NUMBER	Suggested number of tiles per object that should be used to approximate the shape.
SDO_MAXLEVEL	NUMBER	The maximum level for any tile for any object. It will always be greater than the SDO_LEVEL value.
SDO_COMMIT_INTERVAL	NUMBER	The number of geometries (rows) to process, during index creation, before committing the insertion of spatial index entries into the SDOINDEX table. See Appendix A for a discussion of the use of this parameter.
SDO_INDEX_TABLE	VARCHAR2	Name of the SDOINDEX table.
SDO_TABLESPACE	VARCHAR2	Same as in the basic SQL CREATE TABLE statement. Tablespace in which to create the SDOINDEX table.
SDO_INITIAL_EXTENT	NUMBER	Same as in SQL CREATE TABLE statement.
SDO_NEXT_EXTENT	NUMBER	Same as in SQL CREATE TABLE statement.
SDO_PCTINCREASE	NUMBER	Same as in SQL CREATE TABLE statement.
SDO_MIN_EXTENTS	NUMBER	Same as in SQL CREATE TABLE statement.
SDO_MAX_EXTENTS	NUMBER	Same as in SQL CREATE TABLE statement.
SDO_FIXED_METADATA	RAW	If applicable, this column contains the metadata portion of the SDO_GROUPCODE or SDO_CODE for a fixed-level index.
SDO_INDEX_COORDSYS	VARCHAR2	Reserved for future use.
SDO_INDEX_PRIMARY	NUMBER	Indicates if this is a primary or secondary index. 1 = primary, 2 = secondary.
SDO_INDEX_OWNER	VARCHAR2	The owner of the index.
SDO_INDEX_NAME	VARCHAR2	The name of the index.
SDO_TSNAME	VARCHAR2	The schema name of the SDO_INDEX_TABLE.
SDO_COLUMN_NAME	VARCHAR2	The column name on which this index is built.

2.4.2 Spatial Index Data Dictionary View

The index data table will have some or all of the columns shown in Table 2-4.

Table 2-4 Columns in a Spatial Index Data Table

Column Name	Data Type	Purpose
SDO_ROWID	RAW	Row ID of a row in a feature table containing the indexed object.
SDO_CODE	RAW	Index entry for the object in the row identified by SDO_ROWID.
SDO_MAXCODE	RAW	Padded SDO_CODE column.
SDO_GROUPCODE	RAW	Index entry at level SDO_LEVEL.
SDO_META	RAW	Metadata portion of the SDO_CODE for a hybrid index.

The columns, SDO_ROWID and SDO_CODE, are always present. The remaining columns are present only when the selected index type is HYBRID, which is the recommended indexing for the object-relational model.

2.5 Usage Notes

Stored procedures, provided with Spatial release 8.1, assume the existence of the following schema objects: instances of SDO_GEOMETRY and SDO_DIM_ARRAY objects and the metadata table SDO_GEOM_METADATA in the user's schema. While specific instances of the SDO_GEOM_METADATA view may contain additional columns, they are required to contain the columns described in Table 2-3 with the same column names and data types.

2 The Object-Relational Schema