Oracle Spatial User's Guide and Reference
Release 9.0.1
Part Number A88805-01

Home
Book List
Contents
Index
Master Index
Feedback

Go to previous page Go to next page

2
The Object-Relational Schema

The object-relational implementation of 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.

2.1 Simple Example: Inserting, Indexing, and Querying Spatial Data

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 user-defined 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 2-1 shows the areas of interest for four colas.

Figure 2-1 Areas of Interest for Simple Example


Illustration of the areas of interest for the Oracle Spatial examples in this section and in other sections in the manual.

Example 2-1 performs the following operations:

Many concepts and techniques in Example 2-1 are explained in detail in other sections of this chapter.

Example 2-1 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).

CREATE TABLE cola_markets (
  mkt_id NUMBER PRIMARY KEY,
  name VARCHAR2(32),
  shape MDSYS.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 user-defined 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',
  MDSYS.SDO_GEOMETRY(
    2003,  -- 2-dimensional polygon
    NULL,
    NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior)
    MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to
          -- define rectangle (lower left and upper right) with
          -- Cartesian-coordinate 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',
  MDSYS.SDO_GEOMETRY(
    2003,  -- 2-dimensional polygon
    NULL,
    NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
    MDSYS.SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)
  )
);

INSERT INTO cola_markets VALUES(
  3,
  'cola_c',
  MDSYS.SDO_GEOMETRY(
    2003,  -- 2-dimensional polygon
    NULL,
    NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
    MDSYS.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',
  MDSYS.SDO_GEOMETRY(
    2003,  -- 2-dimensional polygon
    NULL,
    NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,4), -- one circle
    MDSYS.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, table-column combination; here: COLA_MARKETS and SHAPE).

INSERT INTO USER_SDO_GEOM_METADATA 
  VALUES (
  'cola_markets',
  'shape',
  MDSYS.SDO_DIM_ARRAY(   -- 20X20 grid
    MDSYS.SDO_DIM_ELEMENT('X', 0, 20, 0.005),
    MDSYS.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 created an R-tree index. 
-- Following line was for an earlier quadtree index:
--    PARAMETERS('SDO_LEVEL = 8');

-------------------------------------------------------------------
-- 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(c.shape, 0.005)
   FROM cola_markets c WHERE c.name = 'cola_c';

-- Is a layer valid? (First, create the results table.)
CREATE TABLE validation_results (mkt_id number, result varchar2(10));
EXECUTE SDO_GEOM.VALIDATE_LAYER('COLA_MARKETS', 'SHAPE', 'MKT_ID', 
  'VALIDATION_RESULTS');
SELECT * from validation_results;

2.2 SDO_GEOMETRY Object Type

In the Spatial object-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. 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.

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 MDSYS.SDO_ELEM_INFO_ARRAY,
 SDO_ORDINATES MDSYS.SDO_ORDINATE_ARRAY);

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

2.2.1 SDO_GTYPE

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.

The SDO_GTYPE value is 4 digits in the format dltt, where:

Table 2-1 shows the valid SDO_GTYPE values. The Geometry Type and Description values reflect the OGIS specification.

Table 2-1 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.)  
Foot 1 For a polygon with holes, enter the exterior boundary first, followed by any interior boundaries.
Foot 2 Polygons in the collection can be disjoint.

The d in the Value column of Table 2-1 is the number of dimensions: 2, 3, or 4. For example, an SDO_GTYPE value of 2003 indicates a 2-dimensional polygon.

Note:

The pre-release 8.1.6 format of a 1-digit SDO_GTYPE value is still supported. If a 1-digit value is used, however, Oracle Spatial determines the number of dimensions from the DIMINFO column of the metadata views described in Section 2.4.

Also, if 1-digit SDO_GTYPE values are converted to 4-digit 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 2-dimensional objects). Points and lines are considered 2-dimensional objects. (However, see Section 6.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 2-dimensional and 3-dimensional 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 10.

2.2.2 SDO_SRID

SDO_SRID 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 5.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 5.

2.2.3 SDO_POINT

SDO_POINT is defined using 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 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.

Note:

Do not use the SDO_POINT attribute in defining a linear referencing system (LRS) point. For information about LRS, see Chapter 6. 

2.2.4 SDO_ELEM_INFO

SDO_ELEM_INFO 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:

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 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 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 OCIColSize 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 2-2.

Table 2-2 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.5. 

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. Note that you must specify a point for each vertex, and the last point specified must be identical to the first (to close the polygon). For example, for a 4-sided 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, 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 lower-left and the upper-right, are required to describe it.

Using this type (that is, defining a rectangle using only two points) is not supported for geodetic data; it is supported only for data associated with a Cartesian coordinate system. With geodetic data, define a rectangle using 5 points (with point 5 the same as point 1) and an SDO_INTERPRETATION value of 1. (You can also use the SDO_CS.VIEWPORT_TRANSFORM function to convert optimized rectangles to valid geodetic rectangles for use with the SDO_FILTER operator.) 

1003 or 2003 

4 

Circle type. Described by three 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 2-4 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 the same.

See Section 2.3.4 and Figure 2-5 for an example of a geometry using this type. 

2.2.5 SDO_ORDINATES

SDO_ORDINATES 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 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 xxx_SDO_GEOM_METADATA views, described in Section 2.4.

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

2.2.6 Usage Considerations

You should use the SDO_GTYPE values as shown in Table 2-1; however, Spatial does not check or enforce all geometry consistency constraints. Spatial does check the following:

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

2.3 Geometry Examples

This section contains examples of several geometry types.

2.3.1 Rectangle

Figure 2-2 illustrates the rectangle that represents cola_a in the example in Section 2.1.

Figure 2-2 Rectangle


Illustration of a rectangle.

In the SDO_GEOMETRY definition of the geometry illustrated in Figure 2-2:

Example 2-2 shows a SQL statement that inserts the geometry illustrated in Figure 2-2 into the database.

Example 2-2 SQL Statement to Insert a Rectangle

INSERT INTO cola_markets VALUES(
  1,
  'cola_a',
  MDSYS.SDO_GEOMETRY(
    2003,  -- 2-dimensional polygon
    NULL,
    NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior)
    MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to
          -- define rectangle (lower left and upper right) with
          -- Cartesian-coordinate data
  )
);

