Format

SDO_UTIL.AFFINETRANSFORMS(
     geometry    IN SDO_GEOMETRY, 
     translation IN VARCHAR2 DEFAULT 'FALSE', 
     tx          IN NUMBER DEFAULT 0.0, 
     ty          IN NUMBER DEFAULT 0.0, 
     tz          IN NUMBER DEFAULT 0.0, 
     scaling     IN VARCHAR2 DEFAULT 'FALSE', 
     psc1        IN SDO_GEOMETRY DEFAULT NULL, 
     sx          IN NUMBER DEFAULT 0.0, 
     sy          IN NUMBER DEFAULT 0.0, 
     sz          IN NUMBER DEFAULT 0.0, 
     rotation    IN VARCHAR2 DEFAULT 'FALSE', 
     p1          IN SDO_GEOMETRY DEFAULT NULL, 
     line1       IN SDO_GEOMETRY DEFAULT NULL, 
     angle       IN NUMBER DEFAULT 0.0, 
     dir         IN NUMBER DEFAULT -1, 
     shearing    IN VARCHAR2 DEFAULT 'FALSE', 
     shxy        IN NUMBER DEFAULT 0.0, 
     shyx        IN NUMBER DEFAULT 0.0, 
     shxz        IN NUMBER DEFAULT 0.0, 
     shzx        IN NUMBER DEFAULT 0.0, 
     shyz        IN NUMBER DEFAULT 0.0, 
     shzy        IN NUMBER DEFAULT 0.0, 
     reflection  IN VARCHAR2 DEFAULT 'FALSE', 
     pref        IN SDO_GEOMETRY DEFAULT NULL, 
     lineR       IN SDO_GEOMETRY DEFAULT NULL, 
     dirR        IN NUMBER DEFAULT -1, 
     planeR      IN VARCHAR2 DEFAULT 'FALSE', 
     n           IN SDO_NUMBER_ARRAY DEFAULT NULL, 
     bigD        IN SDO_NUMBER_ARRAY DEFAULT NULL, 
     ) RETURN SDO_GEOMETRY;

Description

Returns a geometry that reflects an affine transformation of the input geometry.

Parameters

geometry

Input geometry on which to perform the affine transformation.

translation

A string value of TRUE causes translation to be performed; a string value of FALSE (the default) causes translation not to be performed. If this parameter is TRUE, translation is performed about the point at (tx,ty) or (tx,ty,tz).

tx

X-axis value for translation. The default value is 0.0.

ty

Y-axis value for translation. The default value is 0.0.

tz

Z-axis value for translation. The default value is 0.0.

scaling

A string value of TRUE causes scaling to be performed; a string value of FALSE (the default) causes scaling not to be performed.

psc1

Point on the input geometry about which to perform the scaling. If scaling is TRUE, this geometry should be either a zero point (point geometry with 0,0 or 0,0,0 ordinates for scaling about the origin) or a nonzero point (point geometry with ordinates for scaling about a point other than the origin). If scaling is FALSE, psc1 can be a null value.

sx

X-axis value for scaling (about either the point specified in the psc1 parameter or the origin). The default value is 0.0.

sy

Y-axis value for scaling (about either the point specified in the psc1 parameter or the origin). The default value is 0.0.

sz

Z-axis value for scaling (about either the point specified in the psc1 parameter or the origin). The default value is 0.0.

rotation

A string value of TRUE causes rotation to be performed; a string value of FALSE (the default) causes rotation not to be performed.

For two-dimensional geometries, rotation uses the p1 and angle values. For three-dimensional geometries, rotation uses either the angle and dir values or the line1 and angle values.

p1

Point for two-dimensional geometry rotation about a specified point.

line1

Line for rotation about a specified axis.

angle

Angle rotation parameter (in radians) for rotation about a specified axis or about the X, Y, or Z axis. The default value is 0.0.

dir

Rotation parameter for x(0), y(1), or z(2)-axis roll. If the rotation parameter value is TRUE but the dir parameter is not used, use a value of -1 (the default)

shearing

A string value of TRUE causes shearing to be performed; a string value of FALSE (the default) causes shearing not to be performed.

For two-dimensional geometries, shearing uses the shxy and shyx parameter values. For three-dimensional geometries, shearing uses the shxy, shyx, shxz, shzx, shyz, and shzy parameter values.

shxy

Value for shearing due to X along the Y direction. The default value is 0.0.

shyx

Value for shearing due to Y along the X direction. The default value is 0.0.

shxz

Value for shearing due to X along the Z direction (three-dimensional geometries only). The default value is 0.0.

shzx

Value for shearing due to Z along the X direction (three-dimensional geometries only).

shyz

Value for shearing due to Y along the Z direction (three-dimensional geometries only).

shzy

Value for shearing due to Z along the Y direction (three-dimensional geometries only).

reflection

A string value of TRUE causes reflection to be performed; a string value of FALSE (the default) causes reflection not to be performed.

For two-dimensional geometries, reflection uses the lineR value for reflection about an axis and the pref value for the centroid for self-reflection. For three-dimensional geometries, reflection uses the lineR value for reflection about an axis; the dirR value for reflection about the yz, xz, and xy planes; the planeR, n, and bigD values for reflection about a specified plane; and the pref value for the centroid for self-reflection.

pref

Point through which to perform reflection.

lineR

Line along which to perform reflection.

dirR

Number indicating the plane about (through) which to perform reflection: 0 for the yz plane, 1 for the xz plane, or 2 for the xy plane. If the reflection parameter value is TRUE but the dirR parameter is not used, use a value of -1 (the default).

planeR

A string value of TRUE causes reflection about an arbitrary plane to be performed; a string value of FALSE (the default) causes reflection about an arbitrary plane not to be performed.

n

Normal vector of the plane.

bigD

Delta value for the plane equation in three-dimensional geometries.

For three-dimensional geometries, bigD = delta and n = (A,B,C) where n is the normal of the plane in three-dimensional space. Thus, the plane equation is:

Ax+By+Cz+bigD = 3DDotProd(n,anypointonplane)+bigD = 0

Usage Notes

The order of affine transforms matter because these are matrix and vector multiplications.

You should validate the resulting geometry using the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function.

Examples

The following example performs an affine transformation on a two-dimensional geometry.

-- Polygon reflection in 2D about a specified line segment
SELECT SDO_UTIL.AFFINETRANSFORMS(
  geometry =>   sdo_geometry (2003,8307,null,sdo_elem_info_array (1,1003,1),
                              sdo_ordinate_array (0,2,2,0,5,3,3,5,0,2)),
  translation => 'FALSE',
  tx => 0.0,
  ty => 0.0,
  tz => 0.0,
  scaling => 'FALSE',
  psc1 => NULL,
  sx => 0.0,
  sy => 0.0,
  sz => 0.0,
  rotation => 'TRUE',
  p1 => sdo_geometry (2001,8307,sdo_point_type(0,2,null),null,null),
  line1 => NULL,
  angle => -2.35253274913915,
  dir => -1,
  shearing => 'FALSE',
  shxy => 0.0,
  shyx => 0.0,
  shxz => 0.0,
  shzx => 0.0,
  shyz => 0.0,
  shzy => 0.0,
  reflection => 'FALSE',
  pref => NULL,
  lineR => NULL,
  dirR => 0,
  planeR => 'FALSE',
  n => NULL,
  bigD => NULL
) FROM DUAL;

SDO_UTIL.AFFINETRANSFORMS(GEOMETRY=>SDO_GEOMETRY(2003,8307,NULL,SDO_ELEM_INFO_AR
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(0, 2, -2.8284082, 1.98964306, -2.8128727, -2.2529692, .015535417, -2.2426122,
 0, 2))

The following is a simplified version of the preceding example, using the default values for most parameters.

-- Simpler form, using most default values
SELECT SDO_UTIL.AFFINETRANSFORMS(
  geometry =>   sdo_geometry (2003,8307,null,sdo_elem_info_array (1,1003,1),
                              sdo_ordinate_array (0,2,2,0,5,3,3,5,0,2)),
  rotation => 'TRUE',
  p1 => sdo_geometry (2001,8307,sdo_point_type(0,2,null),null,null),
  angle => -2.35253274913915
) FROM DUAL;

SDO_UTIL.AFFINETRANSFORMS(GEOMETRY=>SDO_GEOMETRY(2003,8307,NULL,SDO_ELEM_INFO_AR
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(0, 2, -2.8284082, 1.98964306, -2.8128727, -2.2529692, .015535417, -2.2426122,
 0, 2))

The following example performs an affine transformation on a three-dimensional geometry.

-- Polygon reflection in 3D about a specified plane (z=1 plane in this example)
SELECT SDO_UTIL.AFFINETRANSFORMS(
  geometry => MDSYS.SDO_GEOMETRY(3003, 0, NULL, 
     MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
     MDSYS.SDO_ORDINATE_ARRAY(
     1.5,0,0,
     2.5,1,0,
     1.5,2,0,
     0.5,2,0,
     0.5,0,0,
     1.5,0,0)),
  translation => 'FALSE',
  tx => 0.0, 
  ty => 0.0, 
  tz => 0.0,
  scaling => 'FALSE', 
  psc1 => NULL, 
  sx => 0.0, 
  sy => 0.0, 
  sz => 0.0,
  rotation => 'FALSE', 
  p1 => NULL, 
  line1 => NULL,
  angle => 0.0, 
  dir => 0,
  shearing => 'FALSE', 
  shxy => 0.0, 
  shyx => 0.0, 
  shxz => 0.0, 
  shzx => 0.0, 
  shyz => 0.0, 
  shzy => 0.0,
  reflection => 'TRUE', 
  pref => NULL, 
  lineR => NULL, 
  dirR => -1, 
  planeR => 'TRUE', 
  n => SDO_NUMBER_ARRAY(0.0, 0.0, 1.0),   
  bigD => SDO_NUMBER_ARRAY(-1.0)
) FROM DUAL;

SDO_UTIL.AFFINETRANSFORMS(GEOMETRY=>MDSYS.SDO_GEOMETRY(3003,0,NULL,MDSYS.SDO_ELE
--------------------------------------------------------------------------------
SDO_GEOMETRY(3003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(1.5, 0, 2, 2.5, 1, 2, 1.5, 2, 2, .5, 2, 2, .5, 0, 2, 1.5, 0, 2))

Related Topics

• SDO_UTIL.CONVERT_UNIT

• SDO_UTIL.POINT_AT_BEARING

Format

SDO_UTIL.APPEND(
     geometry1  IN SDO_GEOMETRY, 
     geometry2  IN SDO_GEOMETRY 
     ) RETURN SDO_GEOMETRY;

Description

Appends one geometry to another geometry to create a new geometry.

Parameters

geometry1

Geometry object to which geometry2 is to be appended.

geometry2

Geometry object to append to geometry1.

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 SDO_GEOM Package (Geometry). The APPEND function executes faster than the SDO_GEOM.SDO_UNION function.

The geometry type (SDO_GTYPE value) of the resulting geometry reflects the types of the input geometries and the append operation. For example, if the input geometries are two-dimensional polygons (SDO_GTYPE = 2003), the resulting geometry is a two-dimensional multipolygon (SDO_GTYPE = 2007).

An exception is raised if geometry1 and geometry2 are based on different coordinate systems.

Examples

The following example appends the cola_a and cola_c geometries. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

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_GEOM.SDO_UNION (in SDO_GEOM Package (Geometry))

Format

SDO_UTIL.BEARING_TILT_FOR_POINTS(
     start_point IN SDO_GEOMETRY, 
     end_point   IN SDO_GEOMETRY, 
     tol         IN NUMBER, 
     bearing     OUT NUMBER, 
     tilt        OUT NUMBER 
     ) RETURN SDO_GEOMETRY;

Description

Computes the bearing and tilt from a start point to an end point.

Parameters

start_point

Starting point geometry object from which to compute the bearing and tilt. The point geometry must be based on a geodetic coordinate system.

end_point

Ending point geometry object to use in computing the bearing and tilt. The point geometry must be based on the same geodetic coordinate system as start_point.

tol

Tolerance value (see Tolerance).

bearing

Number of radians, measured clockwise from North.

tilt

Number of radians, measured from the normal.

Usage Notes

The input point geometries must be based on the same geodetic coordinate system. If they are based on a non-geodetic coordinate system, the output bearing is a null value.

The tilt is computed as the arctangent of the difference between the height values divided by the distance between the points (with height excluded from the distance calculation). That is: tilt = atan(height_difference/distance)

To convert radians to decimal degrees or decimal degrees to radians, you can use the SDO_UTIL.CONVERT_UNIT function. To return a point geometry that is at a specified distance and bearing from a start point, you can use the SDO_UTIL.POINT_AT_BEARING function.

Examples

The following example computes the bearing and tilt for two longitude/latitude points, where the elevation of the start point is 0 (zero) and the elevation of the end point is 5000 meters. This example displays the bearing and tilt values in radians.

DECLARE
  bearing NUMBER;
  tilt    NUMBER;
BEGIN
  SDO_UTIL.BEARING_TILT_FOR_POINTS(
    SDO_GEOMETRY(2001, 8307,
      SDO_POINT_TYPE(-71.5, 43, 0), NULL, NULL), -- start_point
    SDO_GEOMETRY(2001, 8307,
      SDO_POINT_TYPE(-71, 43.5, 5000), NULL, NULL), -- end_point
    0.05,  --tolerance
    bearing,
    tilt);
  DBMS_OUTPUT.PUT_LINE('Bearing = ' || bearing);
  DBMS_OUTPUT.PUT_LINE('Tilt = ' || tilt);
END;
/
Bearing = .628239101930666          
Tilt = .0725397288678286910476298724869396973718

The following example is the same as the preceding one, except that it displays the bearing and tilt in decimal degrees instead of radians.

DECLARE
  bearing NUMBER;
  tilt    NUMBER;
BEGIN
  SDO_UTIL.BEARING_TILT_FOR_POINTS(
    SDO_GEOMETRY(2001, 8307,
      SDO_POINT_TYPE(-71.5, 43, 0), NULL, NULL), -- start_point
    SDO_GEOMETRY(2001, 8307,
      SDO_POINT_TYPE(-71, 43.5, 5000), NULL, NULL), -- end_point
    0.05,  --tolerance
    bearing,
    tilt);
  DBMS_OUTPUT.PUT_LINE('Bearing in degrees = '  
    || bearing * 180 / 3.1415926535897932384626433832795);
  DBMS_OUTPUT.PUT_LINE('Tilt in degrees = ' 
    || tilt * 180 / 3.1415926535897932384626433832795);
END;
/
Bearing in degrees = 35.99544906571628894295547577999851892359
Tilt in degrees = 4.15622031114988533540349823511872120415

Related Topics

• SDO_UTIL.CONVERT_UNIT

• SDO_UTIL.POINT_AT_BEARING

Format

SDO_UTIL.CIRCLE_POLYGON(
     point         IN SDO_GEOMETRY, 
     radius        IN NUMBER, 
     arc_tolerance IN NUMBER, 
     start_azimuth IN NUMBER DEFAULT NULL, 
     end_azimuth   IN NUMBER DEFAULT NULL, 
     orientation   IN NUMBER DEFAULT NULL, 
     arc           IN NUMBER DEFAULT NULL 
     ) RETURN SDO_GEOMETRY;

or

SDO_UTIL.CIRCLE_POLYGON(
     center_longitude IN NUMBER, 
     center_latitude  IN NUMBER, 
     radius           IN NUMBER, 
     arc_tolerance    IN NUMBER 
     ) RETURN SDO_GEOMETRY;

or

SDO_UTIL.CIRCLE_POLYGON(
     center_longitude IN NUMBER, 
     center_latitude  IN NUMBER, 
     radius           IN NUMBER, 
     start_azimuth    IN NUMBER, 
     end_azimuth      IN NUMBER, 
     arc_tolerance    IN NUMBER 
     ) RETURN SDO_GEOMETRY;

Description

Creates polygon or polyline approximations of circles and arcs in geodetic coordinate systems.

• The first format allows full control of the output.

• The second format returns the polygon geometry that approximates and is covered by a specified circle.

• The third format returns a line geometry that approximates the boundary of the circle from the start to the end azimuth (an arc).

Parameters

point

The center of the circle. Must be a point geometry in a geodetic coordinate system. The returned geometry will be in the same coordinate system.

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 SDO_GEOM Package (Geometry).)

start_azimuth

Start angle (in degrees) of the arc, measured counterclockwise from due south.

If start_azimuth and end_azimuth are null, the behavior is comparable to the second format, which returns the polygon geometry that approximates and is covered by a specified circle.

end_azimuth

End angle (in degrees) of the arc, measured counterclockwise from due south. If orientation is not explicitly specified, then the arc will be the counterclockwise section of the circle from start_azimuth if end_azimuth is greater than start_azimuth, and the arc will be the clockwise section if end_azimuth is less than start_azimuth.

If start_azimuth and end_azimuth are null, the behavior is comparable to the second format, which returns the polygon geometry that approximates and is covered by a specified circle.

orientation

Controls which portion of the circle from start_azimuth to end_azimuth is used. This controls the shape of the returned output, not the orientation of the output: a returned polygon is always oriented counterclockwise, and a returned arc is always from start_azimuth to end_azimuth. The value can be one of the following:

• 0 or null (default): Automatic (see the end_azimuth parameter description).

• 1 or +1: Arc is drawn counterclockwise from start_azimuth to end_azimuth.

• -1: Arc is drawn clockwise from start_azimuth to end_azimuth.

arc

If set to 1, the result will be a line; if 0 or null (the default), the result is a polygon. If start_azimuth and end_azimuth specify a subset of the circle with a polygon result, the returned polygon will include the center of the circle (that is, will be a sector of the circle).

Usage Notes

The first format of 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 and Graph). The returned geometry has an SDO_SRID value of 8307 (for Longitude / Latitude (WGS 84)).

The second and third formats of this function are useful for creating a polyline approximation to a circular arc or a polygon that represents a sector of the circle.

If the start and end azimuth values are specified, they must not be equal to each other, and must cover no more than a 360 degree rotation. Angles must be in the range -720 to +720.

Circles will always be created with at least four distinct vertices (a square).

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

Format

SDO_UTIL.CONCAT_LINES(
     geometry1  IN SDO_GEOMETRY, 
     geometry2  IN SDO_GEOMETRY 
     ) RETURN SDO_GEOMETRY;

Description

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

Parameters

geometry1

First geometry object for the concatenation operation.

geometry2

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 SDO_LRS Package (Linear Referencing System) ).

The input geometries must be line strings whose vertices are connected by straight line segments. Circular arcs and compound line strings are not supported.

If an input geometry is a multiline geometry, the elements of the geometry must be disjoint. If they are not disjoint, this function may return incorrect results.

The topological relationship between geometry1 and geometry2 must be DISJOINT or TOUCH; and if the relationship is TOUCH, the geometries must intersect only at two end points.

You can use the SDO_AGGR_CONCAT_LINES spatial aggregate function (described in Spatial Aggregate Functions) to concatenate multiple two-dimensional line or multiline geometries.

An exception is raised if geometry1 and geometry2 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_AGGR_CONCAT_LINES (in Spatial Aggregate Functions)

• SDO_LRS.CONCATENATE_GEOM_SEGMENTS (in SDO_LRS Package (Linear Referencing System) )

Format

SDO_UTIL.CONVERT_UNIT(
     ivalue    IN NUMBER, 
     in_unit   IN VARCHAR2, 
     out_unit  IN VARCHAR2 
     ) RETURN NUMBER;

Description

Converts values from one angle, area, or distance unit of measure to another.

Parameters

value

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

in_unit

The unit of measure from which to convert the input value. Must be a value from the SDO_UNIT column of the MDSYS.SDO_ANGLE_UNITS table (described in MDSYS.SDO_ANGLE_UNITS View), the MDSYS.SDO_AREA_UNITS table (described in Unit of Measurement Support), or the MDSYS.SDO_DIST_UNITS table (described in Unit of Measurement Support). For example, to convert decimal degrees to radians, specify Degree.

out_unit

The unit of measure into which to convert the input value. Must be a value from the SDO_UNIT column of the same table used for in_unit. For example, to convert decimal degrees to radians, 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.CONVERT3007TO3008(
     geometry  IN SDO_GEOMETRY  
     ) RETURN SDO_GEOMETRY;

Description

Converts an input three-dimensional multisurface geometry (SDO_GTYPE 3007) to a simple solid geometry (SDO_GTYPE 3008).

Parameters

geometry

Geometry object with SDO_GTYPE 3007 (multisurface). The surfaces are simple polygons without interiors.

Usage Notes

For information about SDO_GEOMETRY attributes for 3D geometries, see Three-Dimensional Spatial Objects.

This function does not check the validity of the returned simple solid geometry. To check the validity of a geometry object, use the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function.

Examples

The following example converts an input three-dimensional multisurface geometry (SDO_GTYPE 3007) to a simple solid geometry (SDO_GTYPE 3008). It assumes that a table named GEOMS3D exists with at least a numeric ID column and a column named GEOM of type SDO_GEOMETRY containing three-dimensional multisurface geometries.

SELECT sdo_util.convert3007to3008(g.geom) FROM geoms3d g WHERE g.id=1;

Format

SDO_UTIL.DELETE_SDO_GEOM_METADATA(     
  owner       IN   VARCHAR2,
  table_name  IN VARCHAR2,
  column_name IN VARCHAR2);

Description

Deletes metadata for a spatial table from the geometry metadata views USER_SDO_GEOMETRY_METADATA and ALL_SDO_GEOMETRY_METADATA.

Parameters

owner

Name of the schema that owns the spatial table. Must be uppercase.

table_name

Name of the spatial table (a feature table that has a column of type SDO_GEOMETRY). Must be uppercase.

column_name

Name of the column of type SDO_GEOMETRY. Must be uppercase.

Usage Notes

Deleting the metadata for a spatial table from the geometry metadata views also effectively disables any spatial index based on that metadata (owner, table_name, and column_name).

If you want to restore the metadata after deleting it, you can use the SDO_UTIL.INSERT_SDO_GEOM_METADATA procedure.

To execute this procedure, you must be the owner of the metadata, have DBA privileges, or have the SELECT or INDEX privilege on the table.

To use this procedure on a spatial table in another user’s schema, you must have DBA privileges or the SELECT privilege on that other user’s table. For example, if USER1 wants to insert geometry metadata for the USER2.COLA_MARKETS table, then USER1 must have DBA privileges or the SELECT privilege on the USER2.COLA_MARKETS table.

Examples

The following example deletes metadata for a spatial table named COLA_MARKETS with the geometry column named SHAPE in the USER2 schema, and it thereby disables any spatial index defined on that metadata. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

EXECUTE SDO_UTIL.DELETE_SDO_GEOM_METADATA ('USER2', 'COLA_MARKETS', 'SHAPE');

Related Topics

• Geometry Metadata Views for an explanation of the USER_SDO_GEOM_METADATA and ALL_SDO_GEOM_METADATA views

• SDO_UTIL.INSERT_SDO_GEOM_METADATA procedure

Format

SDO_UTIL.DENSIFY_GEOMETRY(
     geometry  IN SDO_GEOMETRY, 
     interval  IN NUMBER DEFAULT 5000 
     ) RETURN SDO_GEOMETRY;

Description

Densifies the input geometry, based on an interval value.

Parameters

geometry

Geometry object to be densified.

interval

Interval value to be used for the geometry densification. Should be a positive number. (Zero or a negative number causes the input geometry to be returned.) The default is 5000. For a geodetic geometry, the default is 5000 meters.

Usage Notes

This function densifies the input geometry by adding more points so that no line segment is longer than the given interval.

This function is useful when a geodetic long line is to be shown on a planar map by showing the curvature of the great circle interpolation. When displaying geodetic geometries on a flat or planar map, the function helps you see the geodesic path between vertices along a line string or polygon, instead of connecting those vertices with straight lines. The densification is performed along the geodesic path.

Examples

The following example densifies an input geometry. (Descriptive comments are added in the output.)

SELECT SDO_UTIL.DENSIFY_GEOMETRY(
  SDO_GEOMETRY(2004, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1, 5, 1, 1),
  SDO_ORDINATE_ARRAY(-78.24299, 31.50939, -31.99998, 31.51001, -64.6683, 32.38425)), 1000000)
