SDO_RELATE ANYINTERACT does not work with points in 2d geo coordinates
The original problem was that I was trying to figure out if one of the points in the set is inside a given polygon. I didn't get any results, so I narrowed it down to the simplest case, but I still haven't got any results.
The geometry of the polygon looks like this (a small square around the origin):
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 a point [0,0]
is inside this polygon using SDO_RELATE.
sdo_relate(geom1,
SDO_GEOMETRY('MULTIPOINT((0 0))',8307),
'mask=anyinteract')
I used anyinteract
because according to Oracle documentation :
ANYINTERACT: Objects do not intersect.
The point inside the polygon is clearly disjoint from it. So I spent about an hour initializing the points differently and checking the coordinates and everything before I tried to put in there CONTAINS
instead anyinteract
and finally get the desired result.
So my questions are:
- This is mistake? The parameter is
CONTAINS
clearly stricter thananyinteract
- What's the best way to check if any of the points in a set are in a polygon? The contains does not help here because if any of the points lie outside the SDO_RELATE result is false. There is no type of intersection more suitable than
anyinteract
one that does not work.
source to share
First of all, your polygon is distorted: it won't close. For a polygon, the last point must be the same as the first point. You can find the error by running:
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 fix this, you get another 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 counterclockwise direction. Once you fix 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 the queries are working correctly. Here's 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 three options for your polygon. Try the query:
select *
from t1
where sdo_relate(
geom,
SDO_GEOMETRY('MULTIPOINT((0 0))',8307),
'mask=anyinteract'
) = 'TRUE';
which returns the correct result: only the correct 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 a point in a polygon, it can affect those points that are on the boundary of the polygon (or more precisely, within the boundary tolerance). ANYINTERACT search returns them. Search CONTAINS / INTERNAL.
This can be important, for example, when you want to propagate points to polygons (for example, customer point locations in sales regions), and some customer locations fall on the border between neighboring regions: ANYINTERACT search tells you that these locations are in regions . An INSIDE search will tell you that they are in neither .
Finally, use a simpler syntax for your queries:
select *
from t1
where sdo_anyinteract(
geom,
SDO_GEOMETRY('MULTIPOINT((0 0))',8307)
) = 'TRUE';
Some information on how to get the message corresponding to the error code (for example, 13348). I usually use this technique:
set serveroutput on
exec dbms_output.put_line(sqlerrm(-13348))
The complete error message will be printed. Note that you must pass the error with a minus sign.
source to share