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


View PDF 
This chapter contains descriptions of the geometryrelated PL/SQL subprograms in the SDO_GEOM package, which can be grouped into the following categories:
Relationship (True/False) between two objects: RELATE, WITHIN_DISTANCE
Validation: VALIDATE_GEOMETRY_WITH_CONTEXT, VALIDATE_LAYER_WITH_CONTEXT
Singleobject operations: SDO_ARC_DENSIFY, SDO_AREA, SDO_BUFFER, SDO_CENTROID, SDO_CONVEXHULL, SDO_LENGTH, SDO_MAX_MBR_ORDINATE, SDO_MIN_MBR_ORDINATE, SDO_MBR, SDO_POINTONSURFACE
Twoobject operations: SDO_DISTANCE, SDO_DIFFERENCE, SDO_INTERSECTION, SDO_UNION, SDO_XOR
The geometry subprograms are listed Table 131, and some usage information follows the table.
Table 131 Geometry Subprograms
Subprogram  Description 

SDO_GEOM.RELATE 
Determines how two objects interact. 
SDO_GEOM.SDO_ARC_DENSIFY 
Changes each circular arc into an approximation consisting of straight lines, and each circle into a polygon consisting of a series of straight lines that approximate the circle. 
SDO_GEOM.SDO_AREA 
Computes the area of a twodimensional polygon. 
SDO_GEOM.SDO_BUFFER 
Generates a buffer polygon around or inside a geometry. 
SDO_GEOM.SDO_CENTROID 
Returns the centroid of a polygon. 
SDO_GEOM.SDO_CONVEXHULL 
Returns a polygontype 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_MAX_MBR_ORDINATE 
Returns the maximum value for the specified ordinate (dimension) of the minimum bounding rectangle of a geometry object. 
SDO_GEOM.SDO_MBR 
Returns the minimum bounding rectangle of a geometry. 
SDO_GEOM.SDO_MIN_MBR_ORDINATE 
Returns the minimum value for the specified ordinate (dimension) of the minimum bounding rectangle of a geometry object. 
SDO_GEOM.SDO_POINTONSURFACE 
Returns a point that is guaranteed to be on the surface of a polygon. 
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_WITH_CONTEXT 
Determines if a geometry is valid, and returns context information if the geometry is invalid. 
SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT 
Determines if all geometries stored in a column are valid, and returns context information about any invalid geometries. 
SDO_GEOM.WITHIN_DISTANCE 
Determines if two geometries are within a specified distance from one another. 
The following usage information applies to the geometry subprograms. (See also the Usage Notes under the reference information for each subprogram.)
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.
Format
SDO_GEOM.RELATE(
geom1 IN SDO_GEOMETRY,
dim1 IN SDO_DIM_ARRAY,
mask IN VARCHAR2,
geom2 IN SDO_GEOMETRY,
dim2 IN SDO_DIM_ARRAY
) RETURN VARCHAR2;
or
SDO_GEOM.RELATE(
geom1 IN SDO_GEOMETRY,
mask IN VARCHAR2,
geom2 IN SDO_GEOMETRY,
tol IN NUMBER
) RETURN VARCHAR2;
Description
Examines two geometry objects to determine their spatial relationship.
Parameters
Geometry object.
Dimensional information array corresponding to geom1
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Specifies a list of relationships to check. See the list of keywords in the Usage Notes.
Geometry object.
Dimensional information array corresponding to geom2
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Tolerance value (see Section 1.5.5).
Usage Notes
For better performance, use the SDO_RELATE operator or one of its convenience operator formats (all described in Chapter 12) instead of the SDO_GEOM.RELATE
function, unless you need to use the function. For example, the DETERMINE mask keyword does not apply with the SDO_RELATE operator. For more information about performance considerations with operators and functions, see Section 1.9.
The 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 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.
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.
ON: Returns ON if the boundary and interior of a line (the first object) is completely on the boundary of a polygon (the second object); 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 the 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 tol
is used, all geometry objects must be defined using 4digit SDO_GTYPE values (explained in Section 2.2.1).
An exception is raised if geom1
and geom2
are based on different coordinate systems.
Examples
The following example finds the relationship between each geometry in the SHAPE column and the cola_b
geometry. (The example uses the definitions and data from Section 2.1. The output is reformatted for readability.)
SELECT c.name, SDO_GEOM.RELATE(c.shape, 'determine', c_b.shape, 0.005) relationship FROM cola_markets c, cola_markets c_b WHERE c_b.name = 'cola_b'; NAME RELATIONSHIP  cola_a TOUCH cola_b EQUAL cola_c OVERLAPBDYINTERSECT cola_d DISJOINT
Related Topics
SDO_RELATE operator
Format
SDO_GEOM.SDO_ARC_DENSIFY(
geom IN SDO_GEOMETRY,
dim IN SDO_DIM_ARRAY
params IN VARCHAR2
) RETURN SDO_GEOMETRY;
or
SDO_GEOM.SDO_ARC_DENSIFY(
geom IN SDO_GEOMETRY,
tol IN NUMBER
params IN VARCHAR2
) RETURN SDO_GEOMETRY;
Description
Returns a geometry in which each circular arc in the input geometry is changed into an approximation of the circular arc consisting of straight lines, and each circle is changed into a polygon consisting of a series of straight lines that approximate the circle.
Parameters
Geometry object.
Dimensional information array corresponding to geom
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Tolerance value (see Section 1.5.5).
A quoted string containing an arc tolerance value and optionally a unit value. See the Usage Notes for an explanation of the format and meaning.
Usage Notes
If you have geometries in a projected coordinate system that contain circles or circular arcs, you can use this function to densify them into regular polygons. You can then use the resulting straightline polygon geometries for any Spatial operations, or you can transform them to any projected or geodetic coordinate system.
The params
parameter is a quoted string that must contain the arc_tolerance
keyword and that may contain the unit
keyword to identify the unit of measurement associated with the arc_tolerance
value. For example:
'arc_tolerance=0.05 unit=km'
The arc_tolerance
keyword specifies, for each arc in the geometry, the maximum length of the perpendicular line between the surface of the arc and the straight line between the start and end points of the arc. Figure 131 shows a line whose length is the arc_tolerance
value for the arc between points A and B.
The arc_tolerance
keyword value must be greater than or equal to the tolerance value associated with the geometry. As you increase the arc_tolerance
keyword value, the resulting polygon has fewer sides and a smaller area; as you decrease the arc_tolerance
keyword value, the resulting polygon has more sides and a larger area (but never larger than the original geometry).
If the unit
keyword is specified, the value must be an SDO_UNIT value from the MDSYS.SDO_DIST_UNITS table (for example, 'unit=KM'). If the unit
keyword is not specified, the unit of measurement associated with the geometry is used. See Section 2.6 for more information about unit of measurement specification.
If the function format with tol
is used, all geometry objects must be defined using 4digit SDO_GTYPE values (explained in Section 2.2.1).
Examples
The following example returns the geometry that results from the arc densification of cola_d
, which is a circle. (The example uses the definitions and data from Section 2.1.)
 Arc densification of the circle cola_d SELECT c.name, SDO_GEOM.SDO_ARC_DENSIFY(c.shape, m.diminfo, 'arc_tolerance=0.05') FROM cola_markets c, user_sdo_geom_metadata m WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' AND c.name = 'cola_d'; NAME  SDO_GEOM.SDO_ARC_DENSIFY(C.SHAPE,M.DIMINFO,'ARC_TOLERANCE=0.05')(SDO_GTYPE, SDO_  cola_d SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(8, 7, 8.76536686, 7.15224093, 9.41421356, 7.58578644, 9.84775907, 8.23463314, 10, 9, 9.84775907, 9.76536686, 9.41421356, 10.4142136, 8.76536686, 10.8477591, 8, 11, 7.23463314, 10.8477591, 6.58578644, 10.4142136, 6.15224093, 9.76536686, 6 , 9, 6.15224093, 8.23463314, 6.58578644, 7.58578644, 7.23463314, 7.15224093, 8, 7))
