[Geoserver-devel] [jira] (GEOS-6831) Requesting an area near the bounding box leads to a very poor performance (Oracle)

GeoServer GeoServer Developer
1

Martin Wegner created an issue

GeoServer / BugGEOS-6831

Requesting an area near the bounding box leads to a very poor performance (Oracle)

Issue Type:

BugBug

Affects Versions:

2.6.1

Assignee:

Andrea Aime

Components:

Oracle

Created:

14/Jan/15 6:39 AM

Priority:

CriticalCritical

Reporter:

Martin Wegner

When requesting (GetMap) an area near the bounding box e.g. [0, -180, 90, -90] the created Oracle SQL statement is bad and leads to a horrible performance:

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 [0, -180, 90, -90] leads to [-1.40625, -179.99, 89.99, -88.59375]?

It seems to be the same problem as in issue GEOS-6813.

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)")

Add Comment

Add Comment

This message was sent by Atlassian JIRA (v6.1.6#6162-sha1:7af547c)

Atlassian logo