35.14 SDO_UTIL.EXTRACT_ALL
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
0returns a single geometry containing both rings, and a value of1returns 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-9 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
Parent topic: SDO_UTIL Package (Utility)