Spatial Query Producing Incorrect Results with SDO_RELATE in 11.2 when Polygon Exterior and/or Interior Rings are not Oriented Correctly (Doc ID 1621508.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial and Graph - Version 11.2.0.1 and later
Information in this document applies to any platform.

Symptoms

Spatial query in below testcase returns 1 row on 11.2.0.3, which is incorrect. When running the same query on a 10.2.0.1.0 database, it returns 2 rows.

-- create table, index
create table jwb_test (id number, polygon mdsys.sdo_geometry);


insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid) values
('JWB_TEST', 'POLYGON', mdsys.sdo_dim_array(mdsys.sdo_dim_element('Longitude', -180, 180, 10), mdsys.sdo_dim_element('Latitude', -90, 90, 10)), 4326);

 

-- insert two rows
insert into jwb_test (id, polygon) values (1, mdsys.sdo_geometry(2003, 4326, null, mdsys.sdo_elem_info_array(1, 1003, 1), mdsys.sdo_ordinate_array(6, 59, 6, 60, 7, 60, 7, 59, 6, 59)));
insert into jwb_test (id, polygon) values (2, mdsys.sdo_geometry(2003, 4326, null, mdsys.sdo_elem_info_array(1, 1003, 1), mdsys.sdo_ordinate_array(7, 59, 7, 60, 8, 60, 8, 59, 7, 59)));

 

-- Create index

create index jwb_test_spatial_idx on jwb_test (polygon) indextype is mdsys.spatial_index;


-- query (11.2 returns 1, 10.2 returns 2)

select id from jwb_test where sdo_relate(polygon, mdsys.sdo_geometry(2003, 4326, null, mdsys.sdo_elem_info_array(1, 1003, 3), mdsys.sdo_ordinate_array(7.13, 59.483, 7.37, 59.66)), 'mask=anyinteract querytype=WINDOW') = 'TRUE';


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms