1 2 Previous Next 15 Replies Latest reply: May 9, 2012 3:30 PM by cptkirkh RSS

    using SDO_GEOMETRY

    cptkirkh
      I am not sure which forum to post this on so I thought i would put it under general for now. I have followed the steps listed in this link using my own tables and data. Can someone lead me down the path to enlightenment on what i did wrong?

      http://st-curriculum.oracle.com/obe/db/10g/r2/prod/datamgmt/spatial/spatial_otn.htm

      When i try to build the indexes i get the following error.

      SQL I ran:
      REATE INDEX CLI_TEST_MAIN_SIDX ON CLIENTS_TEST(CLI_MAIN_GEO_LOCATION)
      indextype is mdsys.spatial_index;
      CREATE INDEX CLI_TEST_SECOND_SIDX ON CLIENTS_TEST(CLI_SECOND_GEO_LOCATION)
      indextype is mdsys.spatial_index;
      CREATE INDEX LOC_TEST_SIDX ON LOCATION_TEST(LOC_GEO_LOCATION)
      indextype is mdsys.spatial_index;

      I get this error:
      ORA-06550: line 1, column 8:
      PL/SQL: Statement ignored
      ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10
      ORA-06512: at line 1

      when i run the query:

      SELECT /*+ordered*/
      CLI_RID,
      CLI_FIRST_NAME,
      CLI_LAST_NAME,
      sdo_nn_distance (1) distance
      FROM LOCATION_TEST ,
      CLIENTS_TEST c
      WHERE LOC_RID = 11685
      AND sdo_nn
      (CLI_MAIN_GEO_LOCATION, LOC_GEO_LOCATION, 'sdo_num_res=5', 1) = 'TRUE'

      i get this error:
      ORA-13249: SDO_NN cannot be evaluated without using index
      ORA-06512: at "MDSYS.MD", line 1723
      ORA-06512: at "MDSYS.MDERR", line 17
      ORA-06512: at "MDSYS.PRVT_IDX", line 44
      ORA-06512: at line 1


      a little research has gotten me to this query.
      select comp_id, control, schema, version, status, comp_name from dba_registry
      where comp_id='SDO';
      COMP_ID CONTROL SCHEMA VERSION STATUS COMP_NAME
      SDO     SYS     MDSYS     10.1.0.3.0     OPTION OFF     Spatial

      SO how do i change status to valid?
        • 1. Re: using SDO_GEOMETRY
          Thierry H.
          This might be helpfull
          Database Upgrade to 10.2.0.4

          Maybe close your other similar thread Re: using SDO_GEOMETRY
          • 2. Re: using SDO_GEOMETRY
            Simon Greener
            A few errors here.

            The first is difficult to answer as I know nothing about the underlying table and data for:
            CREATE INDEX LOC_TEST_SIDX ON LOCATION_TEST(LOC_GEO_LOCATION)
            indextype is mdsys.spatial_index;
            All I will say is that it is best practice to use the PARAMETERS clause especially setting the layer_gtype. For example, if your data is 2D and holds only single point sdo_geometries...
            CREATE INDEX LOC_TEST_SIDX ON LOCATION_TEST(LOC_GEO_LOCATION)
            indextype is mdsys.spatial_index
            parameters('sdo_indx_dims=2, layer_gtype=POINT');
            The second is a common problem with SDO_NN with which even experienced users have problems.

            You need to add another hint to your query because you have the predicate WHERE LOC_RID = 11685;

            Try this:
            SELECT /*+ordered INDEX (b CLI_TEST_MAIN_SIDX) */ 
                   CLI_RID, 
                   CLI_FIRST_NAME, 
                   CLI_LAST_NAME,
                   sdo_nn_distance (1) distance
              FROM LOCATION_TEST a, 
                   CLIENTS_TEST b
             WHERE LOC_RID = 11685
               AND sdo_nn(b.CLI_MAIN_GEO_LOCATION, a.LOC_GEO_LOCATION, 'sdo_num_res=5', 1) = 'TRUE'
            The last error seems rather irrelevant.

            If correct, please award the points.

            regards
            Simon
            • 3. Re: using SDO_GEOMETRY
              Barbara Boehmer
              Are you using Enterprise Edition or Standard Edition or Express Edition? I think only Enterprise Edition supports spatial. Your "OPTION OFF" in the dba_registry.status would tend to indicate either you are using an unsupported edition or there is some installation problem.
              • 4. Re: using SDO_GEOMETRY
                Simon Greener
                Barbara,

                The question was not related to whether SDO_NN is supported for Locator users at 10.1 as the query ran and produced a standard error that occurs for all Oracle editions including 11gR2 (Spatial) which is what I am using.

                There is no software based restriction on function use with Spatial or Locator.

                AFAIK, SDO_NN is available for Locator users. The only doco for 10g that I have is for R2 and Appendix D of the Spatial doco does not mention SDO_NN as Spatial only.

                The tip I gave him should solve the SDO_NN query error. Whether he can use it or nor is a licensing issue for him to work out with his IT Manager and local Oracle rep.

                regards
                Simon
                • 5. Re: using SDO_GEOMETRY
                  cptkirkh
                  Sorry for the time between posts i had to get some sleep. First off i would like to thank you Simon for your website it has helped a lot to understand licensing and to get this project rolling. Ok let me see if i can answer your questions a little better. First off i am using Oracle 10gR2 Standard. So that rules out any of the Enterprise features. My underlying data is similar to the link i provided. I have used a third party application we own to to correct address and add long and lat. The basic data looks like this.
                  client_test table
                  CLI_RID
                  CLI_FIRST_NAME
                  CLI_LAST_NAME
                  CLI_MAIN_ADDRESS
                  CLI_CITY
                  CLI_STATE
                  CLI_ZIP
                  CLI_GEO_LOCATION

                  the column labeled cli_geo_location is my data type of SDO_GEOMETRY that was isnerted via a command like this:
                  update clients_test set CLI_GEO_LOCATION = SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE (SUBSTR(flong,0,10),SUBSTR(flat,0,10),NULL), NULL, NULL) where cli_rid = recs.cli_rid;

                  my location _test table
                  LOC_RID
                  LOC_MAIN_ADDRESS
                  LOC_CITY
                  LOC_STATE
                  LOC_ZIP
                  LOC_GEO_LOCATION

                  Where my loc_geo_location column is of sdo_geometry data and the data is inserted the same way as my cli_geo_location column above. I pretty much mimiced the tutorial in the link i posted for both ways of working with this.

                  Simon-- from what i read it seems that i can use the sdo_nn with standard edition. I was able last night to run VALIDATE_SDO and i now have that option set correctly. but i was still getting the error so let me try your suggestions today and i will get back with you.
                  • 6. Re: using SDO_GEOMETRY
                    cptkirkh
                    Simon,
                    It seems after running the original create index that errored out i actually have those three items installed. So i decided to drop them so i can recreate them with the parameters you suggested. SO i went and tried to run this command.


                    drop index cic3.loc_test_sidx

                    and i get this error:


                    ORA-29856: error occurred in the execution of ODCIINDEXDROP routine
                    ORA-29400: data cartridge error
                    ORA-00942: table or view does not exist
                    ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 27
                    ORA-06512: at line 1

                    This seems to happen on all three of the indices i created. SO i htought well fine let me run Alter with those parameters and i looked up the proper syntax from your website.
                    http://www.spatialdbadvisor.com/oracle_spatial_tips_tricks/246/changing-oracle-spatial-index-parameters-on-existing-index

                    so i ran
                    ALTER INDEX cic3.LOC_TEST_SIDX parameters('sdo_indx_dims=2, layer_gtype=POINT');

                    and i got this:

                    ORA-29869: cannot issue ALTER without REBUILD on a domain index marked FAILED

                    So said well i will rebuild the index and i ran this:

                    ALTER INDEX CIC3.LOC_TEST_SIDX REBUILD

                    and i got this error:

                    ORA-29858: error occurred in the execution of ODCIINDEXALTER routine
                    ORA-29400: data cartridge error
                    ORA-01403: no data found
                    ORA-13249: internal error in Spatial index: [mdidxrbd]
                    ORA-13209: internal error while reading SDO_INDEX_METADATA table
                    ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 371
                    ORA-06512: at line 1


                    So i thought fine i will just create a new spatial index on the column and of course i can only have one so that was out. Well then i decided to look at the table "SDO_INDEX_METADATA" listed in the last error. and it has nothing in it. Now for the life of me i cannot find the other reference "MDSYS.SDO_INDEX_METHOD_10I" to see what at line 371 is the issue. What can i do to either alter these indices with the correct parameters or drop them and start again? Thanks.
                    • 7. Re: using SDO_GEOMETRY
                      cptkirkh
                      As you can tell i like to figure things out on my own so i will keep plugging along. Well i managed to drop those indices and tried to run your create index command. I ran the following:

                      CREATE INDEX LOC_TEST_SIDX ON LOCATION_TEST(LOC_GEO_LOCATION)
                      indextype is mdsys.spatial_index
                      parameters('sdo_indx_dims=2, layer_gtype=POINT');

                      this is my error:

                      ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
                      ORA-06550: line 1, column 8:
                      PLS-00201: identifier 'MDSYS.MDPRVT_IDX' must be declared
                      ORA-06550: line 1, column 8:
                      PL/SQL: Statement ignored
                      ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10
                      ORA-06512: at line 1

                      Now i see a MDSYS.PRVT_IDX package but not a MDSYS.MDPRVT_IDX package. Am i missing something? Even though i get this error it does create this index.

                      Edited by: user8602786 on May 8, 2012 9:19 AM
                      • 8. Re: using SDO_GEOMETRY
                        cptkirkh
                        So after all of that i went in and tried the query.

                        SELECT /*+ordered INDEX (b CLI_TEST_MAIN_SIDX) */
                        CLI_RID,
                        CLI_FIRST_NAME,
                        CLI_LAST_NAME,
                        sdo_nn_distance (1) distance
                        FROM LOCATION_TEST a,
                        CLIENTS_TEST b
                        WHERE LOC_RID = 11685
                        AND sdo_nn(b.CLI_MAIN_GEO_LOCATION, a.LOC_GEO_LOCATION, 'sdo_num_res=5', 1) = 'TRUE'


                        and all i got was this

                        ORA-13249: SDO_NN cannot be evaluated without using index
                        ORA-06512: at "MDSYS.MD", line 1723
                        ORA-06512: at "MDSYS.MDERR", line 17
                        ORA-06512: at "MDSYS.PRVT_IDX", line 44
                        ORA-06512: at line 1


                        and at this point i am stumped and out of ideas so hopefully someone can direct me in the right direction. when i run the command:

                        select INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS,ITYP_OWNER,ITYP_NAME,DOMIDX_STATUS,DOMIDX_OPSTATUS from user_indexes where ITYP_NAME is not null;

                        i get this:

                        LOCS_TEST_SIDX     CIC3     LOCATIONS_TEST     VALID     MDSYS     SPATIAL_INDEX     VALID     FAILED

                        now it seems my domidx_opstatus is failed. SO i guess this is due to the fact that my create index failed. so like i said i am stumped so any help wouid be appreciated.

                        Thanks for your help.

                        Edited by: user8602786 on May 8, 2012 9:27 AM
                        • 9. Re: using SDO_GEOMETRY
                          Barbara Boehmer
                          You have a chain reaction of things. You need to start by diagnosing the root problem. You can't run a query using sdo_nn because you don't have a valid index. So, you need to find out why you cannot successfully create an index. At this point, you have such a mess of partial created and partially dropped attempts that may interfere with future attempts that it is hard to tell what is what. Some of the things that you have posted do not match other things, such as the table name. It is difficult to guess whether your problem is due to something with your tables and data and such or something with spatial not working on your system. I think you need to test whether you can do something simple and standard with spatial to determine whether there is a problem there, before you start trying to load complex data. I recommend that you carefully copy and paste the following simple example from the Oracle documentation and see if that executes without error. If it produces an error, then that will tell you that you need to figure out what is wrong with spatial on your system. If it runs without error, then that will tell you that there is something odd about your tables and data. So, please run the following and post the results, so that we will know what to check from there.
                          CREATE TABLE cola_markets (
                            mkt_id NUMBER PRIMARY KEY,
                            name VARCHAR2(32),
                            shape SDO_GEOMETRY)
                          /
                          INSERT INTO cola_markets VALUES(
                            1,
                            'cola_a',
                            SDO_GEOMETRY(
                              2003,  -- two-dimensional polygon
                              NULL,
                              NULL,
                              SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior)
                              SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to
                                    -- define rectangle (lower left and upper right) with
                                    -- Cartesian-coordinate data
                            )
                          )
                          /
                          INSERT INTO user_sdo_geom_metadata
                              (TABLE_NAME,
                               COLUMN_NAME,
                               DIMINFO,
                               SRID)
                            VALUES (
                            'cola_markets',
                            'shape',
                            SDO_DIM_ARRAY(   -- 20X20 grid
                              SDO_DIM_ELEMENT('X', 0, 20, 0.005),
                              SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
                               ),
                            NULL   -- SRID
                          )
                          /
                          CREATE INDEX cola_spatial_idx
                             ON cola_markets(shape)
                             INDEXTYPE IS MDSYS.SPATIAL_INDEX
                          /
                          -- cleanup:
                          DROP INDEX cola_spatial_idx
                          /
                          DELETE FROM user_sdo_geom_metadata
                          WHERE  table_name = 'COLA_MARKETS'
                          AND    column_name = 'SHAPE'
                          /
                          DROP TABLE cola_markets
                          /
                          You should get something like this:
                          TEST@orcl_11gR2> CREATE TABLE cola_markets (
                            2    mkt_id NUMBER PRIMARY KEY,
                            3    name VARCHAR2(32),
                            4    shape SDO_GEOMETRY)
                            5  /
                          
                          Table created.
                          
                          TEST@orcl_11gR2> INSERT INTO cola_markets VALUES(
                            2    1,
                            3    'cola_a',
                            4    SDO_GEOMETRY(
                            5        2003,     -- two-dimensional polygon
                            6        NULL,
                            7        NULL,
                            8        SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior)
                            9        SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to
                           10              -- define rectangle (lower left and upper right) with
                           11              -- Cartesian-coordinate data
                           12    )
                           13  )
                           14  /
                          
                          1 row created.
                          
                          TEST@orcl_11gR2> INSERT INTO user_sdo_geom_metadata
                            2        (TABLE_NAME,
                            3         COLUMN_NAME,
                            4         DIMINFO,
                            5         SRID)
                            6    VALUES (
                            7    'cola_markets',
                            8    'shape',
                            9    SDO_DIM_ARRAY(     -- 20X20 grid
                           10        SDO_DIM_ELEMENT('X', 0, 20, 0.005),
                           11        SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
                           12         ),
                           13    NULL   -- SRID
                           14  )
                           15  /
                          
                          1 row created.
                          
                          TEST@orcl_11gR2> CREATE INDEX cola_spatial_idx
                            2       ON cola_markets(shape)
                            3       INDEXTYPE IS MDSYS.SPATIAL_INDEX
                            4  /
                          
                          Index created.
                          
                          TEST@orcl_11gR2> -- cleanup:
                          TEST@orcl_11gR2> DROP INDEX cola_spatial_idx
                            2  /
                          
                          Index dropped.
                          
                          TEST@orcl_11gR2> DELETE FROM user_sdo_geom_metadata
                            2  WHERE  table_name = 'COLA_MARKETS'
                            3  AND    column_name = 'SHAPE'
                            4  /
                          
                          1 row deleted.
                          
                          TEST@orcl_11gR2> DROP TABLE cola_markets
                            2  /
                          
                          Table dropped.
                          • 10. Re: using SDO_GEOMETRY
                            cptkirkh
                            Sorry about the change in table names. Both tables client_test and location_test contain sdo_geometry data_types. I was using them similar tot he customer and warehouse tutorial i posted. I switched to the location_test table because it has fewer records. I ran your script.

                            1. the cola_markets table was created
                            2. the values were inserted into cola_markets.
                            3. the values were isnerted into the user_sdo_geom_metadata table.
                            4. When i ran the create index code for the aptial index i recieved the same error i have always recieved.

                            my error:


                            ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
                            ORA-06550: line 1, column 8:
                            PLS-00201: identifier 'MDSYS.MDPRVT_IDX' must be declared
                            ORA-06550: line 1, column 8:
                            PL/SQL: Statement ignored
                            ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10
                            ORA-06512: at line 1

                            I will repeat the fact that i don't have any thing in my DB that is called MDSYS.MDPRVT_idx and i do have a MDSYS.PRVT_IDX package.
                            thanks for your help.
                            • 11. Re: using SDO_GEOMETRY
                              Barbara Boehmer
                              What I meant by changing names was, for example, that your posted code that you supposedly ran used the names loc_test and location_test, but your select statement showed locs_test and locations_test.

                              Based on your run of the simple test code and the resulting error, it appears that you have a general problem with spatial on your system. What schema did you run the test script from? It should not be run from MDSYS or SYS or SYSTEM or any other such special user.

                              The error that you are getting usually means that either the object does not exist or you do not have privileges to see it. I trust that you searched for mdsys.mdprvt_idx from the mdsys schema, so that no lack of privileges could be involved. You say you do not have an mdsys.mdprvt_idx package on your system, but I do on my system, so that tends to indicate that something is missing on your system, unless it is due to a difference in versions.

                              I would suggest that you try to do a re-installation of spatial by running <your_oracle_home_directory>\md\admin\catmd.sql while connected as sys. It may be that a previous installation failed. It may also be that it was installed while connected as something other than sys, which may have left pieces in a wrong schema that may need to be cleaned up.
                              • 12. Re: using SDO_GEOMETRY
                                cptkirkh
                                more fishing.

                                I ran the query SELECT comp_name, version, status FROM dba_registry;

                                If you notice below my Oracle spatial is set to 10.1.0.3.0. Now since i am using standard i am not licensed for Spatial but does Locator need this to be up to the current version of 10.2.0.3.0?

                                Here is what I got:


                                COMP_NAME     VERSION     STATUS
                                Oracle Ultra Search     10.2.0.3.0     VALID
                                Oracle Enterprise Manager     10.2.0.3.0     VALID
                                Oracle XML Database     10.2.0.3.0     VALID
                                OLAP Catalog     10.1.0.3.0     OPTION OFF
                                Oracle Text     10.2.0.3.0     VALID
                                Spatial     10.1.0.3.0     VALID
                                Oracle interMedia     10.2.0.3.0     VALID
                                Oracle Expression Filter     10.2.0.3.0     VALID
                                Oracle Workspace Manager     10.2.0.1.0     VALID
                                Oracle Data Mining     10.1.0.3.0     OPTION OFF
                                Oracle Database Catalog Views     10.2.0.3.0     VALID
                                Oracle Database Packages and Types     10.2.0.3.0     VALID
                                JServer JAVA Virtual Machine     10.2.0.3.0     VALID
                                Oracle XDK     10.2.0.3.0     VALID
                                Oracle Database Java Packages     10.2.0.3.0     VALID
                                OLAP Analytic Workspace     10.1.0.3.0     OPTION OFF
                                Oracle OLAP API     10.1.0.3.0     OPTION OFF
                                • 13. Re: using SDO_GEOMETRY
                                  Barbara Boehmer
                                  It looks like your Oracle 10gR2 standard edition should have locator automatically installed. Since the locator is only a portion of spatial, I don't know how Oracle handles and records this internally. The documentation section below tells you what you can and cannot do with locator, but does not say how to re-install it. According to that documentation, you should be able to do what you are trying to do. My inclination would still be to try a re-install of spatial and hope that it correctly installs the features that constitute locator.

                                  http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_locator.htm#SPATL340
                                  • 14. Re: using SDO_GEOMETRY
                                    Barbara Boehmer
                                    You might try running <your_oracle_home_directory>\md\admin\prvtsidx.plb as suggested in the following thread when someone encountered the same error:

                                    Error Creating Index (After DB Upgrade)
                                    1 2 Previous Next