Related Topics
Format
SDO_GEOM.SDO_AREA(
geom IN SDO_GEOMETRY,
dim IN SDO_DIM_ARRAY
[, unit IN VARCHAR2]
) RETURN NUMBER;
or
SDO_GEOM.SDO_AREA(
geom IN SDO_GEOMETRY,
tol IN NUMBER
[, unit IN VARCHAR2]
) RETURN NUMBER;
Description
Returns the area of a twodimensional polygon.
Parameters
Geometry object.
Dimensional information array corresponding to geom
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Unit of measurement: a quoted string with unit=
and an SDO_UNIT value from the MDSYS.SDO_AREA_UNITS table (for example, 'unit=SQ_KM'). See Section 2.6 for more information about unit of measurement specification.
If this parameter is not specified, the unit of measurement associated with the data is assumed. For geodetic data, the default unit of measurement is square meters.
Tolerance value (see Section 1.5.5).
Usage Notes
This function works with any polygon, including polygons with holes.
Lines that close to form a ring have no area.
If the function format with tol
is used, all geometry objects must be defined using 4digit SDO_GTYPE values (explained in Section 2.2.1).
Examples
The following example returns the areas of geometry objects stored in the COLA_MARKETS table. The first statement returns the areas of 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 name, SDO_GEOM.SDO_AREA(shape, 0.005) FROM cola_markets; NAME SDO_GEOM.SDO_AREA(SHAPE,0.005)   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, 0.005) FROM cola_markets c WHERE c.name = 'cola_a'; NAME SDO_GEOM.SDO_AREA(C.SHAPE,0.005)   cola_a 24
Related Topics
None.
Format
SDO_GEOM.SDO_BUFFER(
geom IN SDO_GEOMETRY,
dim IN SDO_DIM_ARRAY,
dist IN NUMBER
[, params IN VARCHAR2]
) RETURN SDO_GEOMETRY;
or
SDO_GEOM.SDO_BUFFER(
geom IN SDO_GEOMETRY,
dist IN NUMBER,
tol IN NUMBER
[, params IN VARCHAR2]
) RETURN SDO_GEOMETRY;
Description
Generates a buffer polygon around or inside a geometry object.
Parameters
Geometry object.
Dimensional information array corresponding to geom
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Distance value. If the value is positive, the buffer is generated around the geometry; if the value is negative (valid only for polygons), the buffer is generated inside the geometry. The absolute value of this parameter must be greater than the tolerance value, as specified in the dimensional array (dim
parameter) or in the tol
parameter.
Tolerance value (see Section 1.5.5).
A quoted string with one or both of the following keywords:
unit
and an SDO_UNIT value from the MDSYS.SDO_DIST_UNITS table. It identifies the unit of measurement associated with the dist
parameter value, and also with the arc tolerance value if the arc_tolerance
keyword is specified. See Section 2.6 for more information about unit of measurement specification.
arc_tolerance
and an arc tolerance value. See the Usage Notes for the SDO_GEOM.SDO_ARC_DENSIFY function in this chapter for more information about the arc_tolerance
keyword.
For example: 'unit=km arc_tolerance=0.05'
If the input geometry is geodetic data, this parameter is required, and arc_tolerance
must be specified, because Spatial uses the value to perform arc densification in computing the result. If the input geometry is Cartesian or projected data, arc_tolerance
has no effect and should not be specified.
If this parameter is not specified for a Cartesian or projected geometry, or if the arc_tolerance
keyword is specified for a geodetic geometry but the unit
keyword is not specified, the unit of measurement associated with the data is assumed.
Usage Notes
This function returns a geometry object representing the buffer polygon.
This function creates a rounded buffer around a point, line, or polygon, or inside a 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 17 for an illustration.
If the function format with tol
is used, all geometry objects must be defined using 4digit SDO_GTYPE values (explained in Section 2.2.1).
With geodetic data, this function is supported by approximations, as explained in Section 6.7.3.
Examples
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 nongeodetic 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))
The following example returns a polygon representing a buffer of 1 around cola_a
using the geodetic definitions and data from Section 6.8.
 Generate a buffer of 1 kilometer around a geometry. SELECT c.name, SDO_GEOM.SDO_BUFFER(c.shape, m.diminfo, 1, 'unit=km arc_tolerance=0.05') 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,'UNIT=KMARC_TOLERANCE=0.05')(SDO_GTYPE,  cola_a SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(.991023822, 1.00002073, .992223711, .995486419, .99551726, .99217077, 1.00001 929, .990964898, 4.99998067, .990964929, 5.00448268, .9921708, 5.00777624, .9954 86449, 5.00897618, 1.00002076, 5.00904194, 6.99997941, 5.00784065, 7.00450033, 5 .00454112, 7.00781357, 5.00002479, 7.009034, .999975166, 7.00903403, .995458814, 7.00781359, .992159303, 7.00450036, .990958058, 6.99997944, .991023822, 1.00002 073))
Related Topics
Format
SDO_GEOM.SDO_CENTROID(
geom1 IN SDO_GEOMETRY,
dim1 IN SDO_DIM_ARRAY
) RETURN SDO_GEOMETRY;
or
SDO_GEOM.SDO_CENTROID(
geom1 IN SDO_GEOMETRY,
tol IN NUMBER
) RETURN SDO_GEOMETRY;
Description
Returns a point geometry that is the centroid of a polygon, multipolygon, point, or point cluster. (The centroid is also known as the "center of gravity.")
For an input geometry consisting of multiple objects, the result is weighted by the area of each polygon in the geometry objects. If the geometry objects are a mixture of polygons and points, the points are not used in the calculation of the centroid. If the geometry objects are all points, the points have equal weight.
Parameters
Geometry object.
Dimensional information array corresponding to geom
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Tolerance value (see Section 1.5.5).
Usage Notes
The function returns a null value if geom1
is not a polygon, multipolygon, point, or point cluster.
If geom1
is a point, the function returns the point (the input geometry).
If the function format with tol
is used, all geometry objects must be defined using 4digit SDO_GTYPE values (explained in Section 2.2.1).
With geodetic data, this function is supported by approximations, as explained in Section 6.7.3.
Depending on the shape and complexity of the input geometry, the returned point might not be on the surface of the input geometry.
Examples
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))
Related Topics
None.
Format
SDO_GEOM.SDO_CONVEXHULL(
geom1 IN SDO_GEOMETRY,
dim1 IN SDO_DIM_ARRAY
) RETURN SDO_GEOMETRY;
or
SDO_GEOM.SDO_CONVEXHULL(
geom1 IN SDO_GEOMETRY,
tol IN NUMBER
) RETURN SDO_GEOMETRY;
Description
Returns a polygontype object that represents the convex hull of a geometry object.
Parameters
Geometry object.
Dimensional information array corresponding to geom
1, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Tolerance value (see Section 1.5.5).
Usage Notes
The convex hull is a simple convex polygon that completely encloses the geometry object. Spatial uses as few straightline 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.
If the geometry (geom1
) contains any arc elements, the function calculates the minimum bounding rectangle (MBR) for each arc element and uses these MBRs in calculating the convex hull of the geometry. If the geometry object (geom1
) is a circle, the function returns a square that minimally encloses the circle.
The function returns a null value if geom1
is of point type, has fewer than three points or vertices, or consists of multiple points all in a straight line.
If the function format with tol
is used, all geometry objects must be defined using 4digit SDO_GTYPE values (explained in Section 2.2.1).
With geodetic data, this function is supported by approximations, as explained in Section 6.7.3.
Examples
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 has the same vertices as 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))
Related Topics
None.
Format
SDO_GEOM.SDO_DIFFERENCE(
geom1 IN SDO_GEOMETRY,
dim1 IN SDO_DIM_ARRAY,
geom2 IN SDO_GEOMETRY,
dim2 IN SDO_DIM_ARRAY
) RETURN SDO_GEOMETRY;
or
SDO_GEOM.SDO_DIFFERENCE(
geom1 IN SDO_GEOMETRY,
geom2 IN SDO_GEOMETRY,
tol IN NUMBER
) RETURN SDO_GEOMETRY;
Description
Returns a geometry object that is the topological difference (MINUS operation) of two geometry objects.
Parameters
Geometry object.
Dimensional information array corresponding to geom1
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Geometry object.
Dimensional information array corresponding to geom2
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Tolerance value (see Section 1.5.5).
Usage Notes
In Figure 132, the shaded area represents the polygon returned when SDO_DIFFERENCE is used with a square (geom1
) and another polygon (geom2
).
If the function format with tol
is used, all geometry objects must be defined using 4digit SDO_GTYPE values (explained in Section 2.2.1).
An exception is raised if geom1
and geom2
are based on different coordinate systems.
Examples
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).
Related Topics
Format
SDO_GEOM.SDO_DISTANCE(
geom1 IN SDO_GEOMETRY,
dim1 IN SDO_DIM_ARRAY,
geom2 IN SDO_GEOMETRY,
dim2 IN SDO_DIM_ARRAY
[, unit IN VARCHAR2]
) RETURN NUMBER;
or
SDO_GEOM.SDO_DISTANCE(
geom1 IN SDO_GEOMETRY,
geom2 IN SDO_GEOMETRY,
tol IN NUMBER
[, unit IN VARCHAR2]
) RETURN NUMBER;
Description
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.
Parameters
Geometry object whose distance from geom2
is to be computed.
Dimensional information array corresponding to geom1
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Geometry object whose distance from geom1
is to be computed.
Dimensional information array corresponding to geom2
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Unit of measurement: a quoted string with unit=
and an SDO_UNIT value from the MDSYS.SDO_DIST_UNITS table (for example, 'unit=KM'). See Section 2.6 for more information about unit of measurement specification.
If this parameter is not specified, the unit of measurement associated with the data is assumed.
Tolerance value (see Section 1.5.5).
Usage Notes
If the function format with tol
is used, all geometry objects must be defined using 4digit SDO_GTYPE values (explained in Section 2.2.1).
An exception is raised if geom1
and geom2
are based on different coordinate systems.
Examples
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, 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'; SDO_GEOM.SDO_DISTANCE(C_B.SHAPE,C_D.SHAPE,0.005)  .846049894
Related Topics
Format
SDO_GEOM.SDO_INTERSECTION(
geom1 IN SDO_GEOMETRY,
dim1 IN SDO_DIM_ARRAY,
geom2 IN SDO_GEOMETRY,
dim2 IN SDO_DIM_ARRAY
) RETURN SDO_GEOMETRY;
or
SDO_GEOM.SDO_INTERSECTION(
geom1 IN SDO_GEOMETRY,
geom2 IN SDO_GEOMETRY,
tol IN NUMBER
) RETURN SDO_GEOMETRY;
Description
Returns a geometry object that is the topological intersection (AND operation) of two geometry objects.
Parameters
Geometry object.
Dimensional information array corresponding to geom1
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Geometry object.
Dimensional information array corresponding to geom2
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Tolerance value (see Section 1.5.5).
Usage Notes
In Figure 133, the shaded area represents the polygon returned when SDO_INTERSECTION is used with a square (geom1
) and another polygon (geom2
).
If the function format with tol
is used, all geometry objects must be defined using 4digit SDO_GTYPE values (explained in Section 2.2.1).
An exception is raised if geom1
and geom2
are based on different coordinate systems.
Examples
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, 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'; SDO_GEOM.SDO_INTERSECTION(C_A.SHAPE,C_C.SHAPE,0.005)(SDO_GTYPE, SDO_SRID, SDO_PO  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).
Related Topics
Format
SDO_GEOM.SDO_LENGTH(
geom IN SDO_GEOMETRY,
dim IN SDO_DIM_ARRAY
[, unit IN VARCHAR2]
) RETURN NUMBER;
or
SDO_GEOM.SDO_LENGTH(
geom IN SDO_GEOMETRY,
tol IN NUMBER
[, unit IN VARCHAR2]
) RETURN NUMBER;
Description
Returns the length or perimeter of a geometry object.
Parameters
Geometry object.
Dimensional information array corresponding to geom
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Unit of measurement: a quoted string with unit=
and an SDO_UNIT value from the MDSYS.SDO_DIST_UNITS table (for example, 'unit=KM'). See Section 2.6 for more information about unit of measurement specification.
If this parameter is not specified, the unit of measurement associated with the data is assumed. For geodetic data, the default unit of measurement is meters.
Tolerance value (see Section 1.5.5).
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 perimeters.
If the function format with tol
is used, all geometry objects must be defined using 4digit SDO_GTYPE values (explained in Section 2.2.1).
Examples
The following example returns the perimeters of geometry objects stored in the COLA_MARKETS table. The first statement returns the perimeters of 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 perimeter 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
Related Topics
None.
Format
SDO_GEOM.SDO_MAX_MBR_ORDINATE(
geom IN SDO_GEOMETRY,
ordinate_pos IN NUMBER
) RETURN NUMBER;
or
SDO_GEOM.SDO_MAX_MBR_ORDINATE(
geom IN SDO_GEOMETRY,
dim IN SDO_DIM_ARRAY,
ordinate_pos IN NUMBER
) RETURN NUMBER;
Description
Returns the maximum value for the specified ordinate (dimension) of the minimum bounding rectangle of a geometry object.
Parameters
Geometry object.
Dimensional information array corresponding to geom
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Position of the ordinate (dimension) in the definition of the geometry object: 1 for the first ordinate, 2 for the second ordinate, and so on. For example, if geom
has X, Y ordinates, 1 identifies the X ordinate and 2 identifies the Y ordinate.
Usage Notes
This function is not supported with geodetic data.
Examples
The following example returns the maximum X (first) ordinate value of the minimum bounding rectangle of the cola_d
geometry in the COLA_MARKETS table. (The example uses the definitions and data from Section 2.1. The minimum bounding rectangle of cola_d
is returned in the example for the SDO_GEOM.SDO_MBR function.)
SELECT SDO_GEOM.SDO_MAX_MBR_ORDINATE(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_d'; SDO_GEOM.SDO_MAX_MBR_ORDINATE(C.SHAPE,M.DIMINFO,1)  10
Related Topics
Format
SDO_GEOM.SDO_MBR(
geom IN SDO_GEOMETRY
[, dim IN SDO_DIM_ARRAY]
) RETURN SDO_GEOMETRY;
Description
Returns the minimum bounding rectangle of a geometry object, that is, a single rectangle that minimally encloses the geometry.
Parameters
Geometry object.
Dimensional information array corresponding to geom
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Usage Notes
This function does not return an MBR geometry if a proper MBR cannot be constructed. Specifically:
If the input geometry is null, the function returns a null geometry.
If the input geometry is a point, the function returns the point.
If the input geometry consists of points all on a straight line, the function returns a twopoint line.
Examples
The following example returns the minimum bounding rectangle of the cola_d
geometry in the COLA_MARKETS table. (The example uses the definitions and data from Section 2.1. Because cola_d
is a circle, the minimum bounding rectangle in this case is a square.)
 Return the minimum bounding rectangle of cola_d (a circle). SELECT SDO_GEOM.SDO_MBR(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_d'; SDO_GEOM.SDO_MBR(C.SHAPE,M.DIMINFO)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR AY(6, 7, 10, 11))
Related Topics
Format
SDO_GEOM.SDO_MIN_MBR_ORDINATE(
geom IN SDO_GEOMETRY,
ordinate_pos IN NUMBER
) RETURN NUMBER;
or
SDO_GEOM.SDO_MIN_MBR_ORDINATE(
geom IN SDO_GEOMETRY,
dim IN SDO_DIM_ARRAY,
ordinate_pos IN NUMBER
) RETURN NUMBER;
Description
Returns the minimum value for the specified ordinate (dimension) of the minimum bounding rectangle of a geometry object.
Parameters
Geometry object.
Dimensional information array corresponding to geom
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Position of the ordinate (dimension) in the definition of the geometry object: 1 for the first ordinate, 2 for the second ordinate, and so on. For example, if geom
has X, Y ordinates, 1 identifies the X ordinate and 2 identifies the Y ordinate.
Usage Notes
This function is not supported with geodetic data.
Examples
The following example returns the minimum X (first) ordinate value of the minimum bounding rectangle of the cola_d
geometry in the COLA_MARKETS table. (The example uses the definitions and data from Section 2.1. The minimum bounding rectangle of cola_d
is returned in the example for the SDO_GEOM.SDO_MBR function.)
SELECT SDO_GEOM.SDO_MIN_MBR_ORDINATE(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_d'; SDO_GEOM.SDO_MIN_MBR_ORDINATE(C.SHAPE,M.DIMINFO,1)  6
Related Topics
Format
SDO_GEOM.SDO_POINTONSURFACE(
geom1 IN SDO_GEOMETRY,
dim1 IN SDO_DIM_ARRAY
) RETURN SDO_GEOMETRY;
or
SDO_GEOM.SDO_POINTONSURFACE(
geom1 IN SDO_GEOMETRY,
tol IN NUMBER
) RETURN SDO_GEOMETRY;
Description
Returns a point that is guaranteed to be on the surface of a polygon geometry object.
Parameters
Polygon geometry object.
Dimensional information array corresponding to geom1
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Tolerance value (see Section 1.5.5).
Usage Notes
This function returns a point geometry object representing a point that is guaranteed to be on the surface of geom1
.
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 about 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 tol
is used, all geometry objects must be defined using 4digit SDO_GTYPE values (explained in Section 2.2.1).
Examples
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))
Related Topics
None.
Format
SDO_GEOM.SDO_UNION(
geom1 IN SDO_GEOMETRY,
dim1 IN SDO_DIM_ARRAY,
geom2 IN SDO_GEOMETRY,
dim2 IN SDO_DIM_ARRAY
) RETURN SDO_GEOMETRY;
or
SDO_GEOM.SDO_UNION(
geom1 IN SDO_GEOMETRY,
geom2 IN SDO_GEOMETRY,
tol IN NUMBER
) RETURN SDO_GEOMETRY;
Description
Returns a geometry object that is the topological union (OR operation) of two geometry objects.
Parameters
Geometry object.
Dimensional information array corresponding to geom1
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Geometry object.
Dimensional information array corresponding to geom2
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Tolerance value (see Section 1.5.5).
Usage Notes
In Figure 134, the shaded area represents the polygon returned when SDO_UNION is used with a square (geom1
) and another polygon (geom2
).
If the function format with tol
is used, all geometry objects must be defined using 4digit SDO_GTYPE values (explained in Section 2.2.1).
If it sufficient to append one geometry to another geometry without performing a topological union operation, and if both geometries are disjoint, using the SDO_UTIL.APPEND function (described in Chapter 19) is faster than using the SDO_UNION function.
An exception is raised if geom1
and geom2
are based on different coordinate systems.
Examples
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).
Related Topics
Format
SDO_GEOM.SDO_XOR(
geom1 IN SDO_XOR,
dim1 IN SDO_DIM_ARRAY,
geom2 IN SDO_GEOMETRY,
dim2 IN SDO_DIM_ARRAY
) RETURN SDO_GEOMETRY;
or
SDO_GEOM.SDO_XOR(
geom1 IN SDO_GEOMETRY,
geom2 IN SDO_GEOMETRY,
tol IN NUMBER
) RETURN SDO_GEOMETRY;
Description
Returns a geometry object that is the topological symmetric difference (XOR operation) of two geometry objects.
Parameters
Geometry object.
Dimensional information array corresponding to geom1
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Geometry object.
Dimensional information array corresponding to geom2
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Tolerance value (see Section 1.5.5).
Usage Notes
In Figure 135, the shaded area represents the polygon returned when SDO_XOR is used with a square (geom1
) and another polygon (geom2
).
If the function format with tol
is used, all geometry objects must be defined using 4digit SDO_GTYPE values (explained in Section 2.2.1).
An exception is raised if geom1
and geom2
are based on different coordinate systems.
Examples
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, 19, 1003, 1), SDO _ORDINATE_ARRAY(1, 7, 1, 1, 5, 1, 5, 3, 3, 3, 4, 5, 5, 5, 5, 7, 1, 7, 5, 5, 5, 3 , 6, 3, 6, 5, 5, 5))
Note that the returned polygon is a multipolygon (SDO_GTYPE = 2007), and the SDO_ORDINATE_ARRAY describes two polygons: one starting and ending at (1, 7) and the other starting and ending at (5, 5).
Related Topics
Format
SDO_GEOM.VALIDATE_GEOMETRY(
theGeometry IN SDO_GEOMETRY,
theDimInfo IN SDO_DIM_ARRAY
) RETURN VARCHAR2;
or
SDO_GEOM.VALIDATE_GEOMETRY(
theGeometry IN SDO_GEOMETRY,
tolerance IN NUMBER
) RETURN VARCHAR2;
Description
Performs a consistency check for valid geometry types. The function checks the representation of the geometry from the tables against the element definitions.
Note: The VALIDATE_GEOMETRY function was deprecated in a previous release of Spatial. The current Spatial release is the last supported release for this function, and it will not be included in future releases of this guide. You should use instead the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function. 
Parameters
Geometry object.
Dimensional information array corresponding to theGeometry
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Tolerance value (see Section 1.5.5).
Usage Notes
This deprecated function performs the same checks as the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function; however, if the geometry is not valid, it does not return information about the context.
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
If the function format with tolerance
is used, the following guidelines apply:
All geometry objects must be defined using 4digit SDO_GTYPE values (explained in Section 2.2.1).
No checking is done to validate that the geometry is within the coordinate system bounds as stored in the DIMINFO field of the USER_SDO_GEOM_METADATA view. If this check is required for your usage, use the function format with theDimInfo
.
Examples
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, 0.005) FROM cola_markets c WHERE c.name = 'cola_c'; NAME  SDO_GEOM.VALIDATE_GEOMETRY(C.SHAPE,0.005)  cola_c TRUE
Related Topics
Format
SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(
theGeometry IN SDO_GEOMETRY,
theDimInfo IN SDO_DIM_ARRAY
) RETURN VARCHAR2;
or
SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(
theGeometry IN SDO_GEOMETRY,
tolerance IN NUMBER
) RETURN VARCHAR2;
Description
Performs a consistency check for valid geometry types and returns context information if the geometry is invalid. The function checks the representation of the geometry from the tables against the element definitions.
Parameters
Geometry object.
Dimensional information array corresponding to theGeometry
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Tolerance value (see Section 1.5.5).
Usage Notes
If the geometry is valid, this function returns TRUE.
If the geometry is not valid, this function returns the following:
An Oracle error message number based on the specific reason the geometry is invalid, or FALSE if the geometry fails for some other reason
The context of the error (the coordinate, edge, or ring that causes the geometry to be invalid)
This function checks for type consistency and geometry consistency.
For type consistency, the function checks for the following:
The SDO_GTYPE is valid.
The SDO_ETYPE values are consistent with the SDO_GTYPE value. For example, if the SDO_GTYPE is 2003, there should be at least one element of type POLYGON in the geometry.
The SDO_ELEM_INFO_ARRAY has valid triplet values.
For geometry consistency, the function checks for the following, as appropriate for the specific geometry type:
Polygons have at least four points, which includes the point that closes the polygon. (The last point is the same as the first.)
Polygons are not selfcrossing.
No two vertices on a line or polygon are the same.
Polygons are oriented correctly. (Exterior ring boundaries must be oriented counterclockwise, and interior ring boundaries must be oriented clockwise.)
An interior polygon ring touches the exterior polygon ring at no more than one point.
If two or more interior polygon rings are in an exterior polygon ring, the interior polygon rings touch at no more than one point.
Line strings have at least two points.
SDO_ETYPE 1digit and 4digit values are not mixed (that is, both used) in defining polygon ring elements.
Points on an arc are not colinear (that is, are not on a straight line) and are not the same point.
Geometries are within the specified bounds of the applicable DIMINFO column value (from the USER_SDO_GEOM_METADATA view).
LRS geometries (see Chapter 7) have three or four dimensions and a valid measure dimension position (3 or 4, depending on the number of dimensions).
In checking for geometry consistency, the function considers the geometry's tolerance value in determining if lines touch or if points are the same.
If the function format with tolerance
is used, the following guidelines apply:
All geometry objects must be defined using 4digit SDO_GTYPE values (explained in Section 2.2.1).
No checking is done to validate that the geometry is within the coordinate system bounds as stored in the DIMINFO field of the USER_SDO_GEOM_METADATA view. If this check is required for your usage, use the function format with theDimInfo
.
You can use this function in a PL/SQL procedure as an alternative to using the SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT procedure. See the Usage Notes for SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT for more information.
Examples
The following example validates a geometry (deliberately created as invalid) named cola_invalid_geom
.
 Validate; provide context if invalid SELECT c.name, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(c.shape, 0.005) FROM cola_markets c WHERE c.name = 'cola_invalid_geom'; NAME  SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(C.SHAPE,0.005)  cola_invalid_geom 13349 [Element <1>] [Ring <1>][Edge <1>][Edge <3>]
