Geographic Information Systems Asked on January 10, 2021
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.
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)
Answered by User1973 on January 10, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP