Database Software

Thanks! That's interesting.

Side note:
That technique doesn't seem to work for LRS geometries:

--get the X coordinate of the second vertex
with data as (select sdo_lrs.convert_to_lrs_geom(sdo_geometry('linestring(10 20, 30 40, 50 60)')) as shape from dual)

select 
    json_value((shape).Get_GeoJson(),'$.coordinates[1][0]' returning number) as x
from 
    data

Error:

ORA-13199: LRS is not supported
ORA-06512: at "MDSYS.SDO_UTIL", line 6410
ORA-06512: at "MDSYS.SDO_UTIL", line 6431
ORA-06512: at "MDSYS.SDO_GEOMETRY", line 156
13199. 00000 -  "%s"
*Cause:    This is an internal error.
*Action:   Contact Oracle Support Services.

But I think we already knew that. The GeoJSON standard doesn't support LRS lines (not Oracle Spatial's fault): SDO_UTIL.TO_GEOJSON() - Support LRS geometries

[David Lapp-Oracle](/ords/forums/user/David Lapp-Oracle)
If member functions can only return scalars, do you know why this query works?

select
 (shape).sdo_ordinates as ordinates 
from
 (select sdo_geometry('linestring(1 2, 3 4, 5 6)') as shape from dual)

In that query, is .sdo_ordinates a member function? And is it returning a varray (a kind of table collection)? If so, why wouldn't it be possible to create a member function for extracting collection elements by index, instead of returning the entire collection?
I've likely misunderstood something there. Just trying to learn.
Thanks.

Member functions are object type methods . They are functions and end with ( )

select a.geom.Get_Gtype() from mydata a;

Object attribute queries return attributes of an object as-is. They are not functions so have no ( )

select a.geom.sdo_gtype from mydata a;

In the case of an object attribute query for sdo_ordinates, the result is varray.

[David Lapp-Oracle](/ords/forums/user/David Lapp-Oracle)
What do you think about this test?

CREATE TYPE my_sdo_geom_type AS OBJECT( 
 shape SDO_GEOMETRY, 

 MEMBER FUNCTION GetOrdinates(
  self IN my_sdo_geom_type,
  idx IN NUMBER
 ) RETURN NUMBER
) 
/ 
CREATE TYPE BODY my_sdo_geom_type AS
  MEMBER FUNCTION GetOrdinates(
    self IN my_sdo_geom_type,
    idx  IN NUMBER
  ) RETURN NUMBER
  IS
  BEGIN 
    return shape.sdo_ordinates(idx);
  END;
END;
/ 
create table lines (my_sdo_geom_col my_sdo_geom_type);
insert into lines (my_sdo_geom_col) 
  values (my_sdo_geom_type(sdo_geometry('linestring(10 20, 30 40, 50 60)')));

select (my_sdo_geom_col).GetOrdinates(1)
from   lines

Result:
10

That seems to work as expected. The custom GetOrdinates() member function returns a specific ordinate.
Would it be possible for Oracle to implement something like that in the sdo_geometry object? That might make it easier to extract ordinates in some cases.

This is a good idea and I will create an enhancement request.

[David Lapp-Oracle](/ords/forums/user/David Lapp-Oracle)
Could we make it so the member function would accept negative numbers? For example, a -1 would return the last ordinate. And a -2 would return the second last ordinate, etc..
Like this:

CREATE TYPE BODY my_sdo_geom_type AS
  MEMBER FUNCTION GetOrdinates(
    self IN my_sdo_geom_type,
    idx  IN NUMBER
  ) RETURN NUMBER
  IS
  BEGIN 
  IF idx BETWEEN 1 AND shape.sdo_ordinates.COUNT THEN
    return shape.sdo_ordinates(idx);
  ELSIF -idx BETWEEN 1 AND shape.sdo_ordinates.COUNT THEN
    RETURN shape.sdo_ordinates(shape.sdo_ordinates.COUNT + 1 + idx);
  ELSE
    RETURN NULL;
  END IF;
  END;
END;
/ 

Source: Use negative number to extract element at end of varray list (in a custom member function)
That's similar to how sdo_util.get_coordinate() works, as demonstrated here: https://gis.stackexchange.com/a/425057/62572.

I'm looking forward to eventually using that member function. I actually think it might result in a performance improvement in some cases.
For example, when it comes to getting the startpoint X and Y of SDO_GEOMETRY lines, this is the fastest technique I've found:

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
  bc_atn_sdo_geom

I imagine the GetOrdinate member function will be equally as fast.

@david-lapp-oracle
I wonder if a member function should be created for SDO_ELEM_INFO too, so that we can easily access multi-part geometry information. I.e., determine where the ordinates for part 1 stop and part 2's ordinates start, etc.
Related info in this answer: How is multi-part SDO_GEOMETRY information stored?