1 Reply Latest reply: Jun 27, 2013 12:48 AM by Stefan Jager RSS

    Spatial index on 4D data

    AswinNaidu

      Hi,

      I have a table with a geometry column (named trackpoints) which is a 4-D trajectory (3 D + time).

      The trackpoints column looks like this:

      MDSYS.GEOMETRY(4402,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(LAT, LON, ALT, TIME,.............)

       

      First i defined the spatial data in the metadata table and then tried to create a spatial index on the table using trackpoints column as shown below:

      CREATE INDEX 'Spatial_idx' ON tablename('Trackpoints')

           INDEXTYPE IS mdsys.spatial_index;

       

      But it throws the following error:

      SQL error: ORA-29855: error occured in the execution of ODCIINDEXCREATE routine

      ORA-13205: internal error while parsing spatial parameters

      ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I"

      29855. 00000 - "error occured in the execution of ODCIIndexCreate routine"

      *Cause:      Failed to successfully execute the ODCIIndexCreate routine.

      *Action: Check to see if the routine has been coded correctly.

       

      Can any one help me create a index on a 4-D column? BTW i am new to Oracle

       

      Thanks

      Aswin

        • 1. Re: Spatial index on 4D data
          Stefan Jager

          Hi Aswin,

           

          First of all: have you read the documentation on LRS geometries? It explains quite a bit about indexing your LRS data. Secondly:

          1. Which version of Oracle are you using?

          select * from v$version;
          

          will tell you this.

           

          2. Can you tell us the results of

          select * from user_sdo_geom_metadata where table_name='TRACKPOINTS';
          

          because LRS needs to define some dimensioning for the measure-values as well: 7.2 LRS Data Model

           

          3. Drop your index, and create it like this:

          create index spatial_idx
          on tablename(trackpoints)
            indextype is mdsys.spatial_index
            parameters('sdo_indx_dims=3');
          

          This way the RTree-index only uses the X-, Y- and Z-values. See 7.3 Indexing of LRS Data.

           

          4. If the above doesn't help you solve this, post a create table statement and some sample data that shows the same problem, so we can try to reproduce and see what is going wrong.

           

          HTH,

          Stefan