Function-based spatial index: Use sdo_geometry() constructor instead of custom function?

Question

I have an Oracle 18c table that has XY columns, but not a SHAPE column. I've created a query on the table that dynamically generates an sdo_geometry SHAPE column.

sdo_geometry(2001, 26917, sdo_point_type(longitudex,latitudey, null), null, null) as shape

---

Function-based spatial index:

In an effort to improve the performance of the query, I've created a function-based spatial index.

The Oracle docs provide instructions for creating a custom function for the purpose of the function-based index. So that's what I've done:

--Create a deterministic function:
create or replace function sdo_geom_from_xy(longitudex in number, latitudey in number)
return sdo_geometry deterministic is
begin
    return sdo_geometry(2001, 26917, sdo_point_type(nvl(longitudex,0), nvl(latitudey,0), null),null, null);
end;

--Update USER_SDO_GEOM_METADATA:
delete from user_sdo_geom_metadata where table_name = 'WO_MV2_SBX';
insert into user_sdo_geom_metadata values(
    'WO_MV2_SBX',
    'maximo.sdo_geom_from_xy(longitudex,latitudey)',
    sdo_dim_array(
        sdo_dim_element('X', 663795.93, 676674.94, 0.001),  --note to self: these coordinates are wrong.
        sdo_dim_element('Y', 4857659, 4879068.83, 0.001)), 26917);

--Create a function-based spatial index:
drop index wo_mv2_sbx_idx;
create index wo_mv2_sbx_idx on 
    wo_mv2_sbx(maximo.sdo_geom_from_xy(longitudex,latitudey))
    indextype is mdsys.spatial_index;

--Create a view that uses the the function-based spatial index:
create or replace view wo_mv2_view_sbx as (
select
    objectid,
    maximo.sdo_geom_from_xy(longitudex,latitudey) as shape    
from
    maximo.wo_mv2_sbx
where
    plussisgis = 1
);
--Note: In order for the view get the benefit the function based index, the view *MUST* use the exact same syntax that was used in the function-based index.

---

Question:

When I use the custom function, everything works as expected. However, it seems strange to me that I would need to create a custom function, when the out-of-box sdo_geometry() constructor would do what I want.

Is there a way to use the sdo_geometry() constructor in a function-based index instead of custom function?

Edit:

There is a related post here:

Can I create an Oracle Spatial View from a non-spatial table? (with an excellent answer from @AlberGodfrind.

Answer 1

It turns out, despite what the Oracle docs imply, we can use the sdo_geometry() constructor instead of a custom function.

--Update USER_SDO_GEOM_METADATA:
delete from user_sdo_geom_metadata where table_name = 'WO_MV2_SBX';        
insert into user_sdo_geom_metadata values(
    'WO_MV2_SBX',
    --I'm using decode() to handle NULL XY values (via my plussisgis flag/column)
    'decode(plussisgis,1,mdsys.sdo_geometry(2001,26917,mdsys.sdo_point_type(longitudex,latitudey,null),null,null))',
    sdo_dim_array(
        sdo_dim_element('X', 663795.93, 676674.94, 0.001),  --note to self: these coordinates are wrong.
        sdo_dim_element('Y', 4857659, 4879068.83, 0.001)), 26917);

--Create a function-based spatial index:
drop index wo_mv2_sbx_idx;    
create index wo_mv2_sbx_idx on 
    wo_mv2_sbx(decode(plussisgis,1,mdsys.sdo_geometry(2001,26917,mdsys.sdo_point_type(longitudex,latitudey,null),null,null)))
    indextype is mdsys.spatial_index;

--Create a view that uses the the function-based spatial index:
create or replace view wo_mv2_view_sbx as (
select
    objectid,
    decode(plussisgis,1,mdsys.sdo_geometry(2001,26917,mdsys.sdo_point_type(longitudex,latitudey,null),null,null)) as shape    
from
    maximo.wo_mv2_sbx
where
    plussisgis = 1
)
--Note: In order for the view get the benefit the function based index, the view *MUST* use the exact same syntax that was used in the function-based index.

So that helps simplify things; no need for a custom function.

---

More info here:

Function for a function-based spatial index: if X not null and Y not null (can't contain spaces)

Function: if either x or y are null, return null