FROM DUAL;

SDO_UTIL.DENSIFY_GEOMETRY(SDO_GEOMETRY(2004,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1,
--------------------------------------------------------------------------------
SDO_GEOMETRY(2004, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1, 13, 1, 1), 
SDO_ORDINATE_ARRAY(-78.24299, 31.50939, -67.77851, 33.0389493, -57.05549, 33.6714211,    
-46.2911, 33.3720604, -35.71139, 32.1574866, -31.99998, 31.51001,  /* 4 points are added inside the long line */ 
-64.6683, 32.38425)) /* single point is still the same as before */

Related Topics

• SDO_GEOM.SDO_ARC_DENSIFY

Format

SDO_UTIL.DROP_WORK_TABLES(
     oidstr IN VARCHAR2);

Description

Drops any transient ("scratch") tables and views in the current schema that were created during the creation of a point cloud or TIN.

Parameters

oidstr

Object ID string representing a hexadecimal number. Use the string given in the error message that indicated that scratch tables need to be dropped.

Usage Notes

If scratch tables still exist from a previous SDO_PC_PKG.CREATE_PC or SDO_TIN_PKG.CREATE_TIN operation when you try to create a point cloud or TIN, an error message is displayed indicating that you must first drop the scratch tables. Use the SDO_UTIL.DROP_WORK_TABLES procedure to drop these scratch tables.

This procedure drops all tables and views that match 'M%_<oidstr>$$%'.

Examples

The following example drops the scratch tables from a previous SDO_PC_PKG.CREATE_PC or SDO_TIN_PKG.CREATE_TIN operation, using an OID string specified in a previous error message.

EXECXUTE SDO_UTIL.DROP_WORK_TABLES('A1B2C3');

Related Topics

• SDO_PC_PKG.CREATE_PC

• SDO_TIN_PKG.CREATE_TIN

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 SDO_GEOM Package (Geometry).)

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 and Graph). 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.CIRCLE_POLYGON

Format

SDO_UTIL.EXPAND_GEOM(
     geometry  IN SDO_GEOMETRY  
     ) RETURN SDO_GEOMETRY;

Description

For a geometry with an exterior ring and/or one or more interior rings, where one or more of the rings are polygons specified in optimized form (optimized rectangles), returns the geometry in a form where all optimized polygon rings are specified as simple polygons (all vertices specified).

Parameters

geometry

Geometry with exterior and/or interior polygon rings specified in optimized form.

Usage Notes

If none of the rings in the input geometry are specified in optimized form (optimized rectangles), the function returns the input geometry.

Simple polygons and optimized rectangles have SDO_ETYPE values of 1003 or 2003 but different SDO_INTERPRETATION values, as explained in SDO_ELEM_INFO.

This function is supported with both 2D and 3D geometries.

This function can be useful if you use any applications that do not work with optimized rectangles, or if you prefer to use simple polygons instead of optimized rectangles.

Examples

The following example uses an input geometry whose exterior and interior polygon rings are optimized rectangles (and in this case, squares): the exterior ring is 8x8, and the interior ring is 2x2. It returns a geometry whose exterior and interior rings are specified as simple polygons.

SELECT sdo_util.expand_geom(sdo_geometry(2003, null, null, -
     sdo_elem_info_array(1,1003,3, 5,2003,3), -
     sdo_ordinate_array(0,0, 8,8, 3,3, 5,5))) FROM DUAL;

SDO_UTIL.EXPAND_GEOM(SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3,5,
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1, 11, 2003, 1), SDO
_ORDINATE_ARRAY(0, 8, 0, 0, 8, 0, 8, 8, 0, 8, 3, 3, 3, 5, 5, 5, 5, 3, 3, 3))

Format

SDO_UTIL.EXTRACT(
     geometry IN SDO_GEOMETRY, 
     element  IN NUMBER, 
     ring     IN NUMBER DEFAULT 0 
     ) RETURN SDO_GEOMETRY;

Description

Returns the two-dimensional geometry that represents a specified element (and optionally a ring) of the input two-dimensional geometry.

Parameters

geometry

Geometry from which to extract the geometry to be returned. Must be a two-dimensional geometry.

element

Number of the element in the geometry: 1 for the first element, 2 for the second element, and so on. Geometries with SDO_GTYPE values (explained in SDO_GTYPE) 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 inPolygon with a Hole, 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 applies to two-dimensional geometries only. For three-dimensional geometries, use the SDO_UTIL.EXTRACT3D function.

This function is useful for extracting a specific element or subelement from a complex geometry. For example, if you have identified a geometry as invalid by using the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function or the SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT procedure (both of which are documented in SDO_GEOM Package (Geometry)), 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 Type 0 (Zero) Element.

This function is not intended for use with geometries that have any null ordinate values. Any null ordinate values in the returned geometry are replaced by 0 (zero).

An exception is raised if element or ring is an invalid number for geometry.

Examples

The following example extracts the first (and only) element in the cola_c geometry. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

SELECT c.name, SDO_UTIL.EXTRACT(c.shape, 1)
   FROM cola_markets c WHERE c.name = 'cola_c';

NAME                                                                            
--------------------------------                                                
SDO_UTIL.EXTRACT(C.SHAPE,1)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_IN
--------------------------------------------------------------------------------
cola_c                                                                          
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(3, 3, 6, 3, 6, 5, 4, 5, 3, 3))

The following example inserts a polygon with a hole (using the same INSERT statement as in Example 2-7 in Polygon with a Hole), 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.EXTRACT3D

• SDO_UTIL.GETVERTICES

• SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT

• SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT

Format

SDO_UTIL.EXTRACT_ALL(
     geometry IN SDO_GEOMETRY, 
     flatten  IN NUMBER DEFAULT 1 
     ) RETURN SDO_GEOMETRY_ARRAY;

Description

Returns all elements and subelements of the input two-dimensional geometry, as an array of one or more geometries. Returns an object of type SDO_GEOMETRY_ARRAY, which is defined as VARRAY OF SDO_GEOMETRY.

Parameters

geometry

Geometry from which to extract all elements and subelements. Must be a two-dimensional geometry.

flatten

A flag indicating whether to "flatten" rings into individual geometries for geometries that contain an exterior ring and one or more interior rings:

• 0 (zero) returns one geometry for each element, but does not flatten rings into individual geometries. (A geometry will still be returned for each element of the input geometry.)

• 1 (the default) or any other nonzero value flattens rings into individual geometries.

For example, if a polygon contains an outer ring and an inner ring, a value of 0 returns a single geometry containing both rings, and a value of 1 returns two geometries, each containing a ring as a geometry.

This parameter is ignored for geometries that do not contain an exterior ring and one or more interior rings.

Usage Notes

This function applies to two-dimensional geometries only. For three-dimensional geometries, use the SDO_UTIL.EXTRACT3D function.

This function enables you to extract all elements and subelements from a geometry, regardless of how many elements and subelements the geometry has. Geometries with SDO_GTYPE values (explained in SDO_GTYPE) ending in 1, 2, or 3 have one element; geometries with SDO_GTYPE values ending in 4, 5, 6, or 7 can have more than one element. For example, a multipolygon with an SDO_GTYPE of 2007 might contain three elements (polygons). To extract individual elements, use the SDO_UTIL.EXTRACT function instead.

For a polygon with one or more holes, with the default value for the flatten parameter, the returned geometry representing an extracted interior ring is reoriented so that its vertices are presented in counterclockwise order (as opposed to the clockwise order within an interior ring). However, if the flatten parameter value is 0, no reorientation is performed.

If geometry is null or has an SDO_GTYPE value ending in 0, this function returns a null geometry.

geometry cannot contain a type 0 (zero) element. Type 0 elements are described in Type 0 (Zero) Element.

This function is not intended for use with geometries that have any null ordinate values. Any null ordinate values in the returned geometry are replaced by 0 (zero).

Examples

The following example extracts all elements from the cola_b geometry. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

SELECT * FROM TABLE(
  SELECT SDO_UTIL.EXTRACT_ALL(c.shape)
    FROM cola_markets c WHERE c.name = 'cola_b');
 
 SDO_GTYPE   SDO_SRID                                                           
---------- ----------                                                           
SDO_POINT(X, Y, Z)                                                              
--------------------------------------------------------------------------------
SDO_ELEM_INFO                                                                   
--------------------------------------------------------------------------------
SDO_ORDINATES                                                                   
--------------------------------------------------------------------------------
      2003                                                                      
                                                                                
SDO_ELEM_INFO_ARRAY(1, 1003, 1)                                                 
SDO_ORDINATE_ARRAY(5, 1, 8, 1, 8, 6, 5, 7, 5, 1)                                

The following example inserts a polygon with a hole (using the same INSERT statement as in Example 2-7 in Polygon with a Hole), and extracts all elements and subelements from the polygon_with_hole geometry. Notice that because the flatten parameter is not specified, in the second geometry returned by the EXTRACT_ALL function the vertices are in counterclockwise order, as opposed to the clockwise order in the hole (second subelement) in the input geometry.

-- Insert polygon with hole.
INSERT INTO cola_markets VALUES(
  10,
  'polygon_with_hole',
  SDO_GEOMETRY(
    2003,  -- two-dimensional polygon
    NULL,
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1), -- polygon with hole
    SDO_ORDINATE_ARRAY(2,4, 4,3, 10,3, 13,5, 13,9, 11,13, 5,13, 2,11, 2,4,
        7,5, 7,10, 10,10, 10,5, 7,5)
  )
);

1 row created.

-- Extract all, with default for flatten.
SELECT * FROM TABLE(
  SELECT SDO_UTIL.EXTRACT_ALL(c.shape)
  FROM cola_markets c WHERE c.name = 'polygon_with_hole');
 
 SDO_GTYPE   SDO_SRID                                                           
---------- ----------                                                           
SDO_POINT(X, Y, Z)                                                              
--------------------------------------------------------------------------------
SDO_ELEM_INFO                                                                   
--------------------------------------------------------------------------------
SDO_ORDINATES                                                                   
--------------------------------------------------------------------------------
      2003                                                                      
                                                                                
SDO_ELEM_INFO_ARRAY(1, 1003, 1)                                                 
SDO_ORDINATE_ARRAY(2, 4, 4, 3, 10, 3, 13, 5, 13, 9, 11, 13, 5, 13, 2, 11, 2, 4) 
                                                                                
 
 SDO_GTYPE   SDO_SRID                                                           
---------- ----------                                                           
SDO_POINT(X, Y, Z)                                                              
--------------------------------------------------------------------------------
SDO_ELEM_INFO                                                                   
--------------------------------------------------------------------------------
SDO_ORDINATES                                                                   
--------------------------------------------------------------------------------
      2003                                                                      
                                                                                
SDO_ELEM_INFO_ARRAY(1, 1003, 1)                                                 
SDO_ORDINATE_ARRAY(7, 5, 10, 5, 10, 10, 7, 10, 7, 5)                            

The following example extracts all elements and subelements from the polygon_with_hole geometry (inserted in the preceding example), and it specifies the flatten parameter value as 0 (zero). This causes the returned array to contain a single geometry that is the same as the input geometry; thus, in the geometry returned by the EXTRACT_ALL function, the vertices are in same clockwise order in the hole (second subelement) as in the input geometry.

-- Extract all, with flatten = 0.
SELECT * FROM TABLE(
  SELECT SDO_UTIL.EXTRACT_ALL(c.shape, 0)
    FROM cola_markets c WHERE c.name = 'polygon_with_hole');
 
 SDO_GTYPE   SDO_SRID                                                           
---------- ----------                                                           
SDO_POINT(X, Y, Z)                                                              
--------------------------------------------------------------------------------
SDO_ELEM_INFO                                                                   
--------------------------------------------------------------------------------
SDO_ORDINATES                                                                   
--------------------------------------------------------------------------------
      2003                                                                      
                                                                                
SDO_ELEM_INFO_ARRAY(1, 1003, 1, 19, 2003, 1)                                    
SDO_ORDINATE_ARRAY(2, 4, 4, 3, 10, 3, 13, 5, 13, 9, 11, 13, 5, 13, 2, 11, 2, 4, 
7, 5, 7, 10, 10, 10, 10, 5, 7, 5)                                               
 
 SDO_GTYPE   SDO_SRID                                                           
---------- ----------                                                           
SDO_POINT(X, Y, Z)                                                              
--------------------------------------------------------------------------------
SDO_ELEM_INFO                                                                   
--------------------------------------------------------------------------------
SDO_ORDINATES                                                                   
--------------------------------------------------------------------------------

Related Topics

• SDO_UTIL.EXTRACT

Format

SDO_UTIL.EXTRACT3D(
     geometry IN SDO_GEOMETRY, 
     label    IN VARCHAR2 
     ) RETURN SDO_GEOMETRY;

Description

Returns the three-dimensional geometry that represents a specified subset of the input three-dimensional geometry.

Parameters

geometry

