Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query a nested table of sdo_geometry

Farid CherNov 1 2011
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 29 2011
Added on Nov 1 2011
0 comments
157 views