Showing results for 
Search instead for 
Do you mean 

How do I get spatial filters to work on a view when the geometry is created by a function?

by Technical Evangelist on ‎02-19-2016 08:03 PM - edited on ‎04-07-2016 05:55 AM by Moderator (627 Views)

Question

I have a view that creates geometry so there is no spatial index and therefore GeoMedia is unable to apply a spatial filter on this view.  I know I can use materialized views but I want the views to be live.  Here is my example view:

create or replace view X as
select P.ID, PUTPOINT(P.X,P.Y) GEOM
from POINTTABLE P;
 
 

Answer

GeoMedia is not the issue here, it just passes the spatial query down to Oracle.  You have to create a function based spatial index on the original table and then add Oracle metadata for the function based geometry used in the view.  Using your view, here is an example:

 

Prerequisites for indexing function based column:

 

connect SYSTEM/password@service

grant query rewrite to user;

 

connect user/password@service

alter session set query_rewrite_integrity = trusted;

alter session set query_rewrite_enabled = true;

 

Oracle Metadata (user has to be substituted by schema name):
 

insert into USER_SDO_GEOM_METADATA values

('POINTTABLE',

'user.PUTPOINT(X,Y)',

MDSYS.SDO_DIM_ARRAY(

MDSYS.SDO_DIM_ELEMENT('X', -2147483647, 2147483647, .00005),

MDSYS.SDO_DIM_ELEMENT('Y', -2147483647, 2147483647, .00005),

MDSYS.SDO_DIM_ELEMENT('Z', -2147483647, 2147483647, .00005)

),NULL);

commit;

 

Now indexing works:

 

create index POINT_SI on POINTTABLE

(user.PUTPOINT(X,Y))

indextype is MDSYS.SPATIAL_INDEX

parameters ('SDO_INDX_DIMS=2 LAYER_GTYPE="COLLECTION" TABLESPACE="INDX"');
 

Contributors