Related Topics
Format
SDO_GEOM.VALIDATE_LAYER(
geom_table IN VARCHAR2,
geom_column IN VARCHAR2,
pkey_column IN VARCHAR2,
result_table IN VARCHAR2
[, commit_interval IN NUMBER]);
Description
Examines a geometry column to determine if the stored geometries follow the defined rules for geometry objects.
Note: The VALIDATE_LAYER procedure was deprecated in a previous release of Spatial. The current Spatial release is the last supported release for this procedure, and it will not be included in future releases of this guide. You should use instead the SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT procedure. 
Parameters
Spatial geometry table.
Geometry object column to be examined.
The primary key column. This must be a single numeric (NUMBER data type) column.
Result table to hold the validation results. A row is added to result_table
for each invalid geometry. If there are no invalid geometries, one or more (depending on the commit_interval
value) rows with a result of DONE are added.
Number of geometries to validate before Spatial performs an internal commit operation and writes a row with a result of DONE to result_table
(if no rows for invalid geometries have been written since the last commit operation). If commit_interval
is not specified, no internal commit operations are performed during the validation.
The commit_interval
option is helpful if you want to look at the contents of result_table
while the validation is in progress. If the primary key is indexed, you can look at the last PKEY_COLUMN value to see approximately how much of the validation is completed.
Usage Notes
This deprecated procedure loads the result table with validation results.
An empty result table (result_table
parameter) must be created before calling this procedure. The format of the result table is: (pkey_column NUMBER, result VARCHAR2(10)). If result_table
is not empty, you should truncate the table before calling the procedure; otherwise, the procedure appends rows to the existing data in the table.
The result table contains one row for each invalid geometry. A row is not written if a geometry is valid, except as follows:
If commit_interval
is not specified (or if the commit_interval
value is greater than the number of geometries in the layer) and no invalid geometries are found, a single row with a RESULT value of DONE is written.
If commit_interval
is specified and if no invalid geometries are found between an internal commit and the previous internal commit (or start of validation for the first internal commit), a single row with the primary key of the last geometry validated and a RESULT value of DONE is written. (If there have been no invalid geometries since the last internal commit operation, this row replaces the previous row that had a result of DONE.)
In each row for an invalid geometry, the PKEY_COLUMN column contains the primary key value of the row containing the invalid geometry, and the RESULT column contains an Oracle error message number. You can then look up this error message to determine the cause of the failure.
This procedure performs the following checks on each geometry in the layer (geom_column
):
All type consistency and geometry consistency checks that are performed by the SDO_GEOM.VALIDATE_GEOMETRY function (see the Usage Notes for that function).
If 4digit SDO_GTYPE values are used, the geometry's SDO_GTYPE specifies the same dimensionality as specified in the applicable DIMINFO column value (from the USER_SDO_GEOM_METADATA view).
The geometry's SRID value (coordinate system) is the same as the one specified in the applicable DIMINFO column value (from the USER_SDO_GEOM_METADATA view).
Examples
The following example validates the geometry objects stored in the SHAPE column of the COLA_MARKETS table. The example includes the creation of the result table. (The example uses the definitions and data from Section 2.1.) In this case, a row with a RESULT value of DONE is written to the result table, because all the geometries are valid.
 Is a layer valid? (First, create the result table.) CREATE TABLE val_results (mkt_id number, result varchar2(10)); CALL SDO_GEOM.VALIDATE_LAYER('COLA_MARKETS','SHAPE','MKT_ID','VAL_RESULTS'); Call completed. SELECT * from val_results; MKT_ID RESULT   DONE
