10 Replies Latest reply: Mar 28, 2014 5:03 AM by Ivan Bush RSS

    Cant create a spatial index

    humpy125

      We are currently using Oracle 11g version 11.2.0.4.0. This was recently upgraded from 11.2.0.2

       

      I have a table that contains SDO line geometries. I recently discovered that our users could no longer use this table as it is intended.  It is used for creating collision diagrams. The error shown within the app was:

      ORA-29875: failed in the execution of the ODCIINDEXINSERT routine ORA-13033: Invalid data in the SDO_ELEM_INFO_ARRAY in SDO_GEOMETRY object ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 720 ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 225 ORA-01403: no data found 

       

      So I dropped the spatial index and tried to recreate it, but when I do I get the following error:

      ORA-29855: error uccurred in the execution of ODCIINDEXCREATE routine

      ORA-13249: internal error in Spatial index: [mdidxrbd]

      ORA-13249: Error in Spatial index: index build failed

      ORA-13249: Error in spatial index: [mdrcrtxfergm]

      ORA-13249: Error in spatial index: [mdpridxtxfergm]

      ORA-13200: internal error [ROWID:AABy2bAAOAAA|WEAAA] in spatial indexing.

      ORA-13206: internal error[] while creating the spatial index

      ORA-13033: Invalid data in the SDO_ELEM_INFO_ARRAY in SDO_GEOMETRY object

      ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", LINE10

       

      I have created a new table and tried copying the data into it and then create the index, but that fails with the same error.  I have done this also for one record and this fails too, however, if I create the geometry like this:

      SDO_GEOMETRY(     2002,  -- two-dimensional line     28355,     NULL,     SDO_ELEM_INFO_ARRAY(1,2,1),     SDO_ORDINATE_ARRAY(527499.571,5249811.468, 527501.246,5249812.983, 527502.316,5249811.801))

       

      Then it will happily create a spatial index.

      When comparing the two geometries, in sql developer using georaptor, I can not see a difference.

       

      I can create a spatial index on other tables with no issues.

       

      Can anyone shed some light on this?

        • 1. Re: Cant create a spatial index
          _jum

          You have a comment in the SDO_GEOMETRY, compare:


          --ok

          SELECT SDO_GEOMETRY( 2002,  -- two-dimensional line
            28355, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),  SDO_ORDINATE_ARRAY(527499.571,5249811.468, 527501.246,5249812.983, 527502.316,5249811.801)) FROM dual;


          --error

          SELECT SDO_GEOMETRY( 2002,  -- two-dimensional line   28355, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(527499.571,5249811.468, 527501.246,5249812.983, 527502.316,5249811.801)) FROM dual;

           

           


          • 2. Re: Cant create a spatial index
            Stefan Jager

            Either it's the comment, or it's somewhere else:

             

            ORA-13033: Invalid data in the SDO_ELEM_INFO_ARRAY in SDO_GEOMETRY object

             

            Which basically boils down to: your data is not valid. Fix the data, and the error should go away. Oracle is pretty good at telling you what is going wrong usually.

            • 3. Re: Cant create a spatial index
              Ivan Bush

              Humpy125,

               

              Stefan is right that there is something wrong with your data.

               

              Run VALIDATE_GEOMETRY_WITH_CONTEXT on your geometries. This will tell you exactly what is wrong.

               

              Ivan

              • 4. Re: Cant create a spatial index
                humpy125

                It is not the comment. If you read the post it states that

                "creating the geometry like this:

                SDO_GEOMETRY(     2002,  -- two-dimensional line     28355,     NULL,     SDO_ELEM_INFO_ARRAY(1,2,1),     SDO_ORDINATE_ARRAY(527499.571,5249811.468, 527501.246,5249812.983, 527502.316,5249811.801))


                Then it will happily create a spatial index."


                Admittedly when I ran the above query it was over several lines. This may have caused some confusion.


                My problem is with the existing geometries, not the ones being added manually.

                • 5. Re: Cant create a spatial index
                  _jum

                  So run VALIDATE_GEOMETRY_WITH_CONTEXT as @Ivan Bush stated and come back with the results .

                  • 6. Re: Cant create a spatial index
                    Stefan Jager

                    humpy125 wrote:

                    If you read the post

                    If you'd read the error message, you'd have known the problem is somewhere in your data, because that is what Oracle is telling you.

                     

                    humpy125 wrote:

                    My problem is with the existing geometries, not the ones being added manually.

                    Yes, that's what Oracle told you. Oracle told you straightaway from the beginning, I'm copying the text of the first error message that was captured by your app:

                     

                    ORA-29875: failed in the execution of the ODCIINDEXINSERT routine ORA-13033: Invalid data in the SDO_ELEM_INFO_ARRAY in SDO_GEOMETRY object ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 720 ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 225 ORA-01403: no data found

                     

                    Since the spatial indexes are self-tuning, the moment the app tried to insert an invalid geometry the index complained. And it did so telling you what it did not like. Looking at the last line of that error message it might even be a geometry which has no data in the SDO_ELEM_INFO_ARRAY, but that last line might also come from somewhere else so it might be something else. Fact is that a geometry with an invalid SDO_ELEM_INFO_ARRAY was passed during an insert.

                    Now the easiest way to fix this would be to prevent the app to insert invalid geometries, and if this is something you can regularly reproduce from your app then trace what the app does, which should allow you to see what kind of geometry the app is trying to insert. Then fix the app so that it doesn't do that anymore and gives the user a meaningful error message.

                     

                    And in the meantime, fix your table using validate_geometry_with_context, as Ju and Ivan already recommended.

                    • 7. Re: Cant create a spatial index
                      humpy125

                      That was the problem, but viewing the data with Georaptor in SQLdeveloper did not reveal where the problem was. The geometries looked identical to geometries created using the same coordinate values as shown in sql developer.

                       

                      running the sdo_utils.getnumvertices function returned values of 2.5 and 3.5.

                       

                      It was not until I used the Java API to  extract the geometries and load them into a JGeometry object that I could see the problem. All the ordinate arrays had 0.0 appended to the end so  a two point line had coordinates like:

                      525834.776, 5251866.324, 525833.424, 5251868.073, 0.0. Hence the value of 2.5 for the number of vertices.

                       

                      How does validating the geometry, using validate_geometry_with_context,  fix it?

                       

                      using SDO_UTIL.rectify_geometry returns the following message:

                      ORA-13199: the given geometry cannot be rectified

                      ORA-06512: at "MDSYS.MD", line 1723

                      ORA-06512: at "MDSYS.MDERR", line 17

                      ORA-06512: at "MDSYS.SDO_UTIL", line 772

                      13199. 00000 -  "%s"

                      *Cause:    This is an internal error.

                      *Action:   Contact Oracle Support Services.

                      • 8. Re: Cant create a spatial index
                        Ivan Bush

                        Humpy125,

                         

                        That is an interesting error. How does this data get into the table? Fixing the data is one problem but you must also stop it from happening again.

                         

                        validate_geometry_with_context does not fix the geometry it shows what the errors are. Can you run it and post what errors it reports please.

                         

                        I will have a think about best solution.

                         

                        Ivan

                        • 9. Re: Cant create a spatial index
                          humpy125

                          Validate_geometry_with_context gives me a bunch of errors like this:

                          ORA-13033: Invalid data in the SDO_ELEM_INFO_ARRAY in SDO_GEOMETRY object


                          The code used to create the geometry, that is added to the table, was some old PL/SQL. It was taking a varchar2 of coordinates, creating an array by splitting the string then converting them to a number.

                          I wrote the contents of this array to a debug file and it seemed the array was creating an extra element with a null value.


                          I am guessing this was converted to 0 when th geometry was created.


                          I have modified this code so it creates the geometry by using the text directly in a query Ie:

                           

                                sdo_qry := 'select SDO_GEOMETRY( 2002,'||Gc_Const.c_spatial_srid||',NULL,

                                      SDO_ELEM_INFO_ARRAY(1,2,1),

                                      SDO_ORDINATE_ARRAY('||p_shape_string||')) from dual'

                          • 10. Re: Cant create a spatial index
                            Ivan Bush

                            Humpy125,

                             

                            Those geometries are clearly wrong. You need an even number of values. i.i the 0 at the end is causing the problem. I would reload this table with geometries that do not have the extra 0 and this problem will disappear. You can check the values in the geometry using a select command in SQL.

                             

                            I have not got a 11.2.0.4 environment at the moment so cannot check. 11.2.0.2 tolerates these geometries. This is not unusual.

                             

                            If I ever get problems with tables with geometries in I always use validate_geometry_with_context as it shows exactly what the version of Oracle does not like. It will also stop people, like me, telling you to run it.

                             

                            Regards.

                             

                            Ivan