4 Replies Latest reply: Mar 17, 2013 3:19 PM by Simon Greener RSS

    Spatial Index Issue(s) ORA-29902 ORA-13200 ORA-13220 ORA-06512

    994501
      I am having issues running the query below. If I change the values of the SDO_ORDINATE_ARRAY, some values work, some values don't. Below is an example of when it fails. I have successful queries using SDO_NN, so something must be working...

      How should I go about figuring out this issue (very new to Spatial)? Working on Windows 7 Prof OS.

      select p.building_polygon
      from spat.building p, spat.firebuilding f
      where p.building_name=f.building_on_fire and
      SDO_RELATE(p.building_polygon,
      SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),
      SDO_ORDINATE_ARRAY(768,278,477,504,155,115)),'mask=ANYINTERACT') = 'TRUE'
      *
      Error at line 0
      ORA-29902: error in executing ODCIIndexStart() routine
      ORA-13200: internal error [WINDOW_OBJECT] in spatial indexing.
      ORA-13220: failed to compare tile with the geometry
      ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 333

      Script used to create objects:
      create user spat identified by spat;
      grant all privileges to spat;

      conn spat/spat;
      alter session set current_schema = spat;
      SET DEFINE OFF;

      CREATE TABLE spat.BUILDING
      (
      BUILDING_ID VARCHAR2(3),
      BUILDING_NAME VARCHAR2(50),
      VERTICES_COUNT INT,
      BUILDING_POLYGON MDSYS.SDO_GEOMETRY,
      CONSTRAINT BUILDING_PK PRIMARY KEY (BUILDING_ID)
      );

      CREATE TABLE spat.FIREHYDRANT
      (
      FIREHYDRANT_ID VARCHAR2(3),
      HYDRANT_POINT MDSYS.SDO_GEOMETRY,
      CONSTRAINT FIREHYDRANT_PK PRIMARY KEY (FIREHYDRANT_ID)
      );

      CREATE TABLE spat.FIREBUILDING
      (
      BUILDING_ON_FIRE VARCHAR(50),
      CONSTRAINT FIREBUILDING_PK PRIMARY KEY (BUILDING_ON_FIRE)
      );

      INSERT INTO USER_SDO_GEOM_METADATA
      VALUES ('BUILDING', 'BUILDING_POLYGON',
      SDO_DIM_ARRAY( SDO_DIM_ELEMENT('X', 0, 820, 0.005),
      SDO_DIM_ELEMENT('Y', 0, 580, 0.005)), NULL);

      INSERT INTO USER_SDO_GEOM_METADATA
      VALUES ('FIREHYDRANT', 'HYDRANT_POINT',
      SDO_DIM_ARRAY( SDO_DIM_ELEMENT('X', 0, 820, 0.005),
      SDO_DIM_ELEMENT('Y', 0, 580, 0.005)), NULL);

      COMMIT;

      CREATE INDEX spat.BUILDING_SPATIAL_IDX
      ON spat.BUILDING(BUILDING_POLYGON)
      INDEXTYPE IS MDSYS.SPATIAL_INDEX;

      CREATE INDEX spat.FIREHYDRANT_SPATIAL_IDX
      ON spat.FIREHYDRANT(HYDRANT_POINT)
      INDEXTYPE IS MDSYS.SPATIAL_INDEX;

      COMMIT;

      ==================UPDATE==========================
      Ok, I've noticed something interesting. If I have 3 points in my ordinate array, the errors are thrown. If I have more than 3 points in my ordinate array, it works. Could anyone explain why this is and if there is a way around it?

      select p.building_polygon
      from spat.building p, elkordy.firebuilding f
      where p.building_name=f.building_on_fire
      and SDO_ANYINTERACT(p.building_polygon
      , SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1)
      --,SDO_ORDINATE_ARRAY(232,91,149,226,277,198))) = 'TRUE'; -- USE ONLY ONE
      --,SDO_ORDINATE_ARRAY(165,84,168,484,769,534,743,108))) = 'TRUE'; -- USE ONLY ONE

      Edited by: AmeerE on Mar 17, 2013 12:19 AM
        • 1. Re: Spatial Index Issue(s) ORA-29902 ORA-13200 ORA-13220 ORA-06512
          Ivan Bush
          Ameer,

          It ia always difficult starting in Oracle Spatial. There is plenty of documentation and examples on the Oracle web site. I think it is advisable to read some of the Oracle documentation.

          If you look at this:
          http://docs.oracle.com/cd/B12037_01/appdev.101/b10826/sdo_objrelschema.htm

          you will see that a polygon needs a minimum of 4 coordinate pairs.

          There is some excellent information on Simon Greener's web site http://www.spatialdbadvisor.com/

          Regards

          Ivan
          • 2. Re: Spatial Index Issue(s) ORA-29902 ORA-13200 ORA-13220 ORA-06512
            Simon Greener
            Ameer,

            You say you are new to Spatial.

            The issue you face is that 3 vertices are not enough for a polygon. For Oracle Spatial a polygon outer boundary (1003) must have 4 vertices with the first being the last.

            So:
            select sdo_geom.validate_geometry(SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(232,91,149,226,277,198)),0.05) as  from dual;
            VGEOM
            -----
            13343
            From Oracle's Error documention;
            ORA-13343: a polygon geometry has fewer than four coordinates
            Cause: A geometry, specified as being a polygon, has fewer than four coordinates in its definition.
            Action: A polygon must consist of at least four distinct coordinates. Correct the geometric definition, or set the appropriate SDO_GTYPE or SDO_ETYPE attribute for this geometry.
            So, the solution is to repeat the first vertex:
            select sdo_geom.validate_geometry(SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(232,91,149,226,277,198,232,91)),0.05) as geom from dual;
            VGEOM
            -----
            13367
            13357 is another error ....
            ORA-13367: wrong orientation for interior/exterior rings
            Cause: In an Oracle Spatial geometry, the exterior and/or interior rings are not oriented correctly.
            Action: Be sure that the exterior rings are oriented counterclockwise and the interior rings are oriented clockwise.
            Note that Action tells you exactly the right ordering for the rings.

            This is solved by reversing the coordinates as follows:
            select sdo_geom.validate_geometry(SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(232,91,277,198,149,226,232,91)),0.05) as vgeom from dual;
            VGEOM
            -----
            TRUE
            This is now a valid geometry and will work in your sdo_anyinteract etc queries.

            Always make sure your geometries both stored in a table's column, and supplied to a query otherwise, are VALID.

            If this answers your query, please award me the points you think I deserve.

            regards
            SImon
            • 3. Re: Spatial Index Issue(s) ORA-29902 ORA-13200 ORA-13220 ORA-06512
              994501
              Thank you both for responding! I came to the same conclusion after some trial and error, but I need to be more thorough with the oracle docs.

              Thanks again!
              • 4. Re: Spatial Index Issue(s) ORA-29902 ORA-13200 ORA-13220 ORA-06512
                Simon Greener
                Ameer,

                Thank you very much for awarding the points: I greatly appreciate it.

                About the documentation, I have been using Oracle Spatial since 8i (and aware of its offerings before).

                I think I reference the Oracle documentation every day and even to the point of keeping a copy of the Oracle documentation on my hard-drive in case my irregular internet connection goes down!

                So, even the "experts" have recourse to the doco... glad to see you joining the club!

                regards
                Simon