Oracle Spatial SDO Aggregate Union fails with ORA-29913 ODCIAGGREGATEMERGE [Oracle Help]

Prev: oracle 9i: errors by clone a DB by a script
Next: NLS_LANG

From: Eduard Witteveen on 12 Jun 2008 04:28

Hello,

I have a (application-)database which is filled with polygons, also
there is a administrative coupling on a guid. The guid is unique for
the administative part, but can contain more than 1 polygon. For this
reason i want to union the polygons, so i have 1 geometry for 1 guid.
Well, i managed to do this trick on the table that contains the point
information, however doing this on the table with the polygons gives
met the following error: ORA-29913 : blabla error blabla
ODCIAGGREGATEMERGE
This error is generated by the function: SDO_AGGR_UNION

I also tried to put filtering on the input of the function, but it
doesnt seem to make any difference(see query 3).

Can somebody help me how i can get a query / view with the information
guid + geometry?

Eduard Witteveen

======================================================================
Below are the queries:
1 - The version
2 - The query i want to execute
3 - The query with added checks for the SDO_AGGR_UNION
======================================================================

SQL*Plus: Release 9.2.0.1.0 - Production on Do Jun 12 10:04:26 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Voer wachtwoord in:

Verbonden met:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

SQL> select * from v$version
2 where banner like 'Oracle%';

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

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

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

SQL>

From: Shakespeare on 12 Jun 2008 05:10

"Eduard Witteveen" <eywitteveen(a)gmail.com> schreef in bericht
news:f6a38868-94f7-42f8-bbc7-6fad5dd6e172(a)2g2000hsn.googlegroups.com...
> Hello,
>
> I have a (application-)database which is filled with polygons, also
> there is a administrative coupling on a guid. The guid is unique for
> the administative part, but can contain more than 1 polygon. For this
> reason i want to union the polygons, so i have 1 geometry for 1 guid.
> Well, i managed to do this trick on the table that contains the point
> information, however doing this on the table with the polygons gives
> met the following error: ORA-29913 : blabla error blabla
> ODCIAGGREGATEMERGE
> This error is generated by the function: SDO_AGGR_UNION
>
> I also tried to put filtering on the input of the function, but it
> doesnt seem to make any difference(see query 3).
>
> Can somebody help me how i can get a query / view with the information
> guid + geometry?
>
>
> Eduard Witteveen
>
>
> ======================================================================
> Below are the queries:
> 1 - The version
> 2 - The query i want to execute
> 3 - The query with added checks for the SDO_AGGR_UNION
> ======================================================================
>
>
> SQL*Plus: Release 9.2.0.1.0 - Production on Do Jun 12 10:04:26 2008
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> Voer wachtwoord in:
>
> Verbonden met:
> Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.7.0 - Production
>
> SQL> select * from v$version
> 2 where banner like 'Oracle%';
>
> BANNER
> ----------------------------------------------------------------
> Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
>
> SQL> SELECT
> 2 DISTINCT(GUID),
> 3 COUNT(GEOMETRIE) AS AANTAL,
> 4 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE
> 5 FROM
> 6 (
> 7 SELECT
> 8 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID,
> 9 MULTI.PARENT AS PARENTGUID,
> 10 GUIDS.ID AS CHILDGUID,
> 11 TOPO.ID,
> 12 TOPO.GEOMETRIE AS GEOMETRIE
> 13 FROM DGDTW_TOPOGRAFIE TOPO
> 14 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
> 15 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
> 16 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI
> 17 ON MULTI.CHILD = GUIDS.ID
> 18 AND ARCHIVE IS NULL
> 19 WHERE TOPO.VERVAL IS NULL
> 20 AND NOT TOPO.PARAMETERS = 515
> 21 ORDER BY GUID
> 22 ) LINES
> 23 GROUP BY GUID;
> SELECT
> *
> FOUT in regel 1:
> .ORA-29913: Er is een fout opgetreden bij het uitvoeren van de
> toelichting
> ODCIAGGREGATEMERGE.
>
>
> SQL> SELECT
> 2 DISTINCT(GUID),
> 3 COUNT(GEOMETRIE) AS AANTAL,
> 4 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE
> 5 FROM
> 6 (
> 7 SELECT
> 8 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID,
> 9 MULTI.PARENT AS PARENTGUID,
> 10 GUIDS.ID AS CHILDGUID,
> 11 TOPO.ID,
> 12 TOPO.GEOMETRIE AS GEOMETRIE
> 13 FROM DGDTW_TOPOGRAFIE TOPO
> 14 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
> 15 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
> 16 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI
> 17 ON MULTI.CHILD = GUIDS.ID
> 18 AND ARCHIVE IS NULL
> 19 WHERE TOPO.VERVAL IS NULL
> 20 AND NOT TOPO.PARAMETERS = 515
> 21 -- check the input for the SDO_AGGR_UNION
> 22 AND TOPO.GEOMETRIE.SDO_GTYPE = 2003
> 23 AND SDO_GEOM.VALIDATE_GEOMETRY(GEOMETRIE, 0.001) = 'TRUE'
> 24 ORDER BY GUID
> 25 ) LINES
> 26 GROUP BY GUID;
> SELECT
> *
> FOUT in regel 1:
> .ORA-29913: Er is een fout opgetreden bij het uitvoeren van de
> toelichting
> ODCIAGGREGATEMERGE.
>
>
> SQL>

Maybe I am not seeing things right here, but why are you selecting all those
columns in your inner query when you only use the GEOMETRIE column on the
outside? Why is the order by clause in the inner query?

Did you try to call SDO_AGGR_UNION on some of the geometries without the
complex query?

Shakespeare

From: Eduard Witteveen on 12 Jun 2008 08:20

1 - I have to get an GUID for the union of the GEOMETRIE, The inner
statment contains indeed too much columns, but i'm used to work from
inside out. This means that i want to have good debug information,
before i start a level higher.

2 - When i perform the query:
SQL> SELECT SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE FROM DGDTW_TOPOGRAFIE;
SELECT SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE FROM DGDTW_TOPOGRAFIE
*
FOUT in regel 1:
..ORA-03113: Einde-van-bestand op communicatiekanaal

On Jun 12, 11:10 am, "Shakespeare" <what...(a)xs4all.nl> wrote:
> "Eduard Witteveen" <eywittev...(a)gmail.com> schreef in berichtnews:f6a38868-94f7-42f8-bbc7-6fad5dd6e172(a)2g2000hsn.googlegroups.com...
>
>
>
> > Hello,
>
> > I have a (application-)database which is filled with polygons, also
> > there is a administrative coupling on a guid. The guid is unique for
> > the administative part, but can contain more than 1 polygon. For this
> > reason i want to union the polygons, so i have 1 geometry for 1 guid.
> > Well, i managed to do this trick on the table that contains the point
> > information, however doing this on the table with the polygons gives
> > met the following error: ORA-29913 : blabla error blabla
> > ODCIAGGREGATEMERGE
> > This error is generated by the function: SDO_AGGR_UNION
>
> > I also tried to put filtering on the input of the function, but it
> > doesnt seem to make any difference(see query 3).
>
> > Can somebody help me how i can get a query / view with the information
> > guid + geometry?
>
> > Eduard Witteveen
>
> > ======================================================================
> > Below are the queries:
> > 1 - The version
> > 2 - The query i want to execute
> > 3 - The query with added checks for the SDO_AGGR_UNION
> > ======================================================================
>
> > SQL*Plus: Release 9.2.0.1.0 - Production on Do Jun 12 10:04:26 2008
>
> > Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> > Voer wachtwoord in:
>
> > Verbonden met:
> > Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
> > With the Partitioning, OLAP and Oracle Data Mining options
> > JServer Release 9.2.0.7.0 - Production
>
> > SQL> select * from v$version
> > 2 where banner like 'Oracle%';
>
> > BANNER
> > ----------------------------------------------------------------
> > Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
>
> > SQL> SELECT
> > 2 DISTINCT(GUID),
> > 3 COUNT(GEOMETRIE) AS AANTAL,
> > 4 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE
> > 5 FROM
> > 6 (
> > 7 SELECT
> > 8 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID,
> > 9 MULTI.PARENT AS PARENTGUID,
> > 10 GUIDS.ID AS CHILDGUID,
> > 11 TOPO.ID,
> > 12 TOPO.GEOMETRIE AS GEOMETRIE
> > 13 FROM DGDTW_TOPOGRAFIE TOPO
> > 14 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
> > 15 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
> > 16 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI
> > 17 ON MULTI.CHILD = GUIDS.ID
> > 18 AND ARCHIVE IS NULL
> > 19 WHERE TOPO.VERVAL IS NULL
> > 20 AND NOT TOPO.PARAMETERS = 515
> > 21 ORDER BY GUID
> > 22 ) LINES
> > 23 GROUP BY GUID;
> > SELECT
> > *
> > FOUT in regel 1:
> > .ORA-29913: Er is een fout opgetreden bij het uitvoeren van de
> > toelichting
> > ODCIAGGREGATEMERGE.
>
> > SQL> SELECT
> > 2 DISTINCT(GUID),
> > 3 COUNT(GEOMETRIE) AS AANTAL,
> > 4 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE
> > 5 FROM
> > 6 (
> > 7 SELECT
> > 8 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID,
> > 9 MULTI.PARENT AS PARENTGUID,
> > 10 GUIDS.ID AS CHILDGUID,
> > 11 TOPO.ID,
> > 12 TOPO.GEOMETRIE AS GEOMETRIE
> > 13 FROM DGDTW_TOPOGRAFIE TOPO
> > 14 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
> > 15 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
> > 16 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI
> > 17 ON MULTI.CHILD = GUIDS.ID
> > 18 AND ARCHIVE IS NULL
> > 19 WHERE TOPO.VERVAL IS NULL
> > 20 AND NOT TOPO.PARAMETERS = 515
> > 21 -- check the input for the SDO_AGGR_UNION
> > 22 AND TOPO.GEOMETRIE.SDO_GTYPE = 2003
> > 23 AND SDO_GEOM.VALIDATE_GEOMETRY(GEOMETRIE, 0.001) = 'TRUE'
> > 24 ORDER BY GUID
> > 25 ) LINES
> > 26 GROUP BY GUID;
> > SELECT
> > *
> > FOUT in regel 1:
> > .ORA-29913: Er is een fout opgetreden bij het uitvoeren van de
> > toelichting
> > ODCIAGGREGATEMERGE.
>
> > SQL>
>
> Maybe I am not seeing things right here, but why are you selecting all those
> columns in your inner query when you only use the GEOMETRIE column on the
> outside? Why is the order by clause in the inner query?
>
> Did you try to call SDO_AGGR_UNION on some of the geometries without the
> complex query?
>
> Shakespeare

From: Eduard Witteveen on 12 Jun 2008 08:33

From: Shakespeare on 12 Jun 2008 09:04

See below... please don't top post (some members here don't like that)

"Eduard Witteveen" <eywitteveen(a)gmail.com> schreef in bericht
news:8240cb4c-dddd-4667-8ef9-a799fb2689e5(a)l42g2000hsc.googlegroups.com...
>1 - I have to get an GUID for the union of the GEOMETRIE, The inner
> statment contains indeed too much columns, but i'm used to work from
> inside out. This means that i want to have good debug information,
> before i start a level higher.
>
> 2 - When i perform the query:
> SQL> SELECT SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
> GEOMETRIE FROM DGDTW_TOPOGRAFIE;
> SELECT SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
> GEOMETRIE FROM DGDTW_TOPOGRAFIE
> *
> FOUT in regel 1:
> .ORA-03113: Einde-van-bestand op communicatiekanaal
>
> On Jun 12, 11:10 am, "Shakespeare" <what...(a)xs4all.nl> wrote:
>> "Eduard Witteveen" <eywittev...(a)gmail.com> schreef in
>> berichtnews:f6a38868-94f7-42f8-bbc7-6fad5dd6e172(a)2g2000hsn.googlegroups.com...
>>
>>
>>
>> > Hello,
>>
>> > I have a (application-)database which is filled with polygons, also
>> > there is a administrative coupling on a guid. The guid is unique for
>> > the administative part, but can contain more than 1 polygon. For this
>> > reason i want to union the polygons, so i have 1 geometry for 1 guid.
>> > Well, i managed to do this trick on the table that contains the point
>> > information, however doing this on the table with the polygons gives
>> > met the following error: ORA-29913 : blabla error blabla
>> > ODCIAGGREGATEMERGE
>> > This error is generated by the function: SDO_AGGR_UNION
>>
>> > I also tried to put filtering on the input of the function, but it
>> > doesnt seem to make any difference(see query 3).
>>
>> > Can somebody help me how i can get a query / view with the information
>> > guid + geometry?
>>
>> > Eduard Witteveen
>>
>> > ======================================================================
>> > Below are the queries:
>> > 1 - The version
>> > 2 - The query i want to execute
>> > 3 - The query with added checks for the SDO_AGGR_UNION
>> > ======================================================================
>>
>> > SQL*Plus: Release 9.2.0.1.0 - Production on Do Jun 12 10:04:26 2008
>>
>> > Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>>
>> > Voer wachtwoord in:
>>
>> > Verbonden met:
>> > Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
>> > With the Partitioning, OLAP and Oracle Data Mining options
>> > JServer Release 9.2.0.7.0 - Production
>>
>> > SQL> select * from v$version
>> > 2 where banner like 'Oracle%';
>>
>> > BANNER
>> > ----------------------------------------------------------------
>> > Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
>>
>> > SQL> SELECT
>> > 2 DISTINCT(GUID),
>> > 3 COUNT(GEOMETRIE) AS AANTAL,
>> > 4 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE
>> > 5 FROM
>> > 6 (
>> > 7 SELECT
>> > 8 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID,
>> > 9 MULTI.PARENT AS PARENTGUID,
>> > 10 GUIDS.ID AS CHILDGUID,
>> > 11 TOPO.ID,
>> > 12 TOPO.GEOMETRIE AS GEOMETRIE
>> > 13 FROM DGDTW_TOPOGRAFIE TOPO
>> > 14 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
>> > 15 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
>> > 16 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI
>> > 17 ON MULTI.CHILD = GUIDS.ID
>> > 18 AND ARCHIVE IS NULL
>> > 19 WHERE TOPO.VERVAL IS NULL
>> > 20 AND NOT TOPO.PARAMETERS = 515
>> > 21 ORDER BY GUID
>> > 22 ) LINES
>> > 23 GROUP BY GUID;
>> > SELECT
>> > *
>> > FOUT in regel 1:
>> > .ORA-29913: Er is een fout opgetreden bij het uitvoeren van de
>> > toelichting
>> > ODCIAGGREGATEMERGE.
>>
>> > SQL> SELECT
>> > 2 DISTINCT(GUID),
>> > 3 COUNT(GEOMETRIE) AS AANTAL,
>> > 4 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE
>> > 5 FROM
>> > 6 (
>> > 7 SELECT
>> > 8 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID,
>> > 9 MULTI.PARENT AS PARENTGUID,
>> > 10 GUIDS.ID AS CHILDGUID,
>> > 11 TOPO.ID,
>> > 12 TOPO.GEOMETRIE AS GEOMETRIE
>> > 13 FROM DGDTW_TOPOGRAFIE TOPO
>> > 14 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
>> > 15 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
>> > 16 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI
>> > 17 ON MULTI.CHILD = GUIDS.ID
>> > 18 AND ARCHIVE IS NULL
>> > 19 WHERE TOPO.VERVAL IS NULL
>> > 20 AND NOT TOPO.PARAMETERS = 515
>> > 21 -- check the input for the SDO_AGGR_UNION
>> > 22 AND TOPO.GEOMETRIE.SDO_GTYPE = 2003
>> > 23 AND SDO_GEOM.VALIDATE_GEOMETRY(GEOMETRIE, 0.001) = 'TRUE'
>> > 24 ORDER BY GUID
>> > 25 ) LINES
>> > 26 GROUP BY GUID;
>> > SELECT
>> > *
>> > FOUT in regel 1:
>> > .ORA-29913: Er is een fout opgetreden bij het uitvoeren van de
>> > toelichting
>> > ODCIAGGREGATEMERGE.
>>
>> > SQL>
>>
>> Maybe I am not seeing things right here, but why are you selecting all
>> those
>> columns in your inner query when you only use the GEOMETRIE column on the
>> outside? Why is the order by clause in the inner query?
>>
>> Did you try to call SDO_AGGR_UNION on some of the geometries without the
>> complex query?
>>
>> Shakespeare
>

ORA-29913 may have something to do with a too small tolerance setting. Try a
larger one and see what happens.
What OS are you on?

Shakespeare

| Next | Last
Pages: 1 2
Prev: oracle 9i: errors by clone a DB by a script
Next: NLS_LANG