The function SDO_AGGR_CONCAT_LINES is returning an error when concatenating lines from a table with simple line geometries (SDO_GTYPE - 2002):
ORA-29400: erro do cartridge de dados
ORA-06502: PL/SQL: erro numérico ou de valor
ORA-06512: na "MDSYS.SDO_UTIL", linha 2101
29400. 00000 - "data cartridge error\n%s"
*Cause: An error has occurred in a data cartridge external procedure.
This message will be followed by a second message giving
more details about the data cartridge error.
*Action: See the data cartridge documentation
for an explanation of the second error message.
I have checked, through SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function that all input geometries are valid.
If I try to group all the input geometries into one row, like:
SELECT SDO_AGGR_CONCAT_LINES(t.SHAPE) AS SHAPE
FROM my_input_lines t;
it returns the error described, but if I group by one particular field, like:
SELECT SDO_AGGR_CONCAT_LINES(t.SHAPE) AS SHAPE
FROM my_input_lines t
GROUP BY my_field;
it works with some fields (returns valid concatenated lines), but not with others (returns the error described). This means that the aggregate functions works merging some of the geometries, but not with others.
Any thougths on the possible solution?
Further developments:
By analysing the SDO_AGGR_CONCAT_LINES documentation from Oracle (as suggested in comments), it says that:
The topological relationship between the geometries in each pair of geometries to be concatenated must be DISJOINT or TOUCH (...)
Based on this I've tried a query to determine which objects did have interactions different then DISJOINT (no interaction) and TOUCH:
WITH T AS (
SELECT
A.DESIGNACAO,
SDO_GEOM.RELATE(A.SHAPE, 'determine', B.SHAPE, 0.05) AS INTERACTION,
A.OBJECTID AS OID1,
b.objectid AS OID2
FROM MOB_REDE_CICLAVEL_LN A
INNER JOIN MOB_REDE_CICLAVEL_LN B
ON A.DESIGNACAO = B.DESIGNACAO
AND a.objectid <> b.objectid -- prevents results for interactions of the object with itself
AND SDO_ANYINTERACT(A.SHAPE, B.SHAPE) = 'TRUE'
)
SELECT *
FROM T
WHERE t.interaction <> 'TOUCH';
I then found that were other interactions between the objects present in the table: 'EQUAL', 'COVEREDBY', 'OVERLAPBDYDISJOINT' AND 'COVERS'.
From this, I tried to discover if any of these types interactions was solely responsible for the resulting error, but doing something like the following, i.e., merging only the objects that present a specific type of interaction:
WITH T AS (
SELECT
A.DESIGNACAO,
SDO_GEOM.RELATE(A.SHAPE, 'determine', B.SHAPE, 0.05) AS INTERACTION,
A.OBJECTID AS OID1,
b.objectid AS OID2
FROM MOB_REDE_CICLAVEL_LN A
INNER JOIN MOB_REDE_CICLAVEL_LN B
ON A.DESIGNACAO = B.DESIGNACAO
AND a.objectid <> b.objectid -- prevents results for interactions of the object with itself
AND SDO_ANYINTERACT(A.SHAPE, B.SHAPE) = 'TRUE'
)
SELECT t.DESIGNACAO, SDO_AGGR_CONCAT_LINES(t.SHAPE) AS SHAPE
FROM MOBILIDADE.MOB_REDE_CICLAVEL_LN T
WHERE T.DESIGNACAO IN (
SELECT DISTINCT DESIGNACAO
FROM T
WHERE t.interaction = 'COVERS'
)
GROUP BY t.DESIGNACAO;
returned error, for any of the identified interactions.
I then "corrected" the identified geometries through a GIS application (ArcGIS in this case) until I had no other interactions between the different geometries other then TOUCH and DISJOINT, but it still returns the same error.