Related Topics
Format
SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT(
geom_table IN VARCHAR2,
geom_column IN VARCHAR2,
result_table IN VARCHAR2
[, commit_interval IN NUMBER]);
Description
Examines a geometry column to determine if the stored geometries follow the defined rules for geometry objects, and returns context information about any invalid geometries.
Parameters
Spatial geometry table.
Geometry object column to be examined.
Result table to hold the validation results. A row is added to result_table
for each invalid geometry. If there are no invalid geometries, one or more (depending on the commit_interval
value) rows with a result of DONE are added.
Number of geometries to validate before Spatial performs an internal commit operation and writes a row with a result of DONE to result_table
(if no rows for invalid geometries have been written since the last commit operation). If commit_interval
is not specified, no internal commit operations are performed during the validation.
The commit_interval
option is helpful if you want to look at the contents of result_table
while the validation is in progress.
Usage Notes
This procedure loads the result table with validation results.
An empty result table (result_table
parameter) must be created before calling this procedure. The format of the result table is: (sdo_rowid ROWID, result VARCHAR2(2000)). If result_table
is not empty, you should truncate the table before calling the procedure; otherwise, the procedure appends rows to the existing data in the table.
The result table contains one row for each invalid geometry. A row is not written if a geometry is valid, except as follows:
If commit_interval
is not specified (or if the commit_interval
value is greater than the number of geometries in the layer) and no invalid geometries are found, a single row with a RESULT value of DONE is written.
If commit_interval
is specified and if no invalid geometries are found between an internal commit and the previous internal commit (or start of validation for the first internal commit), a single row with the primary key of the last geometry validated and a RESULT value of DONE is written. (If there have been no invalid geometries since the last internal commit operation, this row replaces the previous row that had a result of DONE.)
In each row for an invalid geometry, the SDO_ROWID column contains the ROWID value of the row containing the invalid geometry, and the RESULT column contains an Oracle error message number and the context of the error (the coordinate, edge, or ring that causes the geometry to be invalid). You can then look up the error message for more information about the cause of the failure.
This procedure performs the following checks on each geometry in the layer (geom_column
):
All type consistency and geometry consistency checks that are performed by the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function (see the Usage Notes for that function).
If 4digit SDO_GTYPE values are used, the geometry's SDO_GTYPE specifies the same dimensionality as specified in the applicable DIMINFO column value (from the USER_SDO_GEOM_METADATA view).
The geometry's SRID value (coordinate system) is the same as the one specified in the applicable DIMINFO column value (from the USER_SDO_GEOM_METADATA view).
Examples
The following example validates the geometry objects stored in the SHAPE column of the COLA_MARKETS table. The example includes the creation of the result table. For this example, a deliberately invalid geometry was inserted into the table before the validation was performed.
 Is a layer valid? (First, create the result table.) CREATE TABLE val_results (sdo_rowid ROWID, result varchar2(1000));  (Next statement must be on one command line.) CALL SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('COLA_MARKETS','SHAPE','VAL_RESULTS'); Call completed. SQL> SELECT * from val_results; SDO_ROWID  RESULT  Rows Processed <12> AAABXNAABAAAK+YAAC 13349 [Element <1>] [Ring <1>][Edge <1>][Edge <3>]
