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

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

"Eduard Witteveen" <eywitteveen_at_gmail.com> schreef in bericht news:8240cb4c-dddd-4667-8ef9-a799fb2689e5_at_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..._at_xs4all.nl> wrote:
>> "Eduard Witteveen" <eywittev..._at_gmail.com> schreef in
>> berichtnews: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
>

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