SDO_OVERLAPS Query Expecting More Rows Than The Rows Returned

Last updated on FEBRUARY 08, 2017

Applies to:

Symptoms

SDO_OVERLAPS Query Expecting More Rows Than The Rows Returned

set head off
select title, gtt.location.get_wkt()
from GEOMETRY_TEST_TAB gtt
where sdo_overlaps(gtt.location,
mdsys.sdo_geometry('POLYGON((-7 11, -7 -18, 30 -18, 30 11, -7 11))', 8307)) = 'TRUE';

Soap Test No Download1
GEOMETRYCOLLECTION (POINT (26.7262229897779 -13.6514556866172), POINT (-67.18659

Soap Test No Download4
GEOMETRYCOLLECTION (POINT (22.3433716482004 -15.7226852375346), POINT (-66.53302

Soap Test No Download6
GEOMETRYCOLLECTION (POINT (26.5166421038483 -16.648291114826), POINT (-66.922307

Soap Test No Download3
GEOMETRYCOLLECTION (POINT (27.5936685814902 -15.1301005429062), POINT (-61.71430

Soap Test No Download9
GEOMETRYCOLLECTION (POINT (26.7625722733587 -13.8400177425915), POINT (-61.71698



Expecting also the other 5 rows


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;

spool val.txt
DROP TABLE val_results;
CREATE TABLE val_results (sdo_rowid ROWID, result varchar2(1000));
CALL SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('GEOMETRY_TEST_TAB','LOCATION','VAL_RESULTS');
col result format a40
SELECT * from val_results;
spool off

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


set head off
select title, gtt.location.get_wkt()
from GEOMETRY_TEST_TAB gtt
where sdo_overlaps(gtt.location,
mdsys.sdo_geometry('POLYGON((-7 11, -7 -18, 30 -18, 30 11, -7 11))', 8307)) = 'TRUE';

Soap Test No Download1
GEOMETRYCOLLECTION (POINT (26.7262229897779 -13.6514556866172), POINT (-67.18659

Soap Test No Download4
GEOMETRYCOLLECTION (POINT (22.3433716482004 -15.7226852375346), POINT (-66.53302

Soap Test No Download6
GEOMETRYCOLLECTION (POINT (26.5166421038483 -16.648291114826), POINT (-66.922307

Soap Test No Download3
GEOMETRYCOLLECTION (POINT (27.5936685814902 -15.1301005429062), POINT (-61.71430

Soap Test No Download9
GEOMETRYCOLLECTION (POINT (26.7625722733587 -13.8400177425915), POINT (-61.71698



Expecting also the other 5 rows

Cause