Requesting an area touching the dateline may lead to poor performance with Oracle

Description

When requesting (GetMap) an area near the bounding box e.g.

Generated Oracle SQL statement (needed 57 seconds):

SELECT LONG_LAT as LONG_LAT FROM REPORTS_VIEW
WHERE
(SDO_FILTER(LONG_LAT, MDSYS.SDO_GEOMETRY(2003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-179.99,-1.40625,-88.59375,89.99)), 'mask=anyinteract querytype=WINDOW') = 'TRUE')
OR
(SDO_FILTER(LONG_LAT, MDSYS.SDO_GEOMETRY(2003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-1.40625,178.59375,91.40625,180.0)), 'mask=anyinteract querytype=WINDOW') = 'TRUE')

The second where condition is wrong and unnecessary. Without the second where condition the query only took 200 milliseconds:

SELECT LONG_LAT as LONG_LAT FROM REPORTS_VIEW
WHERE
(SDO_FILTER(LONG_LAT, MDSYS.SDO_GEOMETRY(2003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-179.99,-1.40625,-88.59375,89.99)), 'mask=anyinteract querytype=WINDOW') = 'TRUE')

Another question is why

It seems to be the same problem as in issue [https://jira.codehaus.org/browse/GEOS-6813" title="WMS GetFeatureInfo shows very poor performance in Oracle" class="issue-link" data-issue-key="GEOS-6813">GEOS-6813.

A very poor workaround is to reduce the whole bounding box by 1.5 degrees.

Presumption a SRID: 8307 point layer:

MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT(
'LONGITUDE',
-180,
180,
0.005 – tolerance of 1/200 miles (approximately 26 feet)
),
MDSYS.SDO_DIM_ELEMENT(
'LATITUDE',
-90,
90,
0.005 – tolerance of 1/200 miles (approximately 26 feet)
)
),
8307 – spatial reference system 8307 (well-known name is "Longitude / Latitude (WGS 84)")

Environment

None

Status

Assignee

Unassigned

Reporter

codehaus

Triage

None

Fix versions

None

Affects versions

2.6.1

Components

Priority

Low
Configure