sqloracle-databasespatial-queryoracle-spatial

sdo_relate ORA-13343: a polygon geometry has fewer than four coordinates

I am trying to do a SDO_relate however it is returning an error.

My code:

ON sdo_relate (f.tls_da_location, ntp.boundary, 'MASK=ANYINTERACT') = 'TRUE'
WHERE ntp.boundary IS NOT NULL

I have tried this and sdo_util.getnumvertices (ntp.boundary) > 4

However it still returns the error below:

ORA-29902: error in executing ODCIIndexStart() routine
ORA-13249: Internal error: Memory Resident R-tree
ORA-13343: a polygon geometry has fewer than four coordinates
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 333
12801. 00000 -  "error signaled in parallel query server %s"
*Cause:    A parallel query server reached an exception condition.
*Action:   Check the following error message for the cause, and consult
           your error manual for the appropriate action.
*Comment:  This error can be turned off with event 10397, in which
           case the server's actual error is signaled instead.

Does anyone have any other suggestions to ignore these polygons that don't have 4 points?

Solution

  • There are several aspects here ...

    First: the optimizer is free to apply the predicates in any order. In your case, it looks like it applies the spatial filter first, then applies the selector on number of vertices. Which means you get the exception before the test on number of vertices.

    That the optimizer does this is natural: changing the order would mean a full table scan to only return the geometries with 4 points or more, then pass the result through the spatial filter. That would be very slow, and the optimizer rightfully prefers using the index first.

    There is no mechanism (hints or otherwise) to control this behavior. Using a subquery or a view will not make any difference: the optimizer will flatten the query into a simple one. Possibly a subquery with a NO_MERGE hint could work: but it would have the above effect of forcing a full table scan and a full pass of all geometries through the spatial filter. Not a good thing.

    Second: polygons with less than 4 vertices are incorrect. The simplest polygon is a triangle. It has three points (A-B-C), but all polygons must close, i.e. be encoded as four vertices: A-B-C-A. That is one of the rules defined by the OGC Simple Features for SQL specification. There are others that polygons must adhere to:

    Shapes that do not adhere to the rules are invalid. What happens when you use invalid shapes is actually undefined. Depending on the nature of the error and the action you do on this shape (query, measure, buffer, clip, merge ...), you may get any of the following behaviors:

    1. the error is ignored and you get a correct result
    2. you get an exception (that is your case)
    3. you get no error, but the result is incorrect

    The worst possible outcome is #3: you cannot trust the results of your application. It may return the wrong area in m2 of a parcel. Or it may say that two adjacent parcels do not overlap, when in reality they do ... This is very bad.

    Data quality is of prime importance when manipulating and processing spatial data. Note that errors are generally not visible: most map-mapping tools are resilient enough to still show the shapes, and defects are for the most impossible to detect visually.

    The solution is simple: make sure your data is valid. For that you can use the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(). Run it over each shape. It will tell you which shapes are incorrect, and what the error is.

    There is also SDO_UTIL.RECTIFY_GEOMETRY(). This one will attempt to correct the most common errors:

    It does not correct the errors you see (less than four points) because it is uncertain of what is actually wrong. You need to look at what those shapes are, and more important where they came from. Then either correct them or remove them.