SDO_UTIL.GETVERTICES

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 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,
    v5  NUMBER,
    v6  NUMBER,
    v7  NUMBER,
    v8  NUMBER,
    v9  NUMBER,
    v10 NUMBER,
    v11 NUMBER,
    id  NUMBER);

Note:

The VERTEX_SET_TYPE and VERTEX_TYPE types are intended for use by Oracle only. Do not use these types in column definitions or functions that you create.

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

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