Geometry from which to extract the geometry to be returned. Must be a three-dimensional geometry

label

A comma-delimited string of numbers that identify the subset geometry to be returned. Each number identifies the relative position of a geometry item within the input geometry. The items and their positions within the label string are:

• pointID: Point number

• edgeID: Edge number

• ringID: Ring number

• polygonID: Polygon number

• csurfID: Composite surface number

• solidID: Solid number

• multiID: Multisolid number

A value of 0 (zero) means that the item does not apply, and you can omit trailing items that do not apply. For example, '0,2,1,4,1' means that point number does not apply, and it specifies the second edge of the first ring of the fourth polygon of the first composite surface.

Usage Notes

This function applies to three-dimensional geometries only. For two-dimensional geometries, use the SDO_UTIL.EXTRACT function.

This function uses the getElementByLabel method of the oracle.spatial.geometry.ElementExtractor Java class, which is described in Oracle Spatial and Graph Java API Reference.

Examples

The following example extracts, from a specified three-dimensional geometry, the subset geometry consisting of the following: edge 2 of ring 1 of polygon 4 of composite surface 1 of the input geometry.

SELECT SDO_UTIL.EXTRACT3D(
  SDO_GEOMETRY (3008,NULL,NULL ,
    SDO_ELEM_INFO_ARRAY(
      1,1007,1,
      1,1006,6,
      1,1003,1,
      16,1003,1,
      31,1003,1,
      46,1003,1,
      61,1003,1,
      76,1003,1),
    SDO_ORDINATE_ARRAY(
      1.0,0.0,-1.0,
      1.0,1.0,-1.0,
      1.0,1.0,1.0,
      1.0,0.0,1.0,
      1.0,0.0,-1.0,
      1.0,0.0,1.0,
      0.0,0.0,1.0,
      0.0,0.0,-1.0,
      1.0,0.0,-1.0,
      1.0,0.0,1.0,
      0.0,1.0,1.0,
      0.0,1.0,-1.0,
      0.0,0.0,-1.0,
      0.0,0.0,1.0,
      0.0,1.0,1.0,
      1.0,1.0,-1.0,
      0.0,1.0,-1.0,
      0.0,1.0,1.0,
      1.0,1.0,1.0,
      1.0,1.0,-1.0,
      1.0,1.0,1.0,
      0.0,1.0,1.0,
      0.0,0.0,1.0,
      1.0,0.0,1.0,
      1.0,1.0,1.0,
      1.0,1.0,-1.0,
      1.0,0.0,-1.0,
      0.0,0.0,-1.0,
      0.0,1.0,-1.0,
      1.0,1.0,-1.0
      )
    ), 
  '0,2,1,4,1') 
FROM DUAL;
 
SDO_UTIL.EXTRACT3D(SDO_GEOMETRY(3008,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1007,1,1,10
--------------------------------------------------------------------------------
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
0, 1, -1, 0, 1, 1))

Related Topics

• SDO_UTIL.EXTRACT

Format

SDO_UTIL.EXTRUDE(
     geometry  IN SDO_GEOMETRY, 
     grdheight IN SDO_NUMBER_ARRAY, 
     height    IN SDO_NUMBER_ARRAY, 
     tol       IN NUMBER, 
     optional3dSrid IN NUMBER DEFAULT NULL 
     ) RETURN SDO_GEOMETRY;

Description

Returns the three-dimensional extrusion solid geometry from an input two-dimensional polygon or multipolygon geometry.

Parameters

geometry

Two-dimensional polygon geometry from which to return the extrusion geometry. This geometry forms the "base" of the returned geometry.

grdheight

Ground heights as a set of Z (height) values at the base of the solid. The numbers in this array should be the Z (height) values at the base of each vertex in the input geometry.

height

Height values for the extrusion geometry. The numbers in this array should be the Z (height) values at the "top" of each corresponding point in the grdheight array. For example, if the ground height at the base of the first vertex is 0 and the height at that vertex is 10, the solid at that point along the base extends 10 units high.

tol

Tolerance value (see Tolerance).

optional3dSrid

Three-dimensional coordinate system (SRID) to be assigned to the returned geometry. If you do not specify this parameter, Spatial and Graph automatically assigns a three-dimensional SRID value based on the SRID value of the input geometry.

Usage Notes

The input geometry must be a two-dimensional polygon or multipolygon geometry.

If the input geometry is a polygon with multiple inner rings, this function internally combines these inner rings to make them one inner ring, producing a new geometry that approximately represents the original appearance; the function then performs the extrusion process on this new geometry, and returns the result.

Examples

The following example returns the three-dimensional solid geometry representing an extrusion from a two-dimensional polygon geometry.

SELECT SDO_UTIL.EXTRUDE(
  SDO_GEOMETRY(
    2003, 
    null,
    null,
    SDO_ELEM_INFO_ARRAY(1,1003,1),
    SDO_ORDINATE_ARRAY(5, 1,8,1,8,6,5,7,5,1)),
  SDO_NUMBER_ARRAY(0,0,0,0,0),
  SDO_NUMBER_ARRAY(5,10,10,5,5),
  0.005) from dual;

SDO_UTIL.EXTRUDE(SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_O
--------------------------------------------------------------------------------
SDO_GEOMETRY(3008, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1007, 1, 1, 1006, 6, 1, 10
03, 1, 16, 1003, 1, 31, 1003, 1, 46, 1003, 1, 61, 1003, 1, 76, 1003, 1), SDO_ORD
INATE_ARRAY(5, 1, 0, 5, 7, 0, 8, 6, 0, 8, 1, 0, 5, 1, 0, 5, 1, 5, 8, 1, 10, 8, 6
, 10, 5, 7, 5, 5, 1, 5, 5, 1, 0, 8, 1, 0, 8, 1, 10, 5, 1, 5, 5, 1, 0, 8, 1, 0, 8
, 6, 0, 8, 6, 10, 8, 1, 10, 8, 1, 0, 8, 6, 0, 5, 7, 0, 5, 7, 5, 8, 6, 10, 8, 6,
0, 5, 7, 0, 5, 1, 0, 5, 1, 5, 5, 7, 5, 5, 7, 0))

The following example returns the three-dimensional composite solid geometry representing an extrusion from a two-dimensional polygon geometry with inner rings.

SELECT SDO_UTIL.EXTRUDE(
  SDO_GEOMETRY(
    2003, 
    null, 
    null,
    SDO_ELEM_INFO_ARRAY(1, 1003, 1, 11, 2003, 1, 
      21, 2003,1, 31,2003,1, 41, 2003, 1),
    SDO_ORDINATE_ARRAY(0,0, 8,0, 8,8, 0,8, 0,0,
      1,3, 1,4, 2,4, 2,3, 1,3, 1,1, 1,2, 2,2, 2,1, 1,1,
      1,6, 1,7, 2,7, 2,6, 1,6, 3,2, 3,4, 4,4, 4,2, 3,2)),
  SDO_NUMBER_ARRAY(-1.0), 
  SDO_NUMBER_ARRAY(1.0), 
  0.0001) from dual;
 
SDO_UTIL.EXTRUDE(SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1,11,200
--------------------------------------------------------------------------------
SDO_GEOMETRY(3008, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1008, 4, 1, 1007, 1, 1, 10
06, 16, 1, 1003, 1, 46, 1003, 1, 91, 1003, 1, 106, 1003, 1, 121, 1003, 1, 136, 1
003, 1, 151, 1003, 1, 166, 1003, 1, 181, 1003, 1, 196, 1003, 1, 211, 1003, 1, 22
6, 1003, 1, 241, 1003, 1, 256, 1003, 1, 271, 1003, 1, 286, 1003, 1, 301, 1007, 1
, 301, 1006, 10, 301, 1003, 1, 328, 1003, 1, 355, 1003, 1, 370, 1003, 1, 385, 10
03, 1, 400, 1003, 1, 415, 1003, 1, 430, 1003, 1, 445, 1003, 1, 460, 1003, 1, 475
, 1007, 1, 475, 1006, 6, 475, 1003, 1, 490, 1003, 1, 505, 1003, 1, 520, 1003, 1,
 535, 1003, 1, 550, 1003, 1, 565, 1007, 1, 565, 1006, 10, 565, 1003, 1, 592, 100
3, 1, 619, 1003, 1, 634, 1003, 1, 649, 1003, 1, 664, 1003, 1, 679, 1003, 1, 694,
 1003, 1, 709, 1003, 1, 724, 1003, 1), SDO_ORDINATE_ARRAY(4, 0, -1, 4, 2, -1, 4,
 4, -1, 3, 4, -1, 2, 4, -1, 2, 7, -1, 1, 7, -1, 1, 6, -1, 1, 4, -1, 1, 3, -1, 0,
 3, -1, 0, 8, -1, 8, 8, -1, 8, 0, -1, 4, 0, -1, 4, 0, 1, 8, 0, 1, 8, 8, 1, 0, 8,
 1, 0, 3, 1, 1, 3, 1, 1, 4, 1, 1, 6, 1, 1, 7, 1, 2, 7, 1, 2, 4, 1, 3, 4, 1, 4, 4
, 1, 4, 2, 1, 4, 0, 1, 4, 0, -1, 8, 0, -1, 8, 0, 1, 4, 0, 1, 4, 0, -1, 8, 0, -1,
 8, 8, -1, 8, 8, 1, 8, 0, 1, 8, 0, -1, 8, 8, -1, 0, 8, -1, 0, 8, 1, 8, 8, 1, 8,
8, -1, 0, 8, -1, 0, 3, -1, 0, 3, 1, 0, 8, 1, 0, 8, -1, 0, 3, -1, 1, 3, -1, 1, 3,
 1, 0, 3, 1, 0, 3, -1, 1, 3, -1, 1, 4, -1, 1, 4, 1, 1, 3, 1, 1, 3, -1, 1, 4, -1,
 1, 6, -1, 1, 6, 1, 1, 4, 1, 1, 4, -1, 1, 6, -1, 1, 7, -1, 1, 7, 1, 1, 6, 1, 1,
6, -1, 1, 7, -1, 2, 7, -1, 2, 7, 1, 1, 7, 1, 1, 7, -1, 2, 7, -1, 2, 4, -1, 2, 4,
 1, 2, 7, 1, 2, 7, -1, 2, 4, -1, 3, 4, -1, 3, 4, 1, 2, 4, 1, 2, 4, -1, 3, 4, -1,
 4, 4, -1, 4, 4, 1, 3, 4, 1, 3, 4, -1, 4, 4, -1, 4, 2, -1, 4, 2, 1, 4, 4, 1, 4,
4, -1, 4, 2, -1, 4, 0, -1, 4, 0, 1, 4, 2, 1, 4, 2, -1, 0, 3, -1, 1, 3, -1, 1, 1,
 -1, 2, 1, -1, 3, 2, -1, 4, 2, -1, 4, 0, -1, 0, 0, -1, 0, 3, -1, 0, 3, 1, 0, 0,
1, 4, 0, 1, 4, 2, 1, 3, 2, 1, 2, 1, 1, 1, 1, 1, 1, 3, 1, 0, 3, 1, 0, 3, -1, 0, 0
, -1, 0, 0, 1, 0, 3, 1, 0, 3, -1, 0, 0, -1, 4, 0, -1, 4, 0, 1, 0, 0, 1, 0, 0, -1
, 4, 0, -1, 4, 2, -1, 4, 2, 1, 4, 0, 1, 4, 0, -1, 4, 2, -1, 3, 2, -1, 3, 2, 1, 4
, 2, 1, 4, 2, -1, 3, 2, -1, 2, 1, -1, 2, 1, 1, 3, 2, 1, 3, 2, -1, 2, 1, -1, 1, 1
, -1, 1, 1, 1, 2, 1, 1, 2, 1, -1, 1, 1, -1, 1, 3, -1, 1, 3, 1, 1, 1, 1, 1, 1, -1
, 1, 3, -1, 0, 3, -1, 0, 3, 1, 1, 3, 1, 1, 3, -1, 1, 6, -1, 2, 6, -1, 2, 4, -1,
1, 4, -1, 1, 6, -1, 1, 6, 1, 1, 4, 1, 2, 4, 1, 2, 6, 1, 1, 6, 1, 1, 6, -1, 1, 4,
 -1, 1, 4, 1, 1, 6, 1, 1, 6, -1, 1, 4, -1, 2, 4, -1, 2, 4, 1, 1, 4, 1, 1, 4, -1,
 2, 4, -1, 2, 6, -1, 2, 6, 1, 2, 4, 1, 2, 4, -1, 2, 6, -1, 1, 6, -1, 1, 6, 1, 2,
 6, 1, 2, 6, -1, 1, 3, -1, 2, 3, -1, 2, 4, -1, 3, 4, -1, 3, 2, -1, 2, 1, -1, 2,
2, -1, 1, 2, -1, 1, 3, -1, 1, 3, 1, 1, 2, 1, 2, 2, 1, 2, 1, 1, 3, 2, 1, 3, 4, 1,
 2, 4, 1, 2, 3, 1, 1, 3, 1, 1, 3, -1, 1, 2, -1, 1, 2, 1, 1, 3, 1, 1, 3, -1, 1, 2
, -1, 2, 2, -1, 2, 2, 1, 1, 2, 1, 1, 2, -1, 2, 2, -1, 2, 1, -1, 2, 1, 1, 2, 2, 1
, 2, 2, -1, 2, 1, -1, 3, 2, -1, 3, 2, 1, 2, 1, 1, 2, 1, -1, 3, 2, -1, 3, 4, -1,
3, 4, 1, 3, 2, 1, 3, 2, -1, 3, 4, -1, 2, 4, -1, 2, 4, 1, 3, 4, 1, 3, 4, -1, 2, 4
, -1, 2, 3, -1, 2, 3, 1, 2, 4, 1, 2, 4, -1, 2, 3, -1, 1, 3, -1, 1, 3, 1, 2, 3, 1
, 2, 3, -1))

Related Topics

None.

Format

SDO_UTIL.FROM_GEOJSON(
     geometry  IN VARCHAR2, 
     crs       IN VARCHAR2 DEFAULT NULL, 
     srid      IN VARCHAR2 DEFAULT 4326 
     ) RETURN SDO_GEOMETRY;

or

SDO_UTIL.FROM_GEOJSON(
     geometry  IN CLOB, 
     crs       IN VARCHAR2 DEFAULT NULL, 
     srid      IN VARCHAR2 DEFAULT 4326 
     ) RETURN SDO_GEOMETRY;

Description

Converts a GeoJSON object (or more specifically a geometry object in GeoJSON format) to a Spatial and Graph geometry object.

Parameters

geometry

Geometry in GeoJSON format to be converted to SDO_GEOMETRY format. The JSON object data type can be VARCHAR2 or CLOB.

crs

(Reserved for future use. The default is null.)

srid

SDO_SRID value to be used in the returned geometry. The default is 4326, which is the EPSG SRID value for the WGS 84 (longitude/latitude) coordinate system.

Usage Notes

The input geometry must be in GeoJSON format. For information about using JSON data that is stored in Oracle Database, see Oracle Database JSON Developer's Guide.

To convert an SDO_GEOMETRY object to GeoJSON format, use the SDO_UTIL.TO_GEOJSON function.

Examples

The following example shows conversion to and from GeoJSON format. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data, specifically the cola_b geometry from the COLA_MARKETS table.) In this example, specifying srid => NULL causes the returned SDO_GEOMETRY object to have an SDO_SRID value of NULL, as opposed to the default of 4326 if the parameter is not specified.

DECLARE
  cola_b_geom SDO_GEOMETRY;
  returned_geom SDO_GEOMETRY;
  returned_json CLOB;

BEGIN

-- Populate geometry variable with cola market cols_b shape.
SELECT c.shape into cola_b_geom FROM cola_markets c
  WHERE c.name = 'cola_b';

-- From geometry to JSON
returned_json := SDO_UTIL.TO_GEOJSON(cola_b_geom);

-- From JSON to geometry
returned_geom := SDO_UTIL.FROM_GEOJSON(returned_json, srid => NULL);

END;
/

Related Topics

• SDO_UTIL.TO_GEOJSON

Format

SDO_UTIL.FROM_GML311GEOMETRY(
     geometry     IN CLOB,  
     srsNamespace IN VARCHAR2 DEFAULT NULL 
     ) RETURN SDO_GEOMETRY;

or

SDO_UTIL.FROM_GML311GEOMETRY(
     geometry     IN CLOB, 
     srsNamespace IN VARCHAR2, 
     coordOrder   IN NUMBER 
     ) RETURN SDO_GEOMETRY;

or

SDO_UTIL.FROM_GML311GEOMETRY(
     geometry  IN VARCHAR2, 
     srsNamespace  IN VARCHAR2 DEFAULT NULL 
     ) RETURN SDO_GEOMETRY;

or

SDO_UTIL.FROM_GML311GEOMETRY(
     geometry  IN VARCHAR2, 
     coordOrder  IN NUMBER DEFAULT NULL 
     ) RETURN SDO_GEOMETRY;

or

SDO_UTIL.FROM_GML311GEOMETRY(
     geometry  IN VARCHAR2, 
     srsNamespace  IN VARCHAR2, 
     coordOrder  IN NUMBER 
     ) RETURN SDO_GEOMETRY;

Description

Converts a geography markup language (GML 3.1.1) fragment to a Spatial and Graph geometry object.

Parameters

geometry

Geometry in GML version 3.1.1 format to be converted to SDO_GEOMETRY format.

srsNamespace

(Reserved for Oracle use.)

coordOrder

If the data in GML format is in latitude/longitude format instead of the longitude/latitude format used by Oracle Spatial, specify 1 for this parameter. Otherwise, do not specify this parameter. (See the Usage Notes for more information.)

Usage Notes

The input geometry must be a valid GML fragment describing a GML version 3.1.1 geometry type defined in the Open GIS Implementation Specification.

Some EPSG geodetic coordinate systems have the axis order reversed in their definition. For such SRIDs, the data in the GML format might come in as latitude/longitude instead of longitude/latitude. If such GML is to be converted to the SDO_GEOMETRY type, the coordOrder parameter should be specified as 1 so that the latitude/longitude values are converted to longitude/latitude, because longitude/latitude is the order used in the SDO_GEOMETRY type.

Examples

The following example shows conversion to and from GML version 3.1.1 format. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data, specifically the cola_b geometry from the COLA_MARKETS table.)

DECLARE
  gmlgeom CLOB;
  geom_result SDO_GEOMETRY;
  geom SDO_GEOMETRY;  
BEGIN
SELECT c.shape INTO geom FROM cola_markets c WHERE c.name = 'cola_b';
 