2.3.2 Polygon with a Hole

Figure 2-3 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).

Figure 2-3 Polygon with a Hole


Illustration of a polygon with a hole. Exterior ring has 8 sides; interior ring (hole) is a rectangle.

In the SDO_GEOMETRY definition of the geometry illustrated in Figure 2-3:

Example 2-3 shows a SQL statement that inserts the geometry illustrated in Figure 2-3 into the database.

Example 2-3 SQL Statement to Insert a Polygon with a Hole

INSERT INTO cola_markets VALUES(
  10,
  'polygon_with_hole',
  MDSYS.SDO_GEOMETRY(
    2003,  -- 2-dimensional polygon
    NULL,
    NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1), -- polygon with hole
    MDSYS.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):

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):

2.3.3 Compound Line String

Figure 2-4 illustrates 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 (10,10) and (10,14) describe the straight line segment, and points (10,14), (10,6), and (14,10) describe the circular arc.

Figure 2-4 Compound Line String


Illustration of a compound line string.

In the SDO_GEOMETRY definition of the geometry illustrated in Figure 2-4:

Example 2-4 shows a SQL statement that inserts the geometry illustrated in Figure 2-4 into the database.

Example 2-4 SQL Statement to Insert a Compound Line String

INSERT INTO cola_markets VALUES(
  11,
  'compound_line_string',
  MDSYS.SDO_GEOMETRY(
    2002,
    NULL,
    NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(1,4,2, 1,2,1, 3,2,2), -- compound line string
    MDSYS.SDO_ORDINATE_ARRAY(10,10, 10,14, 6,10, 14,10)
  )
);

2.3.4 Compound Polygon

Figure 2-5 illustrates 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 (6,10), (10,1), and (14,10) describe one acute angle-shaped 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)}.

Figure 2-5 Compound Polygon


Illustration of a compound polygon.

In the SDO_GEOMETRY definition of the geometry illustrated in Figure 2-5:

Example 2-5 shows a SQL statement that inserts the geometry illustrated in Figure 2-5 into the database.

Example 2-5 SQL Statement to Insert a Compound Polygon

INSERT INTO cola_markets VALUES(
  12,
  'compound_polygon',
  MDSYS.SDO_GEOMETRY(
    2003,  -- 2-dimensional polygon
    NULL,
    NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(1,1005,2, 1,2,1, 5,2,2), -- compound polygon
    MDSYS.SDO_ORDINATE_ARRAY(6,10, 10,1, 14,10, 10,14, 6,10)
  )
);

2.3.5 Type 0 (Zero) Element

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:

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 2-6 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.

Figure 2-6 Geometry with Type 0 (Zero) Element


Illustration of a type 0 (zero) element.

In the example shown in Figure 2-6:

In this example:

Example 2-6 shows a SQL statement that inserts the geometry with a type 0 element (similar to the geometry illustrated in Figure 2-6) 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 2-6 SQL Statement to Insert a Geometry with a Type 0 Element

INSERT INTO cola_markets VALUES(
  13,
  'type_zero_element_geom',
  MDSYS.SDO_GEOMETRY(
    2003,  -- 2-dimensional polygon
    NULL,
    NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(1,0,57, 11,1003,3), -- 1st is type 0 element
    MDSYS.SDO_ORDINATE_ARRAY(6,6, 12,6, 9,8, 6,10, 12,10, 6,4, 12,12)
  )
);

2.4 Geometry Metadata Structure

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:

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 other two views (ALL_SDO_GEOM_METADATA and DBA_SDO_GEOM_METADATA) are 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 migrating from an earlier release of Spatial, see Appendix A and the information about the SDO_MIGRATE.TO_CURRENT procedure in Chapter 16. 

Each metadata view has the following definition: 

(
  TABLE_NAME   VARCHAR2(32),
  COLUMN_NAME  VARCHAR2(32),
  DIMINFO      MDSYS.SDO_DIM_ARRAY,
  SRID         NUMBER
);

In addition, the ALL_SDO_GEOM_METADATA and DBA_SDO_GEOM_METADATA views have an OWNER column identifying the schema that owns the table specified in TABLE_NAME.

2.4.1 TABLE_NAME

The TABLE_NAME column contains the name of a feature table, such as ROADS or PARKS, that has a column of type SDO_GEOMETRY.

2.4.2 COLUMN_NAME

The COLUMN_NAME column contains the name of the column of type SDO_GEOMETRY. For the tables ROADS and PARKS, this column is called THEGEOMETRY, and therefore the xxx_SDO_GEOM_METADATA views should contain rows with values (ROADS, THEGEOMETRY, SOMEDIMINFO1, NULL) and (PARKS, THEGEOMETRY, SOMEDIMINFO2, NULL).

2.4.3 DIMINFO

The DIMINFO column is a varying length array of an object type, ordered by dimension, and has one entry per 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 2-dimensional geometries, 3 instances for 3-dimensional geometries, and 4 instances for 4-dimensional 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, 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. 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 SomeDimInfo1 must define the X dimension and SomeDimInfo2 must define the Y dimension.

Section 3.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 USER_SDO_GEOM_METADATA view are populated with the data for those objects.

2.4.4 SRID

The SRID column should contain either of the following: the SRID value for the coordinate system (see Chapter 5) for all geometries in the column, or NULL if no specific coordinate system should be associated with the geometries.

2.5 Spatial Index-Related Structures

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.

2.5.1 Spatial Index Views

There are two sets of spatial index metadata views per schema (user): xxx_SDO_INDEX_INFO and xxx_SDO_INDEX_METADATA, where xxx can be USER, DBA, or ALL. These views are read-only to users; they are created and maintained by the Spatial indexing routines.

2.5.1.1 xxx_SDO_INDEX_INFO Views

The following views contain basic information about spatial indexes:

The USER_SDO_INDEX_INFO, ALL_SDO_INDEX_INFO, and DBA_SDO_INDEX_INFO views contain the same columns, as shown Table 2-3. (The columns are listed in their order in the view definition.)

Table 2-3 Columns in the xxx_SDO_INDEX_INFO Views  
Column Name  Data Type  Purpose 

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 R-tree index). 

SDO_INDEX_TABLE 

VARCHAR2 

Name of the spatial index table (described in Section 2.5.2). 

2.5.1.2 xxx_SDO_INDEX_METADATA Views

The following views contain detailed information about spatial index metadata:

The USER_SDO_INDEX_METADATA, ALL_SDO_INDEX_METADATA, and DBA_SDO_INDEX_METADATA views contain the same columns, as shown Table 2-4. (The columns are listed in their order in the view definition.)

Table 2-4 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 R-tree 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 R-tree for an R-tree index. 

SDO_RTREE_NUM_NODES 

NUMBER 

Number of nodes in the R-tree for an R-tree index. 

SDO_RTREE_DIMENSIONALITY 

NUMBER 

Number of dimensions indexed for an R-tree index. 

SDO_RTREE_FANOUT 

NUMBER 

Maximum number of children in each R-tree node for an R-tree index. 

