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

"Eduard Witteveen" <eywitteveen_at_gmail.com> schreef in bericht news:f6a38868-94f7-42f8-bbc7-6fad5dd6e172_at_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 Received on Thu Jun 12 2008 - 04:10:42 CDT