Re: Oracle Spatial SDO Aggregate Union fails with ORA-29913 ODCIAGGREGATEMERGE

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 12 Jun 2008 18:32:19 +0200
Message-ID: <48514f99$0$14357$e4fe514c@news.xs4all.nl>

"Eduard Witteveen" <eywitteveen_at_gmail.com> schreef in bericht news:f26766d3-a914-4f72-96d9-05226fc4bb5e_at_c58g2000hsc.googlegroups.com... On Jun 12, 3:04 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:
> ...
> ORA-29913 may have something to do with a too small tolerance setting. Try
> a
> larger one and see what happens.

Look at the end for output of the tolerance's

> What OS are you on?

The server is running on:
cpu: 3.40 GHZ intel xeon(hyperthreaded)
ram: 4032mb
os: Windows 2003 SP
servicepack: 2

<tolorance10cm>
SQL> SELECT
  2 DISTINCT(GUID),
  3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.1)) AS GEOMETRIE   4 FROM
  5 (
  6 SELECT
  7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID,   8 TOPO.GEOMETRIE AS GEOMETRIE
  9 FROM DGDTW_TOPOGRAFIE TOPO
10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI 13 ON MULTI.CHILD = GUIDS.ID
14 AND ARCHIVE IS NULL
15 WHERE TOPO.VERVAL IS NULL
16 AND NOT TOPO.PARAMETERS = 515
17 -- check the input for the SDO_AGGR_UNION 18 AND TOPO.GEOMETRIE.SDO_GTYPE = 2003 19 AND SDO_GEOM.VALIDATE_GEOMETRY(GEOMETRIE, 0.1) = 'TRUE' 20 ) LINES
21 GROUP BY GUID;
SELECT
*
FOUT in regel 1:
.ORA-29913: Er is een fout opgetreden bij het uitvoeren van de toelichting
ODCIAGGREGATEMERGE.
</tolorance10cm>

<tolorance1m>
SQL> SELECT
  2 DISTINCT(GUID),
  3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 1)) AS GEOMETRIE   4 FROM
  5 (
  6 SELECT
  7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID,   8 TOPO.GEOMETRIE AS GEOMETRIE
  9 FROM DGDTW_TOPOGRAFIE TOPO
10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI 13 ON MULTI.CHILD = GUIDS.ID
14 AND ARCHIVE IS NULL
15 WHERE TOPO.VERVAL IS NULL
16 AND NOT TOPO.PARAMETERS = 515
17 -- check the input for the SDO_AGGR_UNION 18 AND TOPO.GEOMETRIE.SDO_GTYPE = 2003 19 AND SDO_GEOM.VALIDATE_GEOMETRY(GEOMETRIE, 1) = 'TRUE' 20 ) LINES
21 GROUP BY GUID; GUID

GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

{001FA2FC-1448-42C5-BC5F-480AD7392891}
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(-3,403E+38, 3,4028E+38, -3,403E+38, -3,403E+38, 3,4028E+38, -3,403E +38, 3,402

8E+38, 3,4028E+38, -3,403E+38, 3,4028E+38))

{003E7376-7418-4361-A139-F58251E06127}
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(252697,14, 556409,2, 252690,788, 556404,936, 252711,182, 556400,086, 252716,6

7, 556404,21, 252697,14, 556409,2))

GUID

GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

{00708284-7AB4-4CCC-82D1-CE86BD38777F}
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR

AY(247689,96, 558009,63, 247722,7, 558019,81, 247722,152, 558021,61,
247689,42,
558011,33, 247689,96, 558009,63))

{00832FCD-A3D6-4422-BDE1-BE937C0740E8}
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(241710,281, 551011,756, 241711,873, 551007,536, 241720,75, 551012,4, 241735,7

................................
................................
................................

SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(244241,1, 564379,01, 244242,76, 564376,3, 244259,59, 564386,54, 244251,37, 56

4400,14, 244245,54, 564396,48, 244251,6, 564385,54, 244241,1, 564379,01))

{7A42B22C-F30D-460C-8615-8B6083BBB3F7}
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(247852,537, 558231,679, 247850,79, 558231,19, 247855,48, 558215,5, 247862,903

, 558191,244, 247866,38, 558179,88, 247868,317, 558180,469, 247864,422, 558193,2

38, 247863,5, 558196,26, 247852,537, 558231,679))

GUID

GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

{7A687E8B-B8EC-41D9-80CB-3EC2C2C44EF2}
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(-3,403E+38, 3,4028E+38, -3,403E+38, -3,403E+38, 3,4028E+38, -3,403E +38, 3,402

8E+38, 3,4028E+38, -3,403E+38, 3,4028E+38))

ERROR:
ORA-03113: Einde-van-bestand op communicatiekanaal.

375 rijen zijn geselecteerd.

SQL>
</tolorance1m>

<tolorance1cm-withoutcheck>
SQL> SELECT
  2 DISTINCT(GUID),
  3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, .01)) AS GEOMETRIE   4 FROM
  5 (
  6 SELECT
  7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID,   8 TOPO.GEOMETRIE AS GEOMETRIE
  9 FROM DGDTW_TOPOGRAFIE TOPO
10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI 13 ON MULTI.CHILD = GUIDS.ID
14 AND ARCHIVE IS NULL
15 WHERE TOPO.VERVAL IS NULL
16 AND NOT TOPO.PARAMETERS = 515
17 ) LINES
18 GROUP BY GUID;
SELECT
*
FOUT in regel 1:
.ORA-29913: Er is een fout opgetreden bij het uitvoeren van de toelichting
ODCIAGGREGATEMERGE.
</tolorance1cm-withoutcheck>

<tolorance10cm-withoutcheck>
SQL> SELECT
  2 DISTINCT(GUID),
  3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.1)) AS GEOMETRIE   4 FROM
  5 (
  6 SELECT
  7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID,   8 TOPO.GEOMETRIE AS GEOMETRIE
  9 FROM DGDTW_TOPOGRAFIE TOPO
10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI 13 ON MULTI.CHILD = GUIDS.ID
14 AND ARCHIVE IS NULL
15 WHERE TOPO.VERVAL IS NULL
16 AND NOT TOPO.PARAMETERS = 515
17 ) LINES
18 GROUP BY GUID;
SELECT
*
FOUT in regel 1:
.ORA-29913: Er is een fout opgetreden bij het uitvoeren van de toelichting
ODCIAGGREGATEITERATE.
ORA-13347: De co�rdinaten voor een boog mogen niet gelijk zijn. <tolorance10cm-withoutcheck>

It looks like you have some problematic geometries (which is often the case with spatial). The aggregate causes points to overlap. You could try a SMALLER tolerance, like 0.0005. Still, with the aggregate function, if two geometries have some overlap, they will be considered one, which could cause some strange effects. Another thing I have seen in the past is that flushing the SGA before executing your script may help.

By the way: the error is a wrong translation. Call out is translated as toelichting, which is not correct. ODCIAGGREGATEITERATE is an external callout function. Too large values (in size) can cause problems like this. I have not seen workarounds for this. But there must be another way to join multiple geometries to one.

Shakespeare Received on Thu Jun 12 2008 - 11:32:19 CDT

This message: [ Message body ]
Next message: Shakespeare: "Re: Oracle Spatial SDO Aggregate Union fails with ORA-29913 ODCIAGGREGATEMERGE"
Previous message: Eduard Witteveen: "Re: Oracle Spatial SDO Aggregate Union fails with ORA-29913 ODCIAGGREGATEMERGE"
In reply to: Eduard Witteveen: "Re: Oracle Spatial SDO Aggregate Union fails with ORA-29913 ODCIAGGREGATEMERGE"
Next in thread: Shakespeare: "Re: Oracle Spatial SDO Aggregate Union fails with ORA-29913 ODCIAGGREGATEMERGE"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message