32 SDO_UTIL Package (Utility)
This chapter contains descriptions of the spatial utility subprograms shown in Table 32-1.
Table 32-1 Spatial Utility Subprograms
| Subprogram | Description |
|---|---|
|
Returns a geometry that reflects an affine transformation of the input geometry. |
|
|
Appends one geometry to another geometry to create a new geometry. |
|
|
Computes the bearing and tilt from a start point to an end point. |
|
|
Returns the polygon geometry that approximates and is covered by a specified circle. |
|
|
Concatenates two line or multiline two-dimensional geometries to create a new geometry. |
|
|
Converts values from one angle, area, or distance unit of measure to another. |
|
|
Returns the polygon geometry that approximates and is covered by a specified ellipse. |
|
|
Returns the two-dimensional geometry that represents a specified element (and optionally a ring) of the input two-dimensional geometry. |
|
|
Returns all elements and subelements of the input two-dimensional geometry, as an array of one or more geometries. |
|
|
Returns the three-dimensional geometry that represents a specified subset of the input three-dimensional geometry. |
|
|
Returns the three-dimensional extrusion solid geometry from an input two-dimensional polygon geometry. |
|
|
Converts a geography markup language (GML 3.1.1) fragment to a Spatial geometry object. |
|
|
Converts a geography markup language (GML 2.0) fragment to a Spatial geometry object. |
|
|
Converts a KML (Keyhole Markup Language) document to a Spatial geometry object. |
|
|
Converts a geometry in the well-known binary (WKB) format to a Spatial geometry object. |
|
|
Converts a geometry in the well-known text (WKT) format to a Spatial geometry object. |
|
|
Returns the number of elements in the input geometry. |
|
|
Returns the number of vertices in the input geometry. |
|
|
Returns the coordinates of the vertices of the input geometry. |
|
|
Initializes all spatial indexes in a tablespace that was transported to another database. |
|
|
Returns a point that is guaranteed to be an interior point (not on the boundary or edge) on the surface of a polygon geometry object. |
|
|
Returns a point geometry that is at the specified distance and bearing from the start point. |
|
|
Converts all polygon-type elements in a geometry to line-type elements, and sets the SDO_GTYPE value accordingly. |
|
|
Prepares a tablespace to be transported to another database, so that spatial indexes will be preserved during the transport operation. |
|
|
Fixes certain problems with the input geometry, and returns a valid geometry. |
|
|
Removes duplicate (redundant) vertices from a geometry. |
|
|
Returns a line string geometry with the vertices of the input geometry in reverse order. |
|
|
Simplifies the input geometry, based on a threshold value, using the Douglas-Peucker algorithm. |
|
|
Converts a Spatial geometry object to a geography markup language (GML 3.1.1) fragment based on the geometry types defined in the Open GIS |
|
|
Converts a Spatial geometry object to a geography markup language (GML 2.0) fragment based on the geometry types defined in the Open GIS |
|
|
Converts a Spatial geometry object to a KML (Keyhole Markup Language) document. |
|
|
Converts a Spatial geometry object to the well-known binary (WKB) format. |
|
|
Converts a Spatial geometry object to the well-known text (WKT) format. |
|
|
Validates the input geometry, which is in the standard well-known binary (WKB) format; returns the string |
|
|
Validates the input geometry, which is of type CLOB or VARCHAR2 and in the standard well-known text (WKT) format; returns the string |
SDO_UTIL.AFFINETRANSFORMS
SDO_UTIL.AFFINETRANSFORMS(
geometry IN SDO_GEOMETRY,
translation IN VARCHAR2,
tx IN NUMBER,
ty IN NUMBER,
tz IN NUMBER,
scaling IN VARCHAR2,
psc1 IN SDO_GEOMETRY
sx IN NUMBER,
sy IN NUMBER,
sz IN NUMBER,
rotation IN VARCHAR2,
p1 IN SDO_GEOMETRY,
line1 IN SDO_GEOMETRY,
angle IN NUMBER,
dir IN NUMBER,
shearing IN VARCHAR2
shxy IN NUMBER,
shyx IN NUMBER,
shxz IN NUMBER,
shzx IN NUMBER,
shyz IN NUMBER,
shzy IN NUMBER,
reflection IN VARCHAR2
pref IN SDO_GEOMETRY,
lineR IN SDO_GEOMETRY,
dirR IN NUMBER,
planeR IN VARCHAR2,
n IN SDO_NUMBER_ARRAY,
bigD IN SDO_NUMBER_ARRAY,
) RETURN SDO_GEOMETRY;
- geometry
-
Input geometry on which to perform the affine transformation.
- translation
-
A string value of TRUE causes translation to be performed; a string value of FALSE causes translation not to be performed. If this parameter is TRUE, translation is performed about the point at (tx,ty) or (tx,ty,tz).
- tx
-
X-axis value for translation.
- ty
-
Y-axis value for translation.
- tz
-
Z-axis value for translation.
- scaling
-
A string value of TRUE causes scaling to be performed; a string value of FALSE causes scaling not to be performed.
- psc1
-
Point on the input geometry about which to perform the scaling. If
scalingisTRUE, this geometry should be either a zero point (point geometry with 0,0 or 0,0,0 ordinates for scaling about the origin) or a nonzero point (point geometry with ordinates for scaling about a point other than the origin). IfscalingisFALSE,psc1can be a null value. - sx
-
X-axis value for scaling (about either the point specified in the
psc1parameter or the origin). - sy
-
Y-axis value for scaling (about either the point specified in the
psc1parameter or the origin). - sz
-
Z-axis value for scaling (about either the point specified in the
psc1parameter or the origin). - rotation
-
A string value of TRUE causes rotation to be performed; a string value of FALSE causes rotation not to be performed.
For two-dimensional geometries, rotation uses the
p1andanglevalues. For three-dimensional geometries, rotation uses either theangleanddirvalues or theline1andanglevalues. - p1
-
Point for two-dimensional geometry rotation about a specified point.
- line1
-
Line for rotation about a specified axis.
- angle
-
Angle rotation parameter (in radians) for rotation about a specified axis or about the X, Y, or Z axis.
- dir
-
Rotation parameter for x(0), y(1), or z(2)-axis roll. If the
rotationparameter value isTRUEbut thedirparameter is not used, specify a value of -1. - shearing
-
A string value of TRUE causes shearing to be performed; a string value of FALSE causes shearing not to be performed.
For two-dimensional geometries, shearing uses the
shxyandshyxparameter values. For three-dimensional geometries, shearing uses theshxy,shyx,shxz,shzx,shyz, andshzyparameter values. - shxy
-
Value for shearing due to X along the Y direction.
- shyx
-
Value for shearing due to Y along the X direction.
- shxz
-
Value for shearing due to X along the Z direction (three-dimensional geometries only).
- shzx
-
Value for shearing due to Z along the X direction (three-dimensional geometries only).
- shyz
-
Value for shearing due to Y along the Z direction (three-dimensional geometries only).
- shzy
-
Value for shearing due to Z along the Y direction (three-dimensional geometries only).
- reflection
-
A string value of TRUE causes reflection to be performed; a string value of FALSE causes reflection not to be performed.
For two-dimensional geometries, reflection uses the
lineRvalue for reflection about an axis and theprefvalue for the centroid for self-reflection. For three-dimensional geometries, reflection uses thelineRvalue for reflection about an axis; thedirRvalue for reflection about the yz, xz, and xy planes; theplaneR,n, andbigDvalues for reflection about a specified plane; and theprefvalue for the centroid for self-reflection. - pref
-
Point through which to perform reflection.
- lineR
-
Line along which to perform reflection.
- dirR
-
Number indicating the plane about (through) which to perform reflection: 0 for the yz plane, 1 for the xz plane, or 2 for the xy plane. If the
reflectionparameter value isTRUEbut thedirRparameter is not used, specify a value of -1. - planeR
-
A string value of TRUE causes reflection about an arbitrary plane to be performed; a string value of FALSE causes reflection about an arbitrary plane not to be performed.
- n
-
Normal vector of the plane.
- bigD
-
Delta value for the plane equation in three-dimensional geometries.
For three-dimensional geometries, bigD = delta and n = (A,B,C) where n is the normal of the plane in three-dimensional space. Thus, the plane equation is:
Ax+By+Cz+bigD = 3DDotProd(n,anypointonplane)+bigD = 0
The order of affine transforms matter because these are matrix and vector multiplications.
You should validate the resulting geometry using the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function.
The following example performs an affine transformation on a two-dimensional geometry.
-- Polygon reflection in 2D about a specified line segment
SELECT SDO_UTIL.AFFINETRANSFORMS(
geometry => MDSYS.SDO_GEOMETRY(2003, NULL, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(
1.5,0,
2.5,1,
1.5,2,
0.5,2,
0.5,0,
1.5,0)),
translation => 'FALSE',
tx => 0.0,
ty => 0.0,
tz => 0.0,
scaling => 'FALSE',
psc1 => NULL,
sx => 0.0,
sy => 0.0,
sz => 0.0,
rotation => 'FALSE',
p1 => NULL,
line1 => NULL,
angle => 0.0,
dir => 0,
shearing => 'FALSE',
shxy => 0.0,
shyx => 0.0,
shxz => 0.0,
shzx => 0.0,
shyz => 0.0,
shzy => 0.0,
reflection => 'TRUE',
pref => NULL,
lineR => MDSYS.SDO_GEOMETRY(2002,0,NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),
MDSYS.SDO_ORDINATE_ARRAY(2.5,0.0,2.5,2.0)),
dirR => -1,
planeR => 'FALSE',
n => NULL,
bigD => NULL
) FROM DUAL;
SDO_UTIL.AFFINETRANSFORMS(GEOMETRY=>MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(3.5, 0, 2.5, 1, 3.5, 2, 4.5, 2, 4.5, 0, 3.5, 0))
The following example performs an affine transformation on a three-dimensional geometry.
-- Polygon reflection in 3D about a specified plane (z=1 plane in this example)
SELECT SDO_UTIL.AFFINETRANSFORMS(
geometry => MDSYS.SDO_GEOMETRY(3003, 0, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(
1.5,0,0,
2.5,1,0,
1.5,2,0,
0.5,2,0,
0.5,0,0,
1.5,0,0)),
translation => 'FALSE',
tx => 0.0,
ty => 0.0,
tz => 0.0,
scaling => 'FALSE',
psc1 => NULL,
sx => 0.0,
sy => 0.0,
sz => 0.0,
rotation => 'FALSE',
p1 => NULL,
line1 => NULL,
angle => 0.0,
dir => 0,
shearing => 'FALSE',
shxy => 0.0,
shyx => 0.0,
shxz => 0.0,
shzx => 0.0,
shyz => 0.0,
shzy => 0.0,
reflection => 'TRUE',
pref => NULL,
lineR => NULL,
dirR => -1,
planeR => 'TRUE',
n => SDO_NUMBER_ARRAY(0.0, 0.0, 1.0),
bigD => SDO_NUMBER_ARRAY(-1.0)
) FROM DUAL;
SDO_UTIL.AFFINETRANSFORMS(GEOMETRY=>MDSYS.SDO_GEOMETRY(3003,0,NULL,MDSYS.SDO_ELE
--------------------------------------------------------------------------------
SDO_GEOMETRY(3003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(1.5, 0, 2, 2.5, 1, 2, 1.5, 2, 2, .5, 2, 2, .5, 0, 2, 1.5, 0, 2))
SDO_UTIL.APPEND
- geometry1
-
Geometry object to which
geometry2is to be appended. - geometry2
-
Geometry object to append to
geometry1.
This function should be used only on geometries that do not have any spatial interaction (that is, on disjoint objects). If the input geometries are not disjoint, the resulting geometry might be invalid.
This function does not perform a union operation or any other computational geometry operation. To perform a union operation, use the SDO_GEOM.SDO_UNION function, which is described in Chapter 24. The APPEND function executes faster than the SDO_GEOM.SDO_UNION function.
The geometry type (SDO_GTYPE value) of the resulting geometry reflects the types of the input geometries and the append operation. For example, if the input geometries are two-dimensional polygons (SDO_GTYPE = 2003), the resulting geometry is a two-dimensional multipolygon (SDO_GTYPE = 2007).
An exception is raised if geometry1 and geometry2 are based on different coordinate systems.
The following example appends the cola_a and cola_c geometries. (The example uses the definitions and data from Section 2.1.)
SELECT SDO_UTIL.APPEND(c_a.shape, c_c.shape) FROM cola_markets c_a, cola_markets c_c WHERE c_a.name = 'cola_a' AND c_c.name = 'cola_c'; SDO_UTIL.APPEND(C_A.SHAPE,C_C.SHAPE)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SD -------------------------------------------------------------------------------- SDO_GEOMETRY(2007, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3, 5, 1003, 1), SDO_ ORDINATE_ARRAY(1, 1, 5, 7, 3, 3, 6, 3, 6, 5, 4, 5, 3, 3))
SDO_UTIL.BEARING_TILT_FOR_POINTS
SDO_UTIL.BEARING_TILT_FOR_POINTS(
start_point IN SDO_GEOMETRY,
end_point IN SDO_GEOMETRY,
tol IN NUMBER,
bearing OUT NUMBER,
tilt OUT NUMBER
) RETURN SDO_GEOMETRY;
- start_point
-
Starting point geometry object from which to compute the bearing and tilt. The point geometry must be based on a geodetic coordinate system.
- end_point
-
Ending point geometry object to use in computing the bearing and tilt. The point geometry must be based on the same geodetic coordinate system as
start_point. - tol
-
Tolerance value (see Section 1.5.5).
- bearing
-
Number of radians, measured clockwise from North.
- tilt
-
Number of radians, measured from the normal.
The input point geometries must be based on the same geodetic coordinate system. If they are based on a non-geodetic coordinate system, the output bearing is a null value.
The tilt is computed as the arctangent of the difference between the height values divided by the distance between the points (with height excluded from the distance calculation). That is: tilt = atan(height_difference/distance)
To convert radians to decimal degrees or decimal degrees to radians, you can use the SDO_UTIL.CONVERT_UNIT function. To return a point geometry that is at a specified distance and bearing from a start point, you can use the SDO_UTIL.POINT_AT_BEARING function.
The following example computes the bearing and tilt for two longitude/latitude points, where the elevation of the start point is 0 (zero) and the elevation of the end point is 5000 meters. This example displays the bearing and tilt values in radians.
DECLARE
bearing NUMBER;
tilt NUMBER;
BEGIN
SDO_UTIL.BEARING_TILT_FOR_POINTS(
SDO_GEOMETRY(2001, 8307,
SDO_POINT_TYPE(-71.5, 43, 0), NULL, NULL), -- start_point
SDO_GEOMETRY(2001, 8307,
SDO_POINT_TYPE(-71, 43.5, 5000), NULL, NULL), -- end_point
0.05, --tolerance
bearing,
tilt);
DBMS_OUTPUT.PUT_LINE('Bearing = ' || bearing);
DBMS_OUTPUT.PUT_LINE('Tilt = ' || tilt);
END;
/
Bearing = .628239101930666
Tilt = .0725397288678286910476298724869396973718
The following example is the same as the preceding one, except that it displays the bearing and tilt in decimal degrees instead of radians.
DECLARE
bearing NUMBER;
tilt NUMBER;
BEGIN
SDO_UTIL.BEARING_TILT_FOR_POINTS(
SDO_GEOMETRY(2001, 8307,
SDO_POINT_TYPE(-71.5, 43, 0), NULL, NULL), -- start_point
SDO_GEOMETRY(2001, 8307,
SDO_POINT_TYPE(-71, 43.5, 5000), NULL, NULL), -- end_point
0.05, --tolerance
bearing,
tilt);
DBMS_OUTPUT.PUT_LINE('Bearing in degrees = '
|| bearing * 180 / 3.1415926535897932384626433832795);
DBMS_OUTPUT.PUT_LINE('Tilt in degrees = '
|| tilt * 180 / 3.1415926535897932384626433832795);
END;
/
Bearing in degrees = 35.99544906571628894295547577999851892359
Tilt in degrees = 4.15622031114988533540349823511872120415
SDO_UTIL.CIRCLE_POLYGON
SDO_UTIL.CIRCLE_POLYGON(
center_longitude IN NUMBER,
center_latitude IN NUMBER,
radius IN NUMBER,
arc_tolerance IN NUMBER
) RETURN SDO_GEOMETRY;
- center_longitude
-
Center longitude (in degrees) of the circle to be used to create the returned geometry.
- center_latitude
-
Center latitude (in degrees) of the circle to be used to create the returned geometry.
- radius
-
Length (in meters) of the radius of the circle to be used to create the returned geometry.
- arc_tolerance
-
A numeric value to be used to construct the polygon geometry. The
arc_toleranceparameter value has the same meaning and usage guidelines as thearc_tolerancekeyword value in theparamsparameter string for the SDO_GEOM.SDO_ARC_DENSIFY function. The unit of measurement associated with the geometry is associated with thearc_toleranceparameter value. (For more information, see the Usage Notes for the SDO_GEOM.SDO_ARC_DENSIFY function in Chapter 24.)
This function is useful for creating a circle-like polygon around a specified center point when a true circle cannot be used (a circle is not valid for geodetic data with Oracle Spatial). The returned geometry has an SDO_SRID value of 8307 (for Longitude / Latitude (WGS 84)).
The following example returns a circle-like polygon around a point near the center of Concord, Massachusetts. A radius value of 100 meters and an arc_tolerance value of 5 meters are used in computing the polygon vertices.
SELECT SDO_UTIL.CIRCLE_POLYGON(-71.34937, 42.46101, 100, 5) FROM DUAL; SDO_UTIL.CIRCLE_POLYGON(-71.34937,42.46101,100,5)(SDO_GTYPE, SDO_SRID, SDO_POINT -------------------------------------------------------------------------------- SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(-71.34937, 42.4601107, -71.348653, 42.4602824, -71.348211, 42.4607321, -71.34 8211, 42.4612879, -71.348653, 42.4617376, -71.34937, 42.4619093, -71.350087, 42. 4617376, -71.350529, 42.4612879, -71.350529, 42.4607321, -71.350087, 42.4602824, -71.34937, 42.4601107))
SDO_UTIL.CONCAT_LINES
SDO_UTIL.CONCAT_LINES(
geometry1 IN SDO_GEOMETRY,
geometry2 IN SDO_GEOMETRY
) RETURN SDO_GEOMETRY;
- geometry1
-
First geometry object for the concatenation operation.
- geometry2
-
Second geometry object for the concatenation operation.
Each input geometry must be a two-dimensional line or multiline geometry (that is, the SDO_GTYPE value must be 2002 or 2006). This function is not supported for LRS geometries. To concatenate LRS geometric segments, use the SDO_LRS.CONCATENATE_GEOM_SEGMENTS function (described in Chapter 25).
The input geometries must be line strings whose vertices are connected by straight line segments. Circular arcs and compound line strings are not supported.
If an input geometry is a multiline geometry, the elements of the geometry must be disjoint. If they are not disjoint, this function may return incorrect results.
The topological relationship between geometry1 and geometry2 must be DISJOINT or TOUCH; and if the relationship is TOUCH, the geometries must intersect only at two end points.
You can use the SDO_AGGR_CONCAT_LINES spatial aggregate function (described in Chapter 20) to concatenate multiple two-dimensional line or multiline geometries.
An exception is raised if geometry1 and geometry2 are based on different coordinate systems.
The following example concatenates two simple line string geometries.
-- Concatenate two touching lines: one from (1,1) to (5,1) and the
-- other from (5,1) to (8,1).
SELECT SDO_UTIL.CONCAT_LINES(
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),
SDO_ORDINATE_ARRAY(1,1, 5,1)),
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),
SDO_ORDINATE_ARRAY(5,1, 8,1))
) FROM DUAL;
SDO_UTIL.CONCAT_LINES(SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO
--------------------------------------------------------------------------------
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
1, 1, 5, 1, 8, 1))
SDO_UTIL.CONVERT_UNIT
SDO_UTIL.CONVERT_UNIT(
ivalue IN NUMBER,
in_unit IN VARCHAR2,
out_unit IN VARCHAR2
) RETURN NUMBER;
- value
-
Number of units to be converted. For example, to convert 10 decimal degrees to radians, specify
10. - in_unit
-
The unit of measure from which to convert the input value. Must be a value from the SDO_UNIT column of the MDSYS.SDO_ANGLE_UNITS table (described in Section 6.8.2), the MDSYS.SDO_AREA_UNITS table (described in Section 2.10), or the MDSYS.SDO_DIST_UNITS table (described in Section 2.10). For example, to convert decimal degrees to radians, specify
Degree. - out_unit
-
The unit of measure into which to convert the input value. Must be a value from the SDO_UNIT column of the same table used for
in_unit. For example, to convert decimal degrees to radians, specifyRadian.
The value returned by this function might not be correct at an extremely high degree of precision because of the way internal mathematical operations are performed, especially if they involve small numbers or irrational numbers (such as pi). For example, converting 1 decimal degree into decimal minutes results in the value 60.0000017.
The following example converts 1 radian into decimal degrees.
SQL> SELECT SDO_UTIL.CONVERT_UNIT(1, 'Radian', 'Degree') FROM DUAL;
SDO_UTIL.CONVERT_UNIT(1,'RADIAN','DEGREE')
------------------------------------------
57.2957796
SDO_UTIL.DROP_WORK_TABLES
Drops any transient ("scratch") tables and views in the current schema that were created during the creation of a point cloud or TIN.
- oidstr
-
Object ID string representing a hexadecimal number. Use the string given in the error message that indicated that scratch tables need to be dropped.
If scratch tables still exist from a previous SDO_PC_PKG.CREATE_PC or SDO_TIN_PKG.CREATE_TIN operation when you try to create a point cloud or TIN, an error message is displayed indicating that you must first drop the scratch tables. Use the SDO_UTIL.DROP_WORK_TABLES procedure to drop these scratch tables.
This procedure drops all tables and views that match 'M%_<oidstr>$$%'.
The following example drops the scratch tables from a previous SDO_PC_PKG.CREATE_PC or SDO_TIN_PKG.CREATE_TIN operation, using an OID string specified in a previous error message.
EXECXUTE SDO_UTIL.DROP_WORK_TABLES('A1B2C3');
SDO_UTIL.ELLIPSE_POLYGON
SDO_UTIL.ELLIPSE_POLYGON(
center_longitude IN NUMBER,
center_latitude IN NUMBER,
semi_major_axis IN NUMBER,
semi_minor_axis IN NUMBER,
azimuth IN NUMBER,
arc_tolerance IN NUMBER
) RETURN SDO_GEOMETRY;
- center_longitude
-
Center longitude (in degrees) of the ellipse to be used to create the returned geometry.
- center_latitude
-
Center latitude (in degrees) of the ellipse to be used to create the returned geometry.
- semi_major_axis
-
Length (in meters) of the semi-major axis of the ellipse to be used to create the returned geometry.
- semi_minor_axis
-
Length (in meters) of the semi-minor axis of the ellipse to be used to create the returned geometry.
- azimuth
-
Number of degrees of the azimuth (clockwise rotation of the major axis from north) of the ellipse to be used to create the returned geometry. Must be from 0 to 180. The returned geometry is rotated by the specified number of degrees.
- arc_tolerance
-
A numeric value to be used to construct the polygon geometry. The
arc_toleranceparameter value has the same meaning and usage guidelines as thearc_tolerancekeyword value in theparamsparameter string for the SDO_GEOM.SDO_ARC_DENSIFY function. The unit of measurement associated with the geometry is associated with thearc_toleranceparameter value. (For more information, see the Usage Notes for the SDO_GEOM.SDO_ARC_DENSIFY function in Chapter 24.)
This function is useful for creating an ellipse-like polygon around a specified center point when a true ellipse cannot be used (an ellipse is not valid for geodetic data with Oracle Spatial). The returned geometry has an SDO_SRID value of 8307 (for Longitude / Latitude (WGS 84)).
The following example returns an ellipse-like polygon, oriented east-west (azimuth = 90), around a point near the center of Concord, Massachusetts. An arc_tolerance value of 5 meters is used in computing the polygon vertices.
SELECT SDO_UTIL.ELLIPSE_POLYGON(-71.34937, 42.46101, 100, 50, 90, 5) FROM DUAL; SDO_UTIL.ELLIPSE_POLYGON(-71.34937,42.46101,100,50,90,5)(SDO_GTYPE, SDO_SRID, SD -------------------------------------------------------------------------------- SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(-71.350589, 42.46101, -71.350168, 42.4606701, -71.349708, 42.460578, -71.3493 7, 42.4605603, -71.349032, 42.460578, -71.348572, 42.4606701, -71.348151, 42.461 01, -71.348572, 42.4613499, -71.349032, 42.461442, -71.34937, 42.4614597, -71.34 9708, 42.461442, -71.350168, 42.4613499, -71.350589, 42.46101))
SDO_UTIL.EXTRACT
SDO_UTIL.EXTRACT(
geometry IN SDO_GEOMETRY,
element IN NUMBER,
ring IN NUMBER DEFAULT 0
) RETURN SDO_GEOMETRY;
Returns the two-dimensional geometry that represents a specified element (and optionally a ring) of the input two-dimensional geometry.
- geometry
-
Geometry from which to extract the geometry to be returned. Must be a two-dimensional geometry.
- element
-
Number of the element in the geometry: 1 for the first element, 2 for the second element, and so on. Geometries with SDO_GTYPE values (explained in Section 2.2.1) ending in 1, 2, or 3 have one element; geometries with SDO_GTYPE values ending in 4, 5, 6, or 7 can have more than one element. For example, a multipolygon with an SDO_GTYPE of 2007 might contain three elements (polygons).
- ring
-
Number of the subelement (ring) within
element: 1 for the first subelement, 2 for the second subelement, and so on. This parameter is valid only for specifying a subelement of a polygon with one or more holes or of a point cluster:-
For a polygon with holes, its first subelement is its exterior ring, its second subelement is its first interior ring, its third subelement is its second interior ring, and so on. For example, in the polygon with a hole shown in Figure 2-4 in Section 2.7.2, the exterior ring is subelement 1 and the interior ring (the hole) is subelement 2.
-
For a point cluster, its first subelement is the first point in the point cluster, its second subelement is the second point in the point cluster, and so on.
The default is 0, which causes the entire element to be extracted.
-
This function applies to two-dimensional geometries only. For three-dimensional geometries, use the SDO_UTIL.EXTRACT3D function.
This function is useful for extracting a specific element or subelement from a complex geometry. For example, if you have identified a geometry as invalid by using the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function or the SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT procedure (both of which are documented in Chapter 24), you can use the EXTRACT function to extract the invalid geometry in order to examine it.
For a polygon with one or more holes, the returned geometry representing an extracted interior ring is reoriented so that its vertices are presented in counterclockwise order (as opposed to the clockwise order within an interior ring).
If geometry is null or has an SDO_GTYPE value ending in 0, this function returns a null geometry.
geometry cannot contain a type 0 (zero) element. Type 0 elements are described in Section 2.7.7.
This function is not intended for use with geometries that have any null ordinate values. Any null ordinate values in the returned geometry are replaced by 0 (zero).
An exception is raised if element or ring is an invalid number for geometry.
The following example extracts the first (and only) element in the cola_c geometry. (The example uses the definitions and data from Section 2.1.)
SELECT c.name, SDO_UTIL.EXTRACT(c.shape, 1) FROM cola_markets c WHERE c.name = 'cola_c'; NAME -------------------------------- SDO_UTIL.EXTRACT(C.SHAPE,1)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_IN -------------------------------------------------------------------------------- cola_c SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(3, 3, 6, 3, 6, 5, 4, 5, 3, 3))
The following example inserts a polygon with a hole (using the same INSERT statement as in Example 2-7 in Section 2.7.2), and extracts the geometry representing the hole (the second subelement). Notice that in the geometry returned by the EXTRACT function, the vertices are in counterclockwise order, as opposed to the clockwise order in the hole (second subelement) in the input geometry.
-- Insert polygon with hole.
INSERT INTO cola_markets VALUES(
10,
'polygon_with_hole',
SDO_GEOMETRY(
2003, -- two-dimensional polygon
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1), -- polygon with hole
SDO_ORDINATE_ARRAY(2,4, 4,3, 10,3, 13,5, 13,9, 11,13, 5,13, 2,11, 2,4,
7,5, 7,10, 10,10, 10,5, 7,5)
)
);
1 row created.
-- Extract the hole geometry (second subelement).
SELECT SDO_UTIL.EXTRACT(c.shape, 1, 2)
FROM cola_markets c WHERE c.name = 'polygon_with_hole';
SDO_UTIL.EXTRACT(C.SHAPE,1,2)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(7, 5, 10, 5, 10, 10, 7, 10, 7, 5))
SDO_UTIL.EXTRACT_ALL
SDO_UTIL.EXTRACT_ALL(
geometry IN SDO_GEOMETRY,
flatten IN NUMBER DEFAULT 1
) RETURN SDO_GEOMETRY_ARRAY;
Returns all elements and subelements of the input two-dimensional geometry, as an array of one or more geometries. Returns an object of type SDO_GEOMETRY_ARRAY, which is defined as VARRAY OF SDO_GEOMETRY.
- geometry
-
Geometry from which to extract all elements and subelements. Must be a two-dimensional geometry.
- flatten
-
A flag indicating whether to "flatten" rings into individual geometries for geometries that contain an exterior ring and one or more interior rings:
-
0(zero) returns one geometry for each element, but does not flatten rings into individual geometries. (A geometry will still be returned for each element of the input geometry.) -
1(the default) or any other nonzero value flattens rings into individual geometries.
For example, if a polygon contains an outer ring and an inner ring, a value of
0returns a single geometry containing both rings, and a value of1returns two geometries, each containing a ring as a geometry.This parameter is ignored for geometries that do not contain an exterior ring and one or more interior rings.
-
This function applies to two-dimensional geometries only. For three-dimensional geometries, use the SDO_UTIL.EXTRACT3D function.
This function enables you to extract all elements and subelements from a geometry, regardless of how many elements and subelements the geometry has. Geometries with SDO_GTYPE values (explained in Section 2.2.1) ending in 1, 2, or 3 have one element; geometries with SDO_GTYPE values ending in 4, 5, 6, or 7 can have more than one element. For example, a multipolygon with an SDO_GTYPE of 2007 might contain three elements (polygons). To extract individual elements, use the SDO_UTIL.EXTRACT function instead.
For a polygon with one or more holes, with the default value for the flatten parameter, the returned geometry representing an extracted interior ring is reoriented so that its vertices are presented in counterclockwise order (as opposed to the clockwise order within an interior ring). However, if the flatten parameter value is 0, no reorientation is performed.
If geometry is null or has an SDO_GTYPE value ending in 0, this function returns a null geometry.
geometry cannot contain a type 0 (zero) element. Type 0 elements are described in Section 2.7.7.
This function is not intended for use with geometries that have any null ordinate values. Any null ordinate values in the returned geometry are replaced by 0 (zero).
The following example extracts all elements from the cola_b geometry. (The example uses the definitions and data from Section 2.1.)
SELECT * FROM TABLE(
SELECT SDO_UTIL.EXTRACT_ALL(c.shape)
FROM cola_markets c WHERE c.name = 'cola_b');
SDO_GTYPE SDO_SRID
---------- ----------
SDO_POINT(X, Y, Z)
--------------------------------------------------------------------------------
SDO_ELEM_INFO
--------------------------------------------------------------------------------
SDO_ORDINATES
--------------------------------------------------------------------------------
2003
SDO_ELEM_INFO_ARRAY(1, 1003, 1)
SDO_ORDINATE_ARRAY(5, 1, 8, 1, 8, 6, 5, 7, 5, 1)
The following example inserts a polygon with a hole (using the same INSERT statement as in Example 2-7 in Section 2.7.2), and extracts all elements and subelements from the polygon_with_hole geometry. Notice that because the flatten parameter is not specified, in the second geometry returned by the EXTRACT_ALL function the vertices are in counterclockwise order, as opposed to the clockwise order in the hole (second subelement) in the input geometry.
-- Insert polygon with hole.
INSERT INTO cola_markets VALUES(
10,
'polygon_with_hole',
SDO_GEOMETRY(
2003, -- two-dimensional polygon
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1), -- polygon with hole
SDO_ORDINATE_ARRAY(2,4, 4,3, 10,3, 13,5, 13,9, 11,13, 5,13, 2,11, 2,4,
7,5, 7,10, 10,10, 10,5, 7,5)
)
);
1 row created.
-- Extract all, with default for flatten.
SELECT * FROM TABLE(
SELECT SDO_UTIL.EXTRACT_ALL(c.shape)
FROM cola_markets c WHERE c.name = 'polygon_with_hole');
SDO_GTYPE SDO_SRID
---------- ----------
SDO_POINT(X, Y, Z)
--------------------------------------------------------------------------------
SDO_ELEM_INFO
--------------------------------------------------------------------------------
SDO_ORDINATES
--------------------------------------------------------------------------------
2003
SDO_ELEM_INFO_ARRAY(1, 1003, 1)
SDO_ORDINATE_ARRAY(2, 4, 4, 3, 10, 3, 13, 5, 13, 9, 11, 13, 5, 13, 2, 11, 2, 4)
SDO_GTYPE SDO_SRID
---------- ----------
SDO_POINT(X, Y, Z)
--------------------------------------------------------------------------------
SDO_ELEM_INFO
--------------------------------------------------------------------------------
SDO_ORDINATES
--------------------------------------------------------------------------------
2003
SDO_ELEM_INFO_ARRAY(1, 1003, 1)
SDO_ORDINATE_ARRAY(7, 5, 10, 5, 10, 10, 7, 10, 7, 5)
The following example extracts all elements and subelements from the polygon_with_hole geometry (inserted in the preceding example), and it specifies the flatten parameter value as 0 (zero). This causes the returned array to contain a single geometry that is the same as the input geometry; thus, in the geometry returned by the EXTRACT_ALL function, the vertices are in same clockwise order in the hole (second subelement) as in the input geometry.
-- Extract all, with flatten = 0. SELECT * FROM TABLE( SELECT SDO_UTIL.EXTRACT_ALL(c.shape, 0) FROM cola_markets c WHERE c.name = 'polygon_with_hole'); SDO_GTYPE SDO_SRID ---------- ---------- SDO_POINT(X, Y, Z) -------------------------------------------------------------------------------- SDO_ELEM_INFO -------------------------------------------------------------------------------- SDO_ORDINATES -------------------------------------------------------------------------------- 2003 SDO_ELEM_INFO_ARRAY(1, 1003, 1, 19, 2003, 1) SDO_ORDINATE_ARRAY(2, 4, 4, 3, 10, 3, 13, 5, 13, 9, 11, 13, 5, 13, 2, 11, 2, 4, 7, 5, 7, 10, 10, 10, 10, 5, 7, 5) SDO_GTYPE SDO_SRID ---------- ---------- SDO_POINT(X, Y, Z) -------------------------------------------------------------------------------- SDO_ELEM_INFO -------------------------------------------------------------------------------- SDO_ORDINATES --------------------------------------------------------------------------------
SDO_UTIL.EXTRACT3D
Returns the three-dimensional geometry that represents a specified subset of the input three-dimensional geometry.
- geometry
-
Geometry from which to extract the geometry to be returned. Must be a three-dimensional geometry
- label
-
A comma-delimited string of numbers that identify the subset geometry to be returned. Each number identifies the relative position of a geometry item within the input geometry. The items and their positions within the
labelstring are:-
pointID: Point number -
edgeID: Edge number -
ringID: Ring number -
polygonID: Polygon number -
csurfID: Composite surface number -
solidID: Solid number -
multiID: Multisolid number
A value of 0 (zero) means that the item does not apply, and you can omit trailing items that do not apply. For example,
'0,2,1,4,1'means that point number does not apply, and it specifies the second edge of the first ring of the fourth polygon of the first composite surface. -
This function applies to three-dimensional geometries only. For two-dimensional geometries, use the SDO_UTIL.EXTRACT function.
This function uses the getElementByLabel method of the oracle.spatial.geometry.ElementExtractor Java class, which is described in Oracle Spatial Java API Reference.
The following example extracts, from a specified three-dimensional geometry, the subset geometry consisting of the following: edge 2 of ring 1 of polygon 4 of composite surface 1 of the input geometry.
SELECT SDO_UTIL.EXTRACT3D(
SDO_GEOMETRY (3008,NULL,NULL ,
SDO_ELEM_INFO_ARRAY(
1,1007,1,
1,1006,6,
1,1003,1,
16,1003,1,
31,1003,1,
46,1003,1,
61,1003,1,
76,1003,1),
SDO_ORDINATE_ARRAY(
1.0,0.0,-1.0,
1.0,1.0,-1.0,
1.0,1.0,1.0,
1.0,0.0,1.0,
1.0,0.0,-1.0,
1.0,0.0,1.0,
0.0,0.0,1.0,
0.0,0.0,-1.0,
1.0,0.0,-1.0,
1.0,0.0,1.0,
0.0,1.0,1.0,
0.0,1.0,-1.0,
0.0,0.0,-1.0,
0.0,0.0,1.0,
0.0,1.0,1.0,
1.0,1.0,-1.0,
0.0,1.0,-1.0,
0.0,1.0,1.0,
1.0,1.0,1.0,
1.0,1.0,-1.0,
1.0,1.0,1.0,
0.0,1.0,1.0,
0.0,0.0,1.0,
1.0,0.0,1.0,
1.0,1.0,1.0,
1.0,1.0,-1.0,
1.0,0.0,-1.0,
0.0,0.0,-1.0,
0.0,1.0,-1.0,
1.0,1.0,-1.0
)
),
'0,2,1,4,1')
FROM DUAL;
SDO_UTIL.EXTRACT3D(SDO_GEOMETRY(3008,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1007,1,1,10
--------------------------------------------------------------------------------
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
0, 1, -1, 0, 1, 1))
SDO_UTIL.EXTRUDE
SDO_UTIL.EXTRUDE(
geometry IN SDO_GEOMETRY,
grdheight IN SDO_NUMBER_ARRAY,
height IN SDO_NUMBER_ARRAY,
tol IN NUMBER,
optional3dSrid IN NUMBER DEFAULT NULL
) RETURN SDO_GEOMETRY;
Returns the three-dimensional extrusion solid geometry from an input two-dimensional polygon geometry.
- geometry
-
Two-dimensional polygon geometry from which to return the extrusion geometry. This geometry forms the "base" of the returned geometry.
- grdheight
-
Ground heights as a set of Z (height) values at the base of the solid. The numbers in this array should be the Z (height) values at the base of each vertex in the input geometry.
- height
-
Height values for the extrusion geometry. The numbers in this array should be the Z (height) values at the "top" of each corresponding point in the grdheight array. For example, if the ground height at the base of the first vertex is 0 and the height at that vertex is 10, the solid at that point along the base extends 10 units high.
- tol
-
Tolerance value (see Section 1.5.5).
- optional3dSrid
-
Three-dimensional coordinate system (SRID) to be assigned to the returned geometry. If you do not specify this parameter, Spatial automatically assigns a three-dimensional SRID value based on the SRID value of the input geometry.
The input geometry must be a two-dimensional polygon geometry.
If the input geometry is a polygon with multiple inner rings, this function internally combines these inner rings to make them one inner ring, producing a new geometry that approximately represents the original appearance; the function then performs the extrusion process on this new geometry, and returns the result.
The following example returns the three-dimensional solid geometry representing an extrusion from a two-dimensional polygon geometry.
SELECT SDO_UTIL.EXTRUDE(
SDO_GEOMETRY(
2003,
null,
null,
SDO_ELEM_INFO_ARRAY(1,1003,1),
SDO_ORDINATE_ARRAY(5, 1,8,1,8,6,5,7,5,1)),
SDO_NUMBER_ARRAY(0,0,0,0,0),
SDO_NUMBER_ARRAY(5,10,10,5,5),
0.005) from dual;
SDO_UTIL.EXTRUDE(SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_O
--------------------------------------------------------------------------------
SDO_GEOMETRY(3008, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1007, 1, 1, 1006, 6, 1, 10
03, 1, 16, 1003, 1, 31, 1003, 1, 46, 1003, 1, 61, 1003, 1, 76, 1003, 1), SDO_ORD
INATE_ARRAY(5, 1, 0, 5, 7, 0, 8, 6, 0, 8, 1, 0, 5, 1, 0, 5, 1, 5, 8, 1, 10, 8, 6
, 10, 5, 7, 5, 5, 1, 5, 5, 1, 0, 8, 1, 0, 8, 1, 10, 5, 1, 5, 5, 1, 0, 8, 1, 0, 8
, 6, 0, 8, 6, 10, 8, 1, 10, 8, 1, 0, 8, 6, 0, 5, 7, 0, 5, 7, 5, 8, 6, 10, 8, 6,
0, 5, 7, 0, 5, 1, 0, 5, 1, 5, 5, 7, 5, 5, 7, 0))
The following example returns the three-dimensional composite solid geometry representing an extrusion from a two-dimensional polygon geometry with inner rings.
SELECT SDO_UTIL.EXTRUDE(
SDO_GEOMETRY(
2003,
null,
null,
SDO_ELEM_INFO_ARRAY(1, 1003, 1, 11, 2003, 1,
21, 2003,1, 31,2003,1, 41, 2003, 1),
SDO_ORDINATE_ARRAY(0,0, 8,0, 8,8, 0,8, 0,0,
1,3, 1,4, 2,4, 2,3, 1,3, 1,1, 1,2, 2,2, 2,1, 1,1,
1,6, 1,7, 2,7, 2,6, 1,6, 3,2, 3,4, 4,4, 4,2, 3,2)),
SDO_NUMBER_ARRAY(-1.0),
SDO_NUMBER_ARRAY(1.0),
0.0001) from dual;
SDO_UTIL.EXTRUDE(SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1,11,200
--------------------------------------------------------------------------------
SDO_GEOMETRY(3008, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1008, 4, 1, 1007, 1, 1, 10
06, 16, 1, 1003, 1, 46, 1003, 1, 91, 1003, 1, 106, 1003, 1, 121, 1003, 1, 136, 1
003, 1, 151, 1003, 1, 166, 1003, 1, 181, 1003, 1, 196, 1003, 1, 211, 1003, 1, 22
6, 1003, 1, 241, 1003, 1, 256, 1003, 1, 271, 1003, 1, 286, 1003, 1, 301, 1007, 1
, 301, 1006, 10, 301, 1003, 1, 328, 1003, 1, 355, 1003, 1, 370, 1003, 1, 385, 10
03, 1, 400, 1003, 1, 415, 1003, 1, 430, 1003, 1, 445, 1003, 1, 460, 1003, 1, 475
, 1007, 1, 475, 1006, 6, 475, 1003, 1, 490, 1003, 1, 505, 1003, 1, 520, 1003, 1,
535, 1003, 1, 550, 1003, 1, 565, 1007, 1, 565, 1006, 10, 565, 1003, 1, 592, 100
3, 1, 619, 1003, 1, 634, 1003, 1, 649, 1003, 1, 664, 1003, 1, 679, 1003, 1, 694,
1003, 1, 709, 1003, 1, 724, 1003, 1), SDO_ORDINATE_ARRAY(4, 0, -1, 4, 2, -1, 4,
4, -1, 3, 4, -1, 2, 4, -1, 2, 7, -1, 1, 7, -1, 1, 6, -1, 1, 4, -1, 1, 3, -1, 0,
3, -1, 0, 8, -1, 8, 8, -1, 8, 0, -1, 4, 0, -1, 4, 0, 1, 8, 0, 1, 8, 8, 1, 0, 8,
1, 0, 3, 1, 1, 3, 1, 1, 4, 1, 1, 6, 1, 1, 7, 1, 2, 7, 1, 2, 4, 1, 3, 4, 1, 4, 4
, 1, 4, 2, 1, 4, 0, 1, 4, 0, -1, 8, 0, -1, 8, 0, 1, 4, 0, 1, 4, 0, -1, 8, 0, -1,
8, 8, -1, 8, 8, 1, 8, 0, 1, 8, 0, -1, 8, 8, -1, 0, 8, -1, 0, 8, 1, 8, 8, 1, 8,
8, -1, 0, 8, -1, 0, 3, -1, 0, 3, 1, 0, 8, 1, 0, 8, -1, 0, 3, -1, 1, 3, -1, 1, 3,
1, 0, 3, 1, 0, 3, -1, 1, 3, -1, 1, 4, -1, 1, 4, 1, 1, 3, 1, 1, 3, -1, 1, 4, -1,
1, 6, -1, 1, 6, 1, 1, 4, 1, 1, 4, -1, 1, 6, -1, 1, 7, -1, 1, 7, 1, 1, 6, 1, 1,
6, -1, 1, 7, -1, 2, 7, -1, 2, 7, 1, 1, 7, 1, 1, 7, -1, 2, 7, -1, 2, 4, -1, 2, 4,
1, 2, 7, 1, 2, 7, -1, 2, 4, -1, 3, 4, -1, 3, 4, 1, 2, 4, 1, 2, 4, -1, 3, 4, -1,
4, 4, -1, 4, 4, 1, 3, 4, 1, 3, 4, -1, 4, 4, -1, 4, 2, -1, 4, 2, 1, 4, 4, 1, 4,
4, -1, 4, 2, -1, 4, 0, -1, 4, 0, 1, 4, 2, 1, 4, 2, -1, 0, 3, -1, 1, 3, -1, 1, 1,
-1, 2, 1, -1, 3, 2, -1, 4, 2, -1, 4, 0, -1, 0, 0, -1, 0, 3, -1, 0, 3, 1, 0, 0,
1, 4, 0, 1, 4, 2, 1, 3, 2, 1, 2, 1, 1, 1, 1, 1, 1, 3, 1, 0, 3, 1, 0, 3, -1, 0, 0
, -1, 0, 0, 1, 0, 3, 1, 0, 3, -1, 0, 0, -1, 4, 0, -1, 4, 0, 1, 0, 0, 1, 0, 0, -1
, 4, 0, -1, 4, 2, -1, 4, 2, 1, 4, 0, 1, 4, 0, -1, 4, 2, -1, 3, 2, -1, 3, 2, 1, 4
, 2, 1, 4, 2, -1, 3, 2, -1, 2, 1, -1, 2, 1, 1, 3, 2, 1, 3, 2, -1, 2, 1, -1, 1, 1
, -1, 1, 1, 1, 2, 1, 1, 2, 1, -1, 1, 1, -1, 1, 3, -1, 1, 3, 1, 1, 1, 1, 1, 1, -1
, 1, 3, -1, 0, 3, -1, 0, 3, 1, 1, 3, 1, 1, 3, -1, 1, 6, -1, 2, 6, -1, 2, 4, -1,
1, 4, -1, 1, 6, -1, 1, 6, 1, 1, 4, 1, 2, 4, 1, 2, 6, 1, 1, 6, 1, 1, 6, -1, 1, 4,
-1, 1, 4, 1, 1, 6, 1, 1, 6, -1, 1, 4, -1, 2, 4, -1, 2, 4, 1, 1, 4, 1, 1, 4, -1,
2, 4, -1, 2, 6, -1, 2, 6, 1, 2, 4, 1, 2, 4, -1, 2, 6, -1, 1, 6, -1, 1, 6, 1, 2,
6, 1, 2, 6, -1, 1, 3, -1, 2, 3, -1, 2, 4, -1, 3, 4, -1, 3, 2, -1, 2, 1, -1, 2,
2, -1, 1, 2, -1, 1, 3, -1, 1, 3, 1, 1, 2, 1, 2, 2, 1, 2, 1, 1, 3, 2, 1, 3, 4, 1,
2, 4, 1, 2, 3, 1, 1, 3, 1, 1, 3, -1, 1, 2, -1, 1, 2, 1, 1, 3, 1, 1, 3, -1, 1, 2
, -1, 2, 2, -1, 2, 2, 1, 1, 2, 1, 1, 2, -1, 2, 2, -1, 2, 1, -1, 2, 1, 1, 2, 2, 1
, 2, 2, -1, 2, 1, -1, 3, 2, -1, 3, 2, 1, 2, 1, 1, 2, 1, -1, 3, 2, -1, 3, 4, -1,
3, 4, 1, 3, 2, 1, 3, 2, -1, 3, 4, -1, 2, 4, -1, 2, 4, 1, 3, 4, 1, 3, 4, -1, 2, 4
, -1, 2, 3, -1, 2, 3, 1, 2, 4, 1, 2, 4, -1, 2, 3, -1, 1, 3, -1, 1, 3, 1, 2, 3, 1
, 2, 3, -1))
SDO_UTIL.FROM_GML311GEOMETRY
SDO_UTIL.FROM_GML311GEOMETRY(
geometry IN CLOB,
srsNamespace IN VARCHAR2 DEFAULT NULL
) RETURN SDO_GEOMETRY;
or
SDO_UTIL.FROM_GML311GEOMETRY(
geometry IN VARCHAR2,
srsNamespace IN VARCHAR2 DEFAULT NULL
) RETURN SDO_GEOMETRY;
- geometry
-
Geometry in GML version 3.1.1 format to be converted to SDO_GEOMETRY format.
- srsNamespace
-
(Reserved for Oracle use.)
The input geometry must be a valid GML fragment describing a GML version 3.1.1 geometry type defined in the Open GIS Implementation Specification.
The following example shows conversion to and from GML version 3.1.1 format. (The example uses the definitions and data from Section 2.1, specifically the cola_b geometry from the COLA_MARKETS table.)
DECLARE
gmlgeom CLOB;
geom_result SDO_GEOMETRY;
geom SDO_GEOMETRY;
BEGIN
SELECT c.shape INTO geom FROM cola_markets c WHERE c.name = 'cola_b';
-- To GML 3.1.1 geometry
gmlgeom := SDO_UTIL.TO_GML311GEOMETRY(geom);
DBMS_OUTPUT.PUT_LINE('To GML 3.1.1 geometry result = ' || TO_CHAR(gmlgeom));
-- From GML 3.1.3 geometry
geom_result := SDO_UTIL.FROM_GML311GEOMETRY(gmlgeom);
END;
/
To GML 3.1.1 geometry result = <gml:Polygon srsName="SDO:"
xmlns:gml="http://www.opengis.net/gml"><gml:exterior><gml:LinearRing><gml:posLis
t srsDimension="2">5.0 1.0 8.0 1.0 8.0 6.0 5.0 7.0 5.0 1.0
</gml:posList></gml:LinearRing></gml:exterior></gml:Polygon>
PL/SQL procedure successfully completed.
SDO_UTIL.FROM_GMLGEOMETRY
SDO_UTIL.FROM_GMLGEOMETRY(
geometry IN CLOB,
srsNamespace IN VARCHAR2 DEFAULT NULL
) RETURN SDO_GEOMETRY;
or
SDO_UTIL.FROM_GMLGEOMETRY(
geometry IN VARCHAR2,
srsNamespace IN VARCHAR2 DEFAULT NULL
) RETURN SDO_GEOMETRY;
- geometry
-
Geometry in GML version 2.0 format to be converted to SDO_GEOMETRY format.
- srsNamespace
-
(Reserved for Oracle use.)
The input geometry must be a valid GML fragment describing a GML version 2.0 geometry type defined in the Open GIS Implementation Specification.
The following example shows conversion to and from GML version 2.0 format. (The example uses the definitions and data from Section 2.1, specifically the cola_b geometry from the COLA_MARKETS table.)
DECLARE
gmlgeom CLOB;
geom_result SDO_GEOMETRY;
geom SDO_GEOMETRY;
BEGIN
SELECT c.shape INTO geom FROM cola_markets c WHERE c.name = 'cola_b';
-- To GML geometry
gmlgeom := SDO_UTIL.TO_GMLGEOMETRY(geom);
DBMS_OUTPUT.PUT_LINE('To GML geometry result = ' || TO_CHAR(gmlgeom));
-- From GML geometry
geom_result := SDO_UTIL.FROM_GMLGEOMETRY(gmlgeom);
END;
/
To GML geometry result = <gml:Polygon srsName="SDO:"
xmlns:gml="http://www.opengis.net/gml"><gml:outerBoundaryIs><gml:LinearRing><gml
:coordinates decimal="." cs="," ts=" ">5.0,1.0 8.0,1.0 8.0,6.0 5.0,7.0 5.0,1.0
</gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Polygon>
PL/SQL procedure successfully completed.
SDO_UTIL.FROM_KMLGEOMETRY
SDO_UTIL.FROM_KMLGEOMETRY(
geometry IN CLOB
) RETURN SDO_GEOMETRY;
or
SDO_UTIL.FROM_KMLGEOMETRY(
geometry IN VARCHAR2
) RETURN SDO_GEOMETRY;
- geometry
-
Geometry in KML format of type CLOB or VARCHAR2 to be converted to SDO_GEOMETRY format.
The input geometry must be a valid document conforming to the KML 2.1 specification.
This function does not process the whole KML document; it only processes the KML geometry tags.
The following example shows conversion to and from KML format. (The example uses the definitions and data from Section 2.1, specifically the cola_c geometry from the COLA_MARKETS table.)
-- Convert cola_c geometry to a KML document; convert that result to
-- a spatial geometry.
DECLARE
kmlgeom CLOB;
val_result VARCHAR2(5);
geom_result SDO_GEOMETRY;
geom SDO_GEOMETRY;
BEGIN
SELECT c.shape INTO geom FROM cola_markets c WHERE c.name = 'cola_c';
-- To KML geometry
kmlgeom := SDO_UTIL.TO_KMLGEOMETRY(geom);
DBMS_OUTPUT.PUT_LINE('To KML geometry result = ' || TO_CHAR(kmlgeom));
-- From KML geometry
geom_result := SDO_UTIL.FROM_KMLGEOMETRY(kmlgeom);
-- Validate the returned geometry
val_result := SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(geom_result, 0.005);
DBMS_OUTPUT.PUT_LINE('Validation result = ' || val_result);
END;
/
To KML geometry result =
<Polygon><extrude>0</extrude><tessellate>0</tessellate><altitudeMode>relativeToG
round</altitudeMode><outerBoundaryIs><LinearRing><coordinates>3.0,3.0 6.0,3.0
6.0,5.0 4.0,5.0 3.0,3.0 </coordinates></LinearRing></outerBoundaryIs></Polygon>
Validation result = TRUE
SDO_UTIL.FROM_WKBGEOMETRY
The input geometry must be in the well-known binary (WKB) format, as defined by the Open Geospatial Consortium and the International Organization for Standardization (ISO).
This function is patterned after the SQL Multimedia recommendations in ISO 13249-3, Information technology - Database languages - SQL Multimedia and Application Packages - Part 3: Spatial.
To convert an SDO_GEOMETRY object to WKB format, use the SDO_UTIL.TO_WKBGEOMETRY function.
The following example shows conversion to and from WKB and WKT format, and validation of WKB and WKT geometries. (The example uses the definitions and data from Section 2.1, specifically the cola_b geometry from the COLA_MARKETS table.)
DECLARE
wkbgeom BLOB;
wktgeom CLOB;
val_result VARCHAR2(5);
geom_result SDO_GEOMETRY;
geom SDO_GEOMETRY;
BEGIN
SELECT c.shape INTO geom FROM cola_markets c WHERE c.name = 'cola_b';
-- To WBT/WKT geometry
wkbgeom := SDO_UTIL.TO_WKBGEOMETRY(geom);
wktgeom := SDO_UTIL.TO_WKTGEOMETRY(geom);
DBMS_OUTPUT.PUT_LINE('To WKT geometry result = ' || TO_CHAR(wktgeom));
-- From WBT/WKT geometry
geom_result := SDO_UTIL.FROM_WKBGEOMETRY(wkbgeom);
geom_result := SDO_UTIL.FROM_WKTGEOMETRY(wktgeom);
-- Validate WBT/WKT geometry
val_result := SDO_UTIL.VALIDATE_WKBGEOMETRY(wkbgeom);
DBMS_OUTPUT.PUT_LINE('WKB validation result = ' || val_result);
val_result := SDO_UTIL.VALIDATE_WKTGEOMETRY(wktgeom);
DBMS_OUTPUT.PUT_LINE('WKT validation result = ' || val_result);
END;/
To WKT geometry result = POLYGON ((5.0 1.0, 8.0 1.0, 8.0 6.0, 5.0 7.0, 5.0 1.0))
WKB validation result = TRUE
WKT validation result = TRUE
SDO_UTIL.FROM_WKTGEOMETRY
SDO_UTIL.FROM_WKTGEOMETRY(
geometry IN CLOB
) RETURN SDO_GEOMETRY;
or
SDO_UTIL.FROM_WKTGEOMETRY(
geometry IN VARCHAR2
) RETURN SDO_GEOMETRY;
The input geometry must be in the well-known text (WKT) format, as defined by the Open Geospatial Consortium and the International Organization for Standardization (ISO).
This function is patterned after the SQL Multimedia recommendations in ISO 13249-3, Information technology - Database languages - SQL Multimedia and Application Packages - Part 3: Spatial.
To convert an SDO_GEOMETRY object to a CLOB in WKT format, use the SDO_UTIL.TO_WKTGEOMETRY function. (You can use the SQL function TO_CHAR to convert the resulting CLOB to VARCHAR2 type.)
The following example shows conversion to and from WKB and WKT format, and validation of WKB and WKT geometries. (The example uses the definitions and data from Section 2.1, specifically the cola_b geometry from the COLA_MARKETS table.)
DECLARE
wkbgeom BLOB;
wktgeom CLOB;
val_result VARCHAR2(5);
geom_result SDO_GEOMETRY;
geom SDO_GEOMETRY;
BEGIN
SELECT c.shape INTO geom FROM cola_markets c WHERE c.name = 'cola_b';
-- To WBT/WKT geometry
wkbgeom := SDO_UTIL.TO_WKBGEOMETRY(geom);
wktgeom := SDO_UTIL.TO_WKTGEOMETRY(geom);
DBMS_OUTPUT.PUT_LINE('To WKT geometry result = ' || TO_CHAR(wktgeom));
-- From WBT/WKT geometry
geom_result := SDO_UTIL.FROM_WKBGEOMETRY(wkbgeom);
geom_result := SDO_UTIL.FROM_WKTGEOMETRY(wktgeom);
-- Validate WBT/WKT geometry
val_result := SDO_UTIL.VALIDATE_WKBGEOMETRY(wkbgeom);
DBMS_OUTPUT.PUT_LINE('WKB validation result = ' || val_result);
val_result := SDO_UTIL.VALIDATE_WKTGEOMETRY(wktgeom);
DBMS_OUTPUT.PUT_LINE('WKT validation result = ' || val_result);
END;/
To WKT geometry result = POLYGON ((5.0 1.0, 8.0 1.0, 8.0 6.0, 5.0 7.0, 5.0 1.0))
WKB validation result = TRUE
WKT validation result = TRUE
SDO_UTIL.GETNUMELEM
The following example returns the number of elements for each geometry in the SHAPE column of the COLA_MARKETS table. (The example uses the definitions and data from Section 2.1.)
SELECT c.name, SDO_UTIL.GETNUMELEM(c.shape) FROM cola_markets c; NAME SDO_UTIL.GETNUMELEM(C.SHAPE) -------------------------------- ---------------------------- cola_a 1 cola_b 1 cola_c 1 cola_d 1
SDO_UTIL.GETNUMVERTICES
The following example returns the number of vertices for each geometry in the SHAPE column of the COLA_MARKETS table. (The example uses the definitions and data from Section 2.1.)
SELECT c.name, SDO_UTIL.GETNUMVERTICES(c.shape) FROM cola_markets c; NAME SDO_UTIL.GETNUMVERTICES(C.SHAPE) -------------------------------- -------------------------------- cola_a 2 cola_b 5 cola_c 5 cola_d 3
SDO_UTIL.GETVERTICES
This function returns an object of MDSYS.VERTEX_SET_TYPE, which consists of a table of objects of MDSYS.VERTEX_TYPE. Oracle Spatial defines the type VERTEX_SET_TYPE as:
CREATE TYPE vertex_set_type as TABLE OF vertex_type;
Oracle Spatial defines the object type VERTEX_TYPE as:
CREATE TYPE vertex_type AS OBJECT
(x NUMBER,
y NUMBER,
z NUMBER,
w NUMBER,
v5 NUMBER,
v6 NUMBER,
v7 NUMBER,
v8 NUMBER,
v9 NUMBER,
v10 NUMBER,
v11 NUMBER,
id NUMBER);
Note:
The VERTEX_SET_TYPE and VERTEX_TYPE types are intended for use by Oracle only. Do not use these types in column definitions or functions that you create.This function can be useful in finding a vertex that is causing a geometry to be invalid. For example, if you have identified a geometry as invalid by using the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function or the SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT procedure (both of which are documented in Chapter 24), you can use the GETVERTICES function to view the vertices in tabular format.
The following example returns the X and Y coordinates and ID values of the vertices of the geometries in the SHAPE column of the COLA_MARKETS table. (The example uses the definitions and data from Section 2.1.)
SELECT c.mkt_id, c.name, t.X, t.Y, t.id
FROM cola_markets c,
TABLE(SDO_UTIL.GETVERTICES(c.shape)) t
ORDER BY c.mkt_id, t.id;
MKT_ID NAME X Y ID
---------- -------------------------------- ---------- ---------- ----------
1 cola_a 1 1 1
1 cola_a 5 7 2
2 cola_b 5 1 1
2 cola_b 8 1 2
2 cola_b 8 6 3
2 cola_b 5 7 4
2 cola_b 5 1 5
3 cola_c 3 3 1
3 cola_c 6 3 2
3 cola_c 6 5 3
3 cola_c 4 5 4
3 cola_c 3 3 5
4 cola_d 8 7 1
4 cola_d 10 9 2
4 cola_d 8 11 3
15 rows selected.
SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS
Initializes all spatial indexes in a tablespace that was transported to another database.
This procedure is part of the support for using the Oracle transportable tablespace feature with tablespaces that contain any spatial indexes. Use this procedure only either (A) the import operation of pre-Release 11.2 dump files is completed, or (B) after the import operation from different endian platform in Release 11.2 or later is completed. Each user that has a spatial index in the tablespace must call the procedure.
For pre-Release 11.2 dump files, after calling the SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS procedure, you must execute a statement in the following format for each index that is in the imported transportable tablespace:
ALTER INDEX spatial-index-from-imported-tts PARAMETERS ('CLEAR_TTS=TRUE');
For detailed information about transportable tablespaces and transporting tablespaces to other databases, see Oracle Database Administrator's Guide.
The following example for an import of pre-Release 11.2 dump files initializes all spatial indexes in a tablespace that was transported to another database. It also includes the required ALTER INDEX statement for two hypothetical spatial indexes.
CALL SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS;
ALTER INDEX xyz1_spatial_idx PARAMETERS ('CLEAR_TTS=TRUE');
ALTER INDEX xyz2_spatial_idx PARAMETERS ('CLEAR_TTS=TRUE');
In the following example, the owner of the spatial index must call the SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS procedure only if the SELECT statement returns the string Y, to reflect the fact that the spatial indexes are imported from different endian platforms in Release 11.2.
SELECT DECODE(BITAND(sdo_index_version, 1024), 1024, 'Y', 'N') ENDIAN_FLAG FROM user_sdo_index_metadata WHERE sdo_index_name = :index_name; -- If the result is 'Y', perform the next statement. CALL SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS; -- No ALTER INDEX statements are needed.
In this example, if you call the SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS procedure when the SELECT statement returns the string N, the procedure does nothing because there is no need to perform endian conversion.
SDO_UTIL.INTERIOR_POINT
SDO_UTIL.INTERIOR_POINT(
geom IN SDO_GEOMETRY,
tol IN NUMBER DEFAULT 0.00000000005
) RETURN SDO_GEOMETRY;
Returns a point that is guaranteed to be an interior point (not on the boundary or edge) on the surface of a polygon geometry object.
- geom
-
Polygon geometry object. The SDO_GTYPE value of the geometry must be 2003 or 2007. (SDO_GTYPE values are explained in Section 2.2.1.)
- tol
-
Tolerance value (see Section 1.5.5).
This function returns a point geometry object representing a point that is guaranteed to be an interior point on the surface, but not on the boundary or edge, of geom. The returned point can be any interior point on the surface; however, if you call the function multiple times with the same geom and tol parameter values, the returned point will be the same.
The relationship between the returned point and the original geometry is INSIDE, which you can check using the SDO_RELATE operator with'mask=inside'.
In most cases this function is more useful than the SDO_GEOM.SDO_POINTONSURFACE function, which returns a point that is not guaranteed to be an interior point.
The following example returns a geometry object that is an interior point on the surface of cola_a. (The example uses the definitions and data from Section 2.1.)
-- Return an interior point on the surface of a geometry. SELECT SDO_UTIL.INTERIOR_POINT(c.shape, 0.005) FROM cola_markets c WHERE c.name = 'cola_a'; SDO_UTIL.INTERIOR_POINT(C.SHAPE,0.005)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), -------------------------------------------------------------------------------- SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(2.75, 2.875, NULL), NULL, NULL)
SDO_UTIL.POINT_AT_BEARING
SDO_UTIL.POINT_AT_BEARING(
start_point IN SDO_GEOMETRY,
bearing IN NUMBER,
distance IN NUMBER
) RETURN SDO_GEOMETRY;
Returns a point geometry that is at the specified distance and bearing from the start point.
- start_point
-
Point geometry object from which to compute the distance at the specified bearing, to locate the desired point. The point geometry must be based on a geodetic coordinate system.
- bearing
-
Number of radians, measured clockwise from North. Must be in the range of either -pi to pi or 0 to 2*pi. (Either convention on ranges will work).
- distance
-
Number of meters from
start_pointand along the initial bearing direction to the computed destination point. Must be less than one-half the circumference of the Earth.
The input point geometry must be based on a geodetic coordinate system. If it is based on a non-geodetic coordinate system, this function returns a null value.
To convert decimal degrees to radians or nonmetric distances to meters, you can use the SDO_UTIL.CONVERT_UNIT function. To compute the bearing and tilt from a start point to an end point, you can use the SDO_UTIL.BEARING_TILT_FOR_POINTS procedure.
The following example returns the point 100 kilometers at a bearing of 1 radian from the point with the longitude and latitude coordinates (-72, 43).
SELECT SDO_UTIL.POINT_AT_BEARING(
SDO_GEOMETRY(2001, 8307,
SDO_POINT_TYPE(-72, 43, NULL), NULL, NULL),
1, -- 1 radian (57.296 degrees clockwise from North)
100000 -- 100 kilometers
) FROM DUAL;
SDO_UTIL.POINT_AT_BEARING(SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(-72,43,NULL),NUL
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(
-70.957053, 43.4811935))
SDO_UTIL.POLYGONTOLINE
Converts all polygon-type elements in a geometry to line-type elements, and sets the SDO_GTYPE value accordingly.
The order of the vertices of each resulting line-type element is the same as in the associated polygon-type element, and the start and end points of each line-type segment are the same point.
If the input geometry is a line, it is returned.
The following example converts the input polygon geometry, which is the same geometry as cola_b (see Figure 2-1 and Example 2-1 in Section 2.1), to a line string geometry. In the returned geometry, the SDO_GTYPE value (2002) indicates a two-dimensional LINE geometry, and the SDO_ETYPE value (2) and SDO_INTERPRETATION value (1) in the SDO_ELEM_INFO array indicate a line string whose vertices are connected by straight line segments.
SELECT SDO_UTIL.POLYGONTOLINE(
SDO_GEOMETRY(
2003, -- two-dimensional polygon
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)
)
) FROM DUAL;
SDO_UTIL.POLYGONTOLINE(SDO_GEOMETRY(2003,--TWO-DIMENSIONALPOLYGONNULL,NULL,SDO_E
--------------------------------------------------------------------------------
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
5, 1, 8, 1, 8, 6, 5, 7, 5, 1))
SDO_UTIL.PREPARE_FOR_TTS
(Deprecated feature. Previous usage was: "Prepares a tablespace to be transported to another database, so that spatial indexes will be preserved during the transport operation.")
- table_space
-
Tablespace to be transported. Must be the tablespace containing the spatial table or tables. For a partitioned table, must be the tablespace of one of the partitions.
Effective with Oracle Database Release 11.2, this procedure is deprecated. You do not need to call the PREPARE_FOR_TTS procedure before performing a transportable tablespace export operation.
For detailed information about transportable tablespaces and transporting tablespaces to other databases, see Oracle Database Administrator's Guide.
SDO_UTIL.RECTIFY_GEOMETRY
SDO_UTIL.RECTIFY_GEOMETRY(
geometry IN SDO_GEOMETRY,
tolerance IN NUMBER
) RETURN SDO_GEOMETRY;
- geometry
-
Geometry to be checked for problems that can be fixed.
- tolerance
-
Tolerance value (see Section 1.5.5).
This function checks for the following problems that can make a geometry invalid, and fixes the problems in the returned geometry:
-
Duplicate vertices
-
Polygon boundary intersecting itself
-
Incorrect orientation of exterior or interior rings (or both) of a polygon
If the input geometry has any other problem that makes it invalid, the function raises an exception.
If the input geometry is valid, the function returns a geometry that is identical to the input geometry.
For information about using this function as part of the recommended procedure for loading and validating spatial data, see Section 4.3.
This function is used internally by the SDO_UTIL.SIMPLIFY function as part of the geometry simplification process.
The following example checks the cola_b geometry to see if it has problems that can be fixed. (In this case, the geometry is valid, so the input geometry is returned. The example uses the definitions and data from Section 2.1.)
SELECT SDO_UTIL.RECTIFY_GEOMETRY(shape, 0.005) FROM COLA_MARKETS c WHERE c.name = 'cola_b'; SDO_UTIL.RECTIFY_GEOMETRY(SHAPE,0.005)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), -------------------------------------------------------------------------------- SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(5, 1, 8, 1, 8, 6, 5, 7, 5, 1))
SDO_UTIL.REMOVE_DUPLICATE_VERTICES
SDO_UTIL.REMOVE_DUPLICATE_VERTICES
geometry IN SDO_GEOMETRY,
tolerance IN NUMBER
) RETURN SDO_GEOMETRY;
- geometry
-
Geometry from which to remove duplicate vertices.
- tolerance
-
Tolerance value (see Section 1.5.5).
When two consecutive vertices in a geometry are the same or within the tolerance value associated with the geometry, Spatial considers the geometry to be invalid. The Spatial geometry validation functions return the error ORA-13356 in these cases. You can use the REMOVE_DUPLICATE_VERTICES function to change such invalid geometries into valid geometries.
This function also closes polygons so that the first vertex of the ring is the same as the last vertex of the ring.
This function is not supported for any point geometries (including oriented points).
If the input geometry does not contain any duplicate vertices, it is returned.
The following example removes a duplicate vertex from the input geometry, which is the same geometry as cola_b (see Figure 2-1 and Example 2-1 in Section 2.1) except that it has been deliberately made invalid by adding a third vertex that is the same point as the second vertex (8,1).
SELECT SDO_UTIL.REMOVE_DUPLICATE_VERTICES(
SDO_GEOMETRY(
2003, -- two-dimensional polygon
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
SDO_ORDINATE_ARRAY(5,1, 8,1, 8,1, 8,6, 5,7, 5,1) -- 2nd and 3rd points
-- are duplicates.
),
0.005 -- tolerance value
) FROM DUAL;
SDO_UTIL.REMOVE_DUPLICATE_VERTICES(SDO_GEOMETRY(2003,--TWO-DIMENSIONALPOLYGONNUL
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(5, 1, 8, 1, 8, 6, 5, 7, 5, 1))
SDO_UTIL.REVERSE_LINESTRING
Returns a line string geometry with the vertices of the input geometry in reverse order.
- geometry
-
Line string geometry whose vertices are to be reversed in the output geometry. The SDO_GTYPE value of the input geometry must be 2002. (Section 2.2.1 explains SDO_GTYPE values.)
Because the SDO_GTYPE value of the input geometry must be 2002, this function cannot be used to reverse LRS geometries. To reverse an LRS geometry, use the SDO_LRS.REVERSE_GEOMETRY function, which is described in Chapter 25.
The following example returns a line string geometry that reverses the vertices of the input geometry.
SELECT SDO_UTIL.REVERSE_LINESTRING(
SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),
SDO_ORDINATE_ARRAY(-72,43, -71.5,43.5, -71,42, -70,40))
) FROM DUAL;
SDO_UTIL.REVERSE_LINESTRING(SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,
--------------------------------------------------------------------------------
SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
-70, 40, -71, 42, -71.5, 43.5, -72, 43))
SDO_UTIL.SIMPLIFY
SDO_UTIL.SIMPLIFY(
geometry IN SDO_GEOMETRY,
threshold IN NUMBER
tolerance IN NUMBER DEFAULT 0.0000005
) RETURN SDO_GEOMETRY;
Simplifies the input geometry, based on a threshold value, using the Douglas-Peucker algorithm.
- geometry
-
Geometry to be simplified.
- threshold
-
Threshold value to be used for the geometry simplification. Should be a positive number. (Zero causes the input geometry to be returned.) If the input geometry is geodetic, the value is the number of meters; if the input geometry is non-geodetic, the value is the number of units associated with the data.
As the threshold value is decreased, the returned geometry is likely to be closer to the input geometry; as the threshold value is increased, fewer points are likely to be in the returned geometry. See the Usage Notes for more information.
- tolerance
-
Tolerance value (see Section 1.5.5). Must not be greater than
threshold; and for better performance, should not be the same asthreshold. If you do not specify a value, the default value is 0.0000005.
This function also converts arcs to line stings, eliminates duplicate vertices, and corrects many overlapping edge polygon problems. The reason this function sometimes fixes problems is that it internally calls the SDO_UTIL.RECTIFY_GEOMETRY function at the end of the simplification process to ensure that a valid geometry is returned.
This function is useful when you want a geometry with less fine resolution than the original geometry. For example, if the display resolution cannot show the hundreds or thousands of turns in the course of a river or in a political boundary, better performance might result if the geometry were simplified to show only the major turns.
If you use this function with geometries that have more than two dimensions, only the first two dimensions are used in processing the query, and only the first two dimensions in the returned geometry are to be considered valid and meaningful. For example, the measure values in a returned LRS geometry will probably not reflect actual measures in that geometry. In this case, depending on your application needs, you might have several options after the simplification operation, such as ignoring the new measure values or redefining the new LRS geometry to reset the measure values.
This function uses the Douglas-Peucker algorithm, which is explained in several cartography textbooks and reference documents. (In some explanations, the term tolerance is used instead of threshold; however, this is different from the Oracle Spatial meaning of tolerance.)
The returned geometry can be a polygon, line, or point, depending on the geometry definition and the threshold value. The following considerations apply:
-
A polygon can simplify to a line or a point and a line can simplify to a point, if the threshold value associated with the geometry is sufficiently large. For example, a thin rectangle will simplify to a line if the distance between the two parallel long sides is less than the threshold value, and a line will simplify to a point if the distance between the start and end points is less than the threshold value.
-
In a polygon with a hole, if the exterior ring or the interior ring (the hole) simplifies to a line or a point, the interior ring disappears from (is not included in) the resulting geometry.
-
Topological characteristics of the input geometry might not be maintained after simplification. For a collection geometry, the number of elements might increase, to prevent overlapping of individual elements. In all cases, this function will not return an invalid geometry.
The following example simplifies the road shown in Figure 7-20 in Section 7.7. Because the threshold value (6) is fairly large given the input geometry, the resulting LRS line string has only three points: the start and end points, and (12, 4,12). The measure values in the returned geometry are not meaningful, because this function considers only two dimensions.
SELECT SDO_UTIL.SIMPLIFY(
SDO_GEOMETRY(
3302, -- line string, 3 dimensions (X,Y,M), 3rd is linear ref. dimension
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,2,1), -- one line string, straight segments
SDO_ORDINATE_ARRAY(
2,2,0, -- Starting point - Exit1; 0 is measure from start.
2,4,2, -- Exit2; 2 is measure from start.
8,4,8, -- Exit3; 8 is measure from start.
12,4,12, -- Exit4; 12 is measure from start.
12,10,NULL, -- Not an exit; measure automatically calculated and filled.
8,10,22, -- Exit5; 22 is measure from start.
5,14,27) -- Ending point (Exit6); 27 is measure from start.
),
6, -- threshold value for geometry simplification
0.5 -- tolerance
) FROM DUAL;
SDO_UTIL.SIMPLIFY(SDO_GEOMETRY(3302,--LINESTRING,3DIMENSIONS(X,Y,M),3RDISLINEARR
--------------------------------------------------------------------------------
SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
2, 2, 0, 12, 4, 12, 5, 14, 27))
Figure 32-1 shows the result of this example. In Figure 32-1, the thick solid black line is the resulting geometry, the thin solid light line between the start and end points is the input geometry, and the thin dashed line with the arrowhead at the end shows the direction of the segment.
SDO_UTIL.TO_GML311GEOMETRY
Converts a Spatial geometry object to a geography markup language (GML version 3.1.1) fragment based on the geometry types defined in the Open GIS geometry.xsd schema document.
This function does not convert circles, geometries containing any circular arcs, LRS geometries, or geometries with an SDO_ETYPE value of 0 (type 0 elements); it returns an empty CLOB in these cases.
This function converts the input geometry to a GML version 3.1.1 fragment based on some GML geometry types defined in the Open GIS Implementation Specification.
Polygons must be defined using the conventions for Oracle9i and later releases of Spatial. That is, the outer boundary is stored first (with ETYPE=1003) followed by zero or more inner boundary elements (ETYPE=2003). For a polygon with holes, the outer boundary must be stored first in the SDO_ORDINATES definition, followed by coordinates of the inner boundaries.
LRS geometries must be converted to standard geometries (using the SDO_LRS.CONVERT_TO_STD_GEOM or SDO_LRS.CONVERT_TO_STD_LAYER function) before being passed to the TO_GMLGEOMETRY function. (See the Examples section for an example that uses CONVERT_TO_STD_GEOM with the TO_GMLGEOMETRY function.)
Any circular arcs or circles must be densified (using the SDO_GEOM.SDO_ARC_DENSIFY function) or represented as polygons (using the SDO_GEOM.SDO_BUFFER function) before being passed to the TO_GMLGEOMETRY function. (See the Examples section for an example that uses SDO_ARC_DENSIFY with the TO_GMLGEOMETRY function.)
Label points are discarded. That is, if a geometry has a value for the SDO_POINT field and values in SDO_ELEM_INFO and SDO_ORDINATES, the SDO_POINT is not output in the GML fragment.
The SDO_SRID value is output in the form srsName="SDO:<srid>". For example, "SDO:8307" indicates SDO_SRID 8307, and "SDO:" indicates a null SDO_SRID value. No checks are made for the validity or consistency of the SDO_SRID value. For example, the value is not checked to see if it exists in the MDSYS.CS_SRS table or if it conflicts with the SRID value for the layer in the USER_SDO_GEOM_METADATA view.
Coordinates are always output using the <coordinates> tag and decimal='.', cs=',' (that is, with the comma as the coordinate separator), and ts=' ' (that is, with a space as the tuple separator), even if the NLS_NUMERIC_CHARACTERS setting has ',' (comma) as the decimal character.
The GML output is not formatted; there are no line breaks or indentation of tags. To see the contents of the returned CLOB in SQL*Plus, use the TO_CHAR() function or set the SQL*Plus parameter LONG to a suitable value (for example, SET LONG 40000). To get formatted GML output or to use the return value of TO_GMLGEOMETRY in SQLX or Oracle XML DB functions such as XMLELEMENT, use the XMLTYPE(clobval CLOB) constructor.
The following example returns the GML version 3.1.1 fragment for the cola_b geometry in the COLA_MARKETS table. (The example uses the definitions and data from Section 2.1.)
-- Convert cola_b geometry to GML 3.1.1 fragment. SELECT TO_CHAR(SDO_UTIL.TO_GML311GEOMETRY(shape)) AS Gml311Geometry FROM COLA_MARKETS c WHERE c.name = 'cola_b'; GML311GEOMETRY -------------------------------------------------------------------------------- <gml:Polygon srsName="SDO:" xmlns:gml="http://www.opengis.net/gml"><gml:exterior ><gml:LinearRing><gml:posList srsDimension="2">5.0 1.0 8.0 1.0 8.0 6.0 5.0 7.0 5 .0 1.0 </gml:posList></gml:LinearRing></gml:exterior></gml:Polygon>
The following example returns the GML version 3.1.1 fragment for the arc densification of the cola_d geometry in the COLA_MARKETS table. (The example uses the definitions and data from Section 2.1.)
SET LONG 40000
SELECT XMLTYPE(SDO_UTIL.TO_GML311GEOMETRY(
SDO_GEOM.SDO_ARC_DENSIFY(c.shape, m.diminfo, 'arc_tolerance=0.05')))
AS Gml311Geometry 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';
GML311GEOMETRY
--------------------------------------------------------------------------------
<gml:Polygon srsName="SDO:" xmlns:gml="http://www.opengis.net/gml">
<gml:exterior>
<gml:LinearRing>
<gml:posList srsDimension="2">8.0 7.0 8.76536686473018 7.15224093497743 9.
4142135623731 7.58578643762691 9.84775906502257 8.23463313526982 10.0 9.0 9.8477
5906502257 9.76536686473018 9.4142135623731 10.4142135623731 8.76536686473018 10
.8477590650226 8.0 11.0 7.23463313526982 10.8477590650226 6.58578643762691 10.41
42135623731 6.15224093497743 9.76536686473018 6.0 9.0 6.15224093497743 8.2346331
3526982 6.58578643762691 7.5857864376269 7.23463313526982 7.15224093497743 8.0 7
.0 </gml:posList>
</gml:LinearRing>
</gml:exterior>
</gml:Polygon>
The following example converts an LRS geometry to a standard geometry and returns the GML version 3.1.1 fragment for the geometry. (The example uses the definitions and data from Section 7.7.)
SET LONG 40000
-- Convert LRS grometry to standard geometry before using TO_GML311GEOMETRY.
SELECT XMLTYPE(SDO_UTIL.TO_GML311GEOMETRY(
SDO_LRS.CONVERT_TO_STD_GEOM(route_geometry)))
AS Gml311Geometry FROM lrs_routes a WHERE a.route_id = 1;
GML311GEOMETRY
--------------------------------------------------------------------------------
<gml:Curve srsName="SDO:" xmlns:gml="http://www.opengis.net/gml">
<gml:segments>
<gml:LineStringSegment>
<gml:posList srsDimension="2">2.0 2.0 2.0 4.0 8.0 4.0 12.0 4.0 12.0 10.0 8
.0 10.0 5.0 14.0 </gml:posList>
</gml:LineStringSegment>
</gml:segments>
</gml:Curve>
The following examples return GML version 3.1.1 fragments for a variety of geometry types.
-- Point geometry with coordinates in SDO_ORDINATES. Note the
-- coordinates in the GML are (10.0 10.0) and the values in the
-- SDO_POINT field are discarded.
SELECT TO_CHAR(
SDO_UTIL.TO_GML311GEOMETRY(sdo_geometry(2001, 8307,
sdo_point_type(-80, 70, null),
sdo_elem_info_array(1,1,1), sdo_ordinate_array(10, 10)))
)
AS Gml311Geometry FROM DUAL;
GML311GEOMETRY
--------------------------------------------------------------------------------
<gml:Point srsName="SDO:8307" xmlns:gml="http://www.opengis.net/gml"><gml:posLis
t srsDimension="2">10.0 10.0 </gml:posList></gml:Point>
-- Multipolygon
SET LONG 40000
SELECT SDO_UTIL.TO_GML311GEOMETRY(
sdo_geometry(2007, 8307, null,
sdo_elem_info_array(1,1003,1, 13,1003,1, 23,1003,3),
sdo_ordinate_array(10.10,10.20, 20.50,20.10, 30.30,30.30, 40.10,40.10,
30.50, 30.20, 10.10, 10.20,
5,5, 5,6, 6,6, 6,5, 5,5, 7,7, 8,8 ))
)
AS Gml311Geometry FROM DUAL;
GML311GEOMETRY
--------------------------------------------------------------------------------
<gml:MultiSurface srsName="SDO:8307" xmlns:gml="http://www.opengis.net/gml"><gml
:surfaceMember><gml:Polygon><gml:exterior><gml:LinearRing><gml:posList srsDimens
ion="2">10.1 10.2 20.5 20.1 30.3 30.3 40.1 40.1 30.5 30.2 10.1 10.2 </gml:posLis
t></gml:LinearRing></gml:exterior></gml:Polygon></gml:surfaceMember><gml:surface
Member><gml:Polygon><gml:exterior><gml:LinearRing><gml:posList srsDimension="2">
5.0 5.0 5.0 6.0 6.0 6.0 6.0 5.0 5.0 5.0 </gml:posList></gml:LinearRing></gml:ext
erior></gml:Polygon></gml:surfaceMember><gml:surfaceMember><gml:Polygon><gml:ext
erior><gml:LinearRing><gml:posList srsDimension="2">7.0 7.0 8.0 7.0 8.0 8.0 7.0
8.0 7.0 7.0 </gml:posList></gml:LinearRing></gml:exterior></gml:Polygon></gml:su
rfaceMember></gml:MultiSurface>
SET LONG 80
-- Rectangle (geodetic)
SELECT TO_CHAR(
SDO_UTIL.TO_GML311GEOMETRY(sdo_geometry(2003, 8307, null,
sdo_elem_info_array(1,1003,3),
sdo_ordinate_array(10.10,10.10, 20.10,20.10 )))
)
AS Gml311Geometry FROM DUAL;
GML311GEOMETRY
--------------------------------------------------------------------------------
<gml:Polygon srsName="SDO:8307" xmlns:gml="http://www.opengis.net/gml"><gml:exte
rior><gml:LinearRing><gml:posList srsDimension="2">10.1 10.1 20.1 10.1 20.1 20.1
10.1 20.1 10.1 10.1 </gml:posList></gml:LinearRing></gml:exterior></gml:Polygon
>
-- Polygon with holes
SELECT TO_CHAR(
SDO_UTIL.TO_GML311GEOMETRY(sdo_geometry(2003, 262152, null,
sdo_elem_info_array(1,1003,3, 5, 2003, 1, 13, 2003, 1),
sdo_ordinate_array(10.10,10.20, 40.50, 41.10, 30.30, 30.30, 30.30,
40.10, 40.10, 40.10, 30.30, 30.30, 5, 5, 5, 6, 6, 6, 6, 5, 5, 5 )))
)
AS Gml311Geometry FROM DUAL;
GML311GEOMETRY
--------------------------------------------------------------------------------
<gml:Polygon srsName="SDO:262152" xmlns:gml="http://www.opengis.net/gml"><gml:ex
terior><gml:LinearRing><gml:posList srsDimension="2">10.1 10.2 40.5 10.2 40.5 41
.1 10.1 41.1 10.1 10.2 </gml:posList></gml:LinearRing></gml:exterior><gml:interi
or><gml:LinearRing><gml:posList srsDimension="2">30.3 30.3 30.3 40.1 40.1 40.1 3
0.3 30.3 </gml:posList></gml:LinearRing></gml:interior><gml:interior><gml:Linear
Ring><gml:posList srsDimension="2">5.0 5.0 5.0 6.0 6.0 6.0 6.0 5.0 5.0 5.0 </gml
:posList></gml:LinearRing></gml:interior></gml:Polygon>
-- Creating an XMLTYPE from the GML fragment. Also useful for "pretty
-- printing" the GML output.
SET LONG 40000
SELECT XMLTYPE(
SDO_UTIL.TO_GML311GEOMETRY(sdo_geometry(2003, 262152, null,
sdo_elem_info_array(1,1003,1, 11, 2003, 1, 21, 2003, 1),
sdo_ordinate_array(10.10,10.20, 40.50,10.2, 40.5,41.10, 10.1,41.1,
10.10, 10.20, 30.30,30.30, 30.30, 40.10, 40.10, 40.10, 40.10, 30.30,
30.30, 30.30, 5, 5, 5, 6, 6, 6, 6, 5, 5, 5 )))
)
AS Gml311Geometry FROM DUAL;
GML311GEOMETRY
--------------------------------------------------------------------------------
<gml:Polygon srsName="SDO:262152" xmlns:gml="http://www.opengis.net/gml">
<gml:exterior>
<gml:LinearRing>
<gml:posList srsDimension="2">10.1 10.2 40.5 10.2 40.5 41.1 10.1 41.1 10.1
10.2 </gml:posList>
</gml:LinearRing>
</gml:exterior>
<gml:interior>
<gml:LinearRing>
<gml:posList srsDimension="2">30.3 30.3 30.3 40.1 40.1 40.1 40.1 30.3 30.3
30.3 </gml:posList>
GML311GEOMETRY
--------------------------------------------------------------------------------
</gml:LinearRing>
</gml:interior>
<gml:interior>
<gml:LinearRing>
<gml:posList srsDimension="2">5.0 5.0 5.0 6.0 6.0 6.0 6.0 5.0 5.0 5.0 </gm
l:posList>
</gml:LinearRing>
</gml:interior>
</gml:Polygon>
SDO_UTIL.TO_GMLGEOMETRY
Converts a Spatial geometry object to a geography markup language (GML 2.0) fragment based on the geometry types defined in the Open GIS geometry.xsd schema document.
This function does not convert circles, geometries containing any circular arcs, LRS geometries, or geometries with an SDO_ETYPE value of 0 (type 0 elements); it returns an empty CLOB in these cases.
This function converts the input geometry to a GML fragment based on some GML geometry types defined in the Open GIS Implementation Specification.
Polygons must be defined using the conventions for Oracle9i and later releases of Spatial. That is, the outer boundary is stored first (with ETYPE=1003) followed by zero or more inner boundary elements (ETYPE=2003). For a polygon with holes, the outer boundary must be stored first in the SDO_ORDINATES definition, followed by coordinates of the inner boundaries.
LRS geometries must be converted to standard geometries (using the SDO_LRS.CONVERT_TO_STD_GEOM or SDO_LRS.CONVERT_TO_STD_LAYER function) before being passed to the TO_GMLGEOMETRY function. (See the Examples section for an example that uses CONVERT_TO_STD_GEOM with the TO_GMLGEOMETRY function.)
Any circular arcs or circles must be densified (using the SDO_GEOM.SDO_ARC_DENSIFY function) or represented as polygons (using the SDO_GEOM.SDO_BUFFER function) before being passed to the TO_GMLGEOMETRY function. (See the Examples section for an example that uses SDO_ARC_DENSIFY with the TO_GMLGEOMETRY function.)
Label points are discarded. That is, if a geometry has a value for the SDO_POINT field and values in SDO_ELEM_INFO and SDO_ORDINATES, the SDO_POINT is not output in the GML fragment.
The SDO_SRID value is output in the form srsName="SDO:<srid>". For example, "SDO:8307" indicates SDO_SRID 8307, and "SDO:" indicates a null SDO_SRID value. No checks are made for the validity or consistency of the SDO_SRID value. For example, the value is not checked to see if it exists in the MDSYS.CS_SRS table or if it conflicts with the SRID value for the layer in the USER_SDO_GEOM_METADATA view.
Coordinates are always output using the <coordinates> tag and decimal='.', cs=',' (that is, with the comma as the coordinate separator), and ts=' ' (that is, with a space as the tuple separator), even if the NLS_NUMERIC_CHARACTERS setting has ',' (comma) as the decimal character.
The GML output is not formatted; there are no line breaks or indentation of tags. To see the contents of the returned CLOB in SQL*Plus, use the TO_CHAR() function or set the SQL*Plus parameter LONG to a suitable value (for example, SET LONG 40000). To get formatted GML output or to use the return value of TO_GMLGEOMETRY in SQLX or Oracle XML DB functions such as XMLELEMENT, use the XMLTYPE(clobval CLOB) constructor.
The following example returns the GML fragment for the cola_b geometry in the COLA_MARKETS table. (The example uses the definitions and data from Section 2.1.)
-- Convert cola_b geometry to GML fragment. SELECT TO_CHAR(SDO_UTIL.TO_GMLGEOMETRY(shape)) AS GmlGeometry FROM COLA_MARKETS c WHERE c.name = 'cola_b'; GMLGEOMETRY -------------------------------------------------------------------------------- <gml:Polygon srsName="SDO:" xmlns:gml="http://www.opengis.net/gml"><gml:outerBou ndaryIs><gml:LinearRing><gml:coordinates decimal="." cs="," ts=" ">5,1 8,1 8,6 5 ,7 5,1 </gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Polygon>
The following example returns the GML fragment for the arc densification of the cola_d geometry in the COLA_MARKETS table. (The example uses the definitions and data from Section 2.1.)
SET LONG 40000
SELECT XMLTYPE(SDO_UTIL.TO_GMLGEOMETRY(
SDO_GEOM.SDO_ARC_DENSIFY(c.shape, m.diminfo, 'arc_tolerance=0.05')))
AS GmlGeometry 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';
GMLGEOMETRY
--------------------------------------------------------------------------------
<gml:Polygon srsName="SDO:" xmlns:gml="http://www.opengis.net/gml"><gml:outerBou
ndaryIs><gml:LinearRing><gml:coordinates decimal="." cs="," ts=" ">8,7 8.7653668
6473018,7.15224093497743 9.4142135623731,7.58578643762691 9.84775906502257,8.234
63313526982 10,9 9.84775906502257,9.76536686473018 9.4142135623731,10.4142135623
731 8.76536686473018,10.8477590650226 8,11 7.23463313526982,10.8477590650226 6.5
8578643762691,10.4142135623731 6.15224093497743,9.76536686473018 6,9 6.152240934
97743,8.23463313526982 6.58578643762691,7.5857864376269 7.23463313526982,7.15224
093497743 8,7 </gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Pol
ygon>
The following example converts an LRS geometry to a standard geometry and returns the GML fragment for the geometry. (The example uses the definitions and data from Section 7.7.)
SET LONG 40000 -- Convert LRS geometry to standard geometry before using TO_GMLGEOMETRY. SELECT XMLTYPE(SDO_UTIL.TO_GMLGEOMETRY( SDO_LRS.CONVERT_TO_STD_GEOM(route_geometry))) AS GmlGeometry FROM lrs_routes a WHERE a.route_id = 1; GMLGEOMETRY -------------------------------------------------------------------------------- <gml:LineString srsName="SDO:" xmlns:gml="http://www.opengis.net/gml"> <gml:coordinates decimal="." cs="," ts=" ">2,2 2,4 8,4 12,4 12,10 8,10 5,14 </ gml:coordinates> </gml:LineString>
The following examples return GML fragments for a variety of geometry types.
-- Point geometry with coordinates in SDO_ORDINATES. Note the
-- coordinates in the GML are (10,10) and the values in the
-- SDO_POINT field are discarded.
SELECT TO_CHAR(
SDO_UTIL.TO_GMLGEOMETRY(sdo_geometry(2001, 8307,
sdo_point_type(-80, 70, null),
sdo_elem_info_array(1,1,1), sdo_ordinate_array(10, 10)))
)
AS GmlGeometry FROM DUAL;
GMLGEOMETRY
--------------------------------------------------------------------------------
<gml:Point srsName="SDO:8307" xmlns:gml="http://www.opengis.net/gml"><gml:coordi
nates decimal="." cs="," ts=" ">10,10 </gml:coordinates></gml:Point>
-- Multipolygon
SET LONG 40000
SELECT SDO_UTIL.TO_GMLGEOMETRY(
sdo_geometry(2007, 8307, null,
sdo_elem_info_array(1,1003,1, 13,1003,1, 23,1003,3),
sdo_ordinate_array(10.10,10.20, 20.50,20.10, 30.30,30.30, 40.10,40.10,
30.50, 30.20, 10.10, 10.20,
5,5, 5,6, 6,6, 6,5, 5,5, 7,7, 8,8 ))
)
AS GmlGeometry FROM DUAL;
GMLGEOMETRY
--------------------------------------------------------------------------------
<gml:MultiPolygon srsName="SDO:8307" xmlns:gml="http://www.opengis.net/gml"><gml
:polygonMember><gml:Polygon><gml:outerBoundaryIs><gml:LinearRing><gml:coordinate
s decimal="." cs="," ts=" ">10.1,10.2 20.5,20.1 30.3,30.3 40.1,40.1 30.5,30.2 10
.1,10.2 </gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Polygon><
/gml:polygonMember><gml:polygonMember><gml:Polygon><gml:outerBoundaryIs><gml:Lin
earRing><gml:coordinates decimal="." cs="," ts=" ">5.0,5.0 5.0,6.0 6.0,6.0 6.0,5
.0 5.0,5.0 </gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Polygo
n></gml:polygonMember><gml:polygonMember><gml:Polygon><gml:outerBoundaryIs><gml:
LinearRing><gml:coordinates decimal="." cs="," ts=" ">7.0,7.0 8.0,7.0 8.0,8.0 7.
0,8.0 7.0,7.0 </gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Pol
ygon></gml:polygonMember></gml:MultiPolygon>
SQL> SET LONG 80
-- Rectangle (geodetic)
SELECT TO_CHAR(
SDO_UTIL.TO_GMLGEOMETRY(sdo_geometry(2003, 8307, null,
sdo_elem_info_array(1,1003,3),
sdo_ordinate_array(10.10,10.10, 20.10,20.10 )))
)
AS GmlGeometry FROM DUAL;
GMLGEOMETRY
--------------------------------------------------------------------------------
<gml:Box srsName="SDO:8307" xmlns:gml="http://www.opengis.net/gml"><gml:coordina
tes decimal="." cs="," ts=" ">10.1,10.1 20.1,20.1 </gml:coordinates></gml:Box>
-- Polygon with holes
SELECT TO_CHAR(
SDO_UTIL.TO_GMLGEOMETRY(sdo_geometry(2003, 262152, null,
sdo_elem_info_array(1,1003,3, 5, 2003, 1, 13, 2003, 1),
sdo_ordinate_array(10.10,10.20, 40.50, 41.10, 30.30, 30.30, 30.30,
40.10, 40.10, 40.10, 30.30, 30.30, 5, 5, 5, 6, 6, 6, 6, 5, 5, 5 )))
)
AS GmlGeometry FROM DUAL;
GMLGEOMETRY
--------------------------------------------------------------------------------
<gml:Polygon srsName="SDO:262152" xmlns:gml="http://www.opengis.net/gml"><gml:ou
terBoundaryIs><gml:LinearRing><gml:coordinates decimal="." cs="," ts=" ">10.1,10
.2, 40.5,10.2, 40.5,41.1, 10.1,41.1, 10.1,10.2 </gml:coordinates></gml:LinearRin
g></gml:outerBoundaryIs><gml:innerBoundaryIs><gml:LinearRing><gml:coordinates de
cimal="." cs="," ts=" ">30.3,30.3 30.3,40.1 40.1,40.1 30.3,30.3 </gml:coordinate
s></gml:LinearRing></gml:innerBoundaryIs><gml:innerBoundaryIs><gml:LinearRing><g
ml:coordinates decimal="." cs="," ts=" ">5,5 5,6 6,6 6,5 5,5 </gml:coordinates><
/gml:LinearRing></gml:innerBoundaryIs></gml:Polygon>
-- Creating an XMLTYPE from the GML fragment. Also useful for "pretty
-- printing" the GML output.
SET LONG 40000
SELECT XMLTYPE(
SDO_UTIL.TO_GMLGEOMETRY(sdo_geometry(2003, 262152, null,
sdo_elem_info_array(1,1003,1, 11, 2003, 1, 21, 2003, 1),
sdo_ordinate_array(10.10,10.20, 40.50,10.2, 40.5,41.10, 10.1,41.1,
10.10, 10.20, 30.30,30.30, 30.30, 40.10, 40.10, 40.10, 40.10, 30.30,
30.30, 30.30, 5, 5, 5, 6, 6, 6, 6, 5, 5, 5 )))
)
AS GmlGeometry FROM DUAL;
GMLGEOMETRY
--------------------------------------------------------------------------------
<gml:Polygon srsName="SDO:262152" xmlns:gml="http://www.opengis.net/gml"><gml:ou
terBoundaryIs><gml:LinearRing><gml:coordinates decimal="." cs="," ts=" ">10.1,10
.2 40.5,10.2 40.5,41.1 10.1,41.1 10.1,10.2 </gml:coordinates></gml:LinearRing></
gml:outerBoundaryIs><gml:innerBoundaryIs><gml:LinearRing><gml:coordinates decima
l="." cs="," ts=" ">30.3,30.3 30.3,40.1 40.1,40.1 40.1,30.3 30.3,30.3 </gml:coor
dinates></gml:LinearRing></gml:innerBoundaryIs><gml:innerBoundaryIs><gml:LinearR
ing><gml:coordinates decimal="." cs="," ts=" ">5,5 5,6 6,6 6,5 5,5 </gml:coordin
ates></gml:LinearRing></gml:innerBoundaryIs></gml:Polygon>
The following example uses the TO_GMLGEOMETRY function with the Oracle XML DB XMLTYPE data type and the XMLELEMENT and XMLFOREST functions.
SELECT xmlelement("State", xmlattributes(
'http://www.opengis.net/gml' as "xmlns:gml"),
xmlforest(state as "Name", totpop as "Population",
xmltype(sdo_util.to_gmlgeometry(geom)) as "gml:geometryProperty"))
AS theXMLElements FROM states WHERE state_abrv in ('DE', 'UT');
THEXMLELEMENTS
--------------------------------------------------------------------------------
<State xmlns:gml="http://www.opengis.net/gml">
<Name>Delaware</Name>
<Population>666168</Population>
<gml:geometryProperty>
<gml:Polygon srsName="SDO:" xmlns:gml="http://www.opengis.net/gml">
<gml:outerBoundaryIs>
<gml:LinearRing>
<gml:coordinates decimal="." cs="," ts=" ">-75.788704,39.721699 -75.78
8704,39.6479 -75.767014,39.377106 -75.76033,39.296497 -75.756294,39.24585 -75.74
8016,39.143196 -75.722961,38.829895 -75.707695,38.635166 -75.701912,38.560619 -7
5.693871,38.460011 -75.500336,38.454002 -75.341614,38.451855 -75.049339,38.45165
3 -75.053841,38.538429 -75.06015,38.605465 -75.063263,38.611275 -75.065308,38.62
949 -75.065887,38.660919 -75.078697,38.732403 -75.082527,38.772045 -75.091667,38
.801208 -75.094185,38.803699 -75.097572,38.802986 -75.094116,38.793579 -75.09926
6,38.78756 -75.123619,38.781784 -75.137962,38.782703 -75.18692,38.803772 -75.215
019,38.831547 -75.23735,38.849014 -75.260498,38.875 -75.305908,38.914673 -75.316
399,38.930309 -75.317284,38.93676 -75.312851,38.945576 -75.312859,38.945618 -75.
31205,38.967804 -75.31778,38.986012 -75.341431,39.021233 -75.369606,39.041359 -7
5.389229,39.051422 -75.40181,39.06702 -75.401306,39.097713 -75.411369,39.148029
-75.407845,39.175201 -75.396271,39.187778 -75.39225,39.203377 -75.40181,39.23104
9 -75.402817,39.253189 -75.409355,39.264759 -75.434006,39.290424 -75.439041,39.3
13065 -75.453125,39.317093 -75.457657,39.326653 -75.469231,39.330677 -75.486336,
39.341743 -75.494888,39.354324 -75.504448,39.357346 -75.51284,39.366291 -75.5129
24,39.366482 -75.523773,39.392052 -75.538651,39.415707 -75.56749,39.436436 -75.5
9137,39.463696 -75.592941,39.471806 -75.590019,39.488026 -75.587311,39.496136 -7
5.5774,39.508076 -75.554192,39.506947 -75.528442,39.498005 -75.530373,39.510303
-75.527145,39.531326 -75.52803,39.535168 -75.53437,39.540592 -75.519386,39.55528
6 -75.512291,39.567505 -75.515587,39.580639 -75.528046,39.584 -75.538269,39.5935
67 -75.554016,39.601727 -75.560143,39.622578 -75.556602,39.6348 -75.549599,39.63
7699 -75.542397,39.645901 -75.535507,39.647099 -75.514999,39.668499 -75.507523,3
9.69685 -75.496597,39.701302 -75.488914,39.714722 -75.477997,39.714901 -75.47550
2,39.733501 -75.467972,39.746975 -75.463707,39.761101 -75.448494,39.773857 -75.4
38301,39.783298 -75.405701,39.796101 -75.415405,39.801678 -75.454102,39.820202 -
75.499199,39.833199 -75.539703,39.8381 -75.5802,39.838417 -75.594017,39.837345 -
75.596107,39.837044 -75.639488,39.82893 -75.680145,39.813839 -75.71096,39.796352
-75.739716,39.772881 -75.760689,39.74712 -75.774101,39.721699 -75.788704,39.721
699 </gml:coordinates>
</gml:LinearRing>
</gml:outerBoundaryIs>
</gml:Polygon>
</gml:geometryProperty>
</State>
<State xmlns:gml="http://www.opengis.net/gml">
<Name>Utah</Name>
<Population>1722850</Population>
<gml:geometryProperty>
<gml:Polygon srsName="SDO:" xmlns:gml="http://www.opengis.net/gml">
<gml:outerBoundaryIs>
<gml:LinearRing>
<gml:coordinates decimal="." cs="," ts=" ">-114.040871,41.993805 -114.
038803,41.884899 -114.041306,41 -114.04586,40.116997 -114.046295,39.906101 -114.
046898,39.542801 -114.049026,38.67741 -114.049339,38.572968 -114.049095,38.14864
-114.0476,37.80946 -114.05098,37.746284 -114.051666,37.604805 -114.052025,37.10
3989 -114.049797,37.000423 -113.484375,37 -112.898598,37.000401 -112.539604,37.0
00683 -112,37.000977 -111.412048,37.001514 -111.133018,37.00079 -110.75,37.00320
1 -110.5,37.004265 -110.469505,36.998001 -110,36.997967 -109.044571,36.999088 -1
09.045143,37.375 -109.042824,37.484692 -109.040848,37.881176 -109.041405,38.1530
27 -109.041107,38.1647 -109.059402,38.275501 -109.059296,38.5 -109.058868,38.719
906 -109.051765,39 -109.050095,39.366699 -109.050697,39.4977 -109.050499,39.6605
-109.050156,40.222694 -109.047577,40.653641 -109.0494,41.000702 -109.2313,41.00
2102 -109.534233,40.998184 -110,40.997398 -110.047768,40.997696 -110.5,40.994801
-111.045982,40.998013 -111.045815,41.251774 -111.045097,41.579899 -111.045944,4
2.001633 -111.506493,41.999588 -112.108742,41.997677 -112.16317,41.996784 -112.1
72562,41.996643 -112.192184,42.001244 -113,41.998314 -113.875,41.988091 -114.040
871,41.993805 </gml:coordinates>
</gml:LinearRing>
</gml:outerBoundaryIs>
</gml:Polygon>
</gml:geometryProperty>
</State>
SDO_UTIL.TO_KMLGEOMETRY
This function does not convert circles, geometries containing any circular arcs, LRS geometries, or geometries with an SDO_ETYPE value of 0 (type 0 elements); it returns an empty CLOB in these cases.
Polygons must be defined using the conventions for Oracle9i and later releases of Spatial. That is, the outer boundary is stored first (with ETYPE=1003) followed by zero or more inner boundary elements (ETYPE=2003). For a polygon with holes, the outer boundary must be stored first in the SDO_ORDINATES definition, followed by coordinates of the inner boundaries.
LRS geometries must be converted to standard geometries (using the SDO_LRS.CONVERT_TO_STD_GEOM or SDO_LRS.CONVERT_TO_STD_LAYER function) before being passed to the TO_KMLGEOMETRY function.
Any circular arcs or circles must be densified (using the SDO_GEOM.SDO_ARC_DENSIFY function) or represented as polygons (using the SDO_GEOM.SDO_BUFFER function) before being passed to the TO_KMLGEOMETRY function.
Label points are discarded. That is, if a geometry has a value for the SDO_POINT field and values in SDO_ELEM_INFO and SDO_ORDINATES, the SDO_POINT is not output in the KML document.
Solid geometries are converted to KML MultiGeometry objects, because KML 2.1 does not support solids. If you then use the SDO_UTIL.FROM_KMLGEOMETRY function on the MultiGeometry, the result is not an Oracle Spatial solid geometry (that is, its SDO_GTYPE value does not reflect a geometry type of SOLID or MULTISOLID).
The KML output is not formatted; there are no line breaks or indentation of tags. To see the contents of the returned CLOB in SQL*Plus, use the TO_CHAR() function or set the SQL*Plus parameter LONG to a suitable value (for example, SET LONG 2000). To get formatted GML output or to use the return value of TO_KMLGEOMETRY in SQLX or Oracle XML DB functions such as XMLELEMENT, use the XMLTYPE(clobval CLOB) constructor.
The following example shows conversion to and from KML format. (The example uses the definitions and data from Section 2.1, specifically the cola_c geometry from the COLA_MARKETS table.)
-- Convert cola_c geometry to a KML document; convert that result to
-- a spatial geometry.
set long 2000;
DECLARE
kmlgeom CLOB;
val_result VARCHAR2(5);
geom_result SDO_GEOMETRY;
geom SDO_GEOMETRY;
BEGIN
SELECT c.shape INTO geom FROM cola_markets c WHERE c.name = 'cola_c';
-- To KML geometry
kmlgeom := SDO_UTIL.TO_KMLGEOMETRY(geom);
DBMS_OUTPUT.PUT_LINE('To KML geometry result = ' || TO_CHAR(kmlgeom));
-- From KML geometry
geom_result := SDO_UTIL.FROM_KMLGEOMETRY(kmlgeom);
-- Validate the returned geometry.
val_result := SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(geom_result, 0.005);
DBMS_OUTPUT.PUT_LINE('Validation result = ' || val_result);
END;
/
To KML geometry result =
<Polygon><extrude>0</extrude><tessellate>0</tessellate><altitudeMode>relativeToG
round</altitudeMode><outerBoundaryIs><LinearRing><coordinates>3.0,3.0 6.0,3.0
6.0,5.0 4.0,5.0 3.0,3.0 </coordinates></LinearRing></outerBoundaryIs></Polygon>
Validation result = TRUE
SDO_UTIL.TO_WKBGEOMETRY
The input geometry is converted to the well-known binary (WKB) format, as defined by the Open Geospatial Consortium and the International Organization for Standardization (ISO).
This function is patterned after the SQL Multimedia recommendations in ISO 13249-3, Information technology - Database languages - SQL Multimedia and Application Packages - Part 3: Spatial.
To convert a geometry in WKB format to an SDO_GEOMETRY object, use the SDO_UTIL.FROM_WKBGEOMETRY function.
The following example shows conversion to and from WKB and WKT format, and validation of WKB and WKT geometries. (The example uses the definitions and data from Section 2.1, specifically the cola_b geometry from the COLA_MARKETS table.)
DECLARE
wkbgeom BLOB;
wktgeom CLOB;
val_result VARCHAR2(5);
geom_result SDO_GEOMETRY;
geom SDO_GEOMETRY;
BEGIN
SELECT c.shape INTO geom FROM cola_markets c WHERE c.name = 'cola_b';
-- To WBT/WKT geometry
wkbgeom := SDO_UTIL.TO_WKBGEOMETRY(geom);
wktgeom := SDO_UTIL.TO_WKTGEOMETRY(geom);
DBMS_OUTPUT.PUT_LINE('To WKT geometry result = ' || TO_CHAR(wktgeom));
-- From WBT/WKT geometry
geom_result := SDO_UTIL.FROM_WKBGEOMETRY(wkbgeom);
geom_result := SDO_UTIL.FROM_WKTGEOMETRY(wktgeom);
-- Validate WBT/WKT geometry
val_result := SDO_UTIL.VALIDATE_WKBGEOMETRY(wkbgeom);
DBMS_OUTPUT.PUT_LINE('WKB validation result = ' || val_result);
val_result := SDO_UTIL.VALIDATE_WKTGEOMETRY(wktgeom);
DBMS_OUTPUT.PUT_LINE('WKT validation result = ' || val_result);
END;/
To WKT geometry result = POLYGON ((5.0 1.0, 8.0 1.0, 8.0 6.0, 5.0 7.0, 5.0 1.0))
WKB validation result = TRUE
WKT validation result = TRUE
SDO_UTIL.TO_WKTGEOMETRY
The input geometry is converted to the well-known text (WKT) format, as defined by the Open Geospatial Consortium and the International Organization for Standardization (ISO).
This function is patterned after the SQL Multimedia recommendations in ISO 13249-3, Information technology - Database languages - SQL Multimedia and Application Packages - Part 3: Spatial.
To convert a geometry in WKT format to an SDO_GEOMETRY object, use the SDO_UTIL.FROM_WKTGEOMETRY function.
The following example shows conversion to and from WKB and WKT format, and validation of WKB and WKT geometries. (The example uses the definitions and data from Section 2.1, specifically the cola_b geometry from the COLA_MARKETS table.)
DECLARE
wkbgeom BLOB;
wktgeom CLOB;
val_result VARCHAR2(5);
geom_result SDO_GEOMETRY;
geom SDO_GEOMETRY;
BEGIN
SELECT c.shape INTO geom FROM cola_markets c WHERE c.name = 'cola_b';
-- To WBT/WKT geometry
wkbgeom := SDO_UTIL.TO_WKBGEOMETRY(geom);
wktgeom := SDO_UTIL.TO_WKTGEOMETRY(geom);
DBMS_OUTPUT.PUT_LINE('To WKT geometry result = ' || TO_CHAR(wktgeom));
-- From WBT/WKT geometry
geom_result := SDO_UTIL.FROM_WKBGEOMETRY(wkbgeom);
geom_result := SDO_UTIL.FROM_WKTGEOMETRY(wktgeom);
-- Validate WBT/WKT geometry
val_result := SDO_UTIL.VALIDATE_WKBGEOMETRY(wkbgeom);
DBMS_OUTPUT.PUT_LINE('WKB validation result = ' || val_result);
val_result := SDO_UTIL.VALIDATE_WKTGEOMETRY(wktgeom);
DBMS_OUTPUT.PUT_LINE('WKT validation result = ' || val_result);
END;/
To WKT geometry result = POLYGON ((5.0 1.0, 8.0 1.0, 8.0 6.0, 5.0 7.0, 5.0 1.0))
WKB validation result = TRUE
WKT validation result = TRUE
SDO_UTIL.VALIDATE_WKBGEOMETRY
Validates the input geometry, which is in the standard well-known binary (WKB) format; returns the string TRUE if the geometry is valid or FALSE if the geometry is not valid.
To be valid, the input geometry must be in the well-known binary (WKB) format, as defined by the Open Geospatial Consortium and the International Organization for Standardization (ISO).
This function is patterned after the SQL Multimedia recommendations in ISO 13249-3, Information technology - Database languages - SQL Multimedia and Application Packages - Part 3: Spatial.
To validate a geometry in the well-known text (WKT) format, use the SDO_UTIL.VALIDATE_WKTGEOMETRY function.
The following example shows conversion to and from WKB and WKT format, and validation of WKB and WKT geometries. (The example uses the definitions and data from Section 2.1, specifically the cola_b geometry from the COLA_MARKETS table.)
DECLARE
wkbgeom BLOB;
wktgeom CLOB;
val_result VARCHAR2(5);
geom_result SDO_GEOMETRY;
geom SDO_GEOMETRY;
BEGIN
SELECT c.shape INTO geom FROM cola_markets c WHERE c.name = 'cola_b';
-- To WBT/WKT geometry
wkbgeom := SDO_UTIL.TO_WKBGEOMETRY(geom);
wktgeom := SDO_UTIL.TO_WKTGEOMETRY(geom);
DBMS_OUTPUT.PUT_LINE('To WKT geometry result = ' || TO_CHAR(wktgeom));
-- From WBT/WKT geometry
geom_result := SDO_UTIL.FROM_WKBGEOMETRY(wkbgeom);
geom_result := SDO_UTIL.FROM_WKTGEOMETRY(wktgeom);
-- Validate WBT/WKT geometry
val_result := SDO_UTIL.VALIDATE_WKBGEOMETRY(wkbgeom);
DBMS_OUTPUT.PUT_LINE('WKB validation result = ' || val_result);
val_result := SDO_UTIL.VALIDATE_WKTGEOMETRY(wktgeom);
DBMS_OUTPUT.PUT_LINE('WKT validation result = ' || val_result);
END;/
To WKT geometry result = POLYGON ((5.0 1.0, 8.0 1.0, 8.0 6.0, 5.0 7.0, 5.0 1.0))
WKB validation result = TRUE
WKT validation result = TRUE
SDO_UTIL.VALIDATE_WKTGEOMETRY
SDO_UTIL.VALIDATE_WKTGEOMETRY(
geometry IN CLOB
) RETURN VARCHAR2;
or
SDO_UTIL.VALIDATE_WKTGEOMETRY(
geometry IN VARCHAR2
) RETURN VARCHAR2;
Validates the input geometry, which is of type CLOB or VARCHAR2 and in the standard well-known text (WKT) format; returns the string TRUE if the geometry is valid or FALSE if the geometry is not valid.
To be valid, the input geometry must be in the well-known text (WKT) format, as defined by the Open Geospatial Consortium and the International Organization for Standardization (ISO).
This function is patterned after the SQL Multimedia recommendations in ISO 13249-3, Information technology - Database languages - SQL Multimedia and Application Packages - Part 3: Spatial.
To validate a geometry in the well-known binary (WKB) format, use the SDO_UTIL.VALIDATE_WKBGEOMETRY function.
The following example shows conversion to and from WKB and WKT format, and validation of WKB and WKT geometries. (The example uses the definitions and data from Section 2.1, specifically the cola_b geometry from the COLA_MARKETS table.)
DECLARE
wkbgeom BLOB;
wktgeom CLOB;
val_result VARCHAR2(5);
geom_result SDO_GEOMETRY;
geom SDO_GEOMETRY;
BEGIN
SELECT c.shape INTO geom FROM cola_markets c WHERE c.name = 'cola_b';
-- To WBT/WKT geometry
wkbgeom := SDO_UTIL.TO_WKBGEOMETRY(geom);
wktgeom := SDO_UTIL.TO_WKTGEOMETRY(geom);
DBMS_OUTPUT.PUT_LINE('To WKT geometry result = ' || TO_CHAR(wktgeom));
-- From WBT/WKT geometry
geom_result := SDO_UTIL.FROM_WKBGEOMETRY(wkbgeom);
geom_result := SDO_UTIL.FROM_WKTGEOMETRY(wktgeom);
-- Validate WBT/WKT geometry
val_result := SDO_UTIL.VALIDATE_WKBGEOMETRY(wkbgeom);
DBMS_OUTPUT.PUT_LINE('WKB validation result = ' || val_result);
val_result := SDO_UTIL.VALIDATE_WKTGEOMETRY(wktgeom);
DBMS_OUTPUT.PUT_LINE('WKT validation result = ' || val_result);
END;/
To WKT geometry result = POLYGON ((5.0 1.0, 8.0 1.0, 8.0 6.0, 5.0 7.0, 5.0 1.0))
WKB validation result = TRUE
WKT validation result = TRUE