1

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.

  • Have you also checked all that is described in docs.oracle.com/database/121/SPATL/… ? – user30184 Jul 24 '18 at 17:50
  • @user30184 : Yes. It points for some clues on what can be the problem, but doesn't present a solution. – Carlos MSF Jul 25 '18 at 9:54
  • Try to find a minimal test case with a few geometries from your data which can't be merged. – user30184 Jul 25 '18 at 10:35
  • 1
    As the manual explains that function only works if the lines are properly topologically joined, i.e. the last point of one line is the same as the first point in another line. It is designed to assemble constructs like road segments or pipeline sections into longer linear structures. If lines cross or overlap then it will generate invalid shapes. The proper and generic way to merge shapes of any nature is to use SDO_AGGR_UNION. The line concatenation is really just an optimisation when the input lines are already topologically connected. – Albert Godfrind Jul 27 '18 at 21:14
  • @AlbertGodfrind: I've avoided to use SDO_AGGR_UNION because, in the Oracle documentation, explicity says "Do not use SDO_AGGR_UNION to merge line string or multiline string geometries; instead, use the SDO_AGGR_CONCAT_LINES spatial aggregate function.". I tried to use SDO_AGGR_SET_UNION instead and it worked. After your comment I tried SDO_AGGR_UNION and it worked as well, but somewhat different results. The issue about lines with end points touching other line's end points was not the problem, because I had features in those conditions that were merged correctly with SDO_AGGR_CONCAT_LINES ... – Carlos MSF Aug 1 '18 at 9:53
1

I'll divide the answer in two parts: one covering the origin of the error and other about the solution without correcting the original data and discussion about the results of the different aggregation methods:


  1. The error in the application of the SDO_AGGR_CONCAT_LINES occurred due to the existence of a COMPOUNDCURVE / CIRCULARSTRING within one of the geometries.

Actually this is mentioned in the documentation:

The input geometries must be line strings whose vertices are connected by straight line segments. Circular arcs and compound line strings are not supported.

Filtering that geometry out it was able to do the aggregation with SDO_AGGR_CONCAT_LINES, even if there were features that apparently did not respect the topological rules stated in the Oracle documentation of the function:

The topological relationship between the geometries in each pair of geometries to be concatenated must be DISJOINT or TOUCH; and if the relationship is TOUCH, the geometries must intersect only at two end points.

Example:

enter image description here


  1. SDO_AGGR_UNION will work well with linestrings, even if the input data has circular arcs and compound line strings, regardless of the recommendation of Oracle to not use this function with linestrings. SDO_AGGR_SET_UNION will work as well (Oracle states it's faster), but with slightly different results, even if the input tolerance is the same.

Example 1:

Example of the results obtained through the different aggregation functions

Example 2:

Example of the results obtained through the different aggregation functions (2)

Apparently the results show that:

SDO_AGGR_CONCAT_LINES has no input tolerance, so it will only merge the lines if 2 end points of 2 lines are identical. If there are 3 identical end points of 3 lines, it will create a multilinestring (see example image in point 1).

SDO_AGGR_UNION will try to merge overlapping (or nearly overlapping) lines within the specified tolerance.

SDO_AGGR_SET_UNION will disregard overlapping (or nearly overlapping) parts of the input lines within the specified tolerance, resulting in multiline features with gaps in the overlapping parts.


As overall conclusion, I would use SDO_AGGR_UNION for linestring aggregation by default, only considering SDO_AGGR_SET_UNION if performance issues are a problem or SDO_AGGR_CONCAT_LINES if you wanna assure that original vertices are maintained and that only topological sequencial lines are merged.

  • 2
    Your conclusions are correct. SDO_AGGR_SET_UNION was only introduced as a speedier alternative to SDO_AGGR_UNION in 11.2. From 12.1 on, SDO_AGGR_UNION has been re-designed and is much faster with the Vector Performance Accelerator. SDO_AGGR_UNION does a complete geometric union and covers all possible topological cases. SDO_AGGR_CONCAT_LINES is a shortcut for those cases when your need is to assemble longer lines from a set of connected segments (like road segments or pipe section). Thanks for the complete analysis and the great visual illustrations! – Albert Godfrind Aug 1 '18 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.