17
Utility Functions

SDO_UTIL.EXTRACT

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

Geometry from which to extract the geometry to be returned.

element

Number of the element in the geometry: 1 for the first element, 2 for the second element, and so on. Geometries with SDO_GTYPE values (explained in Section 2.2.1) ending in 1, 2, or 3 have one element; geometries with SDO_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).

ring

Number of the subelement (ring) within element: 1 for the first subelement, 2 for the second subelement, and so on. This parameter is valid only for specifying a subelement of a polygon with one or more holes or of a point cluster:

• For a polygon with holes, its first subelement is its exterior ring, its second subelement is its first interior ring, its third subelement is its second interior ring, and so on. For example, in the polygon with a hole shown in Figure 2-3 in Section 2.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 2-3 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,  -- 2-dimensional 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

SDO_UTIL.GETVERTICES

SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT

SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT

SDO_UTIL.GETVERTICES

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

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

SDO_UTIL.EXTRACT

SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT

SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT