SDO_ANYINTERACT Returns Wrong Results When Geometries Are Multipoint (Doc ID 1621516.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

SDO_ANYINTERACT returns wrong results

select title from GEOMETRY_TEST_TAB where sdo_anyinteract(location,
SDO_GEOMETRY( 2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
SDO_ORDINATE_ARRAY(-7,-18, 30,11) )) = 'TRUE';

It only returns 9 rows, it should return 10 rows

Test case:

DROP TABLE GEOMETRY_TEST_TAB;

CREATE TABLE GEOMETRY_TEST_TAB
(TITLE VARCHAR2(200), LOCATION MDSYS.SDO_GEOMETRY);

INSERT INTO GEOMETRY_TEST_TAB
VALUES('Soap Test No Download0',
SDO_GEOMETRY(2004, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1,1),
SDO_ORDINATE_ARRAY(24.1429094125842, -16.281127580584)));

INSERT INTO GEOMETRY_TEST_TAB
VALUES('Soap Test No Download1',
SDO_GEOMETRY(2004, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1,1,3,1,1),
SDO_ORDINATE_ARRAY(26.7262229897779, -13.6514556866172, -67.1865983303874, -15.6015775980827)));

INSERT INTO GEOMETRY_TEST_TAB
VALUES('Soap Test No Download2',
SDO_GEOMETRY(2004, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1,1),
SDO_ORDINATE_ARRAY(25.0044853137932, -16.0851408991837)));

INSERT INTO GEOMETRY_TEST_TAB
VALUES('Soap Test No Download3',
SDO_GEOMETRY(2004, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1,1,3,1,1),
SDO_ORDINATE_ARRAY(27.5936685814902, -15.1301005429062, -61.7143051668083, -20.4783733123901)));


INSERT INTO GEOMETRY_TEST_TAB
VALUES('Soap Test No Download4',
SDO_GEOMETRY(2004, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1,1,3,1,1),
SDO_ORDINATE_ARRAY(22.3433716482004, -15.7226852375346, -66.5330229400412, -14.9479981155969)));

INSERT INTO GEOMETRY_TEST_TAB
VALUES('Soap Test No Download5',
SDO_GEOMETRY(2004, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1,1),
SDO_ORDINATE_ARRAY(22.4717586722219, -16.2816333699711)));

INSERT INTO GEOMETRY_TEST_TAB
VALUES('Soap Test No Download6',
SDO_GEOMETRY(2004, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1,1,3,1,1),
SDO_ORDINATE_ARRAY(26.5166421038483, -16.648291114826, -66.9223071396231, -20.2443365081967)));

INSERT INTO GEOMETRY_TEST_TAB
VALUES('Soap Test No Download7',
SDO_GEOMETRY(2004, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1,1),
SDO_ORDINATE_ARRAY(24.9800182919556, -14.1267593923652)));

INSERT INTO GEOMETRY_TEST_TAB
VALUES('Soap Test No Download8',
SDO_GEOMETRY(2004, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1,1),
SDO_ORDINATE_ARRAY(26.296082277432, -14.1123677706927)));

INSERT INTO GEOMETRY_TEST_TAB
VALUES('Soap Test No Download9',
SDO_GEOMETRY(2004, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1,1,3,1,1),
SDO_ORDINATE_ARRAY(26.7625722733587, -13.8400177425915, -61.7169811785767, -20.8255517071446)));
commit;

delete user_sdo_geom_metadata where table_name = 'MDF_CATALOG_TAB';
INSERT INTO USER_SDO_GEOM_METADATA
VALUES (
'GEOMETRY_TEST_TAB',
'LOCATION',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, .05),
MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, .05)
),
8307
);
commit;

create index GTT_SPATIAL_SIX on GEOMETRY_TEST_TAB (LOCATION) indextype is
mdsys.spatial_index;

select title from GEOMETRY_TEST_TAB where sdo_anyinteract(location,
SDO_GEOMETRY( 2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
SDO_ORDINATE_ARRAY(-7,-18, 30,11) )) = 'TRUE';

It only returns 9 rows, it should return 10 rows

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