SDO_OVERLAPS Query Expecting More Rows Than The Rows Returned
(Doc ID 1597798.1)
Last updated on JANUARY 21, 2020
Applies to:
Oracle Spatial and Graph - Version 11.2.0.3 and laterInformation in this document applies to any platform.
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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Cause |
Solution |