Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SDO_ANYINTERACT giving unexpected results with Multipolygon mask

773416Mar 19 2012 — edited Mar 19 2012
I 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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 16 2012
Added on Mar 19 2012
1 comment
652 views