-- To GML 3.1.1 geometry
gmlgeom := SDO_UTIL.TO_GML311GEOMETRY(geom);
DBMS_OUTPUT.PUT_LINE('To GML 3.1.1 geometry result = ' || TO_CHAR(gmlgeom));
 
-- From GML 3.1.3 geometry
geom_result := SDO_UTIL.FROM_GML311GEOMETRY(gmlgeom);
 
END;
/
To GML 3.1.1 geometry result = <gml:Polygon srsName="SDO:"
xmlns:gml="http://www.opengis.net/gml"><gml:exterior><gml:LinearRing><gml:posLis
t srsDimension="2">5.0 1.0 8.0 1.0 8.0 6.0 5.0 7.0 5.0 1.0
</gml:posList></gml:LinearRing></gml:exterior></gml:Polygon>
 
PL/SQL procedure successfully completed.

Related Topics

• SDO_UTIL.FROM_GMLGEOMETRY

• SDO_UTIL.TO_GML311GEOMETRY

• SDO_UTIL.TO_GMLGEOMETRY

Format

SDO_UTIL.FROM_GMLGEOMETRY(
     geometry     IN CLOB,  
     srsNamespace IN VARCHAR2 DEFAULT NULL 
     ) RETURN SDO_GEOMETRY;

or

SDO_UTIL.FROM_GMLGEOMETRY(
     geometry     IN VARCHAR2, 
     srsNamespace IN VARCHAR2 DEFAULT NULL 
     ) RETURN SDO_GEOMETRY;

Description

Converts a geography markup language (GML 2.0) fragment to a Spatial and Graph geometry object.

Parameters

geometry

Geometry in GML version 2.0 format to be converted to SDO_GEOMETRY format.

srsNamespace

(Reserved for Oracle use.)

Usage Notes

The input geometry must be a valid GML fragment describing a GML version 2.0 geometry type defined in the Open GIS Implementation Specification.

Examples

The following example shows conversion to and from GML version 2.0 format. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data, specifically the cola_b geometry from the COLA_MARKETS table.)

DECLARE
  gmlgeom CLOB;
  geom_result SDO_GEOMETRY;
  geom SDO_GEOMETRY;  
BEGIN
SELECT c.shape INTO geom FROM cola_markets c WHERE c.name = 'cola_b';
 
-- To GML geometry
gmlgeom := SDO_UTIL.TO_GMLGEOMETRY(geom);
DBMS_OUTPUT.PUT_LINE('To GML geometry result = ' || TO_CHAR(gmlgeom));
 
-- From GML geometry
geom_result := SDO_UTIL.FROM_GMLGEOMETRY(gmlgeom);
 
END;
/
To GML geometry result = <gml:Polygon srsName="SDO:"
xmlns:gml="http://www.opengis.net/gml"><gml:outerBoundaryIs><gml:LinearRing><gml
:coordinates decimal="." cs="," ts=" ">5.0,1.0 8.0,1.0 8.0,6.0 5.0,7.0 5.0,1.0
</gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Polygon>
 
PL/SQL procedure successfully completed.

Related Topics

• SDO_UTIL.FROM_GML311GEOMETRY

• SDO_UTIL.TO_GML311GEOMETRY

• SDO_UTIL.TO_GMLGEOMETRY

Format

SDO_UTIL.FROM_JSON(
     geometry  IN VARCHAR2, 
     crs       IN VARCHAR2 DEFAULT NULL, 
     srid      IN VARCHAR2 DEFAULT -1 
     ) RETURN SDO_GEOMETRY;

or

SDO_UTIL.FROM_JSON(
     geometry  IN CLOB, 
     crs       IN VARCHAR2 DEFAULT NULL, 
     srid      IN VARCHAR2 DEFAULT -16 
     ) RETURN SDO_GEOMETRY;

Description

Converts a JSON object (or more specifically a geometry object in JSON format) to a Spatial and Graph geometry object.

Parameters

geometry

Geometry in JSON format to be converted to SDO_GEOMETRY format. The JSON object data type can be VARCHAR2 or CLOB. A geometry in JSON format can also be converted.

crs

(Reserved for future use. The default is null.)

srid

(Reserved for future use. The default is -1.)

Usage Notes

The input geometry must be in JSON format. For information about using JSON data that is stored in Oracle Database, see Oracle Database JSON Developer's Guide.

For information about Spatial and Graph support for JSON, see JSON and GeoJSON Support in Oracle Spatial and Graph.

To convert an SDO_GEOMETRY object to JSON format, use the SDO_UTIL.TO_JSON or SDO_UTIL.TO_JSON_VARCHAR function.

Examples

The following example shows conversion to and from JSON format. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data, specifically the cola_b geometry from the COLA_MARKETS table.)

DECLARE
  cola_b_geom   SDO_GEOMETRY;
  returned_geom SDO_GEOMETRY;
  returned_json CLOB;
BEGIN
  -- Populate geometry variable with cola market cols_b shape.
  SELECT c.shape into cola_b_geom 
  FROM cola_markets c   
  WHERE c.name = 'cola_b';  

  -- From geometry to JSON 
  returned_json := SDO_UTIL.TO_JSON(cola_b_geom);

  -- From JSON to geometry 
  returned_geom := SDO_UTIL.FROM_JSON(returned_json);
  
END; 
/

The following example shows a JSON object that represents a specified geometry being converted back into SDO_GEOMETRY. (In this case the JSON reflects the cola_b geometry from the COLA_MARKETS table, defined in Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

SELECT SDO_UTIL.FROM_JSON(
  '{"polygon": {"boundary": [{"line": {"datapoints": [[5.0, 1.0], 
       [8.0, 1.0], [8.0, 6.0], [5.0, 7.0], [5.0, 1.0]]}}]}}') GEOM
FROM DUAL;

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

• SDO_UTIL.TO_JSON_VARCHAR

Format

SDO_UTIL.FROM_KMLGEOMETRY(
     geometry  IN CLOB  
     ) RETURN SDO_GEOMETRY;

or

SDO_UTIL.FROM_KMLGEOMETRY(
     geometry  IN VARCHAR2 
     ) RETURN SDO_GEOMETRY;

Description

Converts a KML (Keyhole Markup Language) document to a Spatial and Graph geometry object.

Parameters

geometry

Geometry in KML format of type CLOB or VARCHAR2 to be converted to SDO_GEOMETRY format.

Usage Notes

The input geometry must be a valid document conforming to the KML 2.1 specification.

This function does not process the whole KML document; it only processes the KML geometry tags.

Examples

The following example shows conversion to and from KML format. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data, specifically the cola_c geometry from the COLA_MARKETS table.)

-- Convert cola_c geometry to a KML document; convert that result to
-- a spatial geometry.
DECLARE
  kmlgeom CLOB;
  val_result VARCHAR2(5);
  geom_result SDO_GEOMETRY;
  geom SDO_GEOMETRY;
BEGIN
SELECT c.shape INTO geom FROM cola_markets c WHERE c.name = 'cola_c';
 
-- To KML geometry
kmlgeom := SDO_UTIL.TO_KMLGEOMETRY(geom);
DBMS_OUTPUT.PUT_LINE('To KML geometry result = ' || TO_CHAR(kmlgeom));
 
-- From KML geometry
geom_result := SDO_UTIL.FROM_KMLGEOMETRY(kmlgeom);
-- Validate the returned geometry
val_result := SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(geom_result, 0.005);
DBMS_OUTPUT.PUT_LINE('Validation result = ' || val_result);
 
END;
/
To KML geometry result =
<Polygon><extrude>0</extrude><tessellate>0</tessellate><altitudeMode>relativeToG
round</altitudeMode><outerBoundaryIs><LinearRing><coordinates>3.0,3.0 6.0,3.0
6.0,5.0 4.0,5.0 3.0,3.0 </coordinates></LinearRing></outerBoundaryIs></Polygon>
Validation result = TRUE

Related Topics

• SDO_UTIL.TO_KMLGEOMETRY

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 and Graph 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 Simple Example: Inserting_ Indexing_ and Querying Spatial Data, 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

• SDO_UTIL.TO_WKBGEOMETRY

• SDO_UTIL.TO_WKTGEOMETRY

• SDO_UTIL.VALIDATE_WKBGEOMETRY

• SDO_UTIL.VALIDATE_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 and Graph 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 Simple Example: Inserting_ Indexing_ and Querying Spatial Data, 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_WKBGEOMETRY

• SDO_UTIL.TO_WKBGEOMETRY

• SDO_UTIL.TO_WKTGEOMETRY

• SDO_UTIL.VALIDATE_WKBGEOMETRY

• SDO_UTIL.VALIDATE_WKTGEOMETRY

Format

SDO_UTIL.GEO_SEARCH(
     name  IN SDO_VARCHAR2, 
     fuzzy IN NUMBER DEFAULT NULL  
    ) RETURN SDO_GEO_SEARCH_TABLE;

Description

Queries the table with the geographic name hierarchy (ELOC_ADMIN_AREA_SEARCH).

Parameters

name

One or more names from the table with the geographic name hierarchy. Use commas to separate multiple name values.

This table is described in ELOC_ADMIN_AREA_SEARCH Table.

fuzzy

Determines whether Oracle Text fuzzy matching will be used in finding matches for the name value or values. 0 (zero, the default) does not use fuzzy matching; 1 uses fuzzy matching. However, see the Usage Notes for further explanation and examples.

Usage Notes

To use this function, you must understand the concepts in Location Data Enrichment, which also describes the necessary setup actions.

For the fuzzy parameter, if the value is 0 (the default), the values in name must match in spelling the values in the data set for the location, although for a location the data set may permit many variations in spelling and case. If the value is 1, minor errors in name values (like spelling mistakes) will also be considered as matching the location. For example:

• fuzzy=>0 will match ‘nashua, nh, usa’ and ‘nashua, new hampshire, usa’ to the same standard name.

• fuzzy=>1, in addition to matching values included for 0, will match ‘nashuaa,NH,usa’ (where the city name is misspelled) to that same standard name.

Examples

The following example searches for information about San Francisco. It does not use fuzzy matching.

SELECT * from TABLE(sdo_util.geo_search('San Francisco,Ca,UNITED STATES'));

The following example uses fuzzy matching (fuzzy value of 1), and therefore will find matches for San Francisco, California, despite the misspelling of the city name in the name parameter (Sanf Fracisco).

SELECT * from TABLE(sdo_util.geo_search('Sanf Fracisco,Ca,UNITED STATES', 1));

Format

SDO_UTIL.GET_2D_FOOTPRINT(
     geometry  IN SDO_GEOMETRY, 
     tolerance IN NUMBER  DEFAULT 0.0000005  
    ) RETURN SDO_GEOMETRY;

Description

Returns a two-dimensional geometry that reflects the footprint of the input three-dimensional geometry.

Parameters

geometry

Three-dimensional geometry object.

tolerance

Tolerance value (see Tolerance).

Usage Notes

You can use this function to return the 2D (on an x-y plane where z=0) footprint of 3D geometries such as buildings.

Examples

The following example returns the 2D footprint of a 3D geometry. It assumes that a table named FTPTS exists with at least a numeric ID column and a column named GEOMETRY of type SDO_GEOMETRY containing three-dimensional geometries.

SELECT sdo_util.get_2d_footprint(geometry, 0.05) FROM ftpts WHERE id =1;

Format

SDO_UTIL.GETFIRSTVERTEX(
     geometry  IN SDO_GEOMETRY 
     ) RETURN MDSYS.VERTEX_TYPE;

Description

Returns the first coordinate of the vertices of the input geometry.

Parameters

geometry

Input geometry.

Usage Notes

This function returns an object of type MDSYS.VERTEX_TYPE, which is defined as follows:

CREATE TYPE vertex_type AS OBJECT
 (x   NUMBER,
  y   NUMBER,
  z   NUMBER,
  w   NUMBER,
  v5  NUMBER,
  v6  NUMBER,
  v7  NUMBER,
  v8  NUMBER,
  v9  NUMBER,
  v10 NUMBER,
  v11 NUMBER,
  id  NUMBER);

The MYSYS.VERTEX_TYPE type is intended for Oracle use only. Do not use this type in column definitions or in functions that you create.

Examples

This example assumes a table named DATA_2D that has been created and populated as follows:

CREATE TABLE data_2d (geom_name varchar2(12), shape sdo_geometry);

INSERT INTO data_2d VALUES ( 'LINE1',
   sdo_geometry(2002, NULL, NULL,
   sdo_elem_info_array(1,2,1),
   sdo_ordinate_array(12,13, 14,15, 16,17, 18,19, 20,21)));

The following SELECT statement calls both the SDO_UTIL.GetFirstVertex and SDO_UTIL.GetLastVertex functions. The result shows that the first vertex is at (12,13) and the last vertex is at (20,21). (The output is reformatted for readability.)

SELECT geom_name,
       sdo_util.GetFirstVertex(a.shape).X,
       sdo_util.GetFirstVertex(a.shape).Y
FROM data_2d a;

GEOM_NAME    SDO_UTIL.GETFIRSTVERTEX(A.SHAPE).X SDO_UTIL.GETFIRSTVERTEX(A.SHAPE).Y
------------ ---------------------------------- ----------------------------------
LINE1                                        12                                 13


SELECT geom_name,
       sdo_util.GetLastVertex(a.shape).X,
       sdo_util.GetLastVertex(a.shape).Y
FROM data_2d a;

GEOM_NAME    SDO_UTIL.GETLASTVERTEX(A.SHAPE).X SDO_UTIL.GETLASTVERTEX(A.SHAPE).Y
------------ ---------------------------------- ----------------------------------
LINE1                                       20                                21

Related Topics

• SDO_UTIL.GETLASTVERTEX

Format

SDO_UTIL.GETLASTVERTEX(
     geometry  IN SDO_GEOMETRY 
     ) RETURN MDSYS.VERTEX_TYPE;

Description

Returns the last coordinate of the vertices of the input geometry.

Parameters

geometry

Input geometry.

Usage Notes

This function returns an object of type MDSYS.VERTEX_TYPE, which is defined as follows:

CREATE TYPE vertex_type AS OBJECT
 (x   NUMBER,
  y   NUMBER,
  z   NUMBER,
  w   NUMBER,
  v5  NUMBER,
  v6  NUMBER,
  v7  NUMBER,
  v8  NUMBER,
  v9  NUMBER,
  v10 NUMBER,
  v11 NUMBER,
  id  NUMBER);

The MYSYS.VERTEX_TYPE type is intended for Oracle use only. Do not use this type in column definitions or in functions that you create.

Examples

This example assumes a table named DATA_2D that has been created and populated as follows:

CREATE TABLE data_2d (geom_name varchar2(12), shape sdo_geometry);

INSERT INTO data_2d VALUES ( 'LINE1',
   sdo_geometry(2002, NULL, NULL,
   sdo_elem_info_array(1,2,1),
   sdo_ordinate_array(12,13, 14,15, 16,17, 18,19, 20,21)));

The following SELECT statement calls both the SDO_UTIL.GetFirstVertex and SDO_UTIL.GetLastVertex functions. The result shows that the first vertex is at (12,13) and the last vertex is at (20,21). (The output is reformatted for readability.)

SELECT geom_name,
       sdo_util.GetFirstVertex(a.shape).X,
       sdo_util.GetFirstVertex(a.shape).Y
FROM data_2d a;

GEOM_NAME    SDO_UTIL.GETFIRSTVERTEX(A.SHAPE).X SDO_UTIL.GETFIRSTVERTEX(A.SHAPE).Y
------------ ---------------------------------- ----------------------------------
LINE1                                        12                                 13


SELECT geom_name,
       sdo_util.GetLastVertex(a.shape).X,
       sdo_util.GetLastVertex(a.shape).Y
FROM data_2d a;

GEOM_NAME    SDO_UTIL.GETLASTVERTEX(A.SHAPE).X SDO_UTIL.GETLASTVERTEX(A.SHAPE).Y
------------ ---------------------------------- ----------------------------------
LINE1                                       20                                21

Related Topics

• SDO_UTIL.GETFIRSTVERTEX

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 Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

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 Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

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

• SDO_UTIL.GETNUMELEM

Format

SDO_UTIL.GETNURBSAPPROX(
     geometry  IN SDO_GEOMETRY, 
     tolerance IN NUMBER 
     ) RETURN SDO_GEOMETRY;

Description

Returns a line string geometry that is an approximation of the input NURBS curve geometry.

Parameters

geometry

NURBS curve geometry object.

tolerance

Tolerance value (see Tolerance). As long as the tolerance value is valid, it does not affect the operation and output of the function, as explained in the Usage Notes.

Usage Notes

This function is useful for returning a polyline approximation of the input geometry for further processing by subprograms that cannot directly process NURBS curve geometries. The function is called internally by several Oracle Spatial and Graph functions, and it can also be called directly by users.

The input geometry must be a NURBS curve in projected (not geodetic) coordinate system.

If the input geometry does not contain at least one NURBS curve element, the function returns the input geometry.

A tolerance value is required as input because of Oracle Spatial and Graph's internal usage of the function. However, for direct calls to the function by users, the specified tolerance value does not affect the returned polyline, which can have up to approximately 200 points.

The end points of the returned line string geometry are the first and last control points, because a NURBS curve is clamped at its end points.

For information about support for NURBS (non-uniform rational B-spline) geometries, see NURBS Curve Support in Oracle Spatial and Graph.

Examples

The following example creates a spatial table and inserts a NURBS curve geometry, then uses the SDO_UTIL.GETNURBSAPPROX function (with a tolerance value of 0.05) to return a line string geometry that is an approximation of the NURBS curve geometry.

-- Create Table
create table test (gid  INTEGER,
                   geom mdsys.sdo_geometry);
 
-- Insert metadata
Insert into user_sdo_geom_metadata VALUES('TEST', 'GEOM',
  sdo_dim_array( sdo_dim_element('X', -10, 10, 0.05),
                 sdo_dim_element('Y', -10, 10, 0.05)),
                 NULL);
 
-- Two-dimensional NURBS curve with degree 3, 7 control points, and 11 knots
insert into test values(1, SDO_GEOMETRY(2002, NULL, NULL,
                                        SDO_ELEM_INFO_ARRAY(1, 2, 3),
                           SDO_ORDINATE_ARRAY(3, 7, 0, 0, 1, -0.5, 1, 1, 0.2, 2,
                                              1, 0.5, 3.5, 1, 0.8, 2, 1, 0.9, 1,
                                              1, 0.3, 0, 1, 11, 0, 0, 0, 0, 0.25,
                                              0.5, 0.75, 1.0, 1.0, 1.0, 1.0)));
 
