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@xxxxxxxxx> schreef in bericht
news:8240cb4c-dddd-4667-8ef9-a799fb2689e5@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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...@xxxxxxxxx> wrote:
"Eduard Witteveen" <eywittev...@xxxxxxxxx> schreef in
berichtnews:f6a38868-94f7-42f8-bbc7-6fad5dd6e172@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx



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


.

Relevant Pages