Oracle^{®} Spatial User's Guide and Reference Release 9.2 Part No. A9663001 

This chapter contains descriptions of the utility functions and procedures shown in Table 171.
Table 171 Utility Functions and Procedures
Function/Procedure  Description 

SDO_UTIL.EXTRACT  Returns the geometry that represents a specified element (and optionally a ring) of the input geometry. 
SDO_UTIL.GETVERTICES  Returns the coordinates of the vertices of the input geometry. 
Format
SDO_UTIL.EXTRACT(
geometry IN MDSYS.SDO_GEOMETRY,
element IN NUMBER
[, ring IN NUMBER]
) RETURN MDSYS.SDO_GEOMETRY;
Description
Returns the geometry that represents a specified element (and optionally a ring) of the input geometry.
Parameters
Geometry from which to extract the geometry to be returned.
Number of the element in the geometry: 1 for the first element, 2 for the second element, and so on. Geometries with SDO_GTYPE values (explained in Section 2.2.1) ending in 1, 2, or 3 have one element; geometries with SDO_TYPE values ending in 4, 5, 6, or 7 can have more than one element. For example, a multipolygon with an SDO_GTYPE of 2007 might contain three elements (polygons).
Number of the subelement (ring) within element: 1 for the first subelement, 2 for the second subelement, and so on. This parameter is valid only for specifying a subelement of a polygon with one or more holes or of a point cluster:
For a polygon with holes, its first subelement is its exterior ring, its second subelement is its first interior ring, its third subelement is its second interior ring, and so on. For example, in the polygon with a hole shown in Figure 23 in Section 2.3.2, the exterior ring is subelement 1 and the interior ring (the hole) is subelement 2.
For a point cluster, its first subelement is the first point in the point cluster, its second subelement is the second point in the point cluster, and so on.
The default is 0, which causes the entire element to be extracted.
Usage Notes
This function is useful for extracting a specific element or subelement from a complex geometry. For example, if you have identified a geometry as invalid by using the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function or the SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT procedure (both of which are documented in Chapter 11), you can use EXTRACT function to extract the invalid geometry in order to examine it.
For a polygon with one or more holes, the returned geometry representing an extracted interior ring is "reoriented" so that its vertices are presented in counterclockwise order (as opposed to the clockwise order within an interior ring).
If geometry is null or has an SDO_GTYPE value ending in 0, this function returns a null geometry.
geometry cannot contain a type 0 (zero) element. Type 0 elements are described in Section 2.3.6.
An exception is raised if element or ring is an invalid number for geometry.
Examples
The following example extracts the first (and only) element in the cola_c geometry. (The example uses the definitions and data from Section 2.1.)
SELECT c.name, SDO_UTIL.EXTRACT(c.shape, 1) FROM cola_markets c WHERE c.name = 'cola_c'; NAME  SDO_UTIL.EXTRACT(C.SHAPE,1)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_IN  cola_c SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(3, 3, 6, 3, 6, 5, 4, 5, 3, 3))
The following example inserts a polygon with a hole (using the same INSERT statement as in Example 23 in Section 2.3.2), and extracts the geometry representing the hole (the second subelement). Notice that in the geometry returned by the EXTRACT function, the vertices are in counterclockwise order, as opposed to the clockwise order in the hole (second subelement) in the input geometry.
 Insert polygon with hole. INSERT INTO cola_markets VALUES( 10, 'polygon_with_hole', MDSYS.SDO_GEOMETRY( 2003,  2dimensional polygon NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1),  polygon with hole MDSYS.SDO_ORDINATE_ARRAY(2,4, 4,3, 10,3, 13,5, 13,9, 11,13, 5,13, 2,11, 2,4, 7,5, 7,10, 10,10, 10,5, 7,5) ) ); 1 row created.  Extract the hole geometry (second subelement). SELECT SDO_UTIL.EXTRACT(c.shape, 1, 2) FROM cola_markets c WHERE c.name = 'polygon_with_hole'; SDO_UTIL.EXTRACT(C.SHAPE,1,2)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(7, 5, 10, 5, 10, 10, 7, 10, 7, 5))
Related Topics
Format
SDO_UTIL.GETVERTICES(
geometry IN MDSYS.SDO_GEOMETRY
) RETURN MDSYS.VERTEX_SET_TYPE;
Description
Returns a table containing the coordinates of the vertices of the input geometry.
Parameters
Geometry for which to return the coordinates of the vertices.
Usage Notes
This function returns an object of MDSYS.VERTEX_SET_TYPE, which consists of a table of objects of VERTEX_TYPE. Oracle Spatial defines the type VERTEX_SET_TYPE as:
CREATE TYPE vertex_set_type as TABLE OF vertex_type;
Oracle Spatial defines the object type VERTEX_TYPE as:
CREATE TYPE vertex_type AS OBJECT (x NUMBER, y NUMBER, z NUMBER, w NUMBER);
This function can be useful in finding a vertex that is causing a geometry to be invalid. For example, if you have identified a geometry as invalid by using the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function or the SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT procedure (both of which are documented in Chapter 11), you can use the GETVERTICES function to view the vertices in tabular format.
Examples
The following example returns the X and Y coordinates of the vertices of the geometries in the SHAPE column of the COLA_MARKETS table. (The example uses the definitions and data from Section 2.1.)
SELECT c.name, t.X, t.Y FROM cola_markets c, TABLE(SDO_UTIL.GETVERTICES(c.shape)) t; NAME X Y    cola_a 1 1 cola_a 5 7 cola_b 5 1 cola_b 8 1 cola_b 8 6 cola_b 5 7 cola_b 5 1 cola_c 3 3 cola_c 6 3 cola_c 6 5 cola_c 4 5 cola_c 3 3 cola_d 8 7 cola_d 10 9 cola_d 8 11 15 rows selected.
Related Topics

Copyright © 1999, 2002 Oracle Corporation All rights reserved 