-- sdo_util.getNurbsApprox gives an approximate polyline for the NURBS curve.
Select gid, sdo_util.getNurbsApprox(a.geom, 0.05) from test a where gid = 1;
 
       GID
----------
SDO_UTIL.GETNURBSAPPROX(A.GEOM,0.05)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SD
--------------------------------------------------------------------------------
         1
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
0, 0, -.02912839, .059699523, -.05624374, .118211319, -.08139356, .175559751, -.
10462535, .231769184, -.12598662, .286863981, -.14552488, .340868505, -.16328764
, .39380712, -.17932241, .445704191, -.1936767, .496584079, -.20639802, .5464711
5, -.21753387, .595389767, -.22713177, .643364292, -.23523922, .690419091, -.241
90374, .736578527, -.24717284, .781866962, -.25109401, .826308762, -.25371477, .
869928288, -.25508264, .912749906, -.25524512, .954797979, -.25424971, .99609687
, -.25214393, 1.03667094, -.24897529, 1.07654456, -.24479129, 1.11574209, -.2396
3945, 1.15428789, -.23356727, 1.19220633, -.22662227, 1.22952177, -.21885194, 1.
26625857, -.21030381, 1.3024411, -.20102538, 1.33809372, -.19106416, 1.37324079,
 -.18046765, 1.40790668, -.16928338, 1.44211576, -.15755884, 1.47589238, -.14534
154, 1.50926091, -.132679, 1.54224571, -.11961872, 1.57487115, -.10620822, 1.607
16159, -.092495, 1.63914139, -.07852657, 1.67083492, -.06435044, 1.70226654, -.0
5001412, 1.73346062, -.03556511, 1.76444151, -.02105094, 1.79523359, -.0065191,
1.82586121, .007982896, 1.85634874, .022407535, 1.88672054, .036707311, 1.917000
98, .050834714, 1.94721442, .064742236, 1.97738522, .078382506, 2.00753762, .091
725045, 2.03768051, .104772175, 2.06779294, .117529987, 2.09785056, .130004572,
2.12782899, .14220202, 2.15770388, .154128423, 2.18745085, .165789872, 2.2170455
6, .177192457, 2.24646363, .188342269, 2.27568069, .1992454, 2.3046724, .2099079
4, 2.33341438, .22033598, 2.36188226, .23053561, 2.3900517, .240512923, 2.417898
31, .250274008, 2.44539774, .259824957, 2.47252563, .269171861, 2.49925761, .278
32081, 2.52556931, .287277896, 2.55143638, .296049209, 2.57683445, .30464084, 2.
60173916, .31305888, 2.62612614, .321309421, 2.64997102, .329398552, 2.67324946,
 .337332365, 2.69593707, .345116951, 2.71800951, .352758401, 2.7394424, .3602628
05, 2.76021137, .367636255, 2.78029208, .374884841, 2.79966015, .382014654, 2.81
829122, .389031786, 2.83616093, .395942326, 2.85324491, .402752367, 2.8695188, .
409467999, 2.88495824, .416095312, 2.89953885, .422640398, 2.91323629, .42910934
8, 2.92602618, .435508253, 2.93788416, .441843203, 2.94878587, .448120289, 2.958
70695, .454345602, 2.96762302, .460525234, 2.97550973, .466665275, 2.98234271, .
472771816, 2.98809761, .478850948, 2.99275004, .484908761, 2.99627566, .49095134
8, 2.9986501, .496984798, 2.999849, .50301505, 2.999849, .509044541, 2.9986501,
.515072205, 2.99627566, .521096823, 2.99275004, .527117177, 2.98809761, .5331320
5, 2.98234271, .539140223, 2.97550973, .545140477, 2.96762302, .551131595, 2.958
70695, .557112359, 2.94878587, .56308155, 2.93788416, .56903795, 2.92602618, .57
4980341, 2.91323629, .580907505, 2.89953885, .586818223, 2.88495824, .592711277,
 2.8695188, .59858545, 2.85324491, .604439523, 2.83616093, .610272278, 2.8182912
2, .616082496, 2.79966015, .621868959, 2.78029208, .62763045, 2.76021137, .63336
575, 2.7394424, .639073641, 2.71800951, .644752905, 2.69593707, .650402323, 2.67
324946, .656020678, 2.64997102, .661606751, 2.62612614, .667159324, 2.60173916,
.672677178, 2.57683445, .678159097, 2.55143638, .683603861, 2.52556931, .6890102
52, 2.49925761, .694377052, 2.47252563, .699703043, 2.44539774, .704987007, 2.41
789831, .710227725, 2.3900517, .71542398, 2.36188226, .720574553, 2.33341438, .7
25678226, 2.3046724, .730733781, 2.27568069, .735739999, 2.24646363, .740695663,
 2.21704556, .745599554, 2.18745085, .750450454, 2.15770388, .755247146, 2.12782
899, .75998841, 2.09785056, .764673028, 2.06779294, .769299783, 2.03768051, .773
867456, 2.00753762, .778372773, 1.97738522, .782794569, 1.94721442, .787102465,
1.91700098, .791266007, 1.88672054, .795254739, 1.85634874, .799038208, 1.825861
21, .802585958, 1.79523359, .805867536, 1.76444151, .808852487, 1.73346062, .811
510356, 1.70226654, .813810689, 1.67083492, .815723031, 1.63914139, .817216927,
1.60716159, .818261924, 1.57487115, .818827566, 1.54224571, .8188834, 1.50926091
, .818398969, 1.47589238, .817343821, 1.44211576, .8156875, 1.40790668, .8133995
52, 1.37324079, .810449523, 1.33809372, .806806957, 1.3024411, .802441401, 1.266
25857, .797322399, 1.22952177, .791419497, 1.19220633, .784702242, 1.15428789, .
777140177, 1.11574209, .768702849, 1.07654456, .759359802, 1.03667094, .74908058
4, .99609687, .737834738, .954797979, .725591811, .912749906, .712321348, .86992
8288, .697992894, .826308762, .682575995, .781866962, .666040196, .736578527, .6
48355043, .690419091, .629490081, .643364292, .609414855, .595389767, .588098912
, .54647115, .565511797, .496584079, .541623054, .445704191, .51640223, .3938071
2, .48981887, .340868505, .46184252, .286863981, .432442724, .231769184, .401589
029, .175559751, .36925098, .118211319, .335398121, .059699523, .3, 0))
 
1 row selected.

Format

SDO_UTIL.GETVERTICES(
     geometry             IN SDO_GEOMETRY,
     include_oriented_pt  IN NUMBER DEFAULT NULL
     ) 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.

include_oriented_pt

It controls the return of both the point coordinates and the orientation vector as two vertices for an oriented point.

Note:

This is only effective when the input is an oriented point geometry. See Oriented Point for more information about oriented points.

The valid value can be one of the following:

• 0: Returns only the coordinates of the vertices
• 1: Returns both the coordinates and the orientation vector

Usage Notes

This function returns an object of MDSYS.VERTEX_SET_TYPE, which consists of a table of objects of MDSYS.VERTEX_TYPE. Oracle Spatial and Graph defines the type VERTEX_SET_TYPE as:

CREATE TYPE vertex_set_type as TABLE OF vertex_type;

Oracle Spatial and Graph defines the object type VERTEX_TYPE as:

CREATE TYPE vertex_type AS OBJECT
   (x   NUMBER,
    y   NUMBER,
    z   NUMBER,
    w   NUMBER,
    v5  NUMBER,
    v6  NUMBER,
    v7  NUMBER,
    v8  NUMBER,
    v9  NUMBER,
    v10 NUMBER,
    v11 NUMBER,
    id  NUMBER);

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 SDO_GEOM Package (Geometry)), you can use the GETVERTICES function to view the vertices in tabular format.

This function only returns the point coordinates and does not return the orientation vectors when the input is an oriented point geometry. In order to have the orientation vectors also to be returned, you must pass the parameter INCLUDE_ORIENTED_PT set to 1. See the last example in Examples section.

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 Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

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.

The following example returns both, the coordinates and the orientation vector, as two vertices for an oriented point geometry. (This example uses the point geometry created in Example 2-12).

select sdo_util.getvertices(c.shape, 1) from cola_markets c;

SDO_UTIL.GETVERTICES(C.SHAPE,1)(X, Y, Z, W, V5, V6, V7, V8, V9, V10, V11, ID)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
VERTEX_SET_TYPE(VERTEX_TYPE(12, 14, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1), VERTEX_TYPE(.3, .2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2))

Related Topics

• SDO_UTIL.EXTRACT

• SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT

• SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT

• SDO_UTIL.GETNUMVERTICES

Format

SDO_UTIL.INITIALIZE_INDEXES_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 only either (A) the import operation of pre-Release 11.2 dump files is completed, or (B) after the import operation from different endian platform in Release 11.2 or later is completed. Each user that has a spatial index in the tablespace must call the procedure.

For pre-Release 11.2 dump files, after calling the SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS procedure, you must execute a statement in the following format for each index that is in the imported transportable tablespace:

ALTER INDEX spatial-index-from-imported-tts PARAMETERS ('CLEAR_TTS=TRUE');

For detailed information about transportable tablespaces and transporting tablespaces to other databases, see Oracle Database Administrator's Guide.

Examples

The following example for an import of pre-Release 11.2 dump files initializes all spatial indexes in a tablespace that was transported to another database. It also includes the required ALTER INDEX statement for two hypothetical spatial indexes.

CALL SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS;
ALTER INDEX xyz1_spatial_idx PARAMETERS ('CLEAR_TTS=TRUE');
ALTER INDEX xyz2_spatial_idx PARAMETERS ('CLEAR_TTS=TRUE');

In the following example, the owner of the spatial index must call the SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS procedure only if the SELECT statement returns the string Y, to reflect the fact that the spatial indexes are imported from different endian platforms in Release 11.2.

SELECT DECODE(BITAND(sdo_index_version, 1024), 1024, 'Y', 'N') ENDIAN_FLAG
  FROM user_sdo_index_metadata
  WHERE sdo_index_name = :index_name;
-- If the result is 'Y', perform the next statement.
CALL SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS;
-- No ALTER INDEX statements are needed.

In this example, if you call the SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS procedure when the SELECT statement returns the string N, the procedure does nothing because there is no need to perform endian conversion.

Related Topics

None.

Format

SDO_UTIL.INSERT_SDO_GEOM_METADATA(     
  owner       IN   VARCHAR2,
  table_name  IN VARCHAR2,
  column_name IN VARCHAR2,      
  diminfo     IN SDO_DIM_ARRAY,      
  srid        IN NUMBER);      

Description

Adds metadata for a spatial table to the geometry metadata views USER_SDO_GEOMETRY_METADATA and ALL_SDO_GEOMETRY_METADATA.

Parameters

owner

Name of the schema that owns the spatial table. Must be uppercase.

table_name

Name of the spatial table (a feature table that has a column of type SDO_GEOMETRY). Must be uppercase.

column_name

Name of the column of type SDO_GEOMETRY. Must be uppercase.

diminfo

Varying length array of an object type, ordered by dimension, and has one entry for each dimension. (The SDO_DIM_ARRAY type is explained in DIMINFO.)

srid

Either of the following: the SDO_SRID value for the coordinate system for all geometries in the column, or NULL if no specific coordinate system should be associated with the geometries.

Usage Notes

This procedure is an alternative to using the SQL INSERT statement to add metadata for a spatial table to the geometry metadata views. (The use of an INSERT statement to update the USER_SDO_GEOMETRY_METADATA view is shown in Simple Example: Inserting, Indexing, and Querying Spatial Data.)

To use this procedure on a spatial table in another user’s schema, you must have DBA privileges or the SELECT privilege on that other user’s table. For example, if USER1 wants to insert geometry metadata for the USER2.COLA_MARKETS table, then USER1 must have DBA privileges or the SELECT privilege on the USER2.COLA_MARKETS table.

Examples

The following example adds metadata for a spatial table named COLA_MARKETS with the geometry column named SHAPE in the USER2 schema. It also creates the spatial index. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

---------------------------------------------------------------------------
-- UPDATE METADATA VIEWS --
---------------------------------------------------------------------------
-- Add information to the USER_SDO_GEOM_METADATA and USER_SDO_GEOM_METADATA views. This 
-- is required before the spatial index can be created. Do this only once for each layer
-- (that is, table-column combination; here: cola_markets and shape).

EXECUTE SDO_UTIL.INSERT_SDO_GEOM_METADATA ('USER2', 'COLA_MARKETS', 'SHAPE', -
  SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 0, 20, 0.005), -
                SDO_DIM_ELEMENT('Y', 0, 20, 0.005)), - 
  NULL);

-------------------------------------------------------------------
-- CREATE THE SPATIAL INDEX --
-------------------------------------------------------------------

CREATE INDEX cola_spatial_idx
ON cola_markets(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;

Related Topics

• Geometry Metadata Views for an explanation of the USER_SDO_GEOM_METADATA and ALL_SDO_GEOM_METADATA views

• SRID attribute of the SDO_GEOMETRY type

• Coordinate Systems (Spatial Reference Systems) for detailed information about support for coordinate systems

• SDO_UTIL.DELETE_SDO_GEOM_METADATA

Format

SDO_UTIL.INTERIOR_POINT(
     geom IN SDO_GEOMETRY, 
     tol  IN NUMBER  DEFAULT 0.00000000005 
     ) RETURN SDO_GEOMETRY;

Description

Returns a point that is guaranteed to be an interior point (not on the boundary or edge) on the surface of a polygon geometry object.

Parameters

geom

Polygon geometry object. The SDO_GTYPE value of the geometry must be 2003 or 2007. (SDO_GTYPE values are explained in SDO_GTYPE.)

tol

Tolerance value (see Tolerance).

Usage Notes

This function returns a point geometry object representing a point that is guaranteed to be an interior point on the surface, but not on the boundary or edge, of geom. The returned point can be any interior point on the surface; however, if you call the function multiple times with the same geom and tol parameter values, the returned point will be the same.

The relationship between the returned point and the original geometry is INSIDE, which you can check using the SDO_RELATE operator with'mask=inside'.

In most cases this function is more useful than the SDO_GEOM.SDO_POINTONSURFACE function, which returns a point that is not guaranteed to be an interior point.

Examples

The following example returns a geometry object that is an interior point on the surface of cola_a. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

-- Return an interior point on the surface of a geometry.
SELECT SDO_UTIL.INTERIOR_POINT(c.shape, 0.005)
  FROM cola_markets c
  WHERE c.name = 'cola_a';
 
SDO_UTIL.INTERIOR_POINT(C.SHAPE,0.005)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), 
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(2.75, 2.875, NULL), NULL, NULL)

Related Topics

None.

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. To compute the bearing and tilt from a start point to an end point, you can use the SDO_UTIL.BEARING_TILT_FOR_POINTS procedure.

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

• SDO_UTIL.CONVERT_UNIT

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 Simple Example: Inserting_ Indexing_ and Querying Spatial Data), 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.

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

Usage Notes

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

• Duplicate vertices

• Polygon boundary intersecting itself

• Incorrect orientation of exterior or interior rings (or both) of a polygon

If the input geometry has any other problem that makes it invalid, the function raises an exception.

If the input geometry is valid, the function returns a geometry that is identical to the input geometry.

For information about using this function as part of the recommended procedure for loading and validating spatial data, see Recommendations for Loading and Validating Spatial Data.

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

This function internally calls the SDO_GEOM.SDO_SELF_UNION function if necessary.

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 Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

SELECT SDO_UTIL.RECTIFY_GEOMETRY(shape, 0.005)
  FROM COLA_MARKETS c WHERE c.name = 'cola_b';
 
SDO_UTIL.RECTIFY_GEOMETRY(SHAPE,0.005)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), 
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(5, 1, 8, 1, 8, 6, 5, 7, 5, 1))

Related Topics

SDO_UTIL.RECTIFY_GEOMETRY

SDO_GEOM.SDO_SELF_UNION

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

Usage Notes

When two consecutive vertices in a geometry are the same or within the tolerance value associated with the geometry, Spatial and Graph considers the geometry to be invalid. The Spatial and Graph geometry validation functions return the error ORA-13356 in these cases. You can use the REMOVE_DUPLICATE_VERTICES function to change such invalid geometries into valid geometries.

This function also closes polygons so that the first vertex of the ring is the same as the last vertex of the ring.

This function is not supported for any point geometries (including oriented points).

If the input geometry does not contain any duplicate vertices, it is returned.

Examples

The following example removes a duplicate vertex from the input geometry, which is the same geometry as cola_b (see Simple Example: Inserting_ Indexing_ and Querying Spatial Data) 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.

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. (SDO_GTYPE 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 SDO_LRS Package (Linear Referencing System) .

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_LRS.REVERSE_GEOMETRY (in SDO_LRS Package (Linear Referencing System) )

Format

SDO_UTIL.SIMPLIFY(
     geometry     IN SDO_GEOMETRY, 
     threshold    IN NUMBER 
     tolerance    IN NUMBER DEFAULT 0.0000005, 
     remove_loops IN NUMBER DEFAULT 0 
     ) 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 Tolerance). 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.

remove_loops

For some line geometries, when the line is simplified, it might end up with self-crossing loops in the middle. While this is a valid geometry (for lines), in some cases it is not desirable to have these loops in the result of the simplify operation. A value of 0 (the default) does not remove such loops; a value of 1 (or any other nonzero positive number) removes any such loops and always returns simple line segments.

Usage Notes

This function also converts arcs to line stings, eliminates duplicate vertices, and corrects many overlapping edge polygon problems. The reason this function sometimes fixes problems is that it internally calls the SDO_UTIL.RECTIFY_GEOMETRY function at the end of the simplification process to ensure that a valid geometry is returned. However, note that if two perfectly aligned geometries are simplified independently, the geometries might not be aligned after simplification.

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.

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 and Graph meaning of tolerance.)

Compare this function with SDO_UTIL.SIMPLIFYVW, which uses the Visvalingham-Whyatt algorithm.

The returned geometry can be a polygon, line, or point, depending on the geometry definition and the threshold value. The following considerations apply:

• A polygon can simplify to a line or a point and a line can simplify to a point, if the threshold value associated with the geometry is sufficiently large. For example, a thin rectangle will simplify to a line if the distance between the two parallel long sides is less than the threshold value, and a line will simplify to a point if the distance between the start and end points is less than the threshold value.

• In a polygon with a hole, if the exterior ring or the interior ring (the hole) simplifies to a line or a point, the interior ring disappears from (is not included in) the resulting geometry.

• Topological characteristics of the input geometry might not be maintained after simplification. For a collection geometry, the number of elements might increase, to prevent overlapping of individual elements. In all cases, this function will not return an invalid geometry.

This function is not supported for Linear referencing system (LRS) geometries (which are described in Linear Referencing System).

Examples

The following example simplifies a line string geometry that reflects the vertices of the road shown in Figure 7-20 in Example of LRS Functions, although the geometry in this example is not an LRS geometry. With the threshold value as 6, the resulting line string has only three points: the start and end points, and (12, 4,12).

SELECT SDO_UTIL.SIMPLIFY(
  SDO_GEOMETRY(
    2002,  -- line string, 2 dimensions (X,Y)
    NULL,
    NULL,
    SDO_ELEM_INFO_ARRAY(1,2,1), -- one line string, straight segments
    SDO_ORDINATE_ARRAY(
      2,2,   -- Starting point
      2,4,
      8,4,
      12,4,
      12,10,
      8,10,
      5,14)  -- Ending point
  ),
  6, -- threshold value for geometry simplification
  0.5  -- tolerance
) FROM DUAL;

SDO_UTIL.SIMPLIFY(SDO_GEOMETRY(2002,--LINESTRING,2DIMENSIONS(X,Y)NULL,NULL,SDO_E
--------------------------------------------------------------------------------
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
2, 2, 12, 4, 5, 14))

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

SDO_UTIL.SIMPLIFYVW

SDO_UTIL.RECTIFY_GEOMETRY

Format

SDO_UTIL.SIMPLIFYVW(
     geometry         IN SDO_GEOMETRY, 
     vertex_threshold IN NUMBER, 
     tolerance        IN NUMBER DEFAULT 0.0000005, 
     remove_loops     IN NUMBER DEFAULT 0 
     ) RETURN SDO_GEOMETRY;

Description

Simplifies the input geometry, based on a threshold value, using the Visvalingham-Whyatt algorithm.

Parameters

geometry

Geometry to be simplified.

vertex_threshold

Threshold value to be used for the geometry simplification, expressed as a percentage value between 0 and 100. As the value is decreased, the returned geometry is likely to be closer to the input geometry; as the value is increased, fewer points are likely to be in the returned geometry.

You may want to experiment with different values to get the desired level of simplification.

tolerance

Tolerance value (see Tolerance). If you do not specify a value, the default value is 0.0000005.

remove_loops

For some line geometries, when the line is simplified, it might end up with self-crossing loops in the middle. While this is a valid geometry (for lines), in some cases it is not desirable to have these loops in the result of the simplify operation. A value of 0 (the default) does not remove such loops; a value of 1 (or any other nonzero positive number) removes any such loops and always returns simple line segments.

Usage Notes

See the Usage Notes for the SDO_UTIL.SIMPLIFY function, which also simplifies an input geometry but uses a different algorithm (Douglas-Peucker).

Examples

The following example simplifies the same line string geometry used in the example for SDO_UTIL.SIMPLIFY.

SELECT SDO_UTIL.SIMPLIFYVW(
  SDO_GEOMETRY(
    2002,  -- line string, 2 dimensions (X,Y)
    NULL,
    NULL,
    SDO_ELEM_INFO_ARRAY(1,2,1), -- one line string, straight segments
    SDO_ORDINATE_ARRAY(
      2,2,   -- Starting point
      2,4,
      8,4,
      12,4,
      12,10,
      8,10,
      5,14)  -- Ending point
  ),
  80, -- threshold "percentage" value for geometry simplification
  0.5  -- tolerance
) FROM DUAL;

SDO_UTIL.SIMPLIFYVW(SDO_GEOMETRY(2002,--LINESTRING,2DIMENSIONS(X,Y)NULL,NULL,SDO
--------------------------------------------------------------------------------
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
2, 2, 12, 4, 5, 14))

Note that the resulting geometry in this case, using 80 as the vertex_threshold value, has the same points as the example for SDO_UTIL.SIMPLIFY, but without any measure dimension information (that is, the vertices are 2,2, 12,4, and 5,14). A significantly lower vertex_threshold value would probably result in a geometry with more vertices.

Related Topics

SDO_UTIL.SIMPLIFY

SDO_UTIL.RECTIFY_GEOMETRY

Format

SDO_UTIL.THEME3D_GET_BLOCK_TABLE(
     theme_name  IN VARCHAR2 
     ) RETURN NUMBER;

Description

Returns the block table (if any) for a 3D theme (DEM, PC, and TIN themes).

Parameters

theme_name

