7
Geometry Functions for Object-Relational Model

This chapter contains descriptions of the geometry functions, which can be grouped into the following categories:

Relationship (True/False) between two objects: RELATE, WITHIN_DISTANCE
Validation: VALIDATE_GEOMETRY, VALIDATE_LAYER
Single-object operations: SDO_AREA, SDO_BUFFER, SDO_CENTROID, SDO_CONVEXHULL, SDO_LENGTH, SDO_POINTONSURFACE
Two-object operations: SDO_DISTANCE, SDO_DIFFERENCE, SDO_INTERSECTION, SDO_UNION, SDO_XOR

This group also includes the following deprecated functions: SDO_POLY_DIFFERENCE, SDO_POLY_INTERSECTION, SDO_POLY_UNION, SDO_POLY_XOR

The geometry functions are listed Table 7-1, and some usage information follows the table.

Table 7-1 Geometric Functions for the Object-Relational Model

Function	Description
SDO_GEOM.RELATE	Determines how two objects interact.
SDO_GEOM.SDO_AREA	Computes the area of a two-dimensional polygon.
SDO_GEOM.SDO_BUFFER	Generates a buffer polygon around a geometry.
SDO_GEOM.SDO_CENTROID	Returns the centroid of a polygon.
SDO_GEOM.SDO_CONVEXHULL	Returns a polygon-type object that represents the convex hull of a geometry object.
SDO_GEOM.SDO_DIFFERENCE	Returns a geometry object that is the topological difference (MINUS operation) of two geometry objects.
SDO_GEOM.SDO_DISTANCE	Computes the distance between two geometry objects.
SDO_GEOM.SDO_INTERSECTION	Returns a geometry object that is the topological intersection (AND operation) of two geometry objects.
SDO_GEOM.SDO_LENGTH	Computes the length or perimeter of a geometry.
SDO_GEOM.SDO_POINTONSURFACE	Returns a point that is guaranteed to be on the surface of a polygon.
SDO_GEOM.SDO_POLY_DIFFERENCE	Generates a polygon representing the difference between two geometries.
SDO_GEOM.SDO_POLY_INTERSECTION	Generates a polygon representing the intersection of two geometries.
SDO_GEOM.SDO_POLY_UNION	Generates a polygon representing the union of two geometries.
SDO_GEOM.SDO_POLY_XOR	Generates a polygon representing the symmetric difference between two geometries.
SDO_GEOM.SDO_UNION	Returns a geometry object that is the topological union (OR operation) of two geometry objects.
SDO_GEOM.SDO_XOR	Returns a geometry object that is the topological symmetric difference (XOR operation) of two geometry objects.
SDO_GEOM.VALIDATE_GEOMETRY	Determines if a geometry is valid.
SDO_GEOM.VALIDATE_LAYER	Determines if all the geometries stored in a column are valid.
SDO_GEOM.WITHIN_DISTANCE	Determines if two geometries are within a specified Euclidean distance from one another.

Note:
The SDO_POLY_xxx functions are deprecated and will not be supported in future versions of Oracle Spatial. You should use instead the corresponding "generic" (not restricted to polygons) SDO_xxx functions: SDO_GEOM.SDO_DIFFERENCE, SDO_GEOM.SDO_INTERSECTION, SDO_GEOM.SDO_UNION, and SDO_GEOM.SDO_XOR.

The following usage information applies to the geometry functions. (See also the Usage Notes under the reference information for each function.)

Certain combinations of input parameters and operations can return a null value, that is, an empty geometry. For example, requesting the intersection of two disjoint geometry objects returns a null value.
A null value (empty geometry) as an input parameter to a geometry function (for example, SDO_GEOM.RELATE) produces an error.
Certain operations can return a geometry of a different type than one or both input geometries. For example, the intersection of a line and an overlapping polygon returns a line, the intersection of two lines returns a point, and the intersection of two tangent polygons returns a line.

SDO_GEOM.RELATE

Purpose

Examines two geometry objects to determine their spatial relationship.

Format

SDO_GEOM.RELATE(

geom1 IN MDSYS.SDO_GEOMETRY,

dim1 IN MDSYS.SDO_DIM_ARRAY,

mask IN VARCHAR2,

geom2 IN MDSYS.SDO_GEOMETRY,

dim2 IN MDSYS.SDO_DIM_ARRAY

) RETURN VARCHAR2;

