SDO_ANYINTERACT giving unexpected results with Multipolygon mask
773416Mar 19 2012 — edited Mar 19 2012I am trying to run SDO_ANYINTERACT(targetGeom,maskGeom) on a table with simple polygons,however my mask is a multipolygon. The result should be having geometries that interact with any of the polygons inside multipolygon but that is not the case every time, most of the time geometries that do not interact with the polygons of multipolygon are also selected and sometimes the polygons which are suppose to be in the result are not selected. I tried to run validation, it seems there are geometries with duplicate vertices (error : 13356) or some geometries have orientations issues (error:13367). Despite these , using GeoRaptor in SQL developer I am able to make selection using Spatial Query. Please also note that I have defined a function that takes WKT and SRID and returns an SDO_GEOMETRY with SRID set because the normal WKT_TO_GEOM returns geometry with null SRID and that cannot be used in spatial operators, function is below
---------------------------------
create or replace
FUNCTION WKT_TO_GEOM_W_SRID(wkt varchar2,SRID number)
RETURN SDO_GEOMETRY AS
geometry SDO_GEOMETRY;
BEGIN
geometry := SDO_UTIL.FROM_WKTGEOMETRY(wkt);
geometry.SDO_SRID := srid;
RETURN geometry;
END WKT_TO_GEOM_W_SRID;
----------------------------------------------
and my normal query would then be
select * from geom_table where SDO_ANYINTERACT(GEOM,WKT_TO_GEOM_W_SRID('MULTIPOLYGON (((72.566765595152 33.503327474908, 72.61380113256 33.499207573821, 72.618607683828 33.465218389854, 72.572602121691 33.484101269836, 72.566765595152 33.503327474908)), ((72.491405737772 33.503155812362, 72.506168716667 33.513455565079, 72.561787381339 33.443760571694, 72.491405737772 33.503155812362)))',4326))= 'TRUE'
are there any good practise or trick that I am missing here, because I thought of making the index again but since GeoRaptor works fine, it means data is fine.