Related Topics
Format
SDO_GEOM.WITHIN_DISTANCE(
geom1 IN SDO_GEOMETRY,
dim1 IN SDO_DIM_ARRAY,
dist IN NUMBER,
geom2 IN SDO_GEOMETRY,
dim2 IN SDO_DIM_ARRAY
[, units IN VARCHAR2]
) RETURN VARCHAR2;
or
SDO_GEOM.WITHIN_DISTANCE(
geom1 IN SDO_GEOMETRY,
dist IN NUMBER,
geom2 IN SDO_GEOMETRY,
tol IN NUMBER
[, units IN VARCHAR2]
) RETURN VARCHAR2;
Description
Determines if two spatial objects are within some specified distance from each other.
Parameters
Geometry object.
Dimensional information array corresponding to geom1
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Distance value.
Geometry object.
Dimensional information array corresponding to geom2
, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
Tolerance value (see Section 1.5.5).
Unit of measurement: a quoted string with unit=
and an SDO_UNIT value from the MDSYS.SDO_AREA_UNITS table (for example, 'unit=KM'). See Section 2.6 for more information about unit of measurement specification.
If this parameter is not specified, the unit of measurement associated with the data is assumed. For geodetic data, the default unit of measurement is meters.
Usage Notes
For better performance, use the SDO_WITHIN_DISTANCE operator (described in Chapter 12) instead of the SDO_GEOM.WITHIN_DISTANCE
function. For more information about performance considerations with operators and functions, see Section 1.9.
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 tol
is used, all geometry objects must be defined using 4digit SDO_GTYPE values (explained in Section 2.2.1).
An exception is raised if geom1
and geom2
are based on different coordinate systems.
Examples
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
Related Topics