Oracle® Spatial User's Guide and Reference 10g Release 1 (10.1) Part Number B1082601 


View PDF 
Oracle Spatial consists of a set of object data types, type methods, and operators, functions, and procedures that use 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.
This chapter starts with a simple example that inserts, indexes, and queries spatial data. You may find it helpful to read this example quickly before you examine the detailed data type and metadata information later in the chapter.
This chapter contains the following major sections:
This section presents a simple example of creating a spatial table, inserting data, creating the spatial index, and performing spatial queries. It refers to concepts that were explained in Chapter 1 and that will be explained in other sections of this chapter.
The scenario is a soft drink manufacturer that has identified geographical areas of marketing interest for several products (colas). The colas could be those produced by the company or by its competitors, or some combination. Each area of interest could represent any userdefined criterion: for example, an area where that cola has the majority market share, or where the cola is under competitive pressure, or where the cola is believed to have significant growth potential. Each area could be a neighborhood in a city, or a part of a state, province, or country.
Figure 21 shows the areas of interest for four colas.
Example 21 performs the following operations:
Creates a table (COLA_MARKETS) to hold the spatial data
Inserts rows for four areas of interest (cola_a
, cola_b
, cola_c
, cola_d
)
Updates the USER_SDO_GEOM_METADATA view to reflect the dimensional information for the areas
Creates a spatial index (COLA_SPATIAL_IDX)
Performs some spatial queries
Many concepts and techniques in Example 21 are explained in detail in other sections of this chapter.
Example 21 Simple Example: Inserting, Indexing, and Querying Spatial Data
 Create a table for cola (soft drink) markets in a  given geography (such as city or state).  Each row will be an area of interest for a specific  cola (for example, where the cola is most preferred  by residents, where the manufacturer believes the  cola has growth potential, and so on).  (For restrictions on spatial table and column names, see  Section 2.4.1 and Section 2.4.2.) CREATE TABLE cola_markets ( mkt_id NUMBER PRIMARY KEY, name VARCHAR2(32), shape SDO_GEOMETRY);  The next INSERT statement creates an area of interest for  Cola A. This area happens to be a rectangle.  The area could represent any userdefined criterion: for  example, where Cola A is the preferred drink, where  Cola A is under competitive pressure, where Cola A  has strong growth potential, and so on. INSERT INTO cola_markets VALUES( 1, 'cola_a', SDO_GEOMETRY( 2003,  twodimensional polygon NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),  one rectangle (1003 = exterior) SDO_ORDINATE_ARRAY(1,1, 5,7)  only 2 points needed to  define rectangle (lower left and upper right) with  Cartesiancoordinate data ) );  The next two INSERT statements create areas of interest for  Cola B and Cola C. These areas are simple polygons (but not  rectangles). INSERT INTO cola_markets VALUES( 2, 'cola_b', SDO_GEOMETRY( 2003,  twodimensional polygon NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),  one polygon (exterior polygon ring) SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1) ) ); INSERT INTO cola_markets VALUES( 3, 'cola_c', SDO_GEOMETRY( 2003,  twodimensional polygon NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),  one polygon (exterior polygon ring) SDO_ORDINATE_ARRAY(3,3, 6,3, 6,5, 4,5, 3,3) ) );  Now insert an area of interest for Cola D. This is a  circle with a radius of 2. It is completely outside the  first three areas of interest. INSERT INTO cola_markets VALUES( 4, 'cola_d', SDO_GEOMETRY( 2003,  twodimensional polygon NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,4),  one circle SDO_ORDINATE_ARRAY(8,7, 10,9, 8,11) ) );   UPDATE METADATA VIEW    Update the USER_SDO_GEOM_METADATA view. This is required  before the Spatial index can be created. Do this only once for each  layer (that is, tablecolumn combination; here: COLA_MARKETS and SHAPE). INSERT INTO USER_SDO_GEOM_METADATA VALUES ( 'cola_markets', 'shape', SDO_DIM_ARRAY(  20X20 grid SDO_DIM_ELEMENT('X', 0, 20, 0.005), SDO_DIM_ELEMENT('Y', 0, 20, 0.005) ), NULL  SRID );   CREATE THE SPATIAL INDEX   CREATE INDEX cola_spatial_idx ON cola_markets(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX;  Preceding statement created an Rtree index.   PERFORM SOME SPATIAL QUERIES    Return the topological intersection of two geometries. SELECT SDO_GEOM.SDO_INTERSECTION(c_a.shape, c_c.shape, 0.005) FROM cola_markets c_a, cola_markets c_c WHERE c_a.name = 'cola_a' AND c_c.name = 'cola_c';  Do two geometries have any spatial relationship? SELECT SDO_GEOM.RELATE(c_b.shape, 'anyinteract', c_d.shape, 0.005) FROM cola_markets c_b, cola_markets c_d WHERE c_b.name = 'cola_b' AND c_d.name = 'cola_d';  Return the areas of all cola markets. SELECT name, SDO_GEOM.SDO_AREA(shape, 0.005) FROM cola_markets;  Return the area of just cola_a. SELECT c.name, SDO_GEOM.SDO_AREA(c.shape, 0.005) FROM cola_markets c WHERE c.name = 'cola_a';  Return the distance between two geometries. SELECT SDO_GEOM.SDO_DISTANCE(c_b.shape, c_d.shape, 0.005) FROM cola_markets c_b, cola_markets c_d WHERE c_b.name = 'cola_b' AND c_d.name = 'cola_d';  Is a geometry valid? SELECT c.name, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(c.shape, 0.005) FROM cola_markets c WHERE c.name = 'cola_c';  Is a layer valid? (First, create the results table.) CREATE TABLE val_results (sdo_rowid ROWID, result VARCHAR2(2000)); CALL SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('COLA_MARKETS', 'SHAPE', 'VAL_RESULTS', 2); SELECT * from val_results;
With Spatial, 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. 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 spatial tables or spatial geometry tables.
Oracle 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 SDO_ELEM_INFO_ARRAY, SDO_ORDINATES SDO_ORDINATE_ARRAY);
Oracle Spatial also defines the SDO_POINT_TYPE, SDO_ELEM_INFO_ARRAY, and SDO_ORDINATE_ARRAY types, which are used in the SDO_GEOMETRY type definition, as follows:
CREATE TYPE sdo_point_type AS OBJECT ( X NUMBER, Y NUMBER, Z NUMBER); CREATE TYPE sdo_elem_info_array AS VARRAY (1048576) of NUMBER; CREATE TYPE sdo_ordinate_array AS VARRAY (1048576) of NUMBER;
The sections that follow describe the semantics of each SDO_GEOMETRY attribute, and then describe some usage considerations (Section 2.2.6).
The SDO_GEOMETRY object type has methods that provide convenient access to some of the attributes. These methods are described in Chapter 11.
Some Spatial data types are described in locations other than this section:
Section 5.2 describes data types for geocoding.
Oracle Spatial GeoRaster describes data types for Oracle Spatial GeoRaster.
Oracle Spatial Topology and Network Data Models describes data types for the Oracle Spatial topology data model and network data model.
The SDO_GTYPE attribute 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.
The SDO_GTYPE value is 4 digits in the format dltt, where:
d identifies the number of dimensions (2, 3, or 4)
l identifies the linear referencing measure dimension for a threedimensional linear referencing system (LRS) geometry, that is, which dimension (3 or 4) contains the measure value. For a nonLRS geometry, or to accept the Spatial default of the last dimension as the measure for an LRS geometry, specify 0. For information about the linear referencing system (LRS), see Chapter 7.
tt identifies the geometry type (00 through 07, with 08 through 99 reserved for future use).
Table 21 shows the valid SDO_GTYPE values. The Geometry Type and Description values reflect the OGIS specification.
Table 21 Valid SDO_GTYPE Values
Value  Geometry Type  Description 

