Skip Headers
Oracle® Spatial User's Guide and Reference
10g Release 2 (10.2)

Part Number B14255-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

20 SDO_UTIL Package (Utility)

This chapter contains descriptions of the spatial utility subprograms shown in Table 20-1.

Table 20-1 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 two-dimensional 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.FROM_WKBGEOMETRY


Converts a geometry in the well-known binary (WKB) format to a Spatial geometry object.

SDO_UTIL.FROM_WKTGEOMETRY


Converts a geometry in the well-known text (WKT) format to a Spatial geometry object.

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 polygon-type elements in a geometry to line-type 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.RECTIFY_GEOMETRY


Fixes certain problems with the input geometry, and returns a valid geometry.

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 Douglas-Peucker 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.

SDO_UTIL.TO_WKBGEOMETRY


Converts a Spatial geometry object to the well-known binary (WKB) format.

SDO_UTIL.TO_WKTGEOMETRY


Converts a Spatial geometry object to the well-known text (WKT) format.

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.

SDO_UTIL.VALIDATE_WKTGEOMETRY


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.



SDO_UTIL.APPEND

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

geom1

Geometry object to which geom2 is to be appended.

geom2

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


SDO_UTIL.CIRCLE_POLYGON

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

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

Usage Notes

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

Examples

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

Related Topics


SDO_UTIL.CONCAT_LINES

Format

SDO_UTIL.CONCAT_LINES(

     geom1 IN SDO_GEOMETRY,

     geom2 IN SDO_GEOMETRY

     ) RETURN SDO_GEOMETRY;

Description

Concatenates two line or multiline two-dimensional geometries to create a new geometry.

Parameters

geom1

First geometry object for the concatenation operation.

geom2

Second geometry object for the concatenation operation.

Usage Notes

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

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 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 12) to concatenate multiple two-dimensional 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


SDO_UTIL.CONVERT_UNIT

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

input_value

Number of units to be converted. For example, to convert 10 decimal degrees to radians, specify 10.

from_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.7.2), the MDSYS.SDO_AREA_UNITS table (described in Section 2.8), or the MDSYS.SDO_DIST_UNITS table (described in Section 2.8). For example, to convert decimal degrees to radians, specify Degree.

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


SDO_UTIL.ELLIPSE_POLYGON

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

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

Usage Notes

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

Examples

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

Related Topics


SDO_UTIL.EXTRACT

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

Geometry from which to extract the geometry to be returned.

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-3 in Section 2.5.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 15), 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.5.7.

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 2-5 in Section 2.5.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)) 

Related Topics


SDO_UTIL.FROM_WKBGEOMETRY

Format

SDO_UTIL.FROM_WKBGEOMETRY(

     geometry IN BLOB

     ) RETURN SDO_GEOMETRY;

Description

Converts a geometry in the well-known binary (WKB) format to a Spatial geometry object.

Parameters

geometry

Geometry in WKB format to be converted to SDO_GEOMETRY format.

Usage Notes

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.

Examples

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

Related Topics


SDO_UTIL.FROM_WKTGEOMETRY

Format

SDO_UTIL.FROM_WKTGEOMETRY(

     geometry IN CLOB

     ) RETURN SDO_GEOMETRY;

or

SDO_UTIL.FROM_WKTGEOMETRY(

     geometry IN VARCHAR2

     ) RETURN SDO_GEOMETRY;

Description

Converts a geometry in the well-known text (WKT) format to a Spatial geometry object.

Parameters

geometry

Geometry in WKT format to be converted to SDO_GEOMETRY format.

Usage Notes

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

Examples

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

Related Topics


SDO_UTIL.GETNUMELEM

Format

SDO_UTIL.GETNUMELEM(

     geometry IN SDO_GEOMETRY

     ) RETURN NUMBER;

Description

Returns the number of elements in the input geometry.

Parameters

geometry

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


SDO_UTIL.GETNUMVERTICES

Format

SDO_UTIL.GETNUMVERTICES(

     geometry IN SDO_GEOMETRY

     ) RETURN NUMBER;

Description

Returns the number of vertices in the input geometry.

Parameters

geometry

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


SDO_UTIL.GETVERTICES

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

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 15), 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


SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS

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


SDO_UTIL.POINT_AT_BEARING

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

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_point and along the initial bearing direction to the computed destination point. Must be less than one-half 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 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.

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