Name of the 3D theme. Must be a value from the USER_SDO_3DTHEMES or ALL_SDO_3DTHEMES view (described in xxx_SDO_3DTHEMES Views.

Usage Notes

This function returns the name of the block table for the theme, if the theme has an associated block table. If there is no associated block table, the function returns a null value.

Examples

This example does the following for each theme in the USER_SDO_3DTHEMES table: checks if it has multiple LODs and has texture, and returns the block table name. (It assumes that the themes were previously inserted into the USER_SDO_3DTHEMES table.)

SELECT
  name,
  sdo_util.theme3d_has_lod(name) "Has LOD",
  sdo_util.theme3d_has_texture(name) "Has Texture",
  sdo_util.theme3d_get_block_table(name) "Block Table"
FROM user_sdo_3dthemes
ORDER BY name;
 
NAME                                Has LOD Has Texture Block Table
-------------------------------- ---------- ----------- -------------------------
DEM Hawaii Theme 4326                     1           0 DEM_BLOCKS_HAWAII_4326
DEM Hawaii Theme w/ Map Tiles             1           1 DEM_BLOCKS_HAWAII_4326
DEM Splitted Theme                        1           0 DEM_SPLITTED_HAWAII_4326
Geom Theme                                0           0
GeomForDEM Theme                          0           0
GeomForTIN Theme                          0           0
PC Category Theme                         1           0 PC_BLOCKS_CATEGORY
PC Hawaii Theme 4326                      1           0 PC_BLOCKS_HAWAII_4326
PC Intensity Theme                        1           0 PC_BLOCKS_INTENSITY
PC LAS File Theme                         1           0 PC_BLOCKS_LAS
PC RGB Theme                              1           0 PC_BLOCKS_RGB
PC Split Theme                            1           0 PC_SPLIT_BLOCKS_4326
PC Subset Hawaii Theme                    1           0 PC_SUBSET_BLOCKS_4326
PC Theme                                  0           0 PC_BLOCKS_NULL_CRS
TIN Hawaii Theme 4326                     1           0 TIN_BLOCKS_HAWAII_4326
TIN Hawaii Theme w/ Map Tiles             1           1 TIN_BLOCKS_HAWAII_4326
TIN Split Theme                           1           0 TIN_SPLIT_BLOCKS_4326
TIN Subset Hawaii Theme                   1           0 TIN_SUBSET_BLOCKS_4326
 
18 rows selected.

Related Topics

• SDO_UTIL.THEME3D_HAS_LOD

• SDO_UTIL.THEME3D_HAS_TEXTURE

Format

SDO_UTIL.THEME3D_HAS_LOD(
     theme_name  IN VARCHAR2 
     ) RETURN NUMBER;

Description

Checks if a 3D theme has multiple levels of detail (LODs) (for DEM, PC, and TIN themes with pyramiding), or if a theme is involved in a chain of themes at multiple LODs (for SDO_GEOMETRY themes).

Parameters

theme_name

Name of the 3D theme. Must be a value from the USER_SDO_3DTHEMES or ALL_SDO_3DTHEMES view (described in xxx_SDO_3DTHEMES Views.

Usage Notes

This function returns 0 (zero) if the theme does not have multiple LODs or link to a theme with multiple LODs; otherwise, it returns 1.

Examples

This example does the following for each theme in the USER_SDO_3DTHEMES table: checks if it has multiple LODs and has texture, and returns the block table name. (It assumes that the themes were previously inserted into the USER_SDO_3DTHEMES table.)

SELECT
  name,
  sdo_util.theme3d_has_lod(name) "Has LOD",
  sdo_util.theme3d_has_texture(name) "Has Texture",
  sdo_util.theme3d_get_block_table(name) "Block Table"
FROM user_sdo_3dthemes
ORDER BY name;
 
NAME                                Has LOD Has Texture Block Table
-------------------------------- ---------- ----------- -------------------------
DEM Hawaii Theme 4326                     1           0 DEM_BLOCKS_HAWAII_4326
DEM Hawaii Theme w/ Map Tiles             1           1 DEM_BLOCKS_HAWAII_4326
DEM Splitted Theme                        1           0 DEM_SPLITTED_HAWAII_4326
Geom Theme                                0           0
GeomForDEM Theme                          0           0
GeomForTIN Theme                          0           0
PC Category Theme                         1           0 PC_BLOCKS_CATEGORY
PC Hawaii Theme 4326                      1           0 PC_BLOCKS_HAWAII_4326
PC Intensity Theme                        1           0 PC_BLOCKS_INTENSITY
PC LAS File Theme                         1           0 PC_BLOCKS_LAS
PC RGB Theme                              1           0 PC_BLOCKS_RGB
PC Split Theme                            1           0 PC_SPLIT_BLOCKS_4326
PC Subset Hawaii Theme                    1           0 PC_SUBSET_BLOCKS_4326
PC Theme                                  0           0 PC_BLOCKS_NULL_CRS
TIN Hawaii Theme 4326                     1           0 TIN_BLOCKS_HAWAII_4326
TIN Hawaii Theme w/ Map Tiles             1           1 TIN_BLOCKS_HAWAII_4326
TIN Split Theme                           1           0 TIN_SPLIT_BLOCKS_4326
TIN Subset Hawaii Theme                   1           0 TIN_SUBSET_BLOCKS_4326
 
18 rows selected.

Related Topics

• SDO_UTIL.THEME3D_GET_BLOCK_TABLE

• SDO_UTIL.THEME3D_HAS_TEXTURE

Format

SDO_UTIL.THEME3D_HAS_TEXTURE(
     theme_name  IN VARCHAR2 
     ) RETURN NUMBER;

Description

Checks if a 3D theme has textures (for DEM, TIN, map tile server, and SDO_GEOMETRY themes).

Parameters

theme_name

Name of the 3D theme. Must be a value from the USER_SDO_3DTHEMES or ALL_SDO_3DTHEMES view (described in xxx_SDO_3DTHEMES Views.

Usage Notes

This function returns 0 (zero) if the theme does not have textures; otherwise, it returns 1.

Examples

This example does the following for each theme in the USER_SDO_3DTHEMES table: checks if it has multiple LODs and has texture, and returns the block table name. (It assumes that the themes were previously inserted into the USER_SDO_3DTHEMES table.)

SELECT
  name,
  sdo_util.theme3d_has_lod(name) "Has LOD",
  sdo_util.theme3d_has_texture(name) "Has Texture",
  sdo_util.theme3d_get_block_table(name) "Block Table"
FROM user_sdo_3dthemes
ORDER BY name;
 
NAME                                Has LOD Has Texture Block Table
-------------------------------- ---------- ----------- -------------------------
DEM Hawaii Theme 4326                     1           0 DEM_BLOCKS_HAWAII_4326
DEM Hawaii Theme w/ Map Tiles             1           1 DEM_BLOCKS_HAWAII_4326
DEM Splitted Theme                        1           0 DEM_SPLITTED_HAWAII_4326
Geom Theme                                0           0
GeomForDEM Theme                          0           0
GeomForTIN Theme                          0           0
PC Category Theme                         1           0 PC_BLOCKS_CATEGORY
PC Hawaii Theme 4326                      1           0 PC_BLOCKS_HAWAII_4326
PC Intensity Theme                        1           0 PC_BLOCKS_INTENSITY
PC LAS File Theme                         1           0 PC_BLOCKS_LAS
PC RGB Theme                              1           0 PC_BLOCKS_RGB
PC Split Theme                            1           0 PC_SPLIT_BLOCKS_4326
PC Subset Hawaii Theme                    1           0 PC_SUBSET_BLOCKS_4326
PC Theme                                  0           0 PC_BLOCKS_NULL_CRS
TIN Hawaii Theme 4326                     1           0 TIN_BLOCKS_HAWAII_4326
TIN Hawaii Theme w/ Map Tiles             1           1 TIN_BLOCKS_HAWAII_4326
TIN Split Theme                           1           0 TIN_SPLIT_BLOCKS_4326
TIN Subset Hawaii Theme                   1           0 TIN_SUBSET_BLOCKS_4326
 
18 rows selected.

Related Topics

• SDO_UTIL.THEME3D_GET_BLOCK_TABLE

• SDO_UTIL.THEME3D_HAS_LOD

Format

SDO_UTIL.TO_GEOJSON(
     geometry  IN SDO_GEOMETRY 
     ) RETURN CLOB;

Description

Converts an SDO_GEOMETRY object to a geometry of type CLOB in GeoJSON format.

Parameters

geometry

Geometry in SDO_GEOMETRY format to be converted to a GeoJSON object.

Usage Notes

For information about using JSON data that is stored in Oracle Database, see Oracle Database JSON Developer's Guide.

To convert a geometry in GeoJSON format to an SDO_GEOMETRY object, use the SDO_UTIL.FROM_GEOJSON function.

Examples

The following example shows conversion to and from JSON format. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data, specifically the cola_b geometry from the COLA_MARKETS table.)

DECLARE
  cola_b_geom SDO_GEOMETRY;
  returned_geom SDO_GEOMETRY;
  returned_json CLOB;

BEGIN

-- Populate geometry variable with cola market cols_b shape.
SELECT c.shape into cola_b_geom FROM cola_markets c
  WHERE c.name = 'cola_b';

-- From geometry to JSON
returned_json := SDO_UTIL.TO_GEOJSON(cola_b_geom);

-- From JSON to geometry
returned_geom := SDO_UTIL.FROM_GEOJSON(returned_json);

END;
/

The following example shows the GeoJSON object that represents a specified geometry. (In this case the geometry definition reflects the cola_b geometry from the COLA_MARKETS table, defined in Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

SELECT SDO_UTIL.TO_GEOJSON(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))) from DUAL;

SDO_UTIL.TO_GEOJSON(SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SD
--------------------------------------------------------------------------------
{ "type": "Polygon", "coordinates": [ [ [5, 1], [8, 1], [8, 6], [5, 7], [5, 1] ]

Related Topics

• SDO_UTIL.FROM_GEOJSON

Format

SDO_UTIL.TO_GML311GEOMETRY(
     geometry  IN SDO_GEOMETRY 
     ) RETURN CLOB;

Description

Converts a Spatial and Graph geometry object to a geography markup language (GML version 3.1.1) fragment based on the geometry types defined in the Open GIS geometry.xsd schema document.

Parameters

geometry

Geometry for which to return the GML version 3.1.1 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 version 3.1.1 fragment based on some GML geometry types defined in the Open GIS Implementation Specification.

Polygons must be defined using the conventions for Oracle9i and later releases of Spatial and Graph. 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 version 3.1.1 fragment for the cola_b geometry in the COLA_MARKETS table. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

-- Convert cola_b geometry to GML 3.1.1 fragment.
SELECT TO_CHAR(SDO_UTIL.TO_GML311GEOMETRY(shape)) AS Gml311Geometry 
  FROM COLA_MARKETS c WHERE c.name = 'cola_b';

GML311GEOMETRY
--------------------------------------------------------------------------------
<gml:Polygon srsName="SDO:" xmlns:gml="http://www.opengis.net/gml"><gml:exterior
><gml:LinearRing><gml:posList srsDimension="2">5.0 1.0 8.0 1.0 8.0 6.0 5.0 7.0 5
.0 1.0 </gml:posList></gml:LinearRing></gml:exterior></gml:Polygon>

The following example returns the GML version 3.1.1 fragment for the arc densification of the cola_d geometry in the COLA_MARKETS table. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

SET LONG 40000
SELECT XMLTYPE(SDO_UTIL.TO_GML311GEOMETRY(
  SDO_GEOM.SDO_ARC_DENSIFY(c.shape, m.diminfo, 'arc_tolerance=0.05')))
    AS Gml311Geometry FROM cola_markets c, user_sdo_geom_metadata m 
    WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' 
    AND c.name = 'cola_d';

GML311GEOMETRY                                                                  
--------------------------------------------------------------------------------
<gml:Polygon srsName="SDO:" xmlns:gml="http://www.opengis.net/gml">             
  <gml:exterior>                                                                
    <gml:LinearRing>                                                            
      <gml:posList srsDimension="2">8.0 7.0 8.76536686473018 7.15224093497743 9.
4142135623731 7.58578643762691 9.84775906502257 8.23463313526982 10.0 9.0 9.8477
5906502257 9.76536686473018 9.4142135623731 10.4142135623731 8.76536686473018 10
.8477590650226 8.0 11.0 7.23463313526982 10.8477590650226 6.58578643762691 10.41
42135623731 6.15224093497743 9.76536686473018 6.0 9.0 6.15224093497743 8.2346331
3526982 6.58578643762691 7.5857864376269 7.23463313526982 7.15224093497743 8.0 7
.0 </gml:posList>                                                               
    </gml:LinearRing>                                                           
  </gml:exterior>                                                               
</gml:Polygon>

The following example converts an LRS geometry to a standard geometry and returns the GML version 3.1.1 fragment for the geometry. (The example uses the definitions and data from Example of LRS Functions.)

SET LONG 40000
-- Convert LRS grometry to standard geometry before using TO_GML311GEOMETRY.
SELECT XMLTYPE(SDO_UTIL.TO_GML311GEOMETRY(
  SDO_LRS.CONVERT_TO_STD_GEOM(route_geometry)))
  AS Gml311Geometry FROM lrs_routes a WHERE a.route_id = 1;
 
GML311GEOMETRY                                                                  
--------------------------------------------------------------------------------
<gml:Curve srsName="SDO:" xmlns:gml="http://www.opengis.net/gml">               
  <gml:segments>                                                                
    <gml:LineStringSegment>                                                     
      <gml:posList srsDimension="2">2.0 2.0 2.0 4.0 8.0 4.0 12.0 4.0 12.0 10.0 8
.0 10.0 5.0 14.0 </gml:posList>                                                 
    </gml:LineStringSegment>                                                    
  </gml:segments>                                                               
</gml:Curve>

The following examples return GML version 3.1.1 fragments for a variety of geometry types.

-- Point geometry with coordinates in SDO_ORDINATES. Note the
--    coordinates in the GML are (10.0 10.0) and the values in the
--    SDO_POINT field are discarded.
SELECT TO_CHAR(
  SDO_UTIL.TO_GML311GEOMETRY(sdo_geometry(2001, 8307,
    sdo_point_type(-80, 70, null),
    sdo_elem_info_array(1,1,1), sdo_ordinate_array(10, 10)))
)
AS Gml311Geometry FROM DUAL;
 
GML311GEOMETRY                                                                  
--------------------------------------------------------------------------------
<gml:Point srsName="SDO:8307" xmlns:gml="http://www.opengis.net/gml"><gml:posLis
t srsDimension="2">10.0 10.0 </gml:posList></gml:Point>                         
                                                                                
 
-- Multipolygon
SET LONG 40000
SELECT SDO_UTIL.TO_GML311GEOMETRY(
  sdo_geometry(2007, 8307, null,
   sdo_elem_info_array(1,1003,1, 13,1003,1, 23,1003,3),
   sdo_ordinate_array(10.10,10.20, 20.50,20.10, 30.30,30.30, 40.10,40.10,
     30.50, 30.20, 10.10, 10.20,
     5,5, 5,6, 6,6, 6,5, 5,5, 7,7, 8,8 ))
)
AS Gml311Geometry FROM DUAL;
 
GML311GEOMETRY                                                                  
--------------------------------------------------------------------------------
<gml:MultiSurface srsName="SDO:8307" xmlns:gml="http://www.opengis.net/gml"><gml
:surfaceMember><gml:Polygon><gml:exterior><gml:LinearRing><gml:posList srsDimens
ion="2">10.1 10.2 20.5 20.1 30.3 30.3 40.1 40.1 30.5 30.2 10.1 10.2 </gml:posLis
t></gml:LinearRing></gml:exterior></gml:Polygon></gml:surfaceMember><gml:surface
Member><gml:Polygon><gml:exterior><gml:LinearRing><gml:posList srsDimension="2">
5.0 5.0 5.0 6.0 6.0 6.0 6.0 5.0 5.0 5.0 </gml:posList></gml:LinearRing></gml:ext
erior></gml:Polygon></gml:surfaceMember><gml:surfaceMember><gml:Polygon><gml:ext
erior><gml:LinearRing><gml:posList srsDimension="2">7.0 7.0 8.0 7.0 8.0 8.0 7.0 
8.0 7.0 7.0 </gml:posList></gml:LinearRing></gml:exterior></gml:Polygon></gml:su
rfaceMember></gml:MultiSurface>                                                 
                                                                                
SET LONG 80
-- Rectangle (geodetic)
SELECT TO_CHAR(
  SDO_UTIL.TO_GML311GEOMETRY(sdo_geometry(2003, 8307, null,
   sdo_elem_info_array(1,1003,3),
    sdo_ordinate_array(10.10,10.10, 20.10,20.10 )))
)
AS Gml311Geometry FROM DUAL;
 
GML311GEOMETRY                                                                  
--------------------------------------------------------------------------------
<gml:Polygon srsName="SDO:8307" xmlns:gml="http://www.opengis.net/gml"><gml:exte
rior><gml:LinearRing><gml:posList srsDimension="2">10.1 10.1 20.1 10.1 20.1 20.1
 10.1 20.1 10.1 10.1 </gml:posList></gml:LinearRing></gml:exterior></gml:Polygon
>

-- Polygon with holes
SELECT TO_CHAR(
  SDO_UTIL.TO_GML311GEOMETRY(sdo_geometry(2003, 262152, null,
   sdo_elem_info_array(1,1003,3, 5, 2003, 1, 13, 2003, 1),
   sdo_ordinate_array(10.10,10.20, 40.50, 41.10, 30.30, 30.30, 30.30,
     40.10, 40.10, 40.10, 30.30, 30.30, 5, 5, 5, 6, 6, 6, 6, 5, 5, 5 )))
)
AS Gml311Geometry FROM DUAL;
 
GML311GEOMETRY                                                                  
--------------------------------------------------------------------------------
<gml:Polygon srsName="SDO:262152" xmlns:gml="http://www.opengis.net/gml"><gml:ex
terior><gml:LinearRing><gml:posList srsDimension="2">10.1 10.2 40.5 10.2 40.5 41
.1 10.1 41.1 10.1 10.2 </gml:posList></gml:LinearRing></gml:exterior><gml:interi
or><gml:LinearRing><gml:posList srsDimension="2">30.3 30.3 30.3 40.1 40.1 40.1 3
0.3 30.3 </gml:posList></gml:LinearRing></gml:interior><gml:interior><gml:Linear
Ring><gml:posList srsDimension="2">5.0 5.0 5.0 6.0 6.0 6.0 6.0 5.0 5.0 5.0 </gml
:posList></gml:LinearRing></gml:interior></gml:Polygon>                         
                                                                                
 
-- Creating an XMLTYPE from the GML fragment. Also useful for "pretty
--  printing" the GML output.
SET LONG 40000
SELECT XMLTYPE(
  SDO_UTIL.TO_GML311GEOMETRY(sdo_geometry(2003, 262152, null,
   sdo_elem_info_array(1,1003,1, 11, 2003, 1, 21, 2003, 1),
   sdo_ordinate_array(10.10,10.20, 40.50,10.2, 40.5,41.10, 10.1,41.1,
     10.10, 10.20, 30.30,30.30, 30.30, 40.10, 40.10, 40.10, 40.10, 30.30,
     30.30, 30.30, 5, 5, 5, 6, 6, 6, 6, 5, 5, 5 )))
)
AS Gml311Geometry FROM DUAL;
 
GML311GEOMETRY                                                                  
--------------------------------------------------------------------------------
<gml:Polygon srsName="SDO:262152" xmlns:gml="http://www.opengis.net/gml">       
  <gml:exterior>                                                                
    <gml:LinearRing>                                                            
      <gml:posList srsDimension="2">10.1 10.2 40.5 10.2 40.5 41.1 10.1 41.1 10.1
 10.2 </gml:posList>                                                            
    </gml:LinearRing>                                                           
  </gml:exterior>                                                               
  <gml:interior>                                                                
    <gml:LinearRing>                                                            
      <gml:posList srsDimension="2">30.3 30.3 30.3 40.1 40.1 40.1 40.1 30.3 30.3
 30.3 </gml:posList>                                                            
 
GML311GEOMETRY                                                                  
--------------------------------------------------------------------------------
    </gml:LinearRing>                                                           
  </gml:interior>                                                               
  <gml:interior>                                                                
    <gml:LinearRing>                                                            
      <gml:posList srsDimension="2">5.0 5.0 5.0 6.0 6.0 6.0 6.0 5.0 5.0 5.0 </gm
l:posList>                                                                      
    </gml:LinearRing>                                                           
  </gml:interior>                                                               
</gml:Polygon>                                                                  

Related Topics

SDO_UTIL.TO_GMLGEOMETRY

Format

SDO_UTIL.TO_GMLGEOMETRY(
     geometry  IN SDO_GEOMETRY 
     ) RETURN CLOB;

or

SDO_UTIL.TO_GML311GEOMETRY(
     geometry   IN SDO_GEOMETRY, 
     coordOrder IN NUMBER 
     ) RETURN CLOB;

Description

Converts a Spatial and Graph 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

Geometry for which to return the GML fragment.

coordOrder

(Reserved for Oracle use.)

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.

Polygons must be defined using the conventions for Oracle9i and later releases of Spatial and Graph. 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 Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

-- 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 Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

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 Example of LRS Functions.)

SET LONG 40000
-- Convert LRS geometry to standard geometry before using TO_GMLGEOMETRY.
SELECT XMLTYPE(SDO_UTIL.TO_GMLGEOMETRY(
  SDO_LRS.CONVERT_TO_STD_GEOM(route_geometry)))
  AS GmlGeometry FROM lrs_routes a WHERE a.route_id = 1;

GMLGEOMETRY
--------------------------------------------------------------------------------
<gml:LineString srsName="SDO:" xmlns:gml="http://www.opengis.net/gml">          
  <gml:coordinates decimal="." cs="," ts=" ">2,2 2,4 8,4 12,4 12,10 8,10 5,14 </
gml:coordinates>                                                                
</gml:LineString>

The following examples return GML fragments for a variety of geometry types.

-- Point geometry with coordinates in SDO_ORDINATES. Note the
-- coordinates in the GML are (10,10) and the values in the
-- SDO_POINT field are discarded.
SELECT TO_CHAR(
  SDO_UTIL.TO_GMLGEOMETRY(sdo_geometry(2001, 8307,
    sdo_point_type(-80, 70, null),
    sdo_elem_info_array(1,1,1), sdo_ordinate_array(10, 10)))
)
AS GmlGeometry FROM DUAL;

GMLGEOMETRY                                                                     
--------------------------------------------------------------------------------
<gml:Point srsName="SDO:8307" xmlns:gml="http://www.opengis.net/gml"><gml:coordi
nates decimal="." cs="," ts=" ">10,10 </gml:coordinates></gml:Point>            
                 

-- Multipolygon
SET LONG 40000
SELECT SDO_UTIL.TO_GMLGEOMETRY(
  sdo_geometry(2007, 8307, null,
    sdo_elem_info_array(1,1003,1, 13,1003,1, 23,1003,3),
    sdo_ordinate_array(10.10,10.20, 20.50,20.10, 30.30,30.30, 40.10,40.10,
      30.50, 30.20, 10.10, 10.20,
      5,5, 5,6, 6,6, 6,5, 5,5, 7,7, 8,8 ))
 )
 AS GmlGeometry FROM DUAL;
 
GMLGEOMETRY                                                                     
--------------------------------------------------------------------------------
<gml:MultiPolygon srsName="SDO:8307" xmlns:gml="http://www.opengis.net/gml"><gml
:polygonMember><gml:Polygon><gml:outerBoundaryIs><gml:LinearRing><gml:coordinate
s decimal="." cs="," ts=" ">10.1,10.2 20.5,20.1 30.3,30.3 40.1,40.1 30.5,30.2 10
.1,10.2 </gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Polygon><
/gml:polygonMember><gml:polygonMember><gml:Polygon><gml:outerBoundaryIs><gml:Lin
earRing><gml:coordinates decimal="." cs="," ts=" ">5.0,5.0 5.0,6.0 6.0,6.0 6.0,5
.0 5.0,5.0 </gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Polygo
n></gml:polygonMember><gml:polygonMember><gml:Polygon><gml:outerBoundaryIs><gml:
LinearRing><gml:coordinates decimal="." cs="," ts=" ">7.0,7.0 8.0,7.0 8.0,8.0 7.
0,8.0 7.0,7.0 </gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Pol
ygon></gml:polygonMember></gml:MultiPolygon>                                    

SQL> SET LONG 80

-- Rectangle (geodetic)
SELECT TO_CHAR(
  SDO_UTIL.TO_GMLGEOMETRY(sdo_geometry(2003, 8307, null,
    sdo_elem_info_array(1,1003,3),
    sdo_ordinate_array(10.10,10.10, 20.10,20.10 )))
) 
AS GmlGeometry FROM DUAL;

GMLGEOMETRY                                                                     
--------------------------------------------------------------------------------
<gml:Box srsName="SDO:8307" xmlns:gml="http://www.opengis.net/gml"><gml:coordina
tes decimal="." cs="," ts=" ">10.1,10.1 20.1,20.1 </gml:coordinates></gml:Box>  
                                                                                

-- Polygon with holes
SELECT TO_CHAR(
  SDO_UTIL.TO_GMLGEOMETRY(sdo_geometry(2003, 262152, null,
    sdo_elem_info_array(1,1003,3, 5, 2003, 1, 13, 2003, 1),
    sdo_ordinate_array(10.10,10.20, 40.50, 41.10, 30.30, 30.30, 30.30,
      40.10, 40.10, 40.10, 30.30, 30.30, 5, 5, 5, 6, 6, 6, 6, 5, 5, 5 )))
)
AS GmlGeometry FROM DUAL;

GMLGEOMETRY                                                                     
--------------------------------------------------------------------------------
<gml:Polygon srsName="SDO:262152" xmlns:gml="http://www.opengis.net/gml"><gml:ou
terBoundaryIs><gml:LinearRing><gml:coordinates decimal="." cs="," ts=" ">10.1,10
.2, 40.5,10.2, 40.5,41.1, 10.1,41.1, 10.1,10.2 </gml:coordinates></gml:LinearRin
g></gml:outerBoundaryIs><gml:innerBoundaryIs><gml:LinearRing><gml:coordinates de
cimal="." cs="," ts=" ">30.3,30.3 30.3,40.1 40.1,40.1 30.3,30.3 </gml:coordinate
s></gml:LinearRing></gml:innerBoundaryIs><gml:innerBoundaryIs><gml:LinearRing><g
ml:coordinates decimal="." cs="," ts=" ">5,5 5,6 6,6 6,5 5,5 </gml:coordinates><
/gml:LinearRing></gml:innerBoundaryIs></gml:Polygon>                            
                                                                                

-- Creating an XMLTYPE from the GML fragment. Also useful for "pretty
-- printing" the GML output.
SET LONG 40000
SELECT XMLTYPE(
  SDO_UTIL.TO_GMLGEOMETRY(sdo_geometry(2003, 262152, null,
    sdo_elem_info_array(1,1003,1, 11, 2003, 1, 21, 2003, 1),
    sdo_ordinate_array(10.10,10.20, 40.50,10.2, 40.5,41.10, 10.1,41.1,
      10.10, 10.20, 30.30,30.30, 30.30, 40.10, 40.10, 40.10, 40.10, 30.30,
      30.30, 30.30, 5, 5, 5, 6, 6, 6, 6, 5, 5, 5 )))
)
AS GmlGeometry FROM DUAL;

GMLGEOMETRY
--------------------------------------------------------------------------------
<gml:Polygon srsName="SDO:262152" xmlns:gml="http://www.opengis.net/gml"><gml:ou
terBoundaryIs><gml:LinearRing><gml:coordinates decimal="." cs="," ts=" ">10.1,10
.2 40.5,10.2 40.5,41.1 10.1,41.1 10.1,10.2 </gml:coordinates></gml:LinearRing></
gml:outerBoundaryIs><gml:innerBoundaryIs><gml:LinearRing><gml:coordinates decima
l="." cs="," ts=" ">30.3,30.3 30.3,40.1 40.1,40.1 40.1,30.3 30.3,30.3 </gml:coor
dinates></gml:LinearRing></gml:innerBoundaryIs><gml:innerBoundaryIs><gml:LinearR
ing><gml:coordinates decimal="." cs="," ts=" ">5,5 5,6 6,6 6,5 5,5 </gml:coordin
ates></gml:LinearRing></gml:innerBoundaryIs></gml:Polygon> 

The following example uses the TO_GMLGEOMETRY function with the Oracle XML DB XMLTYPE data type and the XMLELEMENT and XMLFOREST functions.

SELECT xmlelement("State", xmlattributes(
  'http://www.opengis.net/gml' as "xmlns:gml"),
  xmlforest(state as "Name", totpop as "Population",
  xmltype(sdo_util.to_gmlgeometry(geom)) as "gml:geometryProperty"))
  AS theXMLElements FROM states WHERE state_abrv in ('DE', 'UT');

THEXMLELEMENTS
--------------------------------------------------------------------------------
<State xmlns:gml="http://www.opengis.net/gml">
  <Name>Delaware</Name>
  <Population>666168</Population>
  <gml:geometryProperty>
    <gml:Polygon srsName="SDO:" xmlns:gml="http://www.opengis.net/gml">
      <gml:outerBoundaryIs>
        <gml:LinearRing>
          <gml:coordinates decimal="." cs="," ts=" ">-75.788704,39.721699 -75.78
8704,39.6479 -75.767014,39.377106 -75.76033,39.296497 -75.756294,39.24585 -75.74
8016,39.143196 -75.722961,38.829895 -75.707695,38.635166 -75.701912,38.560619 -7
5.693871,38.460011 -75.500336,38.454002 -75.341614,38.451855 -75.049339,38.45165
3 -75.053841,38.538429 -75.06015,38.605465 -75.063263,38.611275 -75.065308,38.62
949 -75.065887,38.660919 -75.078697,38.732403 -75.082527,38.772045 -75.091667,38
.801208 -75.094185,38.803699 -75.097572,38.802986 -75.094116,38.793579 -75.09926
6,38.78756 -75.123619,38.781784 -75.137962,38.782703 -75.18692,38.803772 -75.215
019,38.831547 -75.23735,38.849014 -75.260498,38.875 -75.305908,38.914673 -75.316
399,38.930309 -75.317284,38.93676 -75.312851,38.945576 -75.312859,38.945618 -75.
31205,38.967804 -75.31778,38.986012 -75.341431,39.021233 -75.369606,39.041359 -7
5.389229,39.051422 -75.40181,39.06702 -75.401306,39.097713 -75.411369,39.148029
-75.407845,39.175201 -75.396271,39.187778 -75.39225,39.203377 -75.40181,39.23104
9 -75.402817,39.253189 -75.409355,39.264759 -75.434006,39.290424 -75.439041,39.3
13065 -75.453125,39.317093 -75.457657,39.326653 -75.469231,39.330677 -75.486336,
39.341743 -75.494888,39.354324 -75.504448,39.357346 -75.51284,39.366291 -75.5129
24,39.366482 -75.523773,39.392052 -75.538651,39.415707 -75.56749,39.436436 -75.5
9137,39.463696 -75.592941,39.471806 -75.590019,39.488026 -75.587311,39.496136 -7
5.5774,39.508076 -75.554192,39.506947 -75.528442,39.498005 -75.530373,39.510303
-75.527145,39.531326 -75.52803,39.535168 -75.53437,39.540592 -75.519386,39.55528
6 -75.512291,39.567505 -75.515587,39.580639 -75.528046,39.584 -75.538269,39.5935
67 -75.554016,39.601727 -75.560143,39.622578 -75.556602,39.6348 -75.549599,39.63
7699 -75.542397,39.645901 -75.535507,39.647099 -75.514999,39.668499 -75.507523,3
9.69685 -75.496597,39.701302 -75.488914,39.714722 -75.477997,39.714901 -75.47550
2,39.733501 -75.467972,39.746975 -75.463707,39.761101 -75.448494,39.773857 -75.4
38301,39.783298 -75.405701,39.796101 -75.415405,39.801678 -75.454102,39.820202 -
75.499199,39.833199 -75.539703,39.8381 -75.5802,39.838417 -75.594017,39.837345 -
75.596107,39.837044 -75.639488,39.82893 -75.680145,39.813839 -75.71096,39.796352
 -75.739716,39.772881 -75.760689,39.74712 -75.774101,39.721699 -75.788704,39.721
699 </gml:coordinates>
        </gml:LinearRing>
      </gml:outerBoundaryIs>
    </gml:Polygon>
  </gml:geometryProperty>
</State>

<State xmlns:gml="http://www.opengis.net/gml">
  <Name>Utah</Name>
  <Population>1722850</Population>
  <gml:geometryProperty>
    <gml:Polygon srsName="SDO:" xmlns:gml="http://www.opengis.net/gml">
      <gml:outerBoundaryIs>
        <gml:LinearRing>
          <gml:coordinates decimal="." cs="," ts=" ">-114.040871,41.993805 -114.
038803,41.884899 -114.041306,41 -114.04586,40.116997 -114.046295,39.906101 -114.
046898,39.542801 -114.049026,38.67741 -114.049339,38.572968 -114.049095,38.14864
 -114.0476,37.80946 -114.05098,37.746284 -114.051666,37.604805 -114.052025,37.10
3989 -114.049797,37.000423 -113.484375,37 -112.898598,37.000401 -112.539604,37.0
00683 -112,37.000977 -111.412048,37.001514 -111.133018,37.00079 -110.75,37.00320
1 -110.5,37.004265 -110.469505,36.998001 -110,36.997967 -109.044571,36.999088 -1
09.045143,37.375 -109.042824,37.484692 -109.040848,37.881176 -109.041405,38.1530
27 -109.041107,38.1647 -109.059402,38.275501 -109.059296,38.5 -109.058868,38.719
906 -109.051765,39 -109.050095,39.366699 -109.050697,39.4977 -109.050499,39.6605
 -109.050156,40.222694 -109.047577,40.653641 -109.0494,41.000702 -109.2313,41.00
2102 -109.534233,40.998184 -110,40.997398 -110.047768,40.997696 -110.5,40.994801
 -111.045982,40.998013 -111.045815,41.251774 -111.045097,41.579899 -111.045944,4
2.001633 -111.506493,41.999588 -112.108742,41.997677 -112.16317,41.996784 -112.1
72562,41.996643 -112.192184,42.001244 -113,41.998314 -113.875,41.988091 -114.040
871,41.993805 </gml:coordinates>
        </gml:LinearRing>
      </gml:outerBoundaryIs>
    </gml:Polygon>
  </gml:geometryProperty>
</State>

Related Topics

SDO_UTIL.TO_GML311GEOMETRY

Format

SDO_UTIL.TO_JSON(
     geometry  IN SDO_GEOMETRY 
     ) RETURN CLOB;

Description

Converts an SDO_GEOMETRY object to a JSON object in CLOB format.

Parameters

geometry

Geometry in SDO_GEOMETRY format to be converted to a JSON object.

Usage Notes

For information about using JSON data that is stored in Oracle Database, see Oracle Database JSON Developer's Guide.

For information about Spatial and Graph support for JSON, see JSON and GeoJSON Support in Oracle Spatial and Graph.

The SDO_UTIL.TO_JSON_VARCHAR function (which returns a VARCHAR2 result) runs faster that this function. However, because that function returns a VARCHAR2 result, it can be used only on very small geometries. Any geometry that generates more that 4000 bytes of JSON (or 32767 bytes if the database parameter MAX_STRING_SIZE is set to EXTENDED) results in a truncated and invalid JSON object.

To convert a geometry in JSON format to an SDO_GEOMETRY object, use the SDO_UTIL.FROM_JSON function.

Examples

The following example shows conversion to and from JSON format. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data, specifically the cola_b geometry from the COLA_MARKETS table.)

DECLARE
  cola_b_geom   SDO_GEOMETRY;
  returned_geom SDO_GEOMETRY;
  returned_json CLOB;
BEGIN
  -- Populate geometry variable with cola market cols_b shape.
  SELECT c.shape into cola_b_geom FROM cola_markets c
  WHERE c.name = 'cola_b';
  -- From geometry to JSON
  returned_json := SDO_UTIL.TO_JSON(cola_b_geom);

  -- From JSON to geometry
  returned_geom := SDO_UTIL.FROM_JSON(returned_json);
END;
/

The following example shows the JSON object that represents a specified geometry. (In this case the geometry definition reflects the cola_b geometry from the COLA_MARKETS table, defined in Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

SELECT SDO_UTIL.TO_JSON(
  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))) JSON 
FROM DUAL;  
JSON
------
{"polygon": {"boundary": [{"line": {"datapoints": [[5.0, 1.0], [8.0, 1.0], [8.0, 6.0], [5.0, 7.0], [5.0, 1.0]]}}]}}

Related Topics

• SDO_UTIL.TO_JSON_VARCHAR

• SDO_UTIL.FROM_JSON

Format

SDO_UTIL.TO_JSON_VARCHAR(
     geometry  IN SDO_GEOMETRY 
     ) RETURN VARCHAR2;

Description

Converts an SDO_GEOMETRY object to a JSON object in VARCHAR2 format.

Parameters

geometry

Geometry in SDO_GEOMETRY format to be converted to a JSON object.

Usage Notes

For information about using JSON data that is stored in Oracle Database, see Oracle Database JSON Developer's Guide.

For information about Spatial and Graph support for JSON, see JSON and GeoJSON Support in Oracle Spatial and Graph.

This function (which returns a VARCHAR2 result) runs faster than the SDO_UTIL.TO_JSON (which returns a CLOB result). However, this function can be used only on very small geometries. Any geometry that generates more that 4000 bytes of JSON (or 32767 bytes if the database parameter MAX_STRING_SIZE is set to EXTENDED) results in a truncated and invalid JSON object.

To convert a geometry in JSON format to an SDO_GEOMETRY object, use the SDO_UTIL.FROM_JSON function.

Examples

The following example shows conversion to and from JSON format. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data, specifically the cola_b geometry from the COLA_MARKETS table.)

DECLARE
  cola_b_geom   SDO_GEOMETRY;
  returned_geom SDO_GEOMETRY;
  returned_json VARCHAR2(4000);
BEGIN
  -- Populate geometry variable with cola market cols_b shape.
  SELECT c.shape into cola_b_geom FROM cola_markets c
  WHERE c.name = 'cola_b';
  -- From geometry to JSON
  returned_json := SDO_UTIL.TO_JSON_VARCHAR(cola_b_geom);

  -- From JSON to geometry
  returned_geom := SDO_UTIL.FROM_JSON(returned_json);
END;
/

The following example shows the GeoJSON object that represents a specified geometry. (In this case the geometry definition reflects the cola_b geometry from the COLA_MARKETS table, defined in Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)

SELECT SDO_UTIL.TO_JSON_VARCHAR(
  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))) JSON 
FROM DUAL;  
JSON
------
{"polygon": {"boundary": [{"line": {"datapoints": [[5.0, 1.0], [8.0, 1.0], [8.0, 6.0], [5.0, 7.0], [5.0, 1.0]]}}]}}

Related Topics

• SDO_UTIL.TO_JSON

• SDO_UTIL.FROM_JSON

Format

SDO_UTIL.TO_KMLGEOMETRY(
     geometry  IN SDO_GEOMETRY 
     ) RETURN CLOB;

Description

Converts a Spatial and Graph geometry object to a KML (Keyhole Markup Language) document.

Parameters

geometry

Geometry for which to return the KML document.

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.

Polygons must be defined using the conventions for Oracle9i and later releases of Spatial and Graph. That is, the outer boundary is stored first (with ETYPE=1003) followed by zero or more inner boundary elements (ETYPE=2003). For a polygon with holes, the outer boundary must be stored first in the SDO_ORDINATES definition, followed by coordinates of the inner boundaries.

LRS geometries must be converted to standard geometries (using the SDO_LRS.CONVERT_TO_STD_GEOM or SDO_LRS.CONVERT_TO_STD_LAYER function) before being passed to the TO_KMLGEOMETRY function.

Any circular arcs or circles must be densified (using the SDO_GEOM.SDO_ARC_DENSIFY function) or represented as polygons (using the SDO_GEOM.SDO_BUFFER function) before being passed to the TO_KMLGEOMETRY function.

Label points are discarded. That is, if a geometry has a value for the SDO_POINT field and values in SDO_ELEM_INFO and SDO_ORDINATES, the SDO_POINT is not output in the KML document.

Solid geometries are converted to KML MultiGeometry objects, because KML 2.1 does not support solids. If you then use the SDO_UTIL.FROM_KMLGEOMETRY function on the MultiGeometry, the result is not an Oracle Spatial and Graph solid geometry (that is, its SDO_GTYPE value does not reflect a geometry type of SOLID or MULTISOLID).

The KML output is not formatted; there are no line breaks or indentation of tags. To see the contents of the returned CLOB in SQL*Plus, use the TO_CHAR() function or set the SQL*Plus parameter LONG to a suitable value (for example, SET LONG 2000). To get formatted GML output or to use the return value of TO_KMLGEOMETRY in SQLX or Oracle XML DB functions such as XMLELEMENT, use the XMLTYPE(clobval CLOB) constructor.

Examples

The following example shows conversion to and from KML format. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data, specifically the cola_c geometry from the COLA_MARKETS table.)

-- Convert cola_c geometry to a KML document; convert that result to
-- a spatial geometry.
set long 2000;
DECLARE
  kmlgeom CLOB;
  val_result VARCHAR2(5);
  geom_result SDO_GEOMETRY;
  geom SDO_GEOMETRY;
BEGIN
SELECT c.shape INTO geom FROM cola_markets c WHERE c.name = 'cola_c';
 
-- To KML geometry
kmlgeom := SDO_UTIL.TO_KMLGEOMETRY(geom);
DBMS_OUTPUT.PUT_LINE('To KML geometry result = ' || TO_CHAR(kmlgeom));
 
-- From KML geometry
geom_result := SDO_UTIL.FROM_KMLGEOMETRY(kmlgeom);
-- Validate the returned geometry.
val_result := SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(geom_result, 0.005);
DBMS_OUTPUT.PUT_LINE('Validation result = ' || val_result);
 
END;
/
To KML geometry result =
<Polygon><extrude>0</extrude><tessellate>0</tessellate><altitudeMode>relativeToG
round</altitudeMode><outerBoundaryIs><LinearRing><coordinates>3.0,3.0 6.0,3.0
6.0,5.0 4.0,5.0 3.0,3.0 </coordinates></LinearRing></outerBoundaryIs></Polygon>
Validation result = TRUE

Related Topics

• SDO_UTIL.FROM_KMLGEOMETRY

Format

SDO_UTIL.TO_WKBGEOMETRY(
     geometry  IN SDO_GEOMETRY 
     ) RETURN BLOB;

Description

Converts a Spatial and Graph 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 Simple Example: Inserting_ Indexing_ and Querying Spatial Data, 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_WKBGEOMETRY

• SDO_UTIL.FROM_WKTGEOMETRY

• SDO_UTIL.TO_WKTGEOMETRY

• SDO_UTIL.VALIDATE_WKBGEOMETRY

• SDO_UTIL.VALIDATE_WKTGEOMETRY

Format

SDO_UTIL.TO_WKTGEOMETRY(
     geometry  IN SDO_GEOMETRY 
     ) RETURN CLOB;

Description

Converts a Spatial and Graph 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 Simple Example: Inserting_ Indexing_ and Querying Spatial Data, 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_WKBGEOMETRY

• SDO_UTIL.FROM_WKTGEOMETRY

• SDO_UTIL.TO_WKBGEOMETRY

• SDO_UTIL.VALIDATE_WKBGEOMETRY

• SDO_UTIL.VALIDATE_WKTGEOMETRY

Format

SDO_UTIL.VALIDATE_3DTHEME(
     theme_name  IN VARCHAR2 
     ) RETURN VARCHAR2;

Description

Validates a 3D theme.

Parameters

theme_name

Name of the 3D theme. Must be a value from the USER_SDO_3DTHEMES or ALL_SDO_3DTHEMES view (described in xxx_SDO_3DTHEMES Views.

Usage Notes

This function performs several validity checks appropriate to the type of theme. For each check it returns the string TRUE if there are no issues, or a brief description of the issue.

If the specified theme has not been defined, the output indicates that there is no theme with that name.

Examples

The following example validates the themes in the USER_SDO_3DTHEMES table. (It assumes that the themes were previously inserted into the USER_SDO_3DTHEMES table.)

SELECT
    name || ': ' ||
    sdo_util.validate_3dtheme(name) "Theme validity"
  FROM user_sdo_3dthemes
  ORDER BY name;
 
Theme validity
---------------------------------------------------------------------------------
Test PC Hawaii Theme 4326: TRUE
Test PC Hawaii Theme 4326 A: No Theme-related Metadata
Test PC Hawaii Theme 4326 A2: XML metadata for "Test PC Hawaii Theme 4326 A2" not well-formed
Test PC Hawaii Theme 4326 C1: SRID 0 not found
Test PC Hawaii Theme 4326 C2: No VERTICAL SRID 5758 allowed
Test PC Hawaii Theme 4326 D: THEME_COLUMN column and xml metadata do not match, regarding feature table column
Test PC Hawaii Theme 4326 D2: User "MDMETT"'s table "PCS_HAWAII_4326" has no column "PC3", according to USER_TAB_COLUMNS
.
 
Test PC Hawaii Theme 4326 D3: User "MDMETT" has no table "PCS_HAWAII_4326A", according to USER_TABLES.
Test PC Hawaii Theme 4326 D4: THEME_TYPE column and xml metadata do not match, regarding geometric feature type
Test PC Hawaii Theme 4326 D5: User "MDMETT"'s table "PCS_HAWAII_4326" has a column "PC" of type "SDO_PC", not "SDO_PC3",
 according to USER_TAB_COLUMNS.
 
Test PC Hawaii Theme 4326 E: Elevation not strictly monotonously rising from position 1 to 2 (value 123.2 >= 123.1)
Test PC Hawaii Theme 4326 E2: No elevation @color specified at position 2
Test PC Hawaii Theme 4326 E3: No @elevation specified at position 2
Test PC Hawaii Theme 4326 E4: Elevation @color at position 2 does not have hex format RRGGBB
Test PC Hawaii Theme 4326 E5: Elevation @color at position 2 does not have hex format RRGGBB (character 6)
 
15 rows selected.

Related Topics

• SDO_UTIL.THEME3D_GET_BLOCK_TABLE

• SDO_UTIL.THEME3D_HAS_LOD

• SDO_UTIL.THEME3D_HAS_TEXTURE

• SDO_UTIL.VALIDATE_SCENE

• SDO_UTIL.VALIDATE_VIEWFRAME

Format

SDO_UTIL.VALIDATE_SCENE(
     scene_name  IN VARCHAR2 
     ) RETURN VARCHAR2;

Description

Validates a 3D scene.

Parameters

scene_name

Name of the 3D scene. Must be a value from the USER_SDO_SCENES or ALL_SDO_SCENES view (described in xxx_SDO_SCENES Views.

Usage Notes

This function performs several validity checks. For each check it returns the string TRUE if there are no issues, or a brief description of the issue.

If the specified scene has not been defined, the output indicates that there is no scene with that name.

Examples

The following example validates the scenes in the USER_SDO_SCENES table. (It assumes that the scenes were previously inserted into the USER_SDO_SCENES table.)

SELECT
    name || ': ' ||
    sdo_util.validate_scene(name) "Scene validity"
  FROM user_sdo_scenes
  ORDER BY name;
 
Scene validity
---------------------------------------------------------------------------------
Test PC Hawaii Scene 4326: TRUE
Test PC Hawaii Scene 4326 A: No Scene-related Metadata
Test PC Hawaii Scene 4326 A2: XML metadata for "Test PC Hawaii Scene 4326 A2" not well-formed
Test PC Hawaii Scene 4326 B: Referenced theme "Test PC Hawaii Theme 4326 A": No Theme-related Metadata
Test PC Hawaii Scene 4326 C1: SRID 0 not found
Test PC Hawaii Scene 4326 C2: No VERTICAL SRID 5758 allowed
 
6 rows selected.

Related Topics

• SDO_UTIL.VALIDATE_3DTHEME

• SDO_UTIL.VALIDATE_VIEWFRAME

Format

SDO_UTIL.VALIDATE_VIEWFRAME(
     viewframe_name  IN VARCHAR2 
     ) RETURN VARCHAR2;

Description

Validates a 3D viewframe.

Parameters

viewframe_name

Name of the 3D theme. Must be a value from the USER_SDO_VIEWFRAMES or ALL_SDO_VIEWFRAMES view (described in xxx_SDO_VIEWFRAMES Views.

Usage Notes

This function performs several validity checks. For each check it returns the string TRUE if there are no issues, or a brief description of the issue.

If the specified viewframe has not been defined, the output indicates that there is no viewframe with that name.

Examples

The following example validates the themes in the USER_SDO_3DTHEMES table. (It assumes that the themes were previously inserted into the USER_SDO_3DTHEMES table.)

SELECT
    name || ': ' ||
    sdo_util.validate_viewframe(name) "Viewframe validity"
  FROM user_sdo_viewframes
  ORDER BY name;
 
Viewframe validity
---------------------------------------------------------------------------------
Test PC Hawaii Viewpoint 4326: TRUE
Test PC Hawaii Viewpoint 4326 A: No Viewframe-related Metadata
Test PC Hawaii Viewpoint 4326 A2: XML metadata for "Test PC Hawaii Viewpoint 4326 A2" not well-formed
Test PC Hawaii Viewpoint 4326 B1: Referenced scene "Made up": No scene with name "Made up"
Test PC Hawaii Viewpoint 4326 B2: Referenced scene "Test PC Hawaii Scene 4326 B": Referenced theme "Test PC Hawaii Theme
 4326 A": No Theme-related Metadata
 
Test PC Hawaii Viewpoint 4326 B3: SCENE_NAME column and xml metadata do not match, regarding scene name
Test PC Hawaii Viewpoint 4326 C1: SRID 0 not found
Test PC Hawaii Viewpoint 4326 C2: No VERTICAL SRID 5758 allowed
 
8 rows selected.

Related Topics

• SDO_UTIL.VALIDATE_3DTHEME

• SDO_UTIL.VALIDATE_SCENE

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 Simple Example: Inserting_ Indexing_ and Querying Spatial Data, 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_WKBGEOMETRY

• SDO_UTIL.FROM_WKTGEOMETRY

• SDO_UTIL.TO_WKBGEOMETRY

• SDO_UTIL.TO_WKTGEOMETRY

• 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 Simple Example: Inserting_ Indexing_ and Querying Spatial Data, 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_WKBGEOMETRY

• SDO_UTIL.FROM_WKTGEOMETRY

• SDO_UTIL.TO_WKBGEOMETRY

• SDO_UTIL.TO_WKTGEOMETRY

• SDO_UTIL.VALIDATE_WKBGEOMETRY