dl00  UNKNOWN_GEOMETRY  Spatial ignores this geometry. 
dl01  POINT  Geometry contains one point. 
dl02  LINE or CURVE  Geometry contains one line string that can contain straight or circular arc segments, or both. (LINE and CURVE are synonymous in this context.) 
dl03  POLYGON  Geometry contains one polygon with or without holes.^{Foot 1 } 
dl04  COLLECTION  Geometry is a heterogeneous collection of elements.^{Foot 2 } COLLECTION is a superset that includes all other types. 
dl05  MULTIPOINT  Geometry has one or more points. (MULTIPOINT is a superset of POINT.) 
dl06  MULTILINE or MULTICURVE  Geometry has one or more line strings. (MULTILINE and MULTICURVE are synonymous in this context, and each is a superset of both LINE and CURVE.) 
dl07  MULTIPOLYGON  Geometry can have multiple, disjoint polygons (more than one exterior boundary). (MULTIPOLYGON is a superset of POLYGON.) 
The d in the Value column of Table 21 is the number of dimensions: 2, 3, or 4. For example, an SDO_GTYPE value of 2003 indicates a twodimensional polygon.
Note: The prerelease 8.1.6 format of a 1digit SDO_GTYPE value is still supported. If a 1digit value is used, however, Oracle Spatial determines the number of dimensions from the DIMINFO column of the metadata views, described in Section 2.4.3.Also, if 1digit SDO_GTYPE values are converted to 4digit values, any SDO_ETYPE values that end in 3 or 5 in the SDO_ELEM_INFO array (described in Section 2.2.4) must also be converted. 
The number of dimensions reflects the number of ordinates used to represent each vertex (for example, X,Y for twodimensional objects). Points and lines are considered twodimensional objects. (However, see Section 7.2 for dimension information about LRS points.)
In any given layer (column), all geometries must have the same number of dimensions. For example, you cannot mix twodimensional and threedimensional data in the same layer.
The following methods are available for returning the individual dltt components of the SDO_GTYPE for a geometry object: GET_DIMS, GET_LRS_DIM, and GET_GTYPE. These methods are described in Chapter 11.
The SDO_SRID attribute can be used to identify a coordinate system (spatial reference system) to be associated with the geometry. If SDO_SRID is null, no coordinate system is associated with the geometry. If SDO_SRID is not null, it must contain a value from the SRID column of the MDSYS.CS_SRS table (described in Section 6.4.1), and this value must be inserted into the SRID column of the USER_SDO_GEOM_METADATA view (described in Section 2.4).
All geometries in a geometry column must have the same SDO_SRID value.
For information about coordinate systems, see Chapter 6.
The SDO_POINT attribute is defined using the SDO_POINT_TYPE object type, which has the attributes X, Y, and Z, all of type NUMBER. (The SDO_POINT_TYPE definition is shown in Section 2.2.) 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 point geometries in the SDO_POINT attribute for optimal storage; and if you have only point geometries in a layer, it is strongly recommended that you store the point geometries in the SDO_POINT attribute.
Section 2.3.5 illustrates a point geometry and provides examples of inserting and querying point geometries.
Note: Do not use the SDO_POINT attribute in defining a linear referencing system (LRS) point. For information about LRS, see Chapter 7. 
The SDO_ELEM_INFO attribute is defined using a varying length array of numbers. This attribute lets you know how to interpret the ordinates stored in the SDO_ORDINATES attribute (described in Section 2.2.5).
Each triplet set of numbers 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). If there is a second element, its first ordinate will be at SDO_GEOMETRY.SDO_ORDINATES(n), where n reflects the position within the SDO_ORDINATE_ARRAY definition (for example, 19 for the 19th number, as in Figure 23 later in this chapter).
SDO_ETYPE  Indicates the type of the element. Valid values are shown in Table 22.
SDO_ETYPE values 1, 2, 1003, and 2003 are considered simple elements. They are defined by a single triplet entry in the SDO_ELEM_INFO array. For SDO_ETYPE values 1003 and 2003, the first digit indicates exterior (1) or interior (2):
1003: exterior polygon ring (must be specified in counterclockwise order)
2003: interior polygon ring (must be specified in clockwise order)
Note: The use of 3 as an SDO_ETYPE value for polygon ring elements in a single geometry is discouraged. You should specify 3 only if you do not know if the simple polygon is exterior or interior, and you should then upgrade the table or layer to the current format using the SDO_MIGRATE.TO_CURRENT procedure, described in Chapter 17.You cannot mix 1digit and 4digit SDO_ETYPE values in a single geometry. If you use 4digit SDO_ETYPE values, you must use 4digit SDO_GTYPE values. 
SDO_ETYPE values 4, 1005, and 2005 are considered compound elements. They contain at least one header triplet with a series of triplet values that belong to the compound element. For SDO_ETYPE values 1005 and 2005, the first digit indicates exterior (1) or interior (2):
1005: exterior polygon ring (must be specified in counterclockwise order)
2005: interior polygon ring (must be specified in clockwise order)
Note: The use of 5 as an SDO_ETYPE value for polygon ring elements in a single geometry is discouraged. You should specify 5 only if you do not know if the compound polygon is exterior or interior, and you should then upgrade the table or layer to the current format using the SDO_MIGRATE.TO_CURRENT procedure, described in Chapter 17.You cannot mix 1digit and 4digit SDO_ETYPE values in a single geometry. If you use 4digit SDO_ETYPE values, you must use 4digit SDO_GTYPE values. 
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, 1005, or 2005), this field specifies how many subsequent triplet values are part of the element.
If the SDO_ETYPE is not a compound element (1, 2, 1003, or 2003), 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.
Descriptions of valid SDO_ETYPE and SDO_INTERPRETATION value pairs are 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_ETYPE values 4, 1005, or 2005), a set of n triplets (one for each 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 offset minus 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 OCICollSize in the 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 are given in Table 22.
Table 22 Values and Semantics in SDO_ELEM_INFO
SDO_ETYPE  SDO_INTERPRETATION  Meaning 

0  (any numeric value)  Type 0 (zero) element. Used to model geometry types not supported by Oracle Spatial. For more information, see Section 2.3.6. 
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 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 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. 
1003 or 2003  1  Simple polygon whose vertices are connected by straight line segments. You must specify a point for each vertex, and the last point specified must be exactly the same point as the first (to close the polygon), regardless of the tolerance value. For example, for a 4sided polygon, specify 5 points, with point 5 the same as point 1. 
1003 or 2003  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 (tolerance is not considered), and point 3 is not repeated. 
1003 or 2003  3  Rectangle type (sometimes called optimized rectangle). A bounding rectangle such that only two points, the lowerleft and the upperright, are required to describe it. The rectangle type can be used with geodetic or nongeodetic data. However, with geodetic data, use this type only to create a query window (not for storing objects in the database). For detailed information about using this type with geodetic data, including examples, see Section 6.2.3. 
1003 or 2003  4  Circle type. Described by three distinct noncolinear points, all on the circumference of the circle. 
4  n > 1  Compound 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.3.3 and Figure 24 for an example of a geometry using this type. 
1005 or 2005  n > 1  Compound 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 it must not be repeated. The start and end points of the polygon must be exactly the same point (tolerance is ignored). See Section 2.3.4 and Figure 25 for an example of a geometry using this type. 
The SDO_ORDINATES attribute is defined using 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 twodimensional points is stored as {X1, Y1, X2, Y2, X3, Y3, X4, Y4, X1, Y1}. If the points are threedimensional, 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 twodimensional spatial objects. The number of dimensions associated with each point is stored as metadata in the xxx_SDO_GEOM_METADATA views, described in Section 2.4.
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.
You should use the SDO_GTYPE values as shown in Table 21; however, Spatial does not check or enforce all geometry consistency constraints. Spatial does check the following:
For SDO_GTYPE values d001 and d005, any subelement not of SDO_ETYPE 1 is ignored.
For SDO_GTYPE values d002 and d006, any subelement not of SDO_ETYPE 2 or 4 is ignored.
For SDO_GTYPE values d003 and d007, any subelement not of SDO_ETYPE 3 or 5 is ignored. (This includes SDO_ETYPE variants 1003, 2003, 1005, and 2005, which are explained in Section 2.2.4).
The SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function can be used to evaluate the consistency of a single geometry object or of all geometry objects in a specified feature table.
This section contains examples of several geometry types.
Figure 22 illustrates the rectangle that represents cola_a
in the example in Section 2.1.
In the SDO_GEOMETRY definition of the geometry illustrated in Figure 22:
SDO_GTYPE = 2003. The 2 indicates twodimensional, and the 3 indicates a polygon.
SDO_SRID = NULL.
SDO_POINT = NULL.
SDO_ELEM_INFO = (1, 1003, 3). The final 3 in 1,1003,3 indicates that this is a rectangle. Because it is a rectangle, only two ordinates are specified in SDO_ORDINATES (lowerleft and upperright).
SDO_ORDINATES = (1,1, 5,7). These identify the lowerleft and upperright ordinates of the rectangle.
Example 22 shows a SQL statement that inserts the geometry illustrated in Figure 22 into the database.
Example 22 SQL Statement to Insert a Rectangle
INSERT INTO cola_markets VALUES( 1, 'cola_a', SDO_GEOMETRY( 2003,  twodimensional polygon NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),  one rectangle (1003 = exterior) SDO_ORDINATE_ARRAY(1,1, 5,7)  only 2 points needed to  define rectangle (lower left and upper right) with  Cartesiancoordinate data ) );
Figure 23 illustrates a polygon consisting of two elements: an exterior polygon ring and an interior polygon ring. The inner element in this example is treated as a void (a hole).
In the SDO_GEOMETRY definition of the geometry illustrated in Figure 23:
SDO_GTYPE = 2003. The 2 indicates twodimensional, and the 3 indicates a polygon.
SDO_SRID = NULL.
SDO_POINT = NULL.
SDO_ELEM_INFO = (1,1003,1, 19,2003,1). There are two triplet elements: 1,1003,1 and 19,2003,1.
1003 indicates that the element is an exterior polygon ring; 2003 indicates that the element is an interior polygon ring.
19 indicates that the second element (the interior polygon ring) ordinate specification starts at the 19th number in the SDO_ORDINATES array (that is, 7, meaning that the first point is 7,5).
SDO_ORDINATES = (2,4, 4,3, 10,3, 13,5, 13,9, 11,13, 5,13, 2,11, 2,4,
7,5, 7,10, 10,10, 10,5, 7,5).
The area (SDO_GEOM.SDO_AREA function) of the polygon is the area of the exterior polygon minus the area of the interior polygon. In this example, the area is 84 (99  15).
The perimeter (SDO_GEOM.SDO_LENGTH function) of the polygon is the perimeter of the exterior polygon plus the perimeter of the interior polygon. In this example, the perimeter is 52.9193065 (36.9193065 + 16).
Example 23 shows a SQL statement that inserts the geometry illustrated in Figure 23 into the database.
Example 23 SQL Statement to Insert a Polygon with a Hole
INSERT INTO cola_markets VALUES( 10, 'polygon_with_hole', SDO_GEOMETRY( 2003,  twodimensional polygon NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1),  polygon with hole SDO_ORDINATE_ARRAY(2,4, 4,3, 10,3, 13,5, 13,9, 11,13, 5,13, 2,11, 2,4, 7,5, 7,10, 10,10, 10,5, 7,5) ) );
An example of such a "polygon with a hole" might be a land mass (such as a country or an island) with a lake inside it. Of course, an actual land mass might have many such interior polygons: each one would require a triplet element in SDO_ELEM_INFO, plus the necessary ordinate specification.
Exterior and interior rings cannot be nested. For example, if a country has a lake and there is an island in the lake (and perhaps a lake on the island), a separate polygon must be defined for the island; the island cannot be defined as an interior polygon ring within the interior polygon ring of the lake.
In a multipolygon (polygon collection), rings must be grouped by polygon, and the first ring of each polygon must be the exterior ring. For example, consider a polygon collection that contains two polygons (A and B):
Polygon A (one interior "hole"): exterior ring A0, interior ring A1
Polygon B (two interior "holes"): exterior ring B0, interior ring B1, interior ring B2
The elements in SDO_ELEM_INFO and SDO_ORDINATES must be in one of the following orders (depending on whether you specify Polygon A or Polygon B first):
A0, A1; B0, B1, B2
B0, B1, B2; A0, A1
Figure 24 illustrates 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 (10,10) and (10,14) describe the straight line segment, and points (10,14), (6,10), and (14,10) describe the circular arc.
In the SDO_GEOMETRY definition of the geometry illustrated in Figure 24:
SDO_GTYPE = 2002. The first 2 indicates twodimensional, and the second 2 indicates one or more line segments.
SDO_SRID = NULL.
SDO_POINT = NULL.
SDO_ELEM_INFO = (1,4,2, 1,2,1, 3,2,2). There are three triplet elements: 1,4,2, 1,2,1, and 3,2,2.
The first triplet indicates that this element is a compound line string made up of two subelement 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.
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.
SDO_ORDINATES = (10,10, 10,14, 6,10, 14,10).
Example 24 shows a SQL statement that inserts the geometry illustrated in Figure 24 into the database.
Figure 25 illustrates an ice cream coneshaped 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 (6,10), (10,1), and (14,10) describe one acute angleshaped line string, and points (14,10), (10,14), and (6,10) describe the circular arc. The starting point of the line string and the ending point of the circular arc are the same point (6,10). The SDO_ELEM_INFO array contains three triplets for this compound line string. These triplets are {(1,1005,2), (1,2,1), (5,2,2)}.
In the SDO_GEOMETRY definition of the geometry illustrated in Figure 25:
SDO_GTYPE = 2003. The 2 indicates twodimensional, and the 3 indicates a polygon.
SDO_SRID = NULL.
SDO_POINT = NULL.
SDO_ELEM_INFO = (1,1005,2, 1,2,1, 5,2,2). There are three triplet elements: 1,1005,2, 1,2,1, and 5,2,2.
The first triplet indicates that this element is a compound polygon made up of two subelement line strings, which are described using the next two triplets.
The second triplet indicates that the first subelement 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. Because the vertices are twodimensional, the coordinates for the end point of the first line string are at ordinates 5 and 6.
The third triplet indicates that the second subelement line string is made up of a circular arc 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.
SDO_ORDINATES = (6,10, 10,1, 14,10, 10,14, 6,10).
Example 25 shows a SQL statement that inserts the geometry illustrated in Figure 25 into the database.
Figure 26 illustrates a pointonly geometry at coordinates (12,14).
In the SDO_GEOMETRY definition of the geometry illustrated in Figure 26:
SDO_GTYPE = 2001. The 2 indicates twodimensional, and the 1 indicates a single point.
SDO_SRID = NULL.
SDO_POINT = SDO_POINT_TYPE(12, 14, NULL). The SDO_POINT attribute is defined using the SDO_POINT_TYPE object type, because this is a pointonly geometry.
For more information about the SDO_POINT attribute, see Section 2.2.3.
SDO_ELEM_INFO and SDO_ORDINATES are both NULL, as required if the SDO_POINT attribute is specified.
Example 26 shows a SQL statement that inserts the geometry illustrated in Figure 26 into the database.
Example 26 SQL Statement to Insert a PointOnly Geometry
INSERT INTO cola_markets VALUES( 90, 'point_only', SDO_GEOMETRY( 2001, NULL, SDO_POINT_TYPE(12, 14, NULL), NULL, NULL));
You can search for pointonly geometries based on the X, Y, and Z values in the SDO_POINT_TYPE specification. Example 27 is a query that asks for all points whose first coordinate (the X value) is 12, and it finds the point that was inserted in Example 26.
Example 27 Query for PointOnly Geometry Based on a Coordinate Value
SELECT * from cola_markets c WHERE c.shape.SDO_POINT.X = 12; MKT_ID NAME   SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)  90 point_only SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(12, 14, NULL), NULL, NULL)
Type 0 (zero) elements are used to model geometry types that are not supported by Oracle Spatial, such as curves and splines. A type 0 element has an SDO_ETYPE value of 0. (See Section 2.2.4 for information about the SDO_ETYPE.) Type 0 elements are not indexed by Oracle Spatial, and they are ignored by Spatial functions and procedures.
Geometries with type 0 elements must contain at least one nonzero element, that is, an element with an SDO_ETYPE value that is not 0. The nonzero element should be an approximation of the unsupported geometry, and therefore it must have both:
An SDO_ETYPE value associated with a geometry type supported by Spatial
An SDO_INTERPRETATION value that is valid for the SDO_ETYPE value (see Table 22)
(The SDO_INTERPRETATION value for the type 0 element can be any numeric value, and applications are responsible for determining the validity and significance of the value.)
The nonzero element is indexed by Spatial, and it will be returned by the spatial index.
The SDO_GTYPE value for a geometry containing a type 0 element must be set to the value for the geometry type of the nonzero element.
Figure 27 shows a geometry with two elements: a curve (unsupported geometry) and a rectangle (the nonzero element) that approximates the curve. The curve looks like the letter S, and the rectangle is represented by the dashed line.
In the example shown in Figure 27:
The SDO_GTYPE value for the geometry is 2003 (for a twodimensional polygon).
The SDO_ELEM_INFO array contains two triplets for this compound line string. For example, the triplets might be {(1,0,57), (11,1003,3)}. That is:
Ordinate Starting Offset (SDO_STARTING_OFFSET) 
Element Type (SDO_ETYPE) 
Interpretation (SDO_INTERPRETATION) 

