In a related post, I demonstrated how to use the GetVertices() function and the Table() function to extract the vertices of an SDO_GEOMETRY line:
select a.line_id, b.id as vertex_id, b.x, b.y from a_sdo_geometry_tbl a, table(sdo_util.getvertices(a.shape)) b --<<--Here LINE_ID VERTEX_ID X Y ---------- ---------- ---------- ---------- 1 1 671539.685 4863324.18 1 2 671595.050 4863343.17 1 3 671614.014 4863350.34 1 4 671622.204 4863353.53 2 1 71534.5567 4863119.99 2 2 671640.738 4863157.13 2 3 671684.862 4863172.02
As a learning exercise, I want to create my own version of the GetVertices() function using PL/SQL.
I'm open to ideas about what sort of mechanism to use; any of the following would be fine:
- Basic SQL
- MODEL clause
- Custom function
The point is, I want to learn how to extract the vertices and put them into a nested table column in a query, just like the GetVertices() function does.
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);
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.
Is there a way to extract an SDO_GEOMETRY line’s vertices into a nested table column in a query -- without using the GetVertices() function? The output would be a query resultset, not a static table.
The following functions might be useful:
PointN: Returns a point that is the nth vertex in the collection of vertices
GetNumVertices: Returns the number of vertices in the input geometry.