SDO_RTREE_ROOT 

VARCHAR2 

Rowid corresponding to the root node of the R-tree in the index table for an R-tree index. 

SDO_RTREE_SEQ_NAME 

VARCHAR2 

Sequence name associated with the R-tree for an R-tree index.  

SDO_RTREE_PCTFREE 

VARCHAR2 

Minimum percentage of slots in each index tree node to be left empty when an R-tree 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 2-1 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. 

SDO_FIXED_META 

RAW 

If applicable, this column contains the metadata portion of the SDO_GROUPCODE or SDO_CODE for a fixed-level index. 

SDO_TABLESPACE 

VARCHAR2 

Same as in the 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_RTREE_QUALITY 

NUMBER 

Quality score for an R-tree index. Do not attempt to interpret this value directly; instead, use the SDO_TUNE.ANALYZE_RTREE procedure and the SDO_TUNE.QUALITY_DEGRADATION function, which are described in Chapter 17. 

SDO_INDEX_VERSION 

NUMBER 

Internal version number of the index. 

2.5.2 Spatial Index Table Definition

The information in each quadtree spatial index table (each SDO_INDEX_TABLE entry as described in Table 2-4 in Section 2.5.1) depends on whether the index is an R-tree index or a quadtree index.

For an R-tree index, the spatial index table contains the columns shown in Table 2-5.

Table 2-5 Columns in an R-tree 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 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 in each R-tree node, such pairs), where child_rowid is the rowid of a child node, or the rowid of a data item from the base table. 

For a quadtree index, the spatial index table contains the columns shown in Table 2-6.

Table 2-6 Columns in a Quadtree Spatial Index Data Table  
Column Name  Data Type  Purpose 

SDO_CODE 

RAW 

Index entry for the object in the row identified by SDO_ROWID. 

SDO_ROWID 

ROWID 

Rowid of a row in a feature table containing the indexed object. 

SDO_STATUS 

VARCHAR2 

Contains I if the tile is inside the geometry, or contains B if the tile is on the boundary of the geometry. 

SDO_GROUPCODE  

RAW 

Index entry at level SDO_LEVEL (hybrid indexes only). 

For a quadtree index, the SDO_CODE, SDO_ROWID, and SDO_STATUS columns are always present. The SDO_GROUPCODE column is present only when the selected index type is HYBRID.

2.5.3 R-Tree Index Sequence Object

Each R-tree spatial index table has an associated sequence object (SDO_RTREE_SEQ_NAME in the USER_SDO_INDEX_METADATA view, described in Table 2-4 in Section 2.5.1). 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 as a suffix.

2.6 Unit of Measurement Support

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

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 specifies kilometers as the unit of measurement: 

SELECT c.name, SDO_GEOM.SDO_LENGTH(c.shape, m.diminfo, 'unit=KM') 
  FROM cola_markets c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS' 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 2-7.

Table 2-7 Columns in the SDO_DIST_UNITS Table  
Column Name  Data Type  Purpose 

SDO_UNIT 

VARCHAR2 

Unit string to be specified with the unit parameter. Examples: M, KM, CM, MM, MILE, NAUT_MILE, FOOT, INCH 

UNIT_NAME 

VARCHAR2 

Descriptive name of the unit. Examples: Meter, Kilometer, Centimeter, Millimeter, Mile, Nautical Mile, Foot, Inch 

CONVERSION_FACTOR 

NUMBER 

Ratio of the unit to 1 meter. For example, the conversion factor for a meter is 1.0, and the conversion factor for a mile is 1609.344. 

The MDSYS.SDO_AREA_UNITS table contains the columns shown in Table 2-8.

Table 2-8 Columns in the SDO_AREA_UNITS Table  
Column Name  Data Type  Purpose 

SDO_UNIT 

VARCHAR2 

Unit string to be specified with the unit parameter. Examples: SQ_M, SQ_KM, SQ_CM, SQ_MM, SQ_MILE, SQ_FOOT, SQ_INCH 

UNIT_NAME 

VARCHAR2 

Descriptive name of the unit. Examples: Square Meter, Square Kilometer, Square Centimeter, Square Millimeter, Square Mile, Square Foot, Square Inch 

CONVERSION_FACTOR 

NUMBER 

Ratio of the unit to 1 square meter. For example, the conversion factor for a square meter is 1.0, and the conversion factor for a square mile is 2589988. 

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;

Go to previous page Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.
All Rights Reserved.

Home
Book List
Contents
Index
Master Index
Feedback