SDO_RELATE ANYINTERACT doesn't work with points in 2d geocoordinates

The original problem was, that I was trying to find out if one of the points in set is inside given polygon. I was getting no results, so I've reduced it to a most simple case, but still got no results.

The polygon geometry looks like this(small square around the center of coordinates):

geom1 := SDO_GEOMETRY(2003, 8307, null, SDO_ELEM_INFO_ARRAY(1,1003,1),     
SDO_ORDINATE_ARRAY(10,10, 10,-10, -10,-10, -10,10))

Now I was trying to find out, if point [0,0] is inside that polygon using SDO_RELATE.

sdo_relate(geom1, 
        SDO_GEOMETRY('MULTIPOINT((0 0))',8307),
       'mask=anyinteract')

I've used anyinteract, because according to Oracle documentation:

ANYINTERACT: The objects are non-disjoint.

The point inside the polygon is clearly non-disjoint with it. So I've spend about an hour trying to initialize the points in different way and checking coordinates and everything, before I tried to put there CONTAINS instead of ANYINTERACT and finally got the desired output.

So my questions are:

Is this a bug? The contains parameter is clearly more strict, than anyinteract
What is the best way to check if any of the points in the set lies in the polygon? Contains doesn't help here, because if any of the points lies outside, the result od SDO_RELATE is false. There's no intersection type more fitting, than anyinteract, which doesn't work.

Solution

First of all your polygon is malformed: it does not close. For a polygon, the last point must match the first point. You can detect the error by doing:

SQL> select sdo_geom.validate_geometry_with_context (SDO_GEOMETRY(2003, 8307, null, SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(10,10, 10,-10, -10,-10, -10,10)), 0.05) from dual;

13348 [Element <1>] [Ring <1>]    
1 row selected.

ORA-13348: polygon boundary is not closed

Once you correct that, you get a different error:

SQL> select sdo_geom.validate_geometry_with_context (SDO_GEOMETRY(2003, 8307, null, SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(10,10, 10,-10, -10,-10, -10,10, 10,10)), 0.05) from dual;

13367 [Element <1>] [Ring <1>]
1 row selected.

ORA-13367: wrong orientation for interior/exterior rings

For a polygon, the points must be in a counter-clockwise orientation. Once you correct that, the polygon is correct:

SQL> select sdo_geom.validate_geometry_with_context (SDO_GEOMETRY(2003, 8307, null, SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(10,10, -10,10, -10,-10, 10,-10, 10,10)), 0.05) from dual;

TRUE
1 row selected.

And queries work properly. Here is an example:

create table t1 (id number, note varchar2(20), geom sdo_geometry);

insert into t1 (id, note, geom) 
values (
 1,
 'Not closed',
 SDO_GEOMETRY(2003, 8307, null, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(10,10, 10,-10, -10,-10, -10,10))
);

insert into t1 (id, note, geom) 
values (
 2,
 'Wrong orientation',
 SDO_GEOMETRY(2003, 8307, null, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(10,10, 10,-10, -10,-10, -10,10, 10,10))
);

insert into t1 (id, note, geom) 
values (
 3,
 'Valid',
 SDO_GEOMETRY(2003, 8307, null, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(10,10, -10,10, -10,-10, 10,-10, 10,10))
);

commit;

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
values (
  'T1',
  'GEOM', 
  sdo_dim_array (
    sdo_dim_element ('Long',-180,180,0.5),
    sdo_dim_element ('Lat',-90,90,0.5)
  ),
  8307
);
commit;

create index t1_sx on t1 (geom) indextype is mdsys.spatial_index;

The test table contains the three variants of your polygon. Let's try a query:

select *
from t1
where sdo_relate(
  geom, 
  SDO_GEOMETRY('MULTIPOINT((0 0))',8307),
  'mask=anyinteract'
) = 'TRUE';

which returns the correct result: only the valid polygon is correctly identified as containing your point:

        ID NOTE                 GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
---------- -------------------- -------------------------------------------------------------------------------
         3 Valid                SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(10, 10, -10, 10, -10, -10, 10, -10, 10, 10))

1 row selected.

As for the difference between ANYINTERACT and CONTAINS for point-in-polygon it can impact those points that are on the boundary of the polygon (or more precisely within tolerance of the boundary). An ANYINTERACT search returns those. A CONTAINS/INSIDE search does not.

This can be important for example when you want to distribute points to polygons (like customer point locations in sales regions) and some customer location points happen to fall on the boundary between adjacent regions: an ANYINTERACT search will report that those locations are in both regions. An INSIDE search will report that they are in neither.

Finally, use the simpler syntax for your queries:

select *
from t1
where sdo_anyinteract(
  geom, 
  SDO_GEOMETRY('MULTIPOINT((0 0))',8307)
) = 'TRUE';

Some info on how to get the message corresponding to an error code (like 13348). I usually use this technique:

set serveroutput on
exec dbms_output.put_line(sqlerrm(-13348))

That will print out the full error message. Notice that you must pass the error with a minus sign.