2

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),
        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.

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),
        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

Edit:

It turns out that it's not necessary to add a record to ALL_SDO_GEOM_METADATA when using the function-based spatial index in ArcGIS Desktop 10.7.1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.