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


View PDF 
This chapter contains descriptions of the spatial utility subprograms shown in Table 191.
Table 191 Spatial Utility Subprograms
Subprogram  Description 

SDO_UTIL.APPEND 
Appends one geometry to another geometry to create a new geometry. 
SDO_UTIL.CIRCLE_POLYGON 
Returns the polygon geometry that approximates and is covered by a specified circle. 
SDO_UTIL.CONCAT_LINES 
Concatenates two line or multiline twodimensional geometries to create a new geometry. 
SDO_UTIL.CONVERT_UNIT 
Converts values from one angle, area, or distance unit of measure to another. 
SDO_UTIL.ELLIPSE_POLYGON 
Returns the polygon geometry that approximates and is covered by a specified ellipse. 
SDO_UTIL.EXTRACT 
Returns the geometry that represents a specified element (and optionally a ring) of the input geometry. 
SDO_UTIL.GETNUMELEM 
Returns the number of elements in the input geometry. 
SDO_UTIL.GETNUMVERTICES 
Returns the number of vertices in the input geometry. 
SDO_UTIL.GETVERTICES 
Returns the coordinates of the vertices of the input geometry. 
SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS 
Initializes all spatial indexes in a tablespace that was transported to another database. 
SDO_UTIL.POINT_AT_BEARING 
Returns a point geometry that is at the specified distance and bearing from the start point. 
SDO_UTIL.POLYGONTOLINE 
Converts all polygontype elements in a geometry to linetype elements, and sets the SDO_GTYPE value accordingly. 
SDO_UTIL.PREPARE_FOR_TTS 
Prepares a tablespace to be transported to another database, so that spatial indexes will be preserved during the transport operation. 
SDO_UTIL.REMOVE_DUPLICATE_VERTICES 
Removes duplicate (redundant) vertices from a geometry. 
SDO_UTIL.REVERSE_LINESTRING 
Returns a line string geometry with the vertices of the input geometry in reverse order. 
SDO_UTIL.SIMPLIFY 
Simplifies the input geometry, based on a threshold value, using the DouglasPeucker algorithm. 
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. 
Format
SDO_UTIL.APPEND(
geom1 IN SDO_GEOMETRY,
geom2 IN SDO_GEOMETRY
) RETURN SDO_GEOMETRY;
Description
Appends one geometry to another geometry to create a new geometry.
Parameters
Geometry object to which geom2
is to be appended.
Geometry object to append to geom1
.
Usage Notes
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 13. 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 twodimensional polygons (SDO_GTYPE = 2003), the resulting geometry is a twodimensional multipolygon (SDO_GTYPE = 2007).
An exception is raised if geom1
and geom2
are based on different coordinate systems.
Examples
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))
Related Topics
Format
SDO_UTIL.CIRCLE_POLYGON(
center_longitude IN NUMBER,
center_latitude IN NUMBER,
radius IN NUMBER,
arc_tolerance IN NUMBER
) RETURN SDO_GEOMETRY;
Description
Returns the polygon geometry that approximates and is covered by a specified circle.
Parameters
Center longitude (in degrees) of the circle to be used to create the returned geometry.
Center latitude (in degrees) of the circle to be used to create the returned geometry.
Length (in meters) of the radius of the circle to be used to create the returned geometry.
A numeric value to be used to construct the polygon geometry. The arc_tolerance
parameter value has the same meaning and usage guidelines as the arc_tolerance
keyword value in the params
parameter string for the SDO_GEOM.SDO_ARC_DENSIFY function. The unit of measurement associated with the geometry is associated with the arc_tolerance
parameter value. (For more information, see the Usage Notes for the SDO_GEOM.SDO_ARC_DENSIFY function in Chapter 13.)
Usage Notes
This function is useful for creating a circlelike 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)
).
Examples
The following example returns a circlelike polygon around a point near the center of Concord, Massachusetts. A circle radius 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))
Related Topics
Format
SDO_UTIL.CONCAT_LINES(
geom1 IN SDO_GEOMETRY,
geom2 IN SDO_GEOMETRY
) RETURN SDO_GEOMETRY;
Description
Concatenates two line or multiline twodimensional geometries to create a new geometry.
Parameters
First geometry object for the concatenation operation.
Second geometry object for the concatenation operation.
Usage Notes
Each input geometry must be a twodimensional 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 16).
The input geometries must be line strings whose vertices are connected by straight line segments. Circular arcs and compound line strings are not supported.
The topological relationship between geom1
and geom2
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 14) to concatenate multiple twodimensional line or multiline geometries.
An exception is raised if geom1
and geom2
are based on different coordinate systems.
Examples
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))
Related Topics
Format
SDO_UTIL.CONVERT_UNIT(
input_value IN NUMBER,
from_unit IN VARCHAR2,
to_unit IN VARCHAR2
) RETURN NUMBER;
Description
Converts values from one angle, area, or distance unit of measure to another.
Parameters
Number of units to be converted. For example, to convert 10 decimal degrees to radians, specify 10
.
The unit of measure from which to convert the input value. Must be a value from the SDO_UNIT column of the MDSYS.ANGLE_UNITS table (described in Section 6.4.2), the MDSYS.SDO_AREA_UNITS table (described in Section 2.6), or the MDSYS.SDO_DIST_UNITS table (described in Section 2.6). For example, to convert decimal degrees to radians, specify Degree
.
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 from_unit
. For example, to convert decimal degrees to radians, specify Radian
.
Usage Notes
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.
Examples
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
Related Topics
None.
Format
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;
Description
Returns the polygon geometry that approximates and is covered by a specified ellipse.
Parameters
Center longitude (in degrees) of the ellipse to be used to create the returned geometry.
Center latitude (in degrees) of the ellipse to be used to create the returned geometry.
Length (in meters) of the semimajor axis of the ellipse to be used to create the returned geometry.
Length (in meters) of the semiminor axis of the ellipse to be used to create the returned geometry.
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.
A numeric value to be used to construct the polygon geometry. The arc_tolerance
parameter value has the same meaning and usage guidelines as the arc_tolerance
keyword value in the params
parameter string for the SDO_GEOM.SDO_ARC_DENSIFY function. The unit of measurement associated with the geometry is associated with the arc_tolerance
parameter value. (For more information, see the Usage Notes for the SDO_GEOM.SDO_ARC_DENSIFY function in Chapter 13.)
Usage Notes
This function is useful for creating an ellipselike 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)
).
Examples
The following example returns an ellipselike polygon, oriented eastwest (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))
Related Topics
Format
SDO_UTIL.EXTRACT(
geometry IN SDO_GEOMETRY,
element IN NUMBER
[, ring IN NUMBER]
) RETURN SDO_GEOMETRY;
Description
Returns the geometry that represents a specified element (and optionally a ring) of the input geometry.
Parameters
Geometry from which to extract the geometry to be returned.
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_TYPE 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).
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 23 in Section 2.3.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.
Usage Notes
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 13), 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.3.6.
An exception is raised if element
or ring
is an invalid number for geometry
.
Examples
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 23 in Section 2.3.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,  twodimensional 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))
Related Topics
Format
SDO_UTIL.GETNUMELEM(
geometry IN SDO_GEOMETRY
) RETURN NUMBER;
Description
Returns the number of elements in the input geometry.
Parameters
Geometry for which to return the number of elements.
Usage Notes
None.
Examples
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
Related Topics
Format
SDO_UTIL.GETNUMVERTICES(
geometry IN SDO_GEOMETRY
) RETURN NUMBER;
Description
Returns the number of vertices in the input geometry.
Parameters
Geometry for which to return the number of vertices.
Usage Notes
None.
Examples
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
Related Topics
Format
SDO_UTIL.GETVERTICES(
geometry IN SDO_GEOMETRY
) RETURN VERTEX_SET_TYPE;
Description
Returns the coordinates of the vertices of the input geometry.
Parameters
Geometry for which to return the coordinates of the vertices.
Usage Notes
This function returns an object of VERTEX_SET_TYPE, which consists of a table of objects of 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, id NUMBER);
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 13), you can use the GETVERTICES function to view the vertices in tabular format.
Examples
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.
Related Topics
Format
SDO_UTIL.INITIALIZE_INDEX_FOR_TTS;
Description
Initializes all spatial indexes in a tablespace that was transported to another database.
Parameters
None.
Usage Notes
This procedure is part of the support for using the Oracle transportable tablespace feature with tablespaces that contain any spatial indexes. Use this procedure after the import operation is completed; each user that has a spatial index in the tablespace must call the procedure. You must also have called the SDO_UTIL.PREPARE_FOR_TTS procedure just before performing the export operation. See the Usage Notes for the SDO_UTIL.PREPARE_FOR_TTS procedure for more information about using the transportable tablespace feature with spatial data.
For detailed information about transportable tablespaces and transporting tablespaces to other databases, see Oracle Database Administrator's Guide.
Examples
The following example initializes all spatial indexes in a tablespace that was transported to another database.
CALL SDO_UTIL.INITIALIZE_INDEX_FOR_TTS;
Related Topics
Format
SDO_UTIL.POINT_AT_BEARING(
start_point IN SDO_GEOMETRY,
bearing IN NUMBER,
distance IN NUMBER
) RETURN SDO_GEOMETRY;
Description
Returns a point geometry that is at the specified distance and bearing from the start point.
Parameters
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.
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).
Number of meters from start_point
and along the initial bearing direction to the computed destination point. Must be less than onehalf the circumference of the Earth.
Usage Notes
The input point geometry must be based on a geodetic coordinate system. If it is based on a nongeodetic 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.
Examples
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))
Related Topics
Format
SDO_UTIL.POLYGONTOLINE(
geometry IN SDO_GEOMETRY
) RETURN SDO_GEOMETRY;
Description
Converts all polygontype elements in a geometry to linetype elements, and sets the SDO_GTYPE value accordingly.
Parameters
Geometry to convert.
Usage Notes
The order of the vertices of each resulting linetype element is the same as in the associated polygontype element, and the start and end points of each linetype segment are the same point.
If the input geometry is a line, it is returned.
Examples
The following example converts the input polygon geometry, which is the same geometry as cola_b (see Figure 21 and Example 21 in Section 2.1), to a line string geometry. In the returned geometry, the SDO_GTYPE value (2002) indicates a twodimensional 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,  twodimensional 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,TWODIMENSIONALPOLYGONNULL,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))
Related Topics
None.
Format
SDO_UTIL.PREPARE_FOR_TTS(
table_space IN VARCHAR2);
Description
Prepares a tablespace to be transported to another database, so that spatial indexes will be preserved during the transport operation.
Parameters
Tablespace to be transported.
Usage Notes
Before Oracle Database 10g Release 1 (10.1), the Oracle transportable tablespace feature could not be used with tablespaces that contained any spatial indexes. Effective with Oracle Database 10g Release 1 (10.1), you can transport tablespaces that contain spatial indexes; however, you must call the PREPARE_FOR_TTS procedure just before you perform the export operation, and you must call it for each user that has a spatial index in the specified tablespace.
After the export operation is complete, you must call the SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS procedure to initialize all spatial indexes in the transported tablespace.
For detailed information about transportable tablespaces and transporting tablespaces to other databases, see Oracle Database Administrator's Guide.
Examples
The following example prepares a tablespace named TS1
to be transported to another database.
CALL SDO_UTIL.PREPARE_FOR_TTS('TS1');
Related Topics
Format
SDO_UTIL.REMOVE_DUPLICATE_VERTICES
geometry IN SDO_GEOMETRY,
tolerance IN NUMBER
) RETURN SDO_GEOMETRY;
Description
Removes duplicate (redundant) vertices from a geometry.
Parameters
Geometry from which to remove duplicate vertices.
Tolerance value (see Section 1.5.5).
Usage Notes
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 ORA13356 in these cases. You can use the REMOVE_DUPLICATE_VERTICES function to change such invalid geometries into valid geometries.
If the input geometry does not contain any duplicate vertices, it is returned.
Examples
The following example removes a duplicate vertex from the input geometry, which is the same geometry as cola_b (see Figure 21 and Example 21 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,  twodimensional 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,TWODIMENSIONALPOLYGONNUL  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))
Related Topics
None.
Format
SDO_UTIL.REVERSE_LINESTRING(
geometry IN SDO_GEOMETRY
) RETURN SDO_GEOMETRY;
Description
Returns a line string geometry with the vertices of the input geometry in reverse order.
Parameters
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.)
Usage Notes
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 16.
Examples
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))
Related Topics
Format
SDO_UTIL.SIMPLIFY(
geometry IN SDO_GEOMETRY,
threshold IN NUMBER
) RETURN SDO_GEOMETRY;
Description
Simplifies the input geometry, based on a threshold value, using the DouglasPeucker algorithm.
Parameters
Geometry to be simplified.
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 nongeodetic, 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.
Usage Notes
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 DouglasPeucker 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 then 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, individual elements that did not overlap before simplification might now overlap. If overlapping of elements occurs in a multipolygon, the geometry is invalid because Open GIS Consortium rules state that polygon elements may not overlap in a multipolygon. Under certain conditions, single polygon geometries might also become invalid.
Examples
The following example simplifies the road shown in Figure 720 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 ) 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 191 shows the result of this example. In Figure 191, the thick solid black line is the resulting geometry, and the thin solid light line between the start and end points is the input geometry.
Related Topics
None.
Format
SDO_UTIL.TO_GMLGEOMETRY(
thegeom IN SDO_GEOMETRY
) RETURN CLOB;
Description
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.
Parameters
Geometry for which to return the GML fragment.
Usage Notes
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.
The input geometry must have a 4digit SDO_GTYPE value.
Polygons must be defined using the conventions for Oracle9i and higher 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_BUFFER or SDO_GEOM.SDO_ARC_DENSIFY 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.
Examples
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>  LRS geometry. An Empty CLOB is returned. SELECT SDO_UTIL.TO_GMLGEOMETRY( sdo_geometry(2306, 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   Rectangle (geodetic) SELECT TO_CHAR( SDO_UTIL.TO_GMLGEOMETRY(sdo_geometry(2003, 8307, null, sdo_elem_info_array(1,1003,5), 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>
Related Topics
None.