I have two geometry in two feature class ,one named "HY90299 " and the other named "hyboxsdo " ,the two geometry do not intersect .
but when i run a spatial query in oralce ,
"select sdo_relate(t.shape,g.shape ,'mask=ANYINTERACT') from HY90299 t,hyboxsdo g " ,
it return "true", the result is not correct .am I doing something wrong?my oracle version is 11g
you can get the two geometry by 1.i put the two geometry into two shape file . you can get them from here https://pan.baidu.com/s/1YQnwe8nstzgHOAwHgx9JGQ 2.or create the two geometry by wkt ①MULTIPOLYGON (((-16.657423019000021 82.843477248999989, 16.710901260000014 66.242341995000004, 74.611375808999981 57.038061142000004, 111.18630027799998 67.126588820999984, -16.657423019000021 82.843477248999989))) ②MULTIPOLYGON (((60.839999999999975 26.569999999999993, 143.45000000000005 26.569999999999993, 143.45000000000005 55.75, 60.839999999999975 55.75, 60.839999999999975 26.569999999999993)))
Append
1.select * from user_sdo_geom_metadata where table_name='HY90299'
=============================
return "HY90299 SHAPE {{null,-180,180,0.001},{null,-90,90,0.001}} 4326"
2.select sdo_geom.validate_geometry_with_context(c.shape,0.000000005) from hy90299 c
select sdo_geom.validate_geometry_with_context(c.shape,0.001) from hy90299 c
=============================
all return "true"
3.select shape from hy90299
=============================
return "{2003,4326,null,{1,1003,1},{111.186300278,67.126588821,-16.657423019,82.843477249,16.71090126,66.242341995,74.611375809,57.038061142,111.186300278,67.126588821}}"
4.select sdo_geom.relate(t.shape,'determine',sdo_geometry(2003,4326,null, SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(60.840,26.570,143.450,55.750)),0.000000005) as spat_rel from HY90299 t
=============================
return "DISJOINT"
5.select sdo_geom.relate(t.shape,'determine',sdo_geometry(2003,4326,null, SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(60.840,26.570, 143.450,26.570, 143.450,55.750,60.840,55.750,60.840, 26.570)),0.000000005) as spat_rel from HY90299 t
=============================
return "OVERLAPBDYINTERSECT"
From the manual (https://docs.oracle.com/cd/B28359_01/appdev.111/b28400/sdo_operat.htm#SPATL1039), spatial operators "must always be used in a WHERE clause", not in the SELECT part of the query.
To use them (in the WHERE clause, as mentioned), they must be spatially indexed.
If you want to see the spatial relation, you can use one of the spatial functions - e.g:
select t.*,g.*, sdo_geom.relate(t.shape,'determine',g.shape,0.000000005) as spat_rel
from HY90299 t, hyboxsdo g
If you want you can add the function to the WHERE caluse as well, to filter the results - e.g. add in the above snippet:
where sdo_geom.relate(t.shape,'determine',g.shape,0.000000005) not in ('TOUCH','DISJOINT')
For a handful of geometries, you'll be fine. As the number of geometries grows, you must either use spatial indexes and add operator(s) in the WHERE clause, or device another way to filter the rows (e.g. by an attribute, id, etc) - spatial functions do not scale well.
You also have the responsibility to choose the TOLERANCE value that is appropriate for your data and query (I chose 0.000000005 as your shapes seem to have 8 significant decimals).
Last, BUT NOT LEAST, you'd want to make certain that your geometries are valid (again, at the appropriate tolerance).
HTH
APPEND:
1)
with HY90299 as (
select sdo_util.from_wktgeometry(
'MULTIPOLYGON (((-16.657423019000021 82.843477248999989, 16.710901260000014 66.242341995000004, 74.611375808999981 57.038061142000004, 111.18630027799998 67.126588820999984, -16.657423019000021 82.843477248999989)))'
) shape from dual ),
HYBOXSDO as (
select sdo_util.from_wktgeometry(
'MULTIPOLYGON (((60.839999999999975 26.569999999999993, 143.45000000000005 26.569999999999993, 143.45000000000005 55.75, 60.839999999999975 55.75, 60.839999999999975 26.569999999999993)))'
) shape from dual )
select sdo_geom.relate(t.shape,'determine',g.shape,0.000000005)
from HY90299 t,hyboxsdo g ;
The result is DISJOINT - also:
with HY90299 as (
select sdo_geometry(2003,4326,null, SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-16.657423019000021, 82.843477248999989, 16.710901260000014, 66.242341995000004, 74.611375808999981, 57.038061142000004, 111.18630027799998, 67.126588820999984, -16.657423019000021, 82.843477248999989))
shape from dual )
select sdo_geom.relate(t.shape,'determine',
sdo_geometry(2003,4326,null, SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(60.840,26.570,143.450,55.750))
,0.000000005) as spat_rel from HY90299 t
The result is, again, DISJOINT.
Your 'overlapbdyintersect' shouldn't be there - check the contents of your tables (since the difference in your two queries is the 'window' geometry, double check the hyboxsdo table).
2) You are wrong. Tolerance is essential. If you use sdo_geom.relate(t.shape,'determine',g.shape,2) -that is a tolerance of 2 meters- in the above queries, you will get TOUCH instead of DISJOINT (and by this, you can also tell that your geometries are roughly 2m apart). However, with these two geometries, you would never get OVERLAP.
3) A geometry's validity is directly related to the tolerance you use. Your geometries are valid (in 8 decimals) - I'm just saying that it will save you LOTS of headaches if you don't take it for granted. Never assume - check!
4) It doesn't matter how you put the geometries in the table. The only think you might want to consider (especially in production environments) is the number of decimals stored in the database - if your data make good sense at a precision of, say, 3 decimals, you'd be better off rounding or truncating your coordinates to that. Simpler coordinates lead to smaller footprint (database storage) and faster performance.