SDO_GEOM.RELATE(

geom1 IN MDSYS.SDO_GEOMETRY,

mask IN VARCHAR2,

geom2 IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN VARCHAR2;

Parameters

geom1

Geometry object.

dim1

Dimensional information array corresponding to geom1, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

mask

Specifies a list of relationships to check. See the list of keywords in the Usage Notes.

geom2

Geometry object.

dim2

Dimensional information array corresponding to geom2, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

tolerance

Tolerance value (see Section 1.5.4).

Usage Notes

The MDSYS.SDO_GEOM.RELATE function can return the following types of answers:

If you pass a mask listing one or more relationships, the function returns the name of the relationship if it is true for the pair of geometries. If all of the relationships are false, the procedure returns FALSE.
If you pass the DETERMINE keyword in mask, the function returns the one relationship keyword that best matches the geometries. DETERMINE can only be used when SDO_GEOM.RELATE is in the SELECT clause of the SQL statement.
If you pass the ANYINTERACT keyword in mask, the function returns TRUE if the two geometries are not disjoint.

The following mask relationships can be tested:

ANYINTERACT - Returns TRUE if the objects are not disjoint.
CONTAINS - Returns CONTAINS if the second object is entirely within the first object and the object boundaries do not touch; otherwise, returns FALSE.
COVEREDBY - Returns COVEREDBY if the first object is entirely within the second object and the object boundaries touch at one or more points; otherwise, returns FALSE.
COVERS - Returns COVERS if the second object is entirely within the first object and the boundaries touch in one or more places; otherwise, returns FALSE.
DISJOINT - Returns DISJOINT if the objects have no common boundary or interior points; otherwise, returns FALSE.
EQUAL - Returns EQUAL if the objects share every point of their boundaries and interior, including any holes in the objects; otherwise, returns FALSE.
INSIDE - Returns INSIDE if the first object is entirely within the second object and the object boundaries do not touch; otherwise, returns FALSE.
OVERLAPBDYDISJOINT - Returns OVERLAPBDYDISJOINT if the objects overlap, but their boundaries do not interact; otherwise, returns FALSE.
OVERLAPBDYINTERSECT - Returns OVERLAPBDYINTERSECT if the objects overlap, and their boundaries intersect in one or more places; otherwise, returns FALSE.
TOUCH - Returns TOUCH if the two objects share a common boundary point, but no interior points; otherwise, returns FALSE.

Values for mask can be combined using a logical Boolean operator OR. For example, `INSIDE + TOUCH' returns 'INSIDE + TOUCH' or 'FALSE' depending on the outcome of the test.

If the function format with tolerance is used, all geometry objects must be defined using 4-digit SDO_GTYPE values (explained in Section 2.2.1).

Example

The following example checks if there is any spatial interaction between geometry objects cola_b and cola_d. (The example uses the definitions and data from Section 2.1.)

SELECT SDO_GEOM.RELATE(
  c_b.shape, m.diminfo, 'anyinteract', c_d.shape, m.diminfo) 
  FROM cola_markets c_b, cola_markets c_d, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' 
  AND c_b.name = 'cola_b' AND c_d.name = 'cola_d';

SDO_GEOM.RELATE(C_B.SHAPE,M.DIMINFO,'ANYINTERACT',C_D.SHAPE,M.DIMINFO)          
--------------------------------------------------------------------------------
FALSE

SDO_GEOM.SDO_AREA

Purpose

Returns the area of a two-dimensional polygon.

Format

SDO_GEOM.SDO_AREA(

geom IN MDSYS.SDO_GEOMETRY,

dim IN MDSYS.SDO_DIM_ARRAY

) RETURN NUMBER;

SDO_GEOM.SDO_AREA(

geom IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN NUMBER;

Parameters

geom

Geometry object.

dim

Dimensional information array corresponding to geom, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

tolerance

Tolerance value (see Section 1.5.4).

Usage Notes

This function works with any polygon, including polygons with holes.

This function does not support the units parameter that is included with the LOCATOR_WITHIN_DISTANCE operator of interMedia Locator, which is a component of the Oracle interMedia product.

If the function format with tolerance is used, all geometry objects must be defined using 4-digit SDO_GTYPE values (explained in Section 2.2.1).

Example

The following example returns the areas of geometry objects stored in the cola_markets table. The first statement returns the areas all objects; the second returns just the area of cola_a. (The example uses the definitions and data from Section 2.1.)

-- Return the areas of all cola markets.
SELECT c.name, SDO_GEOM.SDO_AREA(c.shape, m.diminfo) 
  FROM cola_markets c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE';

NAME                             SDO_GEOM.SDO_AREA(C.SHAPE,M.DIMINFO)           
-------------------------------- ------------------------------------           
cola_a                                                             24           
cola_b                                                           16.5           
cola_c                                                              5           
cola_d                                                     12.5663706

-- Return the area of just cola_a.
SELECT c.name, SDO_GEOM.SDO_AREA(c.shape, m.diminfo) 
  FROM cola_markets c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' 
  AND c.name = 'cola_a';

NAME                             SDO_GEOM.SDO_AREA(C.SHAPE,M.DIMINFO)           
-------------------------------- ------------------------------------           
cola_a                                                             24

SDO_GEOM.SDO_BUFFER

Purpose

Generates a buffer polygon around a geometry object.

Format

SDO_GEOM.SDO_BUFFER(

geom IN MDSYS.SDO_GEOMETRY,

dim IN MDSYS.SDO_DIM_ARRAY,

distance IN NUMBER,

) RETURN MDSYS.SDO_GEOMETRY;

SDO_GEOM.SDO_BUFFER(

geom IN MDSYS.SDO_GEOMETRY,

distance IN NUMBER,

tolerance IN NUMBER

) RETURN MDSYS.SDO_GEOMETRY;

Parameters

geom

Geometry object.

dim

Dimensional information array corresponding to geom, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

distance

Euclidean distance value.

tolerance

Tolerance value (see Section 1.5.4).

Usage Notes

This function returns a geometry object representing the buffer polygon.

This function creates a rounded buffer around a point, line, or polygon. The buffer within a void is also rounded, and is the same distance from the inner boundary as the outer buffer is from the outer boundary. See Figure 1-11 for an illustration.

If the function format with tolerance is used, all geometry objects must be defined using 4-digit SDO_GTYPE values (explained in Section 2.2.1).

This function does not support the units parameter that is included with the LOCATOR_WITHIN_DISTANCE operator of interMedia Locator, which is a component of the Oracle interMedia product.

Example

The following example returns a polygon representing a buffer of 1 around cola_a. Note the "rounded" corners (for example, at .292893219,.292893219) in the returned polygon. (The example uses the definitions and data from Section 2.1.)

-- Generate a buffer of 1 unit around a geometry.
SELECT c.name, SDO_GEOM.SDO_BUFFER(c.shape, m.diminfo, 1) 
  FROM cola_markets c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS'  AND m.column_name = 'SHAPE' 
  AND c.name = 'cola_a';

NAME                                                                            
--------------------------------                                                
SDO_GEOM.SDO_BUFFER(C.SHAPE,M.DIMINFO,1)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z)
--------------------------------------------------------------------------------
cola_a                                                                          
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1005, 8, 1, 2, 2, 5, 2, 1,
 7, 2, 2, 11, 2, 1, 13, 2, 2, 17, 2, 1, 19, 2, 2, 23, 2, 1), SDO_ORDINATE_ARRAY(
0, 1, .292893219, .292893219, 1, 0, 5, 0, 5.70710678, .292893219, 6, 1, 6, 7, 5.
70710678, 7.70710678, 5, 8, 1, 8, .292893219, 7.70710678, 0, 7, 0, 1))

SDO_GEOM.SDO_CENTROID

Purpose

Returns the centroid of a polygon. (The centroid is also known as the "center of gravity.")

Format

SDO_GEOM.SDO_CENTROID(

geom IN MDSYS.SDO_GEOMETRY,

dim IN MDSYS.SDO_DIM_ARRAY

) RETURN MDSYS.SDO_GEOMETRY;

SDO_GEOM.SDO_CENTROID(

geom IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN MDSYS.SDO_GEOMETRY;

Parameters

geom

Geometry object.

dim

Dimensional information array corresponding to geom, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

tolerance

Tolerance value (see Section 1.5.4).

Usage Notes

This function returns a null value if geom is not a polygon or if geom is a multipolygon.

If the function format with tolerance is used, all geometry objects must be defined using 4-digit SDO_GTYPE values (explained in Section 2.2.1).

Example

The following example returns a geometry object that is the centroid of cola_c. (The example uses the definitions and data from Section 2.1.)

-- Return the centroid of a geometry.
SELECT c.name, SDO_GEOM.SDO_CENTROID(c.shape, m.diminfo) 
  FROM cola_markets c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' 
  AND c.name = 'cola_c';

NAME                                                                            
--------------------------------                                                
SDO_GEOM.SDO_CENTROID(C.SHAPE,M.DIMINFO)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z)
--------------------------------------------------------------------------------
cola_c                                                                          
SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(
4.73333333, 3.93333333))

SDO_GEOM.SDO_CONVEXHULL

Purpose

Returns a polygon-type object that represents the convex hull of a geometry object.

Format

SDO_GEOM.SDO_CONVEXHULL(

geom IN MDSYS.SDO_GEOMETRY,

dim IN MDSYS.SDO_DIM_ARRAY

) RETURN MDSYS.SDO_GEOMETRY;

SDO_GEOM.SDO_CONVEXHULL(

geom IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN MDSYS.SDO_GEOMETRY;

Parameters

geom

Geometry object.

dim

Dimensional information array corresponding to geom, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

tolerance

Tolerance value (see Section 1.5.4).

Usage Notes

The convex hull is a simple convex polygon that completely encloses the geometry object. Spatial uses as few straight-line sides as possible to create the smallest polygon that completely encloses the specified object. A convex hull is a convenient way to get an approximation of a complex geometry object.

The geometry object (geom) cannot be a circle.

This function returns a null value if geom is of point type or has fewer than three points or vertices.

If the function format with tolerance is used, all geometry objects must be defined using 4-digit SDO_GTYPE values (explained in Section 2.2.1).

Example

The following example returns a geometry object that is the convex hull of cola_c (The example uses the definitions and data from Section 2.1. This specific example, however, does not produce useful output -- the returned polygon is identical to the input polygon -- because the input polygon is already a simple convex polygon.)

-- Return the convex hull of a polygon.
SELECT c.name, SDO_GEOM.SDO_CONVEXHULL(c.shape, m.diminfo) 
  FROM cola_markets c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' 
  AND c.name = 'cola_c';

NAME                                                                            
--------------------------------                                                
SDO_GEOM.SDO_CONVEXHULL(C.SHAPE,M.DIMINFO)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, 
--------------------------------------------------------------------------------
cola_c                                                                          
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(6, 3, 6, 5, 4, 5, 3, 3, 6, 3))

SDO_GEOM.SDO_DIFFERENCE

Purpose

Returns a geometry object that is the topological difference (MINUS operation) of two geometry objects.

Format

SDO_GEOM.SDO_DIFFERENCE(

geom1 IN MDSYS.SDO_GEOMETRY,

dim1 IN MDSYS.SDO_DIM_ARRAY,

geom2 IN MDSYS.SDO_GEOMETRY,

dim2 IN MDSYS.SDO_DIM_ARRAY

) RETURN MDSYS.SDO_GEOMETRY;

SDO_GEOM.SDO_DIFFERENCE(

geom1 IN MDSYS.SDO_GEOMETRY,

geom2 IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN MDSYS.SDO_GEOMETRY;

Parameters

geom1

Geometry object.

dim1

Dimensional information array corresponding to geom1, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

geom2

Geometry object.

dim2

Dimensional information array corresponding to geom2, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

tolerance

Tolerance value (see Section 1.5.4).

Usage Notes

In Figure 7-1, the shaded area represents the polygon returned when SDO_DIFFERENCE is used with a square (geom1) and another polygon (geom2).

Figure 7-1 SDO_GEOM.SDO_DIFFERENCE

If the function format with tolerance is used, all geometry objects must be defined using 4-digit SDO_GTYPE values (explained in Section 2.2.1).

You should use this function instead of the deprecated function SDO_GEOM.SDO_POLY_DIFFERENCE.

Example

The following example returns a geometry object that is the topological difference (MINUS operation) of cola_a and cola_c. (The example uses the definitions and data from Section 2.1.)

-- Return the topological difference of two geometries.
SELECT SDO_GEOM.SDO_DIFFERENCE(c_a.shape, m.diminfo, c_c.shape, m.diminfo) 
  FROM cola_markets c_a, cola_markets c_c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' 
  AND c_a.name = 'cola_a' AND c_c.name = 'cola_c';

SDO_GEOM.SDO_DIFFERENCE(C_A.SHAPE,M.DIMINFO,C_C.SHAPE,M.DIMINFO)(SDO_GTYPE, SDO_
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(1, 7, 1, 1, 5, 1, 5, 3, 3, 3, 4, 5, 5, 5, 5, 7, 1, 7)

Note that in the returned polygon, the SDO_ORDINATE_ARRAY starts and ends at the same point (1, 7).

SDO_GEOM.SDO_DISTANCE

Purpose

Computes the distance between two geometry objects. The distance between two geometry objects is the distance between the closest pair of points or segments of the two objects.

Format

SDO_GEOM.SDO_DISTANCE(

geom1 IN MDSYS.SDO_GEOMETRY,

dim1 IN MDSYS.SDO_DIM_ARRAY,

geom2 IN MDSYS.SDO_GEOMETRY,

dim2 IN MDSYS.SDO_DIM_ARRAY

) RETURN NUMBER;

SDO_GEOM.SDO_DISTANCE(

geom1 IN MDSYS.SDO_GEOMETRY,

geom2 IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN NUMBER;

Parameters

geom1

Geometry object whose distance from geom2 is to be computed.

dim1

Dimensional information array corresponding to geom1, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

geom2

Geometry object whose distance from geom1 is to be computed.

dim2

Dimensional information array corresponding to geom2, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

tolerance

Tolerance value (see Section 1.5.4).

Usage Notes

This function does not support the units parameter that is included with the LOCATOR_WITHIN_DISTANCE operator of interMedia Locator, which is a component of the Oracle interMedia product.

If the function format with tolerance is used, all geometry objects must be defined using 4-digit SDO_GTYPE values (explained in Section 2.2.1).

Example

The following example returns the shortest distance between cola_b and cola_d. (The example uses the definitions and data from Section 2.1.)

-- Return the distance between two geometries.
SELECT SDO_GEOM.SDO_DISTANCE(c_b.shape, m.diminfo, c_d.shape, m.diminfo) 
  FROM cola_markets c_b, cola_markets c_d, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' 
  AND c_b.name = 'cola_b' AND c_d.name = 'cola_d';

SDO_GEOM.SDO_DISTANCE(C_B.SHAPE,M.DIMINFO,C_D.SHAPE,M.DIMINFO)       
--------------------------------------------------------------       
                                                    .846049894

SDO_GEOM.SDO_INTERSECTION

Purpose

Returns a geometry object that is the topological intersection (AND operation) of two geometry objects.

Format

SDO_GEOM.SDO_INTERSECTION(

geom1 IN MDSYS.SDO_GEOMETRY,

dim1 IN MDSYS.SDO_DIM_ARRAY,

geom2 IN MDSYS.SDO_GEOMETRY,

dim2 IN MDSYS.SDO_DIM_ARRAY

) RETURN MDSYS.SDO_GEOMETRY;

SDO_GEOM.SDO_INTERSECTION(

geom1 IN MDSYS.SDO_GEOMETRY,

geom2 IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN MDSYS.SDO_GEOMETRY;

Parameters

geom1

Geometry object.

dim1

Dimensional information array corresponding to geom1, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

geom2

Geometry object.

dim2

Dimensional information array corresponding to geom2, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

tolerance

Tolerance value (see Section 1.5.4).

Usage Notes

In Figure 7-2, the shaded area represents the polygon returned when SDO_INTERSECTION is used with a square (geom1) and another polygon (geom2).

Figure 7-2 SDO_GEOM.SDO_INTERSECTION

If the function format with tolerance is used, all geometry objects must be defined using 4-digit SDO_GTYPE values (explained in Section 2.2.1).

You should use this function instead of the deprecated function SDO_GEOM.SDO_POLY_INTERSECTION.

Example

The following example returns a geometry object that is the topological intersection (AND operation) of cola_a and cola_c. (The example uses the definitions and data from Section 2.1.)

-- Return the topological intersection of two geometries.
SELECT SDO_GEOM.SDO_INTERSECTION(c_a.shape, m.diminfo, c_c.shape, m.diminfo) 
  FROM cola_markets c_a, cola_markets c_c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' 
  AND c_a.name = 'cola_a' AND c_c.name = 'cola_c';

SDO_GEOM.SDO_INTERSECTION(C_A.SHAPE,M.DIMINFO,C_C.SHAPE,M.DIMINFO)(SDO_GTYPE, SD
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(4, 5, 3, 3, 5, 3, 5, 5, 4, 5))

Note that in the returned polygon, the SDO_ORDINATE_ARRAY starts and ends at the same point (4, 5).

SDO_GEOM.SDO_LENGTH

Purpose

Returns the length or perimeter of a geometry object.

Format

SDO_GEOM.SDO_LENGTH(

geom IN MDSYS.SDO_GEOMETRY,

dim IN MDSYS.SDO_DIM_ARRAY

) RETURN NUMBER;

SDO_GEOM.SDO_LENGTH(

geom IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN NUMBER;

Parameters

geom

Geometry object.

dim

Dimensional information array corresponding to geom, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

tolerance

Tolerance value (see Section 1.5.4).

Usage Notes

If the input polygon contains one or more holes, this function calculates the perimeters of the exterior boundary and all holes. It returns the sum of all the perimeters.

If the function format with tolerance is used, all geometry objects must be defined using 4-digit SDO_GTYPE values (explained in Section 2.2.1).

This function does not support the units parameter that is included with the LOCATOR_WITHIN_DISTANCE operator of interMedia Locator, which is a component of the Oracle interMedia product.

Example

The following example returns the perimeters of geometry objects stored in the cola_markets table. The first statement returns the perimeters all objects; the second returns just the perimeter of cola_a. (The example uses the definitions and data from Section 2.1.)

-- Return the perimeters of all cola markets.
SELECT c.name, SDO_GEOM.SDO_LENGTH(c.shape, m.diminfo) 
  FROM cola_markets c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE';

NAME                             SDO_GEOM.SDO_LENGTH(C.SHAPE,M.DIMINFO)         
-------------------------------- --------------------------------------         
cola_a                                                               20         
cola_b                                                       17.1622777         
cola_c                                                       9.23606798         
cola_d                                                       12.5663706

-- Return the area of just cola_a.
SELECT c.name, SDO_GEOM.SDO_LENGTH(c.shape, m.diminfo) 
  FROM cola_markets c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' 
  AND c.name = 'cola_a';

NAME                             SDO_GEOM.SDO_LENGTH(C.SHAPE,M.DIMINFO)         
-------------------------------- --------------------------------------         
cola_a                                                               20

SDO_GEOM.SDO_POINTONSURFACE

Purpose

Returns a point that is guaranteed to be on the surface of a polygon geometry object.

Format

SDO_GEOM.SDO_POINTONSURFACE(

geom IN MDSYS.SDO_GEOMETRY,

dim IN MDSYS.SDO_DIM_ARRAY

) RETURN MDSYS.SDO_GEOMETRY;

SDO_GEOM.SDO_POINTONSURFACE(

geom IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN MDSYS.SDO_GEOMETRY;

Parameters

geom

Polygon geometry object.

dim

Dimensional information array corresponding to geom, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

tolerance

Tolerance value (see Section 1.5.4).

Usage Notes

This function returns a point geometry object representing a point that is guaranteed to be on the surface of geom.

The returned point can be any point on the surface. You should not make any assumptions about where on the surface the returned point is, or whether the point is the same or different when the function is called multiple times with the same input parameter values.

If the function format with tolerance is used, all geometry objects must be defined using 4-digit SDO_GTYPE values (explained in Section 2.2.1).

Example

The following example returns a geometry object that is a point on the surface of cola_a. (The example uses the definitions and data from Section 2.1.)

-- Return a point on the surface of a geometry.
SELECT SDO_GEOM.SDO_POINTONSURFACE(c.shape, m.diminfo) 
  FROM cola_markets c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' 
  AND c.name = 'cola_a';

SDO_GEOM.SDO_POINTONSURFACE(C.SHAPE,M.DIMINFO)(SDO_GTYPE, SDO_SRID, SDO_POINT(X,
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(
1, 1))

SDO_GEOM.SDO_POLY_DIFFERENCE

Purpose

Returns a geometry object representing the difference (A minus B) of two polygon objects.

Format

SDO_GEOM.SDO_POLY_DIFFERENCE(

geom1 IN MDSYS.SDO_GEOMETRY,

dim1 IN MDSYS.SDO_DIM_ARRAY,

geom2 IN MDSYS.SDO_GEOMETRY,

dim2 IN MDSYS.SDO_DIM_ARRAY

) RETURN MDSYS.SDO_GEOMETRY;

Parameters

geom1

Polygon geometry object.

dim1

Dimensional information array corresponding to geom1, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

geom2

Polygon geometry object.

dim2

Dimensional information array corresponding to geom2, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

Usage Notes

The SDO_POLY_DIFFERENCE function is deprecated and will not be supported in future releases of Oracle Spatial. You should use instead the SDO_GEOM.SDO_DIFFERENCE function.

This function returns an empty geometry if any of the following occurs:

geom1 or geom2, or both, are not polygons.
geom1 and geom2 are tangent polygons. (Tangent polygons have only a common line or point, but do not otherwise intersect.)
geom2 is INSIDE geom1 or EQUAL to geom1.

SDO_GEOM.SDO_POLY_INTERSECTION

Purpose

Returns a geometry object representing the intersection (A and B) of two polygon objects.

Format

SDO_GEOM.SDO_POLY_INTERSECTION(

geom1 IN MDSYS.SDO_GEOMETRY,

dim1 IN MDSYS.SDO_DIM_ARRAY,

geom2 IN MDSYS.SDO_GEOMETRY,

dim2 IN MDSYS.SDO_DIM_ARRAY

) RETURN MDSYS.SDO_GEOMETRY;

Parameters

geom1

Polygon geometry object.

dim1

Dimensional information array corresponding to geom1, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

geom2

Polygon geometry object.

dim2

Dimensional information array corresponding to geom2, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

Usage Notes

The SDO_POLY_INTERSECTION function is deprecated and will not be supported in future releases of Oracle Spatial. You should use instead the SDO_GEOM.SDO_INTERSECTION function.

This function returns an empty geometry if any of the following occurs:

geom1 or geom2, or both, are not polygons.
geom1 and geom2 are tangent polygons. (Tangent polygons have only a common line or point, but do not otherwise intersect.)
geom1 or geom2 are disjoint.

SDO_GEOM.SDO_POLY_UNION

Purpose

Returns a geometry object representing the union (A or B) of two polygon objects.

Format

SDO_GEOM.SDO_POLY_UNION(

geom1 IN MDSYS.SDO_GEOMETRY,

dim1 IN MDSYS.SDO_DIM_ARRAY,

geom2 IN MDSYS.SDO_GEOMETRY,

dim2 IN MDSYS.SDO_DIM_ARRAY

) RETURN MDSYS.SDO_GEOMETRY;

Parameters

geom1

Polygon geometry object.

dim1

Dimensional information array corresponding to geom1, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

geom2

Polygon geometry object.

dim2

Dimensional information array corresponding to geom2, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

Usage Notes

The SDO_POLY_UNION function is deprecated and will not be supported in future releases of Oracle Spatial. You should use instead the SDO_GEOM.SDO_UNION function.

This function returns an empty geometry if any of the following occurs:

geom1 or geom2, or both, are not polygons.
geom1 and geom2 are tangent polygons. (Tangent polygons have only a common line or point, but do not otherwise intersect.)

SDO_GEOM.SDO_POLY_XOR

Purpose

Returns a geometry object representing the symmetric difference (A xor B) of two polygon objects.

Format

SDO_GEOM.SDO_POLY_XOR(

geom1 IN MDSYS.SDO_GEOMETRY,

dim1 IN MDSYS.SDO_DIM_ARRAY,

geom2 IN MDSYS.SDO_GEOMETRY,

dim2 IN MDSYS.SDO_DIM_ARRAY

) RETURN MDSYS.SDO_GEOMETRY;

Parameters

geom1

Polygon geometry object.

dim1

Dimensional information array corresponding to geom1, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

geom2

Polygon geometry object.

dim2

Dimensional information array corresponding to geom2, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

Usage Notes

The SDO_POLY_XOR function is deprecated and will not be supported in future releases of Oracle Spatial. You should use instead the SDO_GEOM.SDO_XOR function.

This function returns an empty geometry if any of the following occurs:

geom1 or geom2, or both, are not polygons.
geom1 and geom2 are tangent polygons. (Tangent polygons have only a common line or point, but do not otherwise intersect.)

SDO_GEOM.SDO_UNION

Purpose

Returns a geometry object that is the topological union (OR operation) of two geometry objects.

Format

SDO_GEOM.SDO_UNION(

geom1 IN MDSYS.SDO_GEOMETRY,

dim1 IN MDSYS.SDO_DIM_ARRAY,

geom2 IN MDSYS.SDO_GEOMETRY,

dim2 IN MDSYS.SDO_DIM_ARRAY

) RETURN MDSYS.SDO_GEOMETRY;

SDO_GEOM.SDO_UNION(

geom1 IN MDSYS.SDO_GEOMETRY,

geom2 IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN MDSYS.SDO_GEOMETRY;

Parameters

geom1

Geometry object.

dim1

Dimensional information array corresponding to geom1, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

geom2

Geometry object.

dim2

Dimensional information array corresponding to geom2, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

tolerance

Tolerance value (see Section 1.5.4).

Usage Notes

In Figure 7-3, the shaded area represents the polygon returned when SDO_UNION is used with a square (geom1) and another polygon (geom2).

Figure 7-3 SDO_GEOM.SDO_UNION

If the function format with tolerance is used, all geometry objects must be defined using 4-digit SDO_GTYPE values (explained in Section 2.2.1).

You should use this function instead of the deprecated function SDO_GEOM.SDO_POLY_UNION.

Example

The following example returns a geometry object that is the topological union (OR operation) of cola_a and cola_c. (The example uses the definitions and data from Section 2.1.)

-- Return the topological intersection of two geometries.
SELECT SDO_GEOM.SDO_UNION(c_a.shape, m.diminfo, c_c.shape, m.diminfo) 
  FROM cola_markets c_a, cola_markets c_c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' 
  AND c_a.name = 'cola_a' AND c_c.name = 'cola_c';

SDO_GEOM.SDO_UNION(C_A.SHAPE,M.DIMINFO,C_C.SHAPE,M.DIMINFO)(SDO_GTYPE, SDO_SRID,
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(5, 5, 5, 7, 1, 7, 1, 1, 5, 1, 5, 3, 6, 3, 6, 5, 5, 5))

Note that in the returned polygon, the SDO_ORDINATE_ARRAY starts and ends at the same point (5, 5).

SDO_GEOM.SDO_XOR

Purpose

Returns a geometry object that is the topological symmetric difference (XOR operation) of two geometry objects.

Format

SDO_GEOM.SDO_XOR(

geom1 IN MDSYS.SDO_XOR,

dim1 IN MDSYS.SDO_DIM_ARRAY,

geom2 IN MDSYS.SDO_GEOMETRY,

dim2 IN MDSYS.SDO_DIM_ARRAY

) RETURN MDSYS.SDO_GEOMETRY;

SDO_GEOM.SDO_XOR(

geom1 IN MDSYS.SDO_GEOMETRY,

geom2 IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN MDSYS.SDO_GEOMETRY;

Parameters

geom1

Geometry object.

dim1

Dimensional information array corresponding to geom1, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

geom2

Geometry object.

dim2

Dimensional information array corresponding to geom2, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

tolerance

Tolerance value (see Section 1.5.4).

Usage Notes

In Figure 7-4, the shaded area represents the polygon returned when SDO_XOR is used with a square (geom1) and another polygon (geom2).

Figure 7-4 SDO_GEOM.SDO_XOR

If the function format with tolerance is used, all geometry objects must be defined using 4-digit SDO_GTYPE values (explained in Section 2.2.1).

You should use this function instead of the deprecated function SDO_GEOM.SDO_POLY_XOR.

Example

The following example returns a geometry object that is the topological symmetric difference (XOR operation) of cola_a and cola_c. (The example uses the definitions and data from Section 2.1.)

-- Return the topological symmetric difference of two geometries.
SELECT SDO_GEOM.SDO_XOR(c_a.shape, m.diminfo, c_c.shape, m.diminfo) 
  FROM cola_markets c_a, cola_markets c_c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' 
  AND c_a.name = 'cola_a' AND c_c.name = 'cola_c';

SDO_GEOM.SDO_XOR(C_A.SHAPE,M.DIMINFO,C_C.SHAPE,M.DIMINFO)(SDO_GTYPE, SDO_SRID, S
--------------------------------------------------------------------------------
SDO_GEOMETRY(2007, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1, 11, 1003, 1), SDO
_ORDINATE_ARRAY(5, 5, 5, 3, 6, 3, 6, 5, 5, 5, 1, 7, 1, 1, 5, 1, 5, 3, 3, 3, 4, 5
, 5, 5, 5, 7, 1, 7))

Note that in the returned polygon is a multipolygon (SDO_GTYPE = 2007), and the SDO_ORDINATE_ARRAY describes two polygons: one starting and ending at (5, 5) and the other starting and ending at (1, 7).

SDO_GEOM.VALIDATE_GEOMETRY

Purpose

Performs a consistency check for valid geometry types. The function checks the representation of the geometry from the tables against the element definitions.

Format

SDO_GEOM.VALIDATE_GEOMETRY(

geom IN MDSYS.SDO_GEOMETRY,

dim IN MDSYS.SDO_DIM_ARRAY

) RETURN VARCHAR2;

SDO_GEOM.VALIDATE_GEOMETRY(

geom IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN VARCHAR2;

Parameters

geom

Geometry object.

dim

Dimensional information array corresponding to geom, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

tolerance

Tolerance value (see Section 1.5.4).

Usage Notes

If the geometry is valid, this function returns TRUE.

If the geometry is not valid, this function returns one of the following

An Oracle error message number based on the specific reason the geometry is invalid
FALSE if the geometry fails for some other reason

This function checks for the following:

Polygons have at least four points, which includes the point that closes the polygon. (The last point is the same as the first.)
Line strings have at least two points.
In a heterogeneous collection (SDO_GTYPE 4) or multipolygon (SDO_GTYPE 7), all polygons are disjoint.
1-digit and 4-digit SDO_ETYPE values are not mixed (that is, both used) in defining polygon ring elements.

If the function format with tolerance is used, all geometry objects must be defined using 4-digit SDO_GTYPE values (explained in Section 2.2.1).

Example

The following example validates the geometry of cola_c. (The example uses the definitions and data from Section 2.1.)

-- Is a geometry valid?
SELECT c.name, SDO_GEOM.VALIDATE_GEOMETRY(c.shape, m.diminfo) 
  FROM cola_markets c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' 
  AND c.name = 'cola_c';

NAME                                                                            
--------------------------------                                                
SDO_GEOM.VALIDATE_GEOMETRY(C.SHAPE,M.DIMINFO)                                   
--------------------------------------------------------------------------------
cola_c                                                                          
TRUE

SDO_GEOM.VALIDATE_LAYER

Purpose

Examines a geometry column to determine if the stored geometries follow the defined rules for geometry objects.

Format

SDO_GEOM.VALIDATE_LAYER(

geom_table IN VARCHAR2,

geom_column IN VARCHAR2,

pkey_column IN VARCHAR2,

result_table IN VARCHAR2);

Parameters

geom_table

Geometry table.

geom_column

Geometry object column to be examined.

pkey_column

The primary key column. This can be the rowid or any other single-column primary key; however, the specified column must be numeric (NUMBER data type).

result_table

Result table to hold the validation results.

Usage Notes

This procedure populates the result table with validation results. (VALIDATE_LAYER is a procedure, not a function.)

An empty result table (result_table parameter) must be created prior to calling this procedure. The format of the result table is: (pkey_column NUMBER, result VARCHAR2(10)).

In the result table, for each pkey_column the result column contains TRUE if the geometry is valid. If it is invalid, the result column for that geometry contains an Oracle error message number. You can then look up this error message to determine the cause of the failure.

This procedure checks for the following:

Polygons have at least four points, which includes the point that closes the polygon. (The last point is the same as the first.)
Line strings have at least two points.
In a heterogeneous collection (SDO_GTYPE d004) or multipolygon (SDO_GTYPE d007), all polygons are disjoint. For an explanation of SDO_GTYPE values, see Section 2.2.

Example

The following example validates the geometry objects stored in the shape column of the cola_markets table. The example includes the creation of the results table. (The example uses the definitions and data from Section 2.1.)

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

PL/SQL procedure successfully completed.

SELECT * from val_results;

    MKT_ID RESULT
---------- ----------
         1 TRUE
         2 TRUE
         3 TRUE
         4 TRUE

SDO_GEOM.WITHIN_DISTANCE

Purpose

Determines if two spatial objects are within some specified Euclidean distance from each other.

Format

SDO_GEOM.WITHIN_DISTANCE(

geom1 IN MDSYS.SDO_GEOMETRY,

dim1 IN MDSYS.SDO_DIM_ARRAY,

distance IN NUMBER,

geom2 IN MDSYS.SDO_GEOMETRY,

dim2 IN MDSYS.SDO_DIM_ARRAY

) RETURN VARCHAR2;

SDO_GEOM.WITHIN_DISTANCE(

geom1 IN MDSYS.SDO_GEOMETRY,

distance IN NUMBER,

geom2 IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN VARCHAR2;

Parameters

geom1

Geometry object.

dim1

Dimensional information array corresponding to geom1, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

distance

Euclidean distance value.

geom2

Geometry object.

dim2

Dimensional information array corresponding to geom2, usually selected from one of the xxx_SDO_GEOM_METADATA views (see Section 2.4).

tolerance

Tolerance value (see Section 1.5.4).

Usage Notes

This function returns TRUE for object pairs that are within the specified distance, and FALSE otherwise.

The distance between two extended objects (for example, nonpoint objects such as lines and polygons) is defined as the minimum distance between these two objects. Thus the distance between two adjacent polygons is zero.

If the function format with tolerance is used, all geometry objects must be defined using 4-digit SDO_GTYPE values (explained in Section 2.2.1).

Example

The following example checks if cola_b and cola_d are within 1 unit apart at the shortest distance between them. (The example uses the definitions and data from Section 2.1.)

-- Are two geometries within 1 unit of distance apart?
SELECT SDO_GEOM.WITHIN_DISTANCE(c_b.shape, m.diminfo, 1,
     c_d.shape, m.diminfo) 
  FROM cola_markets c_b, cola_markets c_d, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' 
  AND c_b.name = 'cola_b' AND c_d.name = 'cola_d';

SDO_GEOM.WITHIN_DISTANCE(C_B.SHAPE,M.DIMINFO,1,C_D.SHAPE,M.DIMINFO)        
--------------------------------------------------------------------------------
TRUE

7 Geometry Functions for Object-Relational Model

Table 7-1 Geometric Functions for the Object-Relational Model

SDO_GEOM.RELATE

Purpose

Format

Parameters

Usage Notes

Example

Related Topics

SDO_GEOM.SDO_AREA

Purpose

Format

Parameters

Usage Notes

Example

Related Topics

SDO_GEOM.SDO_BUFFER

Purpose

Format

Parameters

Usage Notes

Example

Related Topics

SDO_GEOM.SDO_CENTROID

Purpose

Format

Parameters

Usage Notes

Example

Related Topics

SDO_GEOM.SDO_CONVEXHULL

Purpose

Format

Parameters

Usage Notes

Example

Related Topics

SDO_GEOM.SDO_DIFFERENCE

Purpose

Format

Parameters

Usage Notes

Figure 7-1 SDO_GEOM.SDO_DIFFERENCE

Example

Related Topics

SDO_GEOM.SDO_DISTANCE

Purpose

Format

Parameters

Usage Notes

Example

Related Topics

SDO_GEOM.SDO_INTERSECTION

Purpose

Format

Parameters

Usage Notes

Figure 7-2 SDO_GEOM.SDO_INTERSECTION

Example

Related Topics

SDO_GEOM.SDO_LENGTH

Purpose

Format

Parameters

Usage Notes

Example

Related Topics

SDO_GEOM.SDO_POINTONSURFACE

Purpose

Format

Parameters

Usage Notes

Example

Related Topics

SDO_GEOM.SDO_POLY_DIFFERENCE

Purpose

Format

Parameters

Usage Notes

Related Topics

7
Geometry Functions for Object-Relational Model