SDO_UTIL.POLYGONTOLINE

Format

SDO_UTIL.POLYGONTOLINE(

     geometry IN SDO_GEOMETRY

     ) RETURN SDO_GEOMETRY;

Description

Converts all polygon-type elements in a geometry to line-type elements, and sets the SDO_GTYPE value accordingly.

Parameters

geometry

Geometry to convert.

Usage Notes

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.

Examples

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

Related Topics

None.


SDO_UTIL.PREPARE_FOR_TTS

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

table_space

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.

Transportable tablespaces containing spatial indexes are not supported across endian format platforms (big-endian to little-endian, or little-endian to big-endian).

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


SDO_UTIL.RECTIFY_GEOMETRY

Format

SDO_UTIL.RECTIFY_GEOMETRY(

     geometry IN SDO_GEOMETRY,

     tolerance IN NUMBER

     ) RETURN SDO_GEOMETRY;

Description

Fixes certain problems with the input geometry, and returns a valid geometry.

Parameters

geometry

Geometry to be checked for problems that can be fixed.

tolerance

Tolerance value (see Section 1.5.5).

Usage Notes

This function checks for the following problems that can make a geometry invalid, and fixes the problems in the returned geometry:

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.

This function is used internally by the SDO_UTIL.SIMPLIFY function as part of the geometry simplification process.

Examples

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_ARRAY(5, 1, 8, 1, 8, 6, 5, 7, 5, 1))

Related Topics

SDO_UTIL.RECTIFY_GEOMETRY


SDO_UTIL.REMOVE_DUPLICATE_VERTICES

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

Geometry from which to remove duplicate vertices.

tolerance

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 ORA-13356 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 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))

Related Topics

None.


SDO_UTIL.REVERSE_LINESTRING

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

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

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


SDO_UTIL.SIMPLIFY

Format

SDO_UTIL.SIMPLIFY(

     geometry IN SDO_GEOMETRY,

     threshold IN NUMBER

     tolerance IN NUMBER DEFAULT 0.0000005

     ) RETURN SDO_GEOMETRY;

Description

Simplifies the input geometry, based on a threshold value, using the Douglas-Peucker algorithm.

Parameters

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 as threshold. If you do not specify a value, the default value is 0.0000005.

Usage Notes

This function also convert 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:

Examples

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 20-1 shows the result of this example. In Figure 20-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.

Figure 20-1 Simplification of a Geometry

Description of Figure 20-1 follows
Description of "Figure 20-1 Simplification of a Geometry"

Related Topics

SDO_UTIL.RECTIFY_GEOMETRY


SDO_UTIL.TO_GMLGEOMETRY

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

thegeom

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 4-digit SDO_GTYPE value.

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.

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.


SDO_UTIL.TO_WKBGEOMETRY

Format

SDO_UTIL.TO_WKBGEOMETRY(

     geometry IN SDO_GEOMETRY

     ) RETURN BLOB;

Description

Converts a Spatial geometry object to the well-known binary (WKB) format.

Parameters

geometry

SDO_GEOMETRY object to be converted to WKB format.

Usage Notes

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.

Examples

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

Related Topics


SDO_UTIL.TO_WKTGEOMETRY

Format

SDO_UTIL.TO_WKTGEOMETRY(

     geometry IN SDO_GEOMETRY

     ) RETURN CLOB;

Description

Converts a Spatial geometry object to the well-known text (WKT) format.

Parameters

geometry

SDO_GEOMETRY object to be converted to WKT format.

Usage Notes

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.

Examples

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

Related Topics


SDO_UTIL.VALIDATE_WKBGEOMETRY

Format

SDO_UTIL.VALIDATE_WKBGEOMETRY(

     geometry IN BLOB

     ) RETURN VARCHAR2;

Description

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.

Parameters

geometry

Geometry in WKB format to be checked for validity.

Usage Notes

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.

Examples

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

Related Topics


SDO_UTIL.VALIDATE_WKTGEOMETRY

Format

SDO_UTIL.VALIDATE_WKTGEOMETRY(

     geometry IN CLOB

     ) RETURN VARCHAR2;

or

SDO_UTIL.VALIDATE_WKTGEOMETRY(

     geometry IN VARCHAR2

     ) RETURN VARCHAR2;

Description

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.

Parameters

geometry

Geometry in WKT format to be checked for validity.

Usage Notes

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.

Examples

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

Related Topics