Oracle Spatial SDO Aggregate Union fails with ORA-29913 ODCIAGGREGATEMERGE
Eduard Witteveen
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>
Shakespeare
"Eduard Witteveen" <eywit...@gmail.com> schreef in bericht
news:f6a38868-94f7-42f8...@2g2000hsn.googlegroups.com...
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
Eduard Witteveen
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...@xs4all.nl> wrote:
> "Eduard Witteveen" <eywittev...@gmail.com> schreef in berichtnews:f6a38868-94f7-42f8...@2g2000hsn.googlegroups.com...
Eduard Witteveen
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...@xs4all.nl> wrote:
> "Eduard Witteveen" <eywittev...@gmail.com> schreef in berichtnews:f6a38868-94f7-42f8...@2g2000hsn.googlegroups.com...
Shakespeare
"Eduard Witteveen" <eywit...@gmail.com> schreef in bericht
news:8240cb4c-dddd-4667...@l42g2000hsc.googlegroups.com...
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
Eduard Witteveen
> ...
> 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?
The server is running on:
cpu: 3.40 GHZ intel xeon(hyperthreaded)
ram: 4032mb
os: Windows 2003 SP
servicepack: 2
<tolorance10cm>
SQL> SELECT
2 DISTINCT(GUID),
3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.1)) AS GEOMETRIE
4 FROM
5 (
6 SELECT
7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID,
8 TOPO.GEOMETRIE AS GEOMETRIE
9 FROM DGDTW_TOPOGRAFIE TOPO
10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI
13 ON MULTI.CHILD = GUIDS.ID
14 AND ARCHIVE IS NULL
15 WHERE TOPO.VERVAL IS NULL
16 AND NOT TOPO.PARAMETERS = 515
17 -- check the input for the SDO_AGGR_UNION
18 AND TOPO.GEOMETRIE.SDO_GTYPE = 2003
19 AND SDO_GEOM.VALIDATE_GEOMETRY(GEOMETRIE, 0.1) = 'TRUE'
20 ) LINES
21 GROUP BY GUID;
SELECT
*
FOUT in regel 1:
.ORA-29913: Er is een fout opgetreden bij het uitvoeren van de
toelichting
ODCIAGGREGATEMERGE.
</tolorance10cm>
<tolorance1m>
SQL> SELECT
2 DISTINCT(GUID),
3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 1)) AS GEOMETRIE
4 FROM
5 (
6 SELECT
7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID,
8 TOPO.GEOMETRIE AS GEOMETRIE
9 FROM DGDTW_TOPOGRAFIE TOPO
10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI
13 ON MULTI.CHILD = GUIDS.ID
14 AND ARCHIVE IS NULL
15 WHERE TOPO.VERVAL IS NULL
16 AND NOT TOPO.PARAMETERS = 515
17 -- check the input for the SDO_AGGR_UNION
18 AND TOPO.GEOMETRIE.SDO_GTYPE = 2003
19 AND SDO_GEOM.VALIDATE_GEOMETRY(GEOMETRIE, 1) = 'TRUE'
20 ) LINES
21 GROUP BY GUID;
GUID
--------------------------------------
GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO,
SDO_ORDINATES)
--------------------------------------------------------------------------------
{001FA2FC-1448-42C5-BC5F-480AD7392891}
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
SDO_ORDINATE_ARR
AY(-3,403E+38, 3,4028E+38, -3,403E+38, -3,403E+38, 3,4028E+38, -3,403E
+38, 3,402
8E+38, 3,4028E+38, -3,403E+38, 3,4028E+38))
{003E7376-7418-4361-A139-F58251E06127}
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
SDO_ORDINATE_ARR
AY(252697,14, 556409,2, 252690,788, 556404,936, 252711,182,
556400,086, 252716,6
7, 556404,21, 252697,14, 556409,2))
GUID
--------------------------------------
GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO,
SDO_ORDINATES)
--------------------------------------------------------------------------------
{00708284-7AB4-4CCC-82D1-CE86BD38777F}
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
SDO_ORDINATE_ARR
AY(247689,96, 558009,63, 247722,7, 558019,81, 247722,152, 558021,61,
247689,42,
558011,33, 247689,96, 558009,63))
{00832FCD-A3D6-4422-BDE1-BE937C0740E8}
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
SDO_ORDINATE_ARR
AY(241710,281, 551011,756, 241711,873, 551007,536, 241720,75,
551012,4, 241735,7
................................
................................
................................
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
SDO_ORDINATE_ARR
AY(244241,1, 564379,01, 244242,76, 564376,3, 244259,59, 564386,54,
244251,37, 56
4400,14, 244245,54, 564396,48, 244251,6, 564385,54, 244241,1,
564379,01))
{7A42B22C-F30D-460C-8615-8B6083BBB3F7}
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
SDO_ORDINATE_ARR
AY(247852,537, 558231,679, 247850,79, 558231,19, 247855,48, 558215,5,
247862,903
, 558191,244, 247866,38, 558179,88, 247868,317, 558180,469,
247864,422, 558193,2
38, 247863,5, 558196,26, 247852,537, 558231,679))
GUID
--------------------------------------
GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO,
SDO_ORDINATES)
--------------------------------------------------------------------------------
{7A687E8B-B8EC-41D9-80CB-3EC2C2C44EF2}
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
SDO_ORDINATE_ARR
AY(-3,403E+38, 3,4028E+38, -3,403E+38, -3,403E+38, 3,4028E+38, -3,403E
+38, 3,402
8E+38, 3,4028E+38, -3,403E+38, 3,4028E+38))
ERROR:
ORA-03113: Einde-van-bestand op communicatiekanaal.
375 rijen zijn geselecteerd.
SQL>
</tolorance1m>
<tolorance1cm-withoutcheck>
SQL> SELECT
2 DISTINCT(GUID),
3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, .01)) AS GEOMETRIE
4 FROM
5 (
6 SELECT
7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID,
8 TOPO.GEOMETRIE AS GEOMETRIE
9 FROM DGDTW_TOPOGRAFIE TOPO
10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI
13 ON MULTI.CHILD = GUIDS.ID
14 AND ARCHIVE IS NULL
15 WHERE TOPO.VERVAL IS NULL
16 AND NOT TOPO.PARAMETERS = 515
17 ) LINES
18 GROUP BY GUID;
SELECT
*
FOUT in regel 1:
.ORA-29913: Er is een fout opgetreden bij het uitvoeren van de
toelichting
ODCIAGGREGATEMERGE.
</tolorance1cm-withoutcheck>
<tolorance10cm-withoutcheck>
SQL> SELECT
2 DISTINCT(GUID),
3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.1)) AS GEOMETRIE
4 FROM
5 (
6 SELECT
7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID,
8 TOPO.GEOMETRIE AS GEOMETRIE
9 FROM DGDTW_TOPOGRAFIE TOPO
10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI
13 ON MULTI.CHILD = GUIDS.ID
14 AND ARCHIVE IS NULL
15 WHERE TOPO.VERVAL IS NULL
16 AND NOT TOPO.PARAMETERS = 515
17 ) LINES
18 GROUP BY GUID;
SELECT
*
FOUT in regel 1:
.ORA-29913: Er is een fout opgetreden bij het uitvoeren van de
toelichting
ODCIAGGREGATEITERATE.
ORA-13347: De co÷rdinaten voor een boog mogen niet gelijk zijn.
<tolorance10cm-withoutcheck>
Shakespeare
--------------------------------------------------------------------------------
8E+38, 3,4028E+38, -3,403E+38, 3,4028E+38))
7, 556404,21, 252697,14, 556409,2))
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
8E+38, 3,4028E+38, -3,403E+38, 3,4028E+38))
375 rijen zijn geselecteerd.
SQL>
</tolorance1m>
-------------------------------------------------------------------------------
It looks like you have some problematic geometries (which is often the case
with spatial). The aggregate causes points to overlap. You could try a
SMALLER tolerance, like 0.0005. Still, with the aggregate function, if two
geometries have some overlap, they will be considered one, which could cause
some strange effects. Another thing I have seen in the past is that flushing
the SGA before executing your script may help.
By the way: the error is a wrong translation. Call out is translated as
toelichting, which is not correct. ODCIAGGREGATEITERATE is an external
callout function. Too large values (in size) can cause problems like this. I
have not seen workarounds for this. But there must be another way to join
multiple geometries to one.
Shakespeare
Shakespeare
--------------------------------------------------------------------------------
8E+38, 3,4028E+38, -3,403E+38, 3,4028E+38))
7, 556404,21, 252697,14, 556409,2))
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
8E+38, 3,4028E+38, -3,403E+38, 3,4028E+38))
375 rijen zijn geselecteerd.
SQL>
</tolorance1m>
-----------------------------------------------------------------
Rethinking the case, for a test: what happens when you replace the left
outer join with an inner join? (Besides not getting all the results)
Shakespeare
Eduard Witteveen
> -----------------------------------------------------------------
> Rethinking the case, for a test: what happens when you replace the left
> outer join with an inner join? (Besides not getting all the results)
>
> Shakespeare
Progress! :D
But, when i rewrite the query i get the following error:
SQL*Plus: Release 9.2.0.1.0 - Production on Ma Jun 16 09:08:07 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
2 DISTINCT(GUID),
3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE
4 FROM (
5 -- with parent
6 SELECT
7 DISTINCT(GUID),
8 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE
9 FROM
10 (
11 SELECT
12 MULTI.PARENT AS GUID,
13 TOPO.GEOMETRIE AS GEOMETRIE
14 FROM DGDTW_TOPOGRAFIE TOPO
15 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
16 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
17 INNER JOIN DGDTW_PARENT_CHILD MULTI
18 ON MULTI.CHILD = GUIDS.ID
19 AND MULTI.ARCHIVE IS NULL
20 WHERE TOPO.VERVAL IS NULL
21 AND NOT TOPO.PARAMETERS = 515
22 ORDER BY GUID
23 ) LINES
24 GROUP BY GUID
25 UNION ALL
26 -- without parent
27 (
28 SELECT
29 DISTINCT(GUID),
30 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE
31 FROM
32 (
33 SELECT
34 GUIDS.ID AS GUID,
35 TOPO.GEOMETRIE AS GEOMETRIE
36 FROM DGDTW_TOPOGRAFIE TOPO
37 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
38 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
39 WHERE TOPO.VERVAL IS NULL
40 AND NOT TOPO.PARAMETERS = 515
41 AND GUIDS.ID NOT IN (SELECT CHILD FROM
DGDTW_PARENT_CHILD)
42 ORDER BY GUID
43 ) LINES
44 GROUP BY GUID
45 )
46 ) UNIONLINES
47 GROUP BY GUID
48 ;
ERROR:
ORA-03113: Einde-van-bestand op communicatiekanaal.
Er zijn geen rijen geselecteerd.
SQL>
The following query works, so something goes wrong on doing the
distinct over both union results. Any thoughts?
SELECT
DISTINCT(GUID),
COUNT(*)
FROM (
-- with parent
SELECT
DISTINCT(GUID),
SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE
FROM
(
SELECT
MULTI.PARENT AS GUID,
TOPO.GEOMETRIE AS GEOMETRIE
FROM DGDTW_TOPOGRAFIE TOPO
INNER JOIN DGDTW_OBJECTGUIDS GUIDS
ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
INNER JOIN DGDTW_PARENT_CHILD MULTI
ON MULTI.CHILD = GUIDS.ID
AND MULTI.ARCHIVE IS NULL
WHERE TOPO.VERVAL IS NULL
AND NOT TOPO.PARAMETERS = 515
ORDER BY GUID
) LINES
GROUP BY GUID
UNION ALL
-- without parent
(
SELECT
DISTINCT(GUID),
SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE
FROM
(
SELECT
GUIDS.ID AS GUID,
TOPO.GEOMETRIE AS GEOMETRIE
FROM DGDTW_TOPOGRAFIE TOPO
INNER JOIN DGDTW_OBJECTGUIDS GUIDS
ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
WHERE TOPO.VERVAL IS NULL
AND NOT TOPO.PARAMETERS = 515
AND GUIDS.ID NOT IN (SELECT CHILD FROM DGDTW_PARENT_CHILD)
ORDER BY GUID
) LINES
GROUP BY GUID
)
) UNIONLINES
GROUP BY GUID )
Shakespeare
Do you have a virus scanner running on your server or client? McAfee is well
known for causing ora-03113, I have seen this happening with spatial
queries.
I also know there might be a problem with calling the SDO_AGGR_UNION twice
in one query, caused by caching or something like that. I have seen spatial
queries where we explicitly had to flush the SGA between calls to get the
right and consistent results.
Spatial is something special... we even had to move our tables to a
different platform (Windows!) to perform certain queries because they
failed on our platform (but that used to be Alpha-VMS). There are (were)
many OS-related bugs. You might even consider moving to a more recent
version of the DB.
What happens if you perform the queries on both of the parts of the union
seperately?
Shakespeare