?
Solved

Spatial Index

Posted on 2003-03-26
2
Medium Priority
?
1,367 Views
Last Modified: 2007-12-19
Hi i've got a table with two geometry columns.

geometrie and geometrie_2d (because of an applications bug I need both 3d and 2d data)

(the user_sdo_geom_metadata has been filled correctly)

So i start creating indexes:

SQL> create index DGDTW_TopoIndex_2D on DGDTW_Topografie(Geometrie_2d) indextype is mdsys.spatial_index
  2  /

Index created.

SQL> create index DGDTW_TopoIndex on DGDTW_Topografie(Geometrie) indextype is mdsys.spatial_index ;
create index DGDTW_TopoIndex on DGDTW_Topografie(Geometrie) indextype is mdsys.spatial_index
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-29400: data cartridge error
ORA-01426: numeric overflow
ORA-01426: numeric overflow
ORA-06512: at "MDSYS.SDO_INDEX_METHOD", line 8
ORA-06512: at line 1

Does anyone know what to do about this error?
0
Comment
Question by:harry_1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 300 total points
ID: 8210002
1. Check to see if the routine has been coded correctly.

2. Some examples:
Problem Description
-------------------
You have a table with spatial column and you are trying to create an index on
the geometry column and you get the following errors -
SQL> desc mdtest
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
GEOM MDSYS.SDO_GEOMETRY
SQL> select * from user_sdo_geom_metadata;
TABLE_NAME COLUMN_NAME
-------------------------------- --------------------------------
DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
--------------------------------------------------------------------------------
SRID
----------
MDTEST GEOM
SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 314028.59, 334251.934, 0), SDO_DIM_ELEMENT('Y
', 6248751.55, 6269790.58, 0))
SQL> select * from mdtest;
ID
----------
GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
1
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
329570.943, 6249198.68, 329603.656, 6249201.59, 329736.947, 6249213.45))
2
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
324115.417, 6269186.43, 324186.468, 6269176.66))
3
SDO_GEOMETRY(NULL, NULL, NULL, SDO_ELEM_INFO_ARRAY(), SDO_ORDINATE_ARRAY())
SQL> create index mdidx on mdtest(geom)
indextype is mdsys.spatial_index
parameters ('SDO_LEVEL=2'); 2 3
create index mdidx on mdtest(geom)
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13200: internal error [ROWID:AAAEmZAAIAAACmBAAC] in spatial indexing.
ORA-13206: internal error [] while creating the spatial index
ORA-13193: failed to allocate space for geometry
ORA-06512: at "MDSYS.SDO_INDEX_METHOD", line 7
ORA-06512: at line 1
Solution Description
--------------------
It is basically a data problem.
The problem is because of geometry '3' which has all elements of sdo_geometry
set to 'NULL', but the error message here is very cryptic and misleading.
Explanation
-----------
The above geometry is not a null geometry it is a geometry with just null
attributes and it is invalid.
An object whose value is NULL is called atomically null. In addition, attributes
of an object can be null. These two uses of nulls are different.
If you wanted to have location as null then you should change the column value
itself to 'NULL' (atomic NULL).
Try to correct the data as shown below and try index creation again.
SQL> update mdtest t set t.geom=NULL where t.geom.sdo_gtype is null;
1 row updated.
SQL> select * from mdtest;
ID
----------
GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
1
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
329570.943, 6249198.68, 329603.656, 6249201.59, 329736.947, 6249213.45))
2
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
324115.417, 6269186.43, 324186.468, 6269176.66))
3
ID
----------
GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SQL> create index mdidx on mdtest(geom)
indextype is mdsys.spatial_index
parameters ('SDO_LEVEL=2'); 2 3
Index created.
SQL>
0
 

Author Comment

by:harry_1
ID: 8210330
ah thanx,

after some searching in the data i found that there are a few rows that look like this:

SDO_GEOMETRY(3003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARRAY(~, ~, 0, -~, -~, 0))

After deleting them the index creation went great.

Thanx!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question