Oracle Spatial User's Guide and ReferenceRelease 8.1.7Part Number A85337-01

# 7 Geometry Functions

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

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

Determines how two objects interact.

Computes the area of a two-dimensional polygon.

Generates a buffer polygon around a geometry.

Returns the centroid of a polygon.

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

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

Computes the distance between two geometry objects.

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

Computes the length or perimeter of a geometry.

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

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

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

Determines if a geometry is valid.

Determines if all the geometries stored in a column are valid.

Determines if two geometries are within a specified Euclidean distance from one another.

 Note: The SDO_POLY_xxx functions were deprecated at release 8.1.6 and have been removed from this guide. 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

### Format

SDO_GEOM.RELATE(

geom1 IN MDSYS.SDO_GEOMETRY,

dim1 IN MDSYS.SDO_DIM_ARRAY,

geom2 IN MDSYS.SDO_GEOMETRY,

dim2 IN MDSYS.SDO_DIM_ARRAY

) RETURN VARCHAR2;

or

SDO_GEOM.RELATE(

geom1 IN MDSYS.SDO_GEOMETRY,

geom2 IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN VARCHAR2;

### Description

Examines two geometry objects to determine their spatial relationship.

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

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 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 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 tolerance is used, all geometry objects must be defined using 4-digit SDO_GTYPE values (explained in Section 2.2.1).

### Examples

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

None.

## SDO_GEOM.SDO_AREA

### Format

SDO_GEOM.SDO_AREA(

geom IN MDSYS.SDO_GEOMETRY,

dim IN MDSYS.SDO_DIM_ARRAY

) RETURN NUMBER;

or

SDO_GEOM.SDO_AREA(

geom IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN NUMBER;

### Description

Returns the area of a two-dimensional polygon.

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

### Examples

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

None.

## SDO_GEOM.SDO_BUFFER

### Format

SDO_GEOM.SDO_BUFFER(

geom IN MDSYS.SDO_GEOMETRY,

dim IN MDSYS.SDO_DIM_ARRAY,

distance IN NUMBER,

) RETURN MDSYS.SDO_GEOMETRY;

or

SDO_GEOM.SDO_BUFFER(

geom IN MDSYS.SDO_GEOMETRY,

distance IN NUMBER,

tolerance IN NUMBER

) RETURN MDSYS.SDO_GEOMETRY;

### Description

Generates a buffer polygon around a geometry object.

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

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

### Format

SDO_GEOM.SDO_CENTROID(

geom IN MDSYS.SDO_GEOMETRY,

dim IN MDSYS.SDO_DIM_ARRAY

) RETURN MDSYS.SDO_GEOMETRY;

or

SDO_GEOM.SDO_CENTROID(

geom IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN MDSYS.SDO_GEOMETRY;

### Description

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

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

### 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)
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))
```

None.

## SDO_GEOM.SDO_CONVEXHULL

### Format

SDO_GEOM.SDO_CONVEXHULL(

geom IN MDSYS.SDO_GEOMETRY,

dim IN MDSYS.SDO_DIM_ARRAY

) RETURN MDSYS.SDO_GEOMETRY;

or

SDO_GEOM.SDO_CONVEXHULL(

geom IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN MDSYS.SDO_GEOMETRY;

### Description

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

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

### 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 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)
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))
```

None.

## SDO_GEOM.SDO_DIFFERENCE

### 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;

or

SDO_GEOM.SDO_DIFFERENCE(

geom1 IN MDSYS.SDO_GEOMETRY,

geom2 IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN MDSYS.SDO_GEOMETRY;

### Description

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

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

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

## SDO_GEOM.SDO_DISTANCE

### 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;

or

SDO_GEOM.SDO_DISTANCE(

geom1 IN MDSYS.SDO_GEOMETRY,

geom2 IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) 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

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

### 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, 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

### 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;

or

SDO_GEOM.SDO_INTERSECTION(

geom1 IN MDSYS.SDO_GEOMETRY,

geom2 IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN MDSYS.SDO_GEOMETRY;

### Description

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

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

### 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, 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

### Format

SDO_GEOM.SDO_LENGTH(

geom IN MDSYS.SDO_GEOMETRY,

dim IN MDSYS.SDO_DIM_ARRAY

) RETURN NUMBER;

or

SDO_GEOM.SDO_LENGTH(

geom IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN NUMBER;

### Description

Returns the length or perimeter of a geometry object.

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

### 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)
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)
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
```

None.

## SDO_GEOM.SDO_POINTONSURFACE

### Format

SDO_GEOM.SDO_POINTONSURFACE(

geom IN MDSYS.SDO_GEOMETRY,

dim IN MDSYS.SDO_DIM_ARRAY

) RETURN MDSYS.SDO_GEOMETRY;

or

SDO_GEOM.SDO_POINTONSURFACE(

geom IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN MDSYS.SDO_GEOMETRY;

### Description

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

### 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 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 tolerance is used, all geometry objects must be defined using 4-digit 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)
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))
```

None.

## SDO_GEOM.SDO_UNION

### 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;

or

SDO_GEOM.SDO_UNION(

geom1 IN MDSYS.SDO_GEOMETRY,

geom2 IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN MDSYS.SDO_GEOMETRY;

### Description

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

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

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

## SDO_GEOM.SDO_XOR

### 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;

or

SDO_GEOM.SDO_XOR(

geom1 IN MDSYS.SDO_GEOMETRY,

geom2 IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN MDSYS.SDO_GEOMETRY;

### Description

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

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

### 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, 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

### Format

SDO_GEOM.VALIDATE_GEOMETRY(

geom IN MDSYS.SDO_GEOMETRY,

dim IN MDSYS.SDO_DIM_ARRAY

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

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

### 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 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 self-crossing.

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

• 1-digit and 4-digit SDO_ETYPE 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).

• Geometries are within the extent of the coordinate system.

In checking for geometry consistency, the function considers the geometry's tolerance value in determining if lines touch or if points are the same.

You can use this function in a PL/SQL procedure as an alternative to using the SDO_GEOM.VALIDATE_LAYER procedure. See the Usage Notes for SDO_GEOM.VALIDATE_LAYER for more information.

### 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, m.diminfo)
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

### Format

SDO_GEOM.VALIDATE_LAYER(

geom_table IN VARCHAR2,

geom_column IN VARCHAR2,

pkey_column IN VARCHAR2,

result_table IN VARCHAR2);

### Description

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

### Parameters

geom_table

Geometry table.

geom_column

Geometry object column to be examined.

pkey_column

The primary key column. This must be a single numeric (NUMBER data type) column.

result_table

Result table to hold the validation results. A row is added to result_table for each invalid geometry.

### Usage Notes

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

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.) In each row, 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 the type consistency and geometry consistency checks that are performed by the SDO_GEOM.VALIDATE_GEOMETRY function (see Usage Notes for that function).

• If 4-digit 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).

As an alternative to using the VALIDATE_LAYER procedure, you can use a PL/SQL procedure that calls the SDO_GEOM.VALIDATE_GEOMETRY function for each input row and inserts rows in a result table for errors. With this approach, you have more options in defining the result table; for example, the key field can be a rowid or anything else that you choose.

### 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, no rows are 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));
EXECUTE SDO_GEOM.VALIDATE_LAYER('COLA_MARKETS','SHAPE','MKT_ID','VAL_RESULTS');

PL/SQL procedure successfully completed.

SELECT * from val_results;

no rows selected
```

## SDO_GEOM.WITHIN_DISTANCE

### 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;

or

SDO_GEOM.WITHIN_DISTANCE(

geom1 IN MDSYS.SDO_GEOMETRY,

distance IN NUMBER,

geom2 IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER

) RETURN VARCHAR2;

### Description

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

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

### 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
```