Yes, get_coordinates() is undocumented. I've talked to folks on the Oracle Spatial team about that. They plan to document it in the next release.
If for some reason we didn't want to use get_coordinates(), maybe since it's undocumented, then there are other ways to get the startpoint X&Y from SDO_GEOMETRY.
The performance of the alternative techniques is roughly the same as get_coordinates(), although slightly more complicated:
select
v.x,
v.y
from
sidewalks
cross join
sdo_util.getvertices(shape) v
where
v.id = 1 Or
with
function
sdo_startpoint_x(shape sdo_geometry) return number is
begin
return shape.sdo_ordinates(1);
end;
function sdo_startpoint_y(shape sdo_geometry) return number is
begin
return shape.sdo_ordinates(2);
end;
select
sdo_startpoint_x(shape) as startpoint_x,
sdo_startpoint_y(shape) as startpoint_y
from
sidewalksOr
select
json_value((shape).Get_GeoJson(),'$.coordinates[0][0]' returning number) as x,
json_value((shape).Get_GeoJson(),'$.coordinates[0][1]' returning number) as y
from
sidewalks
There is also:
sdo_lrs.geom_segment_start_pt
But that just returns a LRS geometry. We'd still need to get the XY from the SDO_ORDINATES_ARRAY, which can be done via cross join sdo_util.getvertices(shape). But that's more clunky than I'd like.
Related: Idea: Add a SDO_ORDINATES member function (for extracting collection elements by index)
The nice thing about ST_GEOMETRY is that it's easy to use, well documented, and there aren't several weird ways to do a thing...there's usually just one way to do it. But with SDO_GEOMETRY, as I demonstrated above, it's messier.
Is it possible to lookup SDE.ST_GEOMETRY by index?
I don't think so. That's part of the problem with ST_GEOMETRY. The ordinates are hidden in a blob. Whereas SDO_GEOMETRY ordinates are in plain sight in an array in an object attribute (easily accessible by index number).
Note: With ST_GEOMETRY, we can get a specific point/vertex via the point index number: SDE.ST_PointN. But that's just as slow as SDE.ST_StartPoint. And I assume you weren't really referring to that.
It seems hard to make any real claims about performance without testing more here. The time difference just looks too large for these to be equivalent queries.
Yes, the "startpoint function test" was just one test for a specific use case. I haven't done any official benchmarking with dozens of documented tests or anything like that.
But the test I did was legitimate & correct. And it's a good example of how slow ST_GEOMETRY functions can be when used on a medium-sized dataset. I stand by my statement that ST_GEOMETRY functions are generally slower. I've heard the same from others, including Esri staff.