1  0  57 
11  1003  3 
In this example:
The type 0 element has an SDO_ETYPE value of 0.
The nonzero element (rectangle) has an SDO_ETYPE value of 1003, indicating an exterior polygon ring.
The nonzero element has an SDO_STARTING_OFFSET value of 11 because ordinate x6 is the eleventh ordinate in the geometry.
The type 0 element has an SDO_INTERPRETATION value whose significance is applicationspecific. In this example, the SDO_INTERPRETATION value is 57.
The nonzero element has an SDO_INTERPRETATION value that is valid for the SDO_ETYPE of 1003. In this example, the SDO_INTERPRETATION value is 3, indicating a rectangle defined by two points (lowerleft and upperright).
Example 28 shows a SQL statement that inserts the geometry with a type 0 element (similar to the geometry illustrated in Figure 27) into the database. In the SDO_ORDINATE_ARRAY structure, the curve is defined by points (6,6), (12,6), (9,8), (6,10), and (12,10), and the rectangle is defined by points (6,4) and (12,12).
Example 28 SQL Statement to Insert a Geometry with a Type 0 Element
INSERT INTO cola_markets VALUES( 13, 'type_zero_element_geom', SDO_GEOMETRY( 2003,  twodimensional polygon NULL, NULL, SDO_ELEM_INFO_ARRAY(1,0,57, 11,1003,3),  1st is type 0 element SDO_ORDINATE_ARRAY(6,6, 12,6, 9,8, 6,10, 12,10, 6,4, 12,12) ) );
The geometry metadata describing the dimensions, lower and upper bounds, and tolerance in each dimension is stored in a global table owned by MDSYS (which users should never directly update). Each Spatial user has the following views available in the schema associated with that user:
USER_SDO_GEOM_METADATA contains metadata information for all spatial tables owned by the user (schema). This is the only view that you can update, and it is the one in which Spatial users must insert metadata related to spatial tables.
ALL_SDO_GEOM_METADATA contains metadata information for all spatial tables on which the user has SELECT permission.
Spatial users are responsible for populating these views. For each spatial column, you must insert an appropriate row into the USER_SDO_GEOM_METADATA view. Oracle Spatial ensures that the ALL_SDO_GEOM_METADATA view is also updated to reflect the rows that you insert into USER_SDO_GEOM_METADATA.
Note: These views were new for release 8.1.6. If you are upgrading from an earlier release of Spatial, see Appendix A and the information about the SDO_MIGRATE.TO_CURRENT procedure in Chapter 17. 
Each metadata view has the following definition:
( TABLE_NAME VARCHAR2(32), COLUMN_NAME VARCHAR2(32), DIMINFO SDO_DIM_ARRAY, SRID NUMBER );
In addition, the ALL_SDO_GEOM_METADATA view has an OWNER column identifying the schema that owns the table specified in TABLE_NAME.
The TABLE_NAME column contains the name of a feature table, such as COLA_MARKETS, that has a column of type SDO_GEOMETRY.
The table name is stored in the spatial metadata views in all uppercase characters.
The table name cannot contain spaces or mixedcase letters in a quoted string when inserted into the USER_SDO_GEOM_METADATA view, and it cannot be in a quoted string when used in a query (unless it is in all uppercase characters).
The spatial feature table cannot be an indexorganized table if you plan to create a spatial index on the spatial column.
The COLUMN_NAME column contains the name of the column of type SDO_GEOMETRY. For the COLA_MARKETS table, this column is called SHAPE.
The column name is stored in the spatial metadata views in all uppercase characters.
The column name cannot contain spaces or mixedcase letters in a quoted string when inserted into the USER_SDO_GEOM_METADATA view, and it cannot be in a quoted string when used in a query (unless it is in all uppercase characters).
The DIMINFO column is a varying length array of an object type, ordered by dimension, and has one entry for each dimension. The SDO_DIM_ARRAY type is defined as follows:
Create Type SDO_DIM_ARRAY as VARRAY(4) of SDO_DIM_ELEMENT;
The SDO_DIM_ELEMENT type is defined as:
Create Type SDO_DIM_ELEMENT as OBJECT ( SDO_DIMNAME VARCHAR2(64), SDO_LB NUMBER, SDO_UB NUMBER, SDO_TOLERANCE NUMBER);
The SDO_DIM_ARRAY instance is of size n if there are n dimensions. That is, DIMINFO contains 2 SDO_DIM_ELEMENT instances for twodimensional geometries, 3 instances for threedimensional geometries, and 4 instances for fourdimensional geometries. Each SDO_DIM_ELEMENT instance in the array must have valid (not null) values for the SDO_LB, SDO_UB, and SDO_TOLERANCE attributes.
Note: The number of dimensions reflected in the DIMINFO information must match the number of dimensions of each geometry object in the layer. 
For an explanation of tolerance and how to determine the appropriate SDO_TOLERANCE value, see Section 1.5.5, especially Section 1.5.5.1.
Spatial assumes that the varying length array is ordered by dimension. The DIMINFO varying length array must be ordered by dimension in the same way the ordinates for the points in SDO_ORDINATES varying length array are ordered. For example, if the SDO_ORDINATES varying length array contains {X1, Y1, ..., Xn, Yn}, then the first DIMINFO entry must define the X dimension and the second DIMINFO entry must define the Y dimension.
Example 21 in Section 2.1 shows the use of the SDO_GEOMETRY and SDO_DIM_ARRAY types. This example demonstrates how geometry objects (hypothetical market areas for colas) are represented, and how the COLA_MARKETS feature table and the USER_SDO_GEOM_METADATA view are populated with the data for those objects.
The SRID column should contain either of the following: the SRID value for the coordinate system for all geometries in the column, or NULL if no specific coordinate system should be associated with the geometries. (For information about coordinate systems, see Chapter 6.)
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.7. The spatial index data and metadata are stored in tables that are created and maintained by the Spatial indexing routines. These tables are created in the schema of the owner of the feature (underlying) table that has a spatial index created on a column of type SDO_GEOMETRY.
There are two sets of spatial index metadata views for each schema (user): xxx_SDO_INDEX_INFO and xxx_SDO_INDEX_METADATA, where xxx can be USER or ALL. These views are readonly to users; they are created and maintained by the Spatial indexing routines.
The following views contain basic information about spatial indexes:
USER_SDO_INDEX_INFO contains index information for all spatial tables owned by the user.
ALL_SDO_INDEX_INFO contains index information for all spatial tables on which the user has SELECT permission.
The USER_SDO_INDEX_INFO and ALL_SDO_INDEX_INFO views contain the same columns, as shown Table 23, except that the USER_SDO_INDEX_INFO view does not contain the SDO_INDEX_OWNER column. (The columns are listed in their order in the view definition.)
Table 23 Columns in the xxx_SDO_INDEX_INFO Views
Column Name  Data Type  Purpose 

