Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Spatial Index

Posted on 2003-03-26
2
Medium Priority
?
1,387 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
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.
Suggested Courses
Course of the Month12 days, 1 hour left to enroll

564 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