3 Replies Latest reply: Nov 22, 2013 8:26 AM by Ying Hu-Oracle RSS

    SDO_AGGR_UNION gives ODCI errors

    1010855


      I have a Oracle spatial table having over 2 millon records. I need to aggregate all the buffer geometries based on names such that for each name there will be only one aggregated geometry. There are around 5000 distinct name.

       

      I am running the following query,

       

      SELECT NAME, COUNT(GEOM),

           SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE

           (SDO_GEOM.SDO_BUFFER

           (GEOM, 100.0, 0.5, 'UNIT=FOOT'), 0.05))

      FROM GEOM_TABLE

      GROUP BY NAME;

       

      I am getting the following errors,

       

      ORA-29913: error in executing ODCIAGGREGATEMERGE callout

      ORA-29913: error in executing ODCIAGGREGATEITERATE callout

      ORA-13347: the coordinates defining an arc are not distinct

       

      When I am limiting the number of geometries during aggregation it is working fine but failing with real data. Since SDO_AGGR_UNION is very slow in performance, what are the other available solutions. SDO_AGGR_SET_UNION doesn't either.

       

      Regards,

      Ananda

        • 1. Re: SDO_AGGR_UNION gives ODCI errors
          Ying Hu-Oracle

          You may split your query into two, to measure their performance:

           

          CREATE TABLE BUFF_GEOM_TABLE (NAME VARCHAR2(***), GEOM SDO_GEOMETRY);

          INSERT INTO BUF_GEOM_TABLE SELECT NAME,  SDO_GEOM.SDO_BUFFER(GEOM, 100.0, 0.5, 'UNIT=FOOT'), 0.05)

          FROM GEOM_TABLE;

           

          SELECT NAME, COUNT(GEOM),

               SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE

               (GEOM, 0.05))

          FROM BUFF_GEOM_TABLE

          GROUP BY NAME;

           

          If the bottleneck is with SDO_AGGR_UNION, you may have some options:

          If you have 12c Oracle Spatial, you can enable the Vector Performance Accelerator (VPA) feature by setting the

          SPATIAL_VECTOR_ACCELERATION database system parameter to the value TRUE.

          ALTER SYSTEM/SESSION SET SPATIAL_VECTOR_ACCELERATION = TRUE;

           

          If you have 11gR2, you may use SDO_AGGR_SET_UNION. Please see an example of SDO_AGGR_SET_UNION:

          Spatial Aggregate Functions

          • 2. Re: SDO_AGGR_UNION gives ODCI errors
            1010855

            Insert into select is also giving the following error. Even tried /*+APPEND*/ hint but not working.

            ORA-29532: Java call terminated by uncaught Java exception:

            java.lang.IllegalArgumentException: Illegal Capacity: -1

            ORA-06512: at "MDSYS.SDO_GEOM", line 1308

            ORA-06512: at "MDSYS.SDO_GEOM", line 138

             

            Regards,

            Ananda

            • 3. Re: SDO_AGGR_UNION gives ODCI errors
              Ying Hu-Oracle

              So it looks to be a sdo_buffer problem. Just make sure your geometries are validated,

              and maybe ask oracle support to get some sdo_buffer patch.