0

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'
1

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;
/
  • Hi travis, I'm also not sure about the .RELATE without an index - but I will check if I can the geometries by checking the validity. – ABX Jul 3 '13 at 19:28
0

Hmmm. A multi-polygon that contains overlapping elements is actually invalid. So to find them, you should first validate your polygons. All the ones with overlapping elements will be diagnosed and identified.

Check out the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT() function.

Albert

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.