I have published a number of times and implementation of functions what would extract a specific point from any geometry object. These appear in my LINEAR PL/SQL package and the T_GEOMETRY object that was published as part of the PACT book I wrote with Dr Siva Ravada.
When working for a customer recently I had extracted the ST_VertexN, ST_StartVertex and ST_EndVertex functions from the source of the PACKT book for use by a process that needed to extract a point.
After having deployed it, I accidently deleted what I had done. For specific reasons I won’t go in to I could not access the original source, so I re-wrote the main ST_VertexN function from scratch. I publish that source here for no other reason than as a reflection on the way that algorithms or implementations can change.
FUNCTION ST_VertexN(p_geom IN mdsys.sdo_geometry,
c_i_invalid_vertex Constant pls_integer :=-20123;
c_s_invalid_vertex Constant VarChar2(100) :='Vertex position (*POSN*) is invalid.';
v_vertex pls_integer := NVL(p_vertex,-1); -- Index into coordinates array
FUNCTION ST_StartVertex(p_geom IN mdsys.sdo_geometry)
FUNCTION ST_EndVertex(p_geom IN mdsys.sdo_geometry)
The code had come from the PACT book. Now, I would not normally use the mdsys.vertex_set_type in PL/SQL. Why? Because it involves:
An addition pass over the whole array;
The creation of a new memory object that holds a copy of the original sdo_ordinates.
What if the person had only asked for the first point? It would be faster just to access the sdo_ordinate_array by converting vertex references to ordinate references.
I needed to get this written quickly to I decided to use the mdsys.vertex_set_type approach as it involved no conversion of the input p_vertex number to ordinates. The main saving, in the end, was that in this implementation I went straight from checking the input p_vertex value to returning the sdo_geometry where previously I had used IF statements to check if the geometry was a point, whether it was encoded in sdo_point_type or sdo_ordinates etc. This checking is done via case statements.
Actually, I think the new implementation looks fresher and cleaner and easier to debug.
Update: I had a bit of time, so I modified the ST_VertexN code so that it no longer needed mdsys.vertex_set_type processing directly against the sdo_ordinate_array. I decided to call this function ST_PointN as follows:
FUNCTION ST_PointN(p_geom IN mdsys.sdo_geometry,
c_i_invalid_Point Constant pls_integer :=-20123;
c_s_invalid_Point Constant VarChar2(100) :='Point position (*POSN*) is invalid.';
v_Point pls_integer := NVL(p_Point,-1); -- Index into coordinates array