[GIS] How to find overlapping polygons within a multipolygon (Oracle spatial)

oracle-spatialoverlapping-featuressdo-geometry

I am working with Oracle spatial and have a table with MULTIPOLYGONS.
How can I find the database entries where the polygons of a multipolygon overlap – within the same geometry?

So, a function that checks the polygons of a multipolygon for overlapping.

I found several examples how to compare polygons in order to find overlapping polygons stored in a different row or in a different table: 

SELECT *
FROM
  GEOTAB TabA, GEOTAB TabB
WHERE
  TabA.ID = 1234
  and
  SDO_OVERLAPS(TabA.GEOM,  TabA.GEOM) = 'TRUE'

but what I need is something like:

Select * FROM GEOTAB where SDO_OVERLAPSINTERNAL(GEOM) = 'TRUE'

Best Answer

I thought that having multiple parts of a single polygon overlapping was invalid, but I haven't been able to find any documentation stating that, so maybe I am wrong.

Either way, you could use the SDO_UTIL.EXTRACT function to get each of the parts, then use SDO_GEOM.RELATE to tell if they overlap. I'm not sure if SDO_OVERLAPS will work because you will not have an index build on the A shape.

This is where I would start (this creates, but I haven't tested it):

CREATE OR REPLACE FUNCTION SDO_OVERLAPSINTERNAL(geom IN SDO_GEOMETRY)
        RETURN VARCHAR2 AS
    TYPE list_of_shapes IS TABLE OF SDO_GEOMETRY;
    shapes list_of_shapes := list_of_shapes();

    currentShape SDO_GEOMETRY;
    intersectionCount NUMBER;
BEGIN
    shapes.EXTEND; 
    shapes(1) := SDO_UTIL.EXTRACT(geom, 1);
    FOR i IN 2..SDO_UTIL.GETNUMELEM(geom) LOOP
        currentShape := SDO_UTIL.EXTRACT(geom, i);

        FOR k IN shapes.FIRST .. shapes.LAST LOOP
            IF SDO_GEOM.RELATE(shapes(k),'OVERLAPBDYDISJOINT+OVERLAPBDYINTERSECT', currentShape, 0.005) = 'TRUE' THEN
                RETURN 'TRUE';
            END IF;
        END LOOP;

        shapes.EXTEND; 
        shapes(shapes.LAST) := currentShape;
    END LOOP;   
    RETURN 'FALSE';
END;
/
Related Question