SDO_INDEX_OWNER  VARCHAR2  Owner of the index (ALL_SDO_INDEX_INFO views only). 
INDEX_NAME  VARCHAR2  Name of the index. 
TABLE_NAME  VARCHAR2  Name of the table containing the column on which this index is built. 
COLUMN_NAME  VARCHAR2  Name of the column on which this index is built. 
SDO_INDEX_TYPE  VARCHAR2  Contains QTREE (for a quadtree index) or RTREE (for an Rtree index). 
SDO_INDEX_TABLE  VARCHAR2  Name of the spatial index table (described in Section 2.5.2). 
SDO_INDEX_STATUS  VARCHAR2  Contains DEFERRED if the index status has been set to deferred (using the index_status keyword with the ALTER INDEX statement) and VALID if the index status is not deferred. 
The following views contain detailed information about spatial index metadata:
USER_SDO_INDEX_METADATA contains index information for all spatial tables owned by the user. (USER_SDO_INDEX_METADATA is the same as SDO_INDEX_METADATA, which was the only metadata view for Oracle Spatial release 8.1.5.)
ALL_SDO_INDEX_METADATA contains index information for all spatial tables on which the user has SELECT permission.
Note: These views were new for release 8.1.6. If you are upgrading from an earlier release of Spatial, see Appendix A. 
The USER_SDO_INDEX_METADATA and ALL_SDO_INDEX_METADATA views contain the same columns, as shown Table 24. (The columns are listed in their order in the view definition.)
Table 24 Columns in the xxx_SDO_INDEX_METADATA Views
Column Name  Data Type  Purpose 

SDO_INDEX_OWNER  VARCHAR2  Owner of the index. 
SDO_INDEX_TYPE  VARCHAR2  Contains QTREE (for a quadtree index) or RTREE (for an Rtree index). 
SDO_INDEX_NAME  VARCHAR2  Name of the index. 
SDO_INDEX_TABLE  VARCHAR2  Name of the spatial index table (described in Section 2.5.2). 
SDO_INDEX_PRIMARY  NUMBER  Indicates if this is a primary or secondary index. 1 = primary, 2 = secondary. 
SDO_INDEX_PARTITION  VARCHAR2  For a partitioned index, name of the index partition. 
SDO_PARTITIONED  NUMBER  Contains 0 if the index is not partitioned or 1 if the index is partitioned. 
SDO_TSNAME  VARCHAR2  Schema name of the SDO_INDEX_TABLE. 
SDO_COLUMN_NAME  VARCHAR2  Name of the column on which this index is built. 
SDO_INDEX_DIMS  NUMBER  Number of dimensions of the geometry objects in the column on which this index is built. 
SDO_RTREE_HEIGHT  NUMBER  Height of the Rtree. 
SDO_RTREE_NUM_NODES  NUMBER  Number of nodes in the Rtree. 
SDO_RTREE_DIMENSIONALITY  NUMBER  Number of dimensions indexed. 
SDO_RTREE_FANOUT  NUMBER  Maximum number of children in each Rtree node. 
SDO_RTREE_ROOT  VARCHAR2  Rowid corresponding to the root node of the Rtree in the index table. 
SDO_RTREE_SEQ_NAME  VARCHAR2  Sequence name associated with the Rtree. 
SDO_RTREE_PCTFREE  NUMBER  Minimum percentage of slots in each index tree node to be left empty when an Rtree index is created. 
SDO_LAYER_GTYPE  VARCHAR2  Contains DEFAULT if the layer can contain both point and polygon data, or a value from the Geometry Type column of Table 21 in Section 2.2.1. 
SDO_LEVEL  NUMBER  The fixed tiling level at which to tile all objects in the geometry column for a quadtree index. 
SDO_NUMTILES  NUMBER  Suggested number of tiles per object that should be used to approximate the shape for a quadtree index. 
SDO_MAXLEVEL  NUMBER  Maximum level for any tile for any object for a quadtree index. It will always be greater than the SDO_LEVEL value. 
SDO_COMMIT_INTERVAL  NUMBER  Number of geometries (rows) to process, during index creation, before committing the insertion of spatial index entries into the SDOINDEX table. (Applies to quadtree indexes only.) 
SDO_FIXED_META  RAW  If applicable, this column contains the metadata portion of the SDO_GROUPCODE or SDO_CODE for a fixedlevel index. 
SDO_TABLESPACE  VARCHAR2  Same as in the SQL CREATE TABLE statement. Tablespace in which to create the SDOINDEX table. 
SDO_INITIAL_EXTENT  VARCHAR2  Same as in the SQL CREATE TABLE statement. 
SDO_NEXT_EXTENT  VARCHAR2  Same as in the SQL CREATE TABLE statement. 
SDO_PCTINCREASE  NUMBER  Same as in the SQL CREATE TABLE statement. 
SDO_MIN_EXTENTS  NUMBER  Same as in the SQL CREATE TABLE statement. 
SDO_MAX_EXTENTS  NUMBER  Same as in the SQL CREATE TABLE statement. 
SDO_RTREE_QUALITY  NUMBER  Quality score for an index. See the information about Rtree quality in Section 1.7.2. 
SDO_INDEX_VERSION  NUMBER  Internal version number of the index. 
SDO_INDEX_GEODETIC  VARCHAR2  Contains TRUE if the index is geodetic (see Section 4.1.2) and FALSE if the index is not geodetic. 
SDO_INDEX_STATUS  VARCHAR2  Contains DEFERRED if the index status has been set to deferred (using the index_status keyword with the ALTER INDEX statement) and VALID if the index status is not deferred. 
For an Rtree index, a spatial index table (each SDO_INDEX_TABLE entry as described in Table 24 in Section 2.5.1) contains the columns shown in Table 25.
Table 25 Columns in an RTree Spatial Index Data Table
Column Name  Data Type  Purpose 

NODE_ID  NUMBER  Unique ID number for this node of the tree. 
NODE_LEVEL  NUMBER  Level of the node in the tree. Leaf nodes (nodes whose entries point to data items in the base table) are at level 1, their parent nodes are at level 2, and so on. 
INFO  BLOB  Other information in a node. Includes an array of <child_mbr, child_rowid> pairs (maximum of fanout value, or number of children for such pairs in each Rtree node), where child_rowid is the rowid of a child node, or the rowid of a data item from the base table. 
Each Rtree spatial index table has an associated sequence object (SDO_RTREE_SEQ_NAME in the USER_SDO_INDEX_METADATA view, described in Table 24 in Section 2.5.1.2). The sequence is used to ensure that simultaneous updates can be performed to the index by multiple concurrent users.
The sequence name is the index table name with the letter S replacing the letter T before the underscore (for example, the sequence object MDRS_5C01$ is associated with the index table MDRT_5C01$).
Geometry functions that involve measurement allow an optional unit
parameter to specify the unit of measurement for a specified distance or area, if a georeferenced coordinate system (SDO_SRID value) is associated with the input geometry or geometries. The unit
parameter is not valid for geometries with a null SDO_SRID value (that is, an orthogonal Cartesian system). For information about support for coordinate systems, see Chapter 6.
The default unit of measure is the one associated with the georeferenced coordinate system. The unit of measure for most coordinate systems is the meter, and in these cases the default unit for distances is meter and the default unit for areas is square meter. By using the unit
parameter, however, you can have Spatial automatically convert and return results that are more meaningful to application users, for example, displaying the distance to a restaurant in miles.
The unit
parameter must be enclosed in single quotation marks and contain the string unit=
and a valid SDO_UNIT value from the MDSYS.SDO_DIST_UNITS or MDSYS.SDO_AREA_UNITS table. For example, 'unit=KM' in the following example (using data and definitions from Example 64 in Section 6.8) specifies kilometers as the unit of measurement:
SELECT c.name, SDO_GEOM.SDO_LENGTH(c.shape, m.diminfo, 'unit=KM') FROM cola_markets_cs c, user_sdo_geom_metadata m WHERE m.table_name = 'COLA_MARKETS_CS' AND m.column_name = 'SHAPE';
Spatial uses the information in the MDSYS.SDO_DIST_UNITS and MDSYS.SDO_AREA_UNITS tables to determine which unit names are valid and what ratios to use in comparing or converting between different units.
The MDSYS.SDO_DIST_UNITS table contains the columns shown in Table 26.
Table 26 Columns in the SDO_DIST_UNITS Table
The MDSYS.SDO_AREA_UNITS table contains the columns shown in Table 27.
Table 27 Columns in the SDO_AREA_UNITS Table
For a complete list of supported unit strings, unit names, and conversion factors, view the contents of the MDSYS.SDO_DIST_UNITS and MDSYS.SDO_AREA_UNITS tables. For example:
SELECT * from MDSYS.SDO_DIST_UNITS; SELECT * from MDSYS.SDO_AREA_UNITS;