Oracle Spatial SDO Aggregate Union fails with ORA-29913 ODCIAGGREGATEMERGE

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

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

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> Received on Thu Jun 12 2008 - 03:28:08 CDT