[GIS] Intersecting polygons in Oracle

intersectionoracle-spatial

Have a polygon in table 2 with its id, no and geometry. In table 1 I have polygons with the same fields. The polygon in table in intersects with a few polygons from table 1.What I am trying to do is clip of the polygons which overlap polygon from table 2 and insert the same fields and geometry in table 3

insert into table 3  
select a.store_id,b.store_id,a.store_number,a.client_id,sdo_geom.sdo_intersection(b.geometry,a.geo metry,0.005)  
from table_1 a, table_2 b  
where b.store_id=34746  
and sdo_anyinteract(b.geometry,a.geometry)='True';

When I do run this I dont get any output. Where would I be going wrong

Best Answer

This query will fail with ann error like this:

ORA-29902: error in executing ODCIIndexStart() routine
ORA-13207: incorrect use of the [SDO_ANYINTERACT] operator

You need to write "= 'TRUE' " instead of " = 'True' " (notice the capitals).

Apart from that, the query is also incorrectly written: since you lookup table A using a window from table B, then you need to specify the columns in that order in the spatial predicate, so write sdo_anyinteract(a.geometry,b.geometry) instead of sdo_anyinteract(b.geometry,a.geometry)

So the proper way to write your query is this:

insert into table 3  
select a.store_id, b.store_id, a.store_number, a.client_id, 
  sdo_geom.sdo_intersection(b.geometry,a.geometry,0.005)  
from table_1 a, table_2 b  
where b.store_id=34746  
and sdo_anyinteract(a.geometry,b.geometry)='TRUE';

That may still return no rows. It all depends on the actual data you are using.

Related Solutions

PostGIS Polygons – How to Separate Polygons Based on Intersection

Since you said you get a group of intersecting polygons for each polygon you're interested in, you may want to create what is referred to as a "polygon overlay".

This isn't exactly what Adam's solution is doing. To see the difference, take a look at this picture of an ABC intersection:

ABC intersection

I believe Adam's solution will create an "AB" polygon that covers both the area of "AB!C" and "ABC", as well as an "AC" polygon that covers "AC!B" and "ABC", and a "BC" polygon that is "BC!A" and "ABC". So the "AB", "AC", and "BC" output polygons would all overlap the "ABC" area.

A polygon overlay produces non-overlapping polygons, so AB!C would be one polygon and ABC would be one polygon.

Creating a polygon overlay in PostGIS is actually pretty straightforward.

There are basically three steps.

Step 1 is extract the linework [Note that I'm using the exterior ring of the polygon, it does get a little more complicated if you want to correctly handle holes]:

SELECT ST_ExteriorRing(polygon_col) AS the_geom FROM my_table) AS lines

Step 2 is to "node" the linework (produce a node at every intersection). Some libraries like JTS have "Noder" classes you can use to do this, but in PostGIS the ST_Union function does it for you:

SELECT ST_Union(the_geom) AS the_geom FROM (...your lines...) AS noded_lines

Step 3 is to create all the possible non-overlapping polygons that can come from all those lines, done by the ST_Polygonize function:

SELECT ST_Polygonize(the_geom) AS the_geom FROM (...your noded lines...)

You could save the output of each of those steps into a temp table, or you can combine them all into a single statement:

CREATE TABLE my_poly_overlay AS
SELECT geom FROM ST_Dump((
    SELECT ST_Polygonize(the_geom) AS the_geom FROM (
        SELECT ST_Union(the_geom) AS the_geom FROM (
            SELECT ST_ExteriorRing(polygon_col) AS the_geom FROM my_table) AS lines
        ) AS noded_lines
    )
)

I'm using ST_Dump because the output of ST_Polygonize is a geometry collection, and it is (usually) more convenient to have a table where each row is one of the polygons that makes up the polygon overlay.

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

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;
/

Best Answer

Related Solutions

PostGIS Polygons – How to Separate Polygons Based on Intersection

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

Related Question