Query a nested table of sdo_geometry
I have the following situation in the my database:
SQL> drop type obj_type1 force;
create or replace TYPE obj_type1 AS OBJECT (ids integer,geom sdo_geometry) ;
drop type obj_type2 force;
create or replace TYPE obj_type2 AS OBJECT (ids integer,m obj_type1) ;
drop type geom_tab force;
create or replace TYPE geom_tab AS table OF obj_type2;
drop table tab1;
create table tab1(name varchar2(40), geom_info geom_tab) nested table geom_info store as geom_nt;
--Inserting Data
insert into tab1 values ('1st',geom_tab(
obj_type2(1,obj_type1(1,MDSYS.SDO_GEOMETRY(2002, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), MDSYS.SDO_ORDINATE_ARRAY(12679.2, 2804.31, 12678.5, 2832.08)))),
obj_type2 (2, obj_type1(2,MDSYS.SDO_GEOMETRY(2002, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), MDSYS.SDO_ORDINATE_ARRAY(12678.5, 2832.08, 12677.9, 2859.85))))));
insert into tab1 values ('2st',geom_tab(
obj_type2(1,obj_type1(1,MDSYS.SDO_GEOMETRY(2002, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), MDSYS.SDO_ORDINATE_ARRAY(12677.9, 2859.85, 12677.3, 2887.63)))),
obj_type2 (2,obj_type1(MDSYS.SDO_GEOMETRY(2002, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), MDSYS.SDO_ORDINATE_ARRAY(12677.3, 2887.63, 12676.7, 2915.4))))));
--updating metadata
INSERT INTO user_sdo_geom_metadata
(TABLE_NAME,
COLUMN_NAME,
DIMINFO,
SRID)
VALUES (
'geom_nt', --nested table name
'm.geom',
MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-9267,28329,5E-7),MDSYS.SDO_DIM_ELEMENT('Y',-2598,26587,5E-7)),
NULL -- SRID
);
commit;
-- spatial index creation
create index geom_ump_idx1 on geom_nt (m.geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
--another table to query
create table querypointss ( geometry sdo_geometry);
INSERT INTO user_sdo_geom_metadata
(TABLE_NAME,
COLUMN_NAME,
DIMINFO,
SRID)
VALUES (
'querypointss', --nested table name
'geometry',
MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-9267,28329,5E-7),MDSYS.SDO_DIM_ELEMENT('Y',-2598,26587,5E-7)),
NULL -- SRID
);
insert into querypointss values (
MDSYS.SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(4224.0,6828.0,NULL), NULL, NULL));
commit;
--The Following Query works fine
SELECT *
FROM tab1 C, table(c.geom_info) tt, querypointss PP
WHERE mdsys.sdo_anyinteract(tt.m.geom,
MDSYS.SDO_GEOMETRY(2002, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), MDSYS.SDO_ORDINATE_ARRAY(12677.9, 2859.85, 12677.3, 2887.63))
) = 'TRUE';
--But when the second argument of the sdo_anyinteract is a column from a table it won't work
SELECT *
FROM tab1 C, table(c.geom_info) tt, querypointss PP
WHERE mdsys.sdo_anyinteract(tt.m.geom,
PP.geometry
) = 'TRUE';
and it returns this error:
Error report:
SQL Error: ORA-13268: error obtaining dimension from USER_SDO_GEOM_METADATA
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 8
ORA-06512: at "MDSYS.SDO_3GL", line 132
ORA-06512: at "MDSYS.SDO_3GL", line 256
13268. 00000 - "error obtaining dimension from USER_SDO_GEOM_METADATA"
*Cause: There is no entry in the USER_SDO_GEOM_METADATA view for the specified
geometry table.
*Action: Insert an entry for the destination geometry table with
the correct dimension information.