Oracle8i Spatial User's Guide and Reference Release 8.1.5 A6729501 

The objectrelational 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.
In the Spatial objectrelational 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 userdefined 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 21 shows the 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^{1}. 
4 
Collection 
Geometry is a heterogeneous collection of elements.^{2} 
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). 
^{1}
For a polygon with holes, enter the exterior boundary first, followed by any interior boundaries. ^{2} All polygons in the collection must be disjoint. 
Values 899 are reserved for future use. The enumeration of types shown in Table 21 is recommended, however, Spatial does not check or enforce all geometry consistency constraints. Spatial does check the following constraints:
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 nonnull, 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_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.
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 22.
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 N1, the end point of one subelement is the same as the starting point of the next subelement. The starting point for subelements 2...N2 is the same as the end point of subelement 1...N1. The last ordinate of subelement N is either the starting offset1 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 22.
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 lowerleft and the upperright, 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 22 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 23 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 2dimensional points, is stored as {X1, Y1, X2, Y2, X3, Y3, X4, Y4, X1, Y1}. If the points are 3dimensional, 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 2dimensional 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 nonnull. There are no special values used to delimit elements in a multielement 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.
Figure 21 illustrates a geometry with two elements. The inner element in this example is treated as a void (a hole).
In Figure 22, consider a crescentshaped 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).
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 2dimensional, 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 23, 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 2dimensional, 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.
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 Ndimensions. That is, SDO_GEOM_METADATA.DIMINFO contains 2 SDO_DIM_ELEMENT instances for 2dimensional geometries, or 3 for 3dimensional 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.
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.
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 userspecified 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 23.
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 fixedlevel 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. 
The index data table will have some or all of the columns shown in Table 24.
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 objectrelational model.
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 23 with the same column names and data types.