[Geoserver-devel] oracle datastore possible inefficient queries..

GeoServer GeoServer Developer
1

Gday Geoserver folks..

I'm having a play with geoserver talking to an Oracle Spatial 9i database..
would like to ask a question about the Oracle datastore..

submitting the following query:

<?xml version="1.0"?>
<wfs:GetFeature service="WFS" version="1.0.0"
  outputFormat="GML2"
  xmlns:topp="http://www.openplans.org/topp&quot;
  xmlns:wfs="http://www.opengis.net/wfs&quot;
  xmlns:ogc="http://www.opengis.net/ogc&quot;
  xmlns:gml="http://www.opengis.net/gml&quot;

  <wfs:Query typeName="topp:AIR_PHOTO_INDEX">
      <ogc:Filter>
      <ogc:Intersects>
        <ogc:PropertyName>GEOMETRY</ogc:PropertyName>
            <gml:Polygon>
              <gml:outerBoundaryIs>
                <gml:LinearRing>
                  <gml:coordinates decimal="." cs=","
ts="">130.84310775,-12.38133377 130.84337329,-12.38129283
130.84344202,-12.38166684 130.84338598,-12.38167355 130.8431756,-12.38169938
130.8431584,-12.3816064 130.84310775,-12.38133377</gml:coordinates>
                </gml:LinearRing>
              </gml:outerBoundaryIs>
            </gml:Polygon>
        </ogc:Intersects>
      </ogc:Filter>
  </wfs:Query>
</wfs:GetFeature>

results in the following SQL being executed on oracle (according to the
catalinga log):
SELECT UFI, PHOTO_ID, PROJECT, RUN, SCALE, CAPTURE_DATE, GEOMETRY FROM
AIR_PHOTO_INDEX WHERE NOT
SDO_RELATE("GEOMETRY",MDSYS.SDO_GEOMETRY(2003,8311,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(130.84310775,-12.3816064,130.84310775,-12.38133377,130.8431584,-12.38133377,130.8431584,-12.3816064,130.84310775,-12.3816064)),'mask=disjoint
querytype=WINDOW') = 'TRUE'

that query takes about 20 seconds to run on the oracle machine.. and about 30
seconds to return from a geoserver point of view.

However, the query is strange, its asking for all the features NOT NOT in the
specified area.. the following takes about 0.01 seconds, same results returned..
SELECT UFI, PHOTO_ID, PROJECT, RUN, SCALE, CAPTURE_DATE, GEOMETRY FROM
AIR_PHOTO_INDEX WHERE
SDO_RELATE("GEOMETRY",MDSYS.SDO_GEOMETRY(2003,8311,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(130.84310775,-12.3816064,130.84310775,-12.38133377,130.8431584,-12.38133377,130.8431584,-12.3816064,130.84310775,-12.3816064)),'mask=anyinteract
querytype=WINDOW') = 'TRUE'

(note i've removed a NOT and subs. disjoint with anyinteract)

.. also the BBOX filter type (i.e. a different WFS query) does not use a
SDO_FILTER like it possibly could, instead a SDO_RELATE the same as above is
generated which are a lot slower.

This problem is greatly amplified on some of our larger datasets such as roads
and cadastre.

What is the status of development on the Oracle datastore ? We would like to use
WFS rather than develop our own SOAP stuff based on more efficient SQL queries..
what are our options in terms of promoting the Oracle datastore development ?

versions: Geoserver is 1.2.4, OS is linux redhat 9, Oracle 9i DB is on Linux
(separate machine)

Thanks in advance..

-ivan

Ivan Price
Spatial Systems Manager
Northern Territory Land Information System
Department of Infrastructure, Planning and Environment
Northern Territory Government
Tel: (08) 8924 4024
Fax: (08) 8924 4045
Email: ivan.price@anonymised.com

2

Quoting Ivan.Price@anonymised.com:

Gday Geoserver folks..

I'm having a play with geoserver talking to an Oracle Spatial 9i
database..
would like to ask a question about the Oracle datastore..

submitting the following query:

<?xml version="1.0"?>
<wfs:GetFeature service="WFS" version="1.0.0"
  outputFormat="GML2"
  xmlns:topp="http://www.openplans.org/topp&quot;
  xmlns:wfs="http://www.opengis.net/wfs&quot;
  xmlns:ogc="http://www.opengis.net/ogc&quot;
  xmlns:gml="http://www.opengis.net/gml&quot;
>
  <wfs:Query typeName="topp:AIR_PHOTO_INDEX">
      <ogc:Filter>
      <ogc:Intersects>
        <ogc:PropertyName>GEOMETRY</ogc:PropertyName>
            <gml:Polygon>
              <gml:outerBoundaryIs>
                <gml:LinearRing>
                  <gml:coordinates decimal="." cs=","
ts="">130.84310775,-12.38133377 130.84337329,-12.38129283
130.84344202,-12.38166684 130.84338598,-12.38167355
130.8431756,-12.38169938
130.8431584,-12.3816064 130.84310775,-12.38133377</gml:coordinates>
                </gml:LinearRing>
              </gml:outerBoundaryIs>
            </gml:Polygon>
        </ogc:Intersects>
      </ogc:Filter>
  </wfs:Query>
</wfs:GetFeature>

results in the following SQL being executed on oracle (according to
the
catalinga log):
SELECT UFI, PHOTO_ID, PROJECT, RUN, SCALE, CAPTURE_DATE, GEOMETRY
FROM
AIR_PHOTO_INDEX WHERE NOT

SDO_RELATE("GEOMETRY",MDSYS.SDO_GEOMETRY(2003,8311,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(130.84310775,-12.3816064,130.84310775,-12.38133377,130.8431584,-12.38133377,130.8431584,-12.3816064,130.84310775,-12.3816064)),'mask=disjoint

querytype=WINDOW') = 'TRUE'

that query takes about 20 seconds to run on the oracle machine.. and
about 30
seconds to return from a geoserver point of view.

However, the query is strange, its asking for all the features NOT
NOT in the
specified area.. the following takes about 0.01 seconds, same results
returned..
SELECT UFI, PHOTO_ID, PROJECT, RUN, SCALE, CAPTURE_DATE, GEOMETRY
FROM
AIR_PHOTO_INDEX WHERE

SDO_RELATE("GEOMETRY",MDSYS.SDO_GEOMETRY(2003,8311,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(130.84310775,-12.3816064,130.84310775,-12.38133377,130.8431584,-12.38133377,130.8431584,-12.3816064,130.84310775,-12.3816064)),'mask=anyinteract

querytype=WINDOW') = 'TRUE'

(note i've removed a NOT and subs. disjoint with anyinteract)

.. also the BBOX filter type (i.e. a different WFS query) does not
use a
SDO_FILTER like it possibly could, instead a SDO_RELATE the same as
above is
generated which are a lot slower.

This problem is greatly amplified on some of our larger datasets such
as roads
and cadastre.

Hmmm... There was much discussion about this, and I was convinced to go
back to the SDO_RELATE (despite the fact I'm not sure that it exactly
does what the OGC intersect wants, as I had tests that showed it as
different, but the performance hit is obviously major).

Are you _sure_ you're on 1.2.4? That's where the upgrade should have
gotten in...

See:
http://svn.geotools.org/geotools/branches/2.0.x/gt/plugin/oraclespatial/src/org/geotools/filter/SQLEncoderOracle.java
The old doNotDisjoint method is commented out...

I also put the release as a datastore extra, at:
http://prdownloads.sourceforge.net/geoserver/geoserver-oracle-ext-1.2.4.zip?download
So even if you're on less than 1.2.4, you should be able to just unzip
and drop in the replacement with that. But please let me know if 1.2.4
isn't working, it really should have had that fix.

What is the status of development on the Oracle datastore ? We would
like to use
WFS rather than develop our own SOAP stuff based on more efficient
SQL queries..
what are our options in terms of promoting the Oracle datastore
development ?

Development is pretty slow, but I do what I can to make sure it's
working reasonably well. But keep in mind that development is
completely open, so if you developed faster sql encodings in the oracle
datastore then I'd be more than happy to roll them in, and would highly
recommend taking that route. Things should be decently fast at the
moment, and I believe there are some more optimizations you could do,
more on the structs connections instead of the filters, if you're
interested. Also note that we have an OCI connector, so if you can
install the thick client stuff you also should get much faster speeds.

best regards,

Chris

versions: Geoserver is 1.2.4, OS is linux redhat 9, Oracle 9i DB is
on Linux
(separate machine)

Thanks in advance..

-ivan

Ivan Price
Spatial Systems Manager
Northern Territory Land Information System
Department of Infrastructure, Planning and Environment
Northern Territory Government
Tel: (08) 8924 4024
Fax: (08) 8924 4045
Email: ivan.price@anonymised.com

-------------------------------------------------------
SF email is sponsored by - The IT Product Guide
Read honest & candid reviews on hundreds of IT Products from real
users.
Discover which products truly live up to the hype. Start reading now.
http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

----------------------------------------------------------
This mail sent through IMP: https://webmail.limegroup.com/