[Geoserver-devel] Propose to add an option for latLongBoundingBox range checking before database query.

GeoServer GeoServer Developer
1

Hi Andrea,

It seem like there might be some confusion with the different oracle issues floating around. The oracle version(https://jira.codehaus.org/browse/GEOT-4912 ) issue has nothing to do with the patch Lingbo is suggesting on implementing. However to clear up the confusion, I have advised Lingbo to create appropriate Jira task and submit a proposal instead so that the issue can be discussed accordingly around the proposal instead.

Thanks

Victor

···

From: Jiang, Lingbo (Digital, Marsfield)
Sent: Wednesday, 11 March 2015 12:49 PM
To: Tey, Victor (Mineral Resources, Kensington)
Subject: FW: [Geoserver-devel] [ExternalEmail] Re: Propose to add an option for latLongBoundingBox range checking before database query.

From: andrea.aime@…403… [mailto:andrea.aime@…403…] On Behalf Of Andrea Aime
Sent: Wednesday, 11 March 2015 1:50 PM
To: Jiang, Lingbo (Digital, Marsfield)
Cc: Rahkonen Jukka (MML); Jody Garnett; Geoserver-devel
Subject: Re: [Geoserver-devel] [ExternalEmail] Re: Propose to add an option for latLongBoundingBox range checking before database query.

On Sun, Mar 8, 2015 at 11:53 PM, <Lingbo.Jiang@…254…> wrote:

Hi Everyone,

I am working a patch on the “Honour the configured BBOX” with geoserver wms module. And we found that the number of database queries could reduce significantly with the BBOX to filter out the useless query. And it do improve the performance lots.

Hem… you found that it helps a lot in a particular Oracle version (according to other comments) and had the suggestion from two

core developers and PSC members to try and do the fix in the Oracle data store.

Sorry to pull seniority here, but you cannot just ignore the comments you don’t like and go on your merry way, whatever

comes out of a discussion needs an agreement to move on with code.

So could you please share why you think this must be done in a general way, as opposed to inside the Oracle store?

So far, I am trying to put this BBOX application option into the configuration file. I noticed that there are few choice:

  1. ApplicationContext.xml.

  2. Wms.xml

  3. Global.xml

Could someone suggest that which one will better to suit and what’s best way to add this option. A code snippet will be even great.

Neither of them, this kind of setting would have to go in resource.xml as it would be a per layer one, not all layers

in an installation would be Oracle, or would have to be locked to a bbox (wfs-t layers definitely do not, poeple will add

data out of the bbox you configured.

Cheers

Andrea

–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/NWWaa2 for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

2

On Wed, Mar 11, 2015 at 6:11 AM, <Victor.Tey@anonymised.com> wrote:

Hi Andrea,

It seem like there might be some confusion with the different oracle
issues floating around. The oracle version(
https://jira.codehaus.org/browse/GEOT-4912 ) issue has nothing to do with
the patch Lingbo is suggesting on implementing. However to clear up the
confusion, I have advised Lingbo to create appropriate Jira task and submit
a proposal instead so that the issue can be discussed accordingly around
the proposal instead.

Yes, indeed that ticket has nothing to do with it (actually I don't
understand why you're bringing it up? Maybe you wanted to link a different
ticket?)
But earlier in this thread you said (copying and pasting):

---------

What one of our developer have found with oracle enterprise edition is that
the database actually optimize the query when a query is made outside the
bounds of the dataset. However when we ran the same query on a oracle
standard edition, the query crashed the database. We have logged a ticket
with Oracle as part of our contract with them and its currently under
investigation.

---------

So, this statement makes me (and Jody) think your bbox problem problem is
Oracle version specific.
In other words, can you show us how this new configuration would provide a
_significant_ benefit with a non
broken database query optimizer? (yes, a theoretical general benefit is
evident, you don't even access the data,
it has to be faster, but if we get only 0.1% speedup on the overall WMS/WFS
request it's not worth the effort and the maintenance of that code).

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

3

I have been trying to think of any other data source that could benefit ? Perhaps WMS cascade?

Even with patching this on geoserver I would like to see a GeoTools patch for this very real performance issue with the oracle dialect of JDBCDataStore.

On Tue, Mar 10, 2015 at 10:13 PM Victor.Tey@anonymised.com wrote:

Hi Andrea,

It seem like there might be some confusion with the different oracle issues floating around. The oracle version(https://jira.codehaus.org/browse/GEOT-4912 ) issue has nothing to do with the patch Lingbo is suggesting on implementing. However to clear up the confusion, I have advised Lingbo to create appropriate Jira task and submit a proposal instead so that the issue can be discussed accordingly around the proposal instead.

Thanks

Victor

From: Jiang, Lingbo (Digital, Marsfield)
Sent: Wednesday, 11 March 2015 12:49 PM
To: Tey, Victor (Mineral Resources, Kensington)
Subject: FW: [Geoserver-devel] [ExternalEmail] Re: Propose to add an option for latLongBoundingBox range checking before database query.

From: andrea.aime@anonymised.com03… [mailto:andrea.aime@anonymised.com] On Behalf Of Andrea Aime
Sent: Wednesday, 11 March 2015 1:50 PM

To: Jiang, Lingbo (Digital, Marsfield)

Cc: Rahkonen Jukka (MML); Jody Garnett; Geoserver-devel

Subject: Re: [Geoserver-devel] [ExternalEmail] Re: Propose to add an option for latLongBoundingBox range checking before database query.

On Sun, Mar 8, 2015 at 11:53 PM, <Lingbo.Jiang@anonymised.com> wrote:

Hi Everyone,

I am working a patch on the “Honour the configured BBOX” with geoserver wms module. And we found that the number of database queries could reduce significantly with the BBOX to filter out the useless query. And it do improve the performance lots.

Hem… you found that it helps a lot in a particular Oracle version (according to other comments) and had the suggestion from two

core developers and PSC members to try and do the fix in the Oracle data store.

Sorry to pull seniority here, but you cannot just ignore the comments you don’t like and go on your merry way, whatever

comes out of a discussion needs an agreement to move on with code.

So could you please share why you think this must be done in a general way, as opposed to inside the Oracle store?

So far, I am trying to put this BBOX application option into the configuration file. I noticed that there are few choice:

  1. ApplicationContext.xml.

  2. Wms.xml

  3. Global.xml

Could someone suggest that which one will better to suit and what’s best way to add this option. A code snippet will be even great.

Neither of them, this kind of setting would have to go in resource.xml as it would be a per layer one, not all layers

in an installation would be Oracle, or would have to be locked to a bbox (wfs-t layers definitely do not, poeple will add

data out of the bbox you configured.

Cheers

Andrea

–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/NWWaa2 for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@anonymised.comsourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

4

Hi Andrea,

The jira for “Honour the configured BBOX” issue is here(https://jira.codehaus.org/browse/GEOS-6872).

GeoServer currently makes unnecessary database request to areas not covered under the bounding box of the dataset. And we had observed that some wms queries take minutes to return and crashed the database(part of reason came from the oracle index problem) as well. This made us understanding that the query do need optimizing, especially in the situation that there is a big map area query from a small area’s dataset. Thus we thought that we can create an option to improve the way it handles the query by limiting the query request to areas that falls within the bounding box of the dataset. Then we came out the ideal that do a BBOX check before the query is a simple ideal solution because it could filter out 90% useless query which is out of dataset BBOX scope.

Our initial solution is to do a data BBOX check in wms.GetMap module. Because it is an ideal place with easy accessing the dataset BBOX of every layer. It also has the access to query BBOX as well, thus we could easily do a overlap check to filter out the useless query which is majority of query. Plus by this way , I believe that all the WMS query performance could be well improved no matter it is on oracle or sql .

I agree with you that we could do it on oracle datastore level in geotools providing other DB has no need at all. It could fix our oracle problem only without affecting others . with your expertise, could you help to pinpoint where we may have good access with dataset BBOX and query BBOX together in Geotools .

Lingbo

···

From: andrea.aime@…403… [mailto:andrea.aime@…403…] On Behalf Of Andrea Aime
Sent: Thursday, 12 March 2015 8:12 AM
To: Tey, Victor (Mineral Resources, Kensington)
Cc: Geoserver-devel; Jiang, Lingbo (Digital, Marsfield)
Subject: Re: [Geoserver-devel] Propose to add an option for latLongBoundingBox range checking before database query.

On Wed, Mar 11, 2015 at 6:11 AM, <Victor.Tey@…254…> wrote:

Hi Andrea,

It seem like there might be some confusion with the different oracle issues floating around. The oracle version(https://jira.codehaus.org/browse/GEOT-4912 ) issue has nothing to do with the patch Lingbo is suggesting on implementing. However to clear up the confusion, I have advised Lingbo to create appropriate Jira task and submit a proposal instead so that the issue can be discussed accordingly around the proposal instead.

Yes, indeed that ticket has nothing to do with it (actually I don’t understand why you’re bringing it up? Maybe you wanted to link a different ticket?)

But earlier in this thread you said (copying and pasting):

What one of our developer have found with oracle enterprise edition is that the database actually optimize the query when a query is made outside the bounds of the dataset. However when we ran the same query on a oracle standard edition, the query crashed the database. We have logged a ticket with Oracle as part of our contract with them and its currently under investigation.

So, this statement makes me (and Jody) think your bbox problem problem is Oracle version specific.

In other words, can you show us how this new configuration would provide a significant benefit with a non

broken database query optimizer? (yes, a theoretical general benefit is evident, you don’t even access the data,

it has to be faster, but if we get only 0.1% speedup on the overall WMS/WFS request it’s not worth the effort and the maintenance of that code).

Cheers

Andrea

–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/NWWaa2 for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

5

Hey Lingbo, I am a geotools lead (like Andrea) and I would be happy to
help.

The reason to catch it there (other than it being the right thing to do) is
that it will fix more than just the WMS GetMap case. WMS GetFeatureInfo and
WFS GetFeatures would also be affected after all.

The GeoTools project is built with maven (much like GeoServer), the classes
you are looking for are in JDBCDataStore:

* https://github.com/geotools/geotools/tree/master/modules/library/jdbc

In particular there is an OracleDialect
<https://github.com/geotools/geotools/blob/master/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleDialect.java&gt;
and OracleFilterToSQL
<https://github.com/geotools/geotools/blob/master/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleFilterToSQL.java&gt;
where we construct all the oracle specific SQL queries:

*
https://github.com/geotools/geotools/tree/master/modules/plugin/jdbc/jdbc-oracle

The code of interest in OracleFilterToSQL goes like this:

            if(filter instanceof Beyond || filter instanceof DWithin)
                doSDODistance(filter, e1, e2, extraData);
            else if(filter instanceof BBOX && looseBBOXEnabled) {
                doSDOFilter(filter, e1, e2, extraData);
            } else
                doSDORelate(filter, e1, e2, swapped, extraData);

So you will want to look carefully at the doSDORelate and doSDOFilter
methods.

I notice there is already a method here that does something like what you
want, perhaps you just have a bug to fix in the following code.

    Expression clipToWorld(BinarySpatialOperator filter, Expression e) {
        if (e instanceof Literal) {
            Geometry eval = e.evaluate(filter, Geometry.class);
            // Oracle cannot deal with filters using geometries that span
beyond the whole world
            // in case the
            if (dialect != null && isCurrentGeometryGeodetic() &&
                    !WORLD.contains(eval.getEnvelopeInternal())) {
                Geometry result = eval.intersection(JTS.toGeometry(WORLD));

                if (result != null && !result.isEmpty()) {
                    if(result instanceof GeometryCollection) {
                        result =
distillSameTypeGeometries((GeometryCollection) result, eval);
                    }
                    FilterFactory2 ff =
CommonFactoryFinder.getFilterFactory2();
                    e = ff.literal( result );
                }
            }
        }
        return e;
    }

You may need to change this code to clip against your index bounds rather
than "WORLD".

I agree with you that we could do it on oracle datastore level in geotools

providing other DB has no need at all. It could fix our oracle problem
only without affecting others . with your expertise, could you help to pinpoint
where we may have good access with dataset BBOX and query BBOX together
in Geotools .

Lingbo

*From:* andrea.aime@anonymised.com [mailto:andrea.aime@anonymised.com
<andrea.aime@anonymised.com>] *On Behalf Of *Andrea Aime
*Sent:* Thursday, 12 March 2015 8:12 AM
*To:* Tey, Victor (Mineral Resources, Kensington)
*Cc:* Geoserver-devel; Jiang, Lingbo (Digital, Marsfield)
*Subject:* Re: [Geoserver-devel] Propose to add an option for
latLongBoundingBox range checking before database query.

On Wed, Mar 11, 2015 at 6:11 AM, <Victor.Tey@anonymised.com> wrote:

Hi Andrea,

It seem like there might be some confusion with the different oracle
issues floating around. The oracle version(
https://jira.codehaus.org/browse/GEOT-4912 ) issue has nothing to do with
the patch Lingbo is suggesting on implementing. However to clear up the
confusion, I have advised Lingbo to create appropriate Jira task and submit
a proposal instead so that the issue can be discussed accordingly around
the proposal instead.

Yes, indeed that ticket has nothing to do with it (actually I don't
understand why you're bringing it up? Maybe you wanted to link a different
ticket?)

But earlier in this thread you said (copying and pasting):

---------

What one of our developer have found with oracle enterprise edition is
that the database actually optimize the query when a query is made outside
the bounds of the dataset. However when we ran the same query on a oracle
standard edition, the query crashed the database. We have logged a ticket
with Oracle as part of our contract with them and its currently under
investigation.

---------

So, this statement makes me (and Jody) think your bbox problem problem is
Oracle version specific.

In other words, can you show us how this new configuration would provide a
_significant_ benefit with a non

broken database query optimizer? (yes, a theoretical general benefit is
evident, you don't even access the data,

it has to be faster, but if we get only 0.1% speedup on the overall
WMS/WFS request it's not worth the effort and the maintenance of that code).

Cheers

Andrea

--

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/NWWaa2 for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

6

Jody,

Thanks for your input.

Do you know how we could get index bounds at this level?

Lingbo

···

From: Jody Garnett [mailto:jody.garnett@…403…]
Sent: Tuesday, 17 March 2015 8:49 AM
To: Jiang, Lingbo (Digital, Marsfield)
Cc: Andrea Aime; Tey, Victor (Mineral Resources, Kensington); geoserver-devel@lists.sourceforge.net
Subject: Re: [Geoserver-devel] Propose to add an option for latLongBoundingBox range checking before database query.

Hey Lingbo, I am a geotools lead (like Andrea) and I would be happy to help.

The reason to catch it there (other than it being the right thing to do) is that it will fix more than just the WMS GetMap case. WMS GetFeatureInfo and WFS GetFeatures would also be affected after all.

The GeoTools project is built with maven (much like GeoServer), the classes you are looking for are in JDBCDataStore:

In particular there is an OracleDialect and OracleFilterToSQL where we construct all the oracle specific SQL queries:

The code of interest in OracleFilterToSQL goes like this:

if(filter instanceof Beyond || filter instanceof DWithin)

doSDODistance(filter, e1, e2, extraData);

else if(filter instanceof BBOX && looseBBOXEnabled) {

doSDOFilter(filter, e1, e2, extraData);

} else

doSDORelate(filter, e1, e2, swapped, extraData);

So you will want to look carefully at the doSDORelate and doSDOFilter methods.

I notice there is already a method here that does something like what you want, perhaps you just have a bug to fix in the following code.

Expression clipToWorld(BinarySpatialOperator filter, Expression e) {

if (e instanceof Literal) {

Geometry eval = e.evaluate(filter, Geometry.class);

// Oracle cannot deal with filters using geometries that span beyond the whole world

// in case the

if (dialect != null && isCurrentGeometryGeodetic() &&

!WORLD.contains(eval.getEnvelopeInternal())) {

Geometry result = eval.intersection(JTS.toGeometry(WORLD));

if (result != null && !result.isEmpty()) {

if(result instanceof GeometryCollection) {

result = distillSameTypeGeometries((GeometryCollection) result, eval);

}

FilterFactory2 ff = CommonFactoryFinder.getFilterFactory2();

e = ff.literal( result );

}

}

}

return e;

}

You may need to change this code to clip against your index bounds rather than “WORLD”.

I agree with you that we could do it on oracle datastore level in geotools providing other DB has no need at all. It could fix our oracle problem only without affecting others . with your expertise, could you help to pinpoint where we may have good access with dataset BBOX and query BBOX together in Geotools .

Lingbo

From: andrea.aime@…403… [mailto:andrea.aime@…403…] On Behalf Of Andrea Aime
Sent: Thursday, 12 March 2015 8:12 AM
To: Tey, Victor (Mineral Resources, Kensington)
Cc: Geoserver-devel; Jiang, Lingbo (Digital, Marsfield)
Subject: Re: [Geoserver-devel] Propose to add an option for latLongBoundingBox range checking before database query.

On Wed, Mar 11, 2015 at 6:11 AM, <Victor.Tey@…254…> wrote:

Hi Andrea,

It seem like there might be some confusion with the different oracle issues floating around. The oracle version(https://jira.codehaus.org/browse/GEOT-4912 ) issue has nothing to do with the patch Lingbo is suggesting on implementing. However to clear up the confusion, I have advised Lingbo to create appropriate Jira task and submit a proposal instead so that the issue can be discussed accordingly around the proposal instead.

Yes, indeed that ticket has nothing to do with it (actually I don’t understand why you’re bringing it up? Maybe you wanted to link a different ticket?)

But earlier in this thread you said (copying and pasting):

What one of our developer have found with oracle enterprise edition is that the database actually optimize the query when a query is made outside the bounds of the dataset. However when we ran the same query on a oracle standard edition, the query crashed the database. We have logged a ticket with Oracle as part of our contract with them and its currently under investigation.

So, this statement makes me (and Jody) think your bbox problem problem is Oracle version specific.

In other words, can you show us how this new configuration would provide a significant benefit with a non

broken database query optimizer? (yes, a theoretical general benefit is evident, you don’t even access the data,

it has to be faster, but if we get only 0.1% speedup on the overall WMS/WFS request it’s not worth the effort and the maintenance of that code).

Cheers

Andrea

–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/NWWaa2 for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

7

I think the index is constructed with the bounds from the table metadata.

I expect the data store can cache anything it wants. Check the bounds implementation to see if is based on the index or metadata configuration.

On Mon, Mar 16, 2015 at 5:44 PM Lingbo.Jiang@anonymised.com wrote:

Jody,

Thanks for your input.

Do you know how we could get index bounds at this level?

Lingbo

From: Jody Garnett [mailto:jody.garnett@anonymised.com]

Sent: Tuesday, 17 March 2015 8:49 AM

To: Jiang, Lingbo (Digital, Marsfield)

Cc: Andrea Aime; Tey, Victor (Mineral Resources, Kensington); geoserver-devel@lists.sourceforge.net

Subject: Re: [Geoserver-devel] Propose to add an option for latLongBoundingBox range checking before database query.

Hey Lingbo, I am a geotools lead (like Andrea) and I would be happy to help.

The reason to catch it there (other than it being the right thing to do) is that it will fix more than just the WMS GetMap case. WMS GetFeatureInfo and WFS GetFeatures would also be affected after all.

The GeoTools project is built with maven (much like GeoServer), the classes you are looking for are in JDBCDataStore:

In particular there is an OracleDialect and OracleFilterToSQL where we construct all the oracle specific SQL queries:

The code of interest in OracleFilterToSQL goes like this:

if(filter instanceof Beyond || filter instanceof DWithin)

doSDODistance(filter, e1, e2, extraData);

else if(filter instanceof BBOX && looseBBOXEnabled) {

doSDOFilter(filter, e1, e2, extraData);

} else

doSDORelate(filter, e1, e2, swapped, extraData);

So you will want to look carefully at the doSDORelate and doSDOFilter methods.

I notice there is already a method here that does something like what you want, perhaps you just have a bug to fix in the following code.

Expression clipToWorld(BinarySpatialOperator filter, Expression e) {

if (e instanceof Literal) {

Geometry eval = e.evaluate(filter, Geometry.class);

// Oracle cannot deal with filters using geometries that span beyond the whole world

// in case the

if (dialect != null && isCurrentGeometryGeodetic() &&

!WORLD.contains(eval.getEnvelopeInternal())) {

Geometry result = eval.intersection(JTS.toGeometry(WORLD));

if (result != null && !result.isEmpty()) {

if(result instanceof GeometryCollection) {

result = distillSameTypeGeometries((GeometryCollection) result, eval);

}

FilterFactory2 ff = CommonFactoryFinder.getFilterFactory2();

e = ff.literal( result );

}

}

}

return e;

}

You may need to change this code to clip against your index bounds rather than “WORLD”.

I agree with you that we could do it on oracle datastore level in geotools providing other DB has no need at all. It could fix our oracle problem only without affecting others . with your expertise, could you help to pinpoint where we may have good access with dataset BBOX and query BBOX together in Geotools .

Lingbo

From: andrea.aime@anonymised.com03… [mailto:andrea.aime@anonymised.com] On Behalf Of Andrea Aime
Sent: Thursday, 12 March 2015 8:12 AM
To: Tey, Victor (Mineral Resources, Kensington)
Cc: Geoserver-devel; Jiang, Lingbo (Digital, Marsfield)
Subject: Re: [Geoserver-devel] Propose to add an option for latLongBoundingBox range checking before database query.

On Wed, Mar 11, 2015 at 6:11 AM, <Victor.Tey@anonymised.com> wrote:

Hi Andrea,

It seem like there might be some confusion with the different oracle issues floating around. The oracle version(https://jira.codehaus.org/browse/GEOT-4912 ) issue has nothing to do with the patch Lingbo is suggesting on implementing. However to clear up the confusion, I have advised Lingbo to create appropriate Jira task and submit a proposal instead so that the issue can be discussed accordingly around the proposal instead.

Yes, indeed that ticket has nothing to do with it (actually I don’t understand why you’re bringing it up? Maybe you wanted to link a different ticket?)

But earlier in this thread you said (copying and pasting):

What one of our developer have found with oracle enterprise edition is that the database actually optimize the query when a query is made outside the bounds of the dataset. However when we ran the same query on a oracle standard edition, the query crashed the database. We have logged a ticket with Oracle as part of our contract with them and its currently under investigation.

So, this statement makes me (and Jody) think your bbox problem problem is Oracle version specific.

In other words, can you show us how this new configuration would provide a significant benefit with a non

broken database query optimizer? (yes, a theoretical general benefit is evident, you don’t even access the data,

it has to be faster, but if we get only 0.1% speedup on the overall WMS/WFS request it’s not worth the effort and the maintenance of that code).

Cheers

Andrea

–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/NWWaa2 for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

8

If you look at the OracleDialect class, it’s already caching some information related to SRSs, I guess it would be a good place
to do the bbox caching.
I’m wondering if we should also do version checks, like, you said this is happening only on a specific version of Oracle,
or at least add a parameter to drive these extra bbox checks at the factory level, so that not all users would have to
go though the extra work.

Cheers
Andrea

···

On Tue, Mar 17, 2015 at 2:30 AM, Jody Garnett <jody.garnett@anonymised.com> wrote:

I think the index is constructed with the bounds from the table metadata.

I expect the data store can cache anything it wants. Check the bounds implementation to see if is based on the index or metadata configuration.

On Mon, Mar 16, 2015 at 5:44 PM Lingbo.Jiang@anonymised.com wrote:

Jody,

Thanks for your input.

Do you know how we could get index bounds at this level?

Lingbo

From: Jody Garnett [mailto:jody.garnett@anonymised.com]

Sent: Tuesday, 17 March 2015 8:49 AM

To: Jiang, Lingbo (Digital, Marsfield)

Cc: Andrea Aime; Tey, Victor (Mineral Resources, Kensington); geoserver-devel@lists.sourceforge.net

Subject: Re: [Geoserver-devel] Propose to add an option for latLongBoundingBox range checking before database query.

Hey Lingbo, I am a geotools lead (like Andrea) and I would be happy to help.

The reason to catch it there (other than it being the right thing to do) is that it will fix more than just the WMS GetMap case. WMS GetFeatureInfo and WFS GetFeatures would also be affected after all.

The GeoTools project is built with maven (much like GeoServer), the classes you are looking for are in JDBCDataStore:

In particular there is an OracleDialect and OracleFilterToSQL where we construct all the oracle specific SQL queries:

The code of interest in OracleFilterToSQL goes like this:

if(filter instanceof Beyond || filter instanceof DWithin)

doSDODistance(filter, e1, e2, extraData);

else if(filter instanceof BBOX && looseBBOXEnabled) {

doSDOFilter(filter, e1, e2, extraData);

} else

doSDORelate(filter, e1, e2, swapped, extraData);

So you will want to look carefully at the doSDORelate and doSDOFilter methods.

I notice there is already a method here that does something like what you want, perhaps you just have a bug to fix in the following code.

Expression clipToWorld(BinarySpatialOperator filter, Expression e) {

if (e instanceof Literal) {

Geometry eval = e.evaluate(filter, Geometry.class);

// Oracle cannot deal with filters using geometries that span beyond the whole world

// in case the

if (dialect != null && isCurrentGeometryGeodetic() &&

!WORLD.contains(eval.getEnvelopeInternal())) {

Geometry result = eval.intersection(JTS.toGeometry(WORLD));

if (result != null && !result.isEmpty()) {

if(result instanceof GeometryCollection) {

result = distillSameTypeGeometries((GeometryCollection) result, eval);

}

FilterFactory2 ff = CommonFactoryFinder.getFilterFactory2();

e = ff.literal( result );

}

}

}

return e;

}

You may need to change this code to clip against your index bounds rather than “WORLD”.

I agree with you that we could do it on oracle datastore level in geotools providing other DB has no need at all. It could fix our oracle problem only without affecting others . with your expertise, could you help to pinpoint where we may have good access with dataset BBOX and query BBOX together in Geotools .

Lingbo

From: andrea.aime@anonymised.com03… [mailto:andrea.aime@anonymised.com] On Behalf Of Andrea Aime
Sent: Thursday, 12 March 2015 8:12 AM
To: Tey, Victor (Mineral Resources, Kensington)
Cc: Geoserver-devel; Jiang, Lingbo (Digital, Marsfield)
Subject: Re: [Geoserver-devel] Propose to add an option for latLongBoundingBox range checking before database query.

On Wed, Mar 11, 2015 at 6:11 AM, <Victor.Tey@anonymised.com> wrote:

Hi Andrea,

It seem like there might be some confusion with the different oracle issues floating around. The oracle version(https://jira.codehaus.org/browse/GEOT-4912 ) issue has nothing to do with the patch Lingbo is suggesting on implementing. However to clear up the confusion, I have advised Lingbo to create appropriate Jira task and submit a proposal instead so that the issue can be discussed accordingly around the proposal instead.

Yes, indeed that ticket has nothing to do with it (actually I don’t understand why you’re bringing it up? Maybe you wanted to link a different ticket?)

But earlier in this thread you said (copying and pasting):

What one of our developer have found with oracle enterprise edition is that the database actually optimize the query when a query is made outside the bounds of the dataset. However when we ran the same query on a oracle standard edition, the query crashed the database. We have logged a ticket with Oracle as part of our contract with them and its currently under investigation.

So, this statement makes me (and Jody) think your bbox problem problem is Oracle version specific.

In other words, can you show us how this new configuration would provide a significant benefit with a non

broken database query optimizer? (yes, a theoretical general benefit is evident, you don’t even access the data,

it has to be faster, but if we get only 0.1% speedup on the overall WMS/WFS request it’s not worth the effort and the maintenance of that code).

Cheers

Andrea

–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/NWWaa2 for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

–

==

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 for more information.

==

Ing. Andrea Aime

@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

9

Hi,

The bounds in the table metadata do not have a direct connection with index bounds. They are inserted by hand or automatically by ogr2ogr or other utilities. DIMINFO puts the maximum extents for the index, though, and it is impossible to insert new features which fall outside these bounds. Therefore we use always in DIMINFO such bounds which cover whole Finland and a little bit extra so that is an unusable metadata for reducing spatial extents of queries in our case. We put more realistic bounds into layer metadata with Geoserver but that would not be accessible for Geotools.

Jody has written 5 years ago that the correct place to look is sdo_root_mbr of the rtree index http://lists.refractions.net/pipermail/udig-users/2010-August/001049.html

However, in this 7 years old thread https://community.oracle.com/thread/626377?start=0&tstart=0 it is said that accessing the bounds directly from Oracle should be very fast:

“For fastest speed, if you are getting the mbr of an entire layer, use sdo_tune.extent_of, which does use the top level MBR values stored in the index to very quickly give you the extent of the layer. For projected data this has been the case for some time. For geodetic data the reading of the index metadata might be new for 11g.”

Is the Oracle bug that started this discussion just in that sdo_tune.extent_of does not work with some Oracle versions?

-Jukka Rahkonen-

···

Jody Garnett wrote:

I think the index is constructed with the bounds from the table metadata.

I expect the data store can cache anything it wants. Check the bounds implementation to see if is based on the index or metadata configuration.

On Mon, Mar 16, 2015 at 5:44 PM Lingbo.Jiang@anonymised.com wrote:

Jody,

Thanks for your input.

Do you know how we could get index bounds at this level?

Lingbo

From: Jody Garnett [mailto:jody.garnett@anonymised.com]

Sent: Tuesday, 17 March 2015 8:49 AM

To: Jiang, Lingbo (Digital, Marsfield)

Cc: Andrea Aime; Tey, Victor (Mineral Resources, Kensington); geoserver-devel@lists.sourceforge.net

Subject: Re: [Geoserver-devel] Propose to add an option for latLongBoundingBox range checking before database query.

Hey Lingbo, I am a geotools lead (like Andrea) and I would be happy to help.

The reason to catch it there (other than it being the right thing to do) is that it will fix more than just the WMS GetMap case. WMS GetFeatureInfo and WFS GetFeatures would also be affected after all.

The GeoTools project is built with maven (much like GeoServer), the classes you are looking for are in JDBCDataStore:

In particular there is an OracleDialect and OracleFilterToSQL where we construct all the oracle specific SQL queries:

The code of interest in OracleFilterToSQL goes like this:

if(filter instanceof Beyond || filter instanceof DWithin)

doSDODistance(filter, e1, e2, extraData);

else if(filter instanceof BBOX && looseBBOXEnabled) {

doSDOFilter(filter, e1, e2, extraData);

} else

doSDORelate(filter, e1, e2, swapped, extraData);

So you will want to look carefully at the doSDORelate and doSDOFilter methods.

I notice there is already a method here that does something like what you want, perhaps you just have a bug to fix in the following code.

Expression clipToWorld(BinarySpatialOperator filter, Expression e) {

if (e instanceof Literal) {

Geometry eval = e.evaluate(filter, Geometry.class);

// Oracle cannot deal with filters using geometries that span beyond the whole world

// in case the

if (dialect != null && isCurrentGeometryGeodetic() &&

!WORLD.contains(eval.getEnvelopeInternal())) {

Geometry result = eval.intersection(JTS.toGeometry(WORLD));

if (result != null && !result.isEmpty()) {

if(result instanceof GeometryCollection) {

result = distillSameTypeGeometries((GeometryCollection) result, eval);

}

FilterFactory2 ff = CommonFactoryFinder.getFilterFactory2();

e = ff.literal( result );

}

}

}

return e;

}

You may need to change this code to clip against your index bounds rather than “WORLD”.

I agree with you that we could do it on oracle datastore level in geotools providing other DB has no need at all. It could fix our oracle problem only without affecting others . with your expertise, could you help to pinpoint where we may have good access with dataset BBOX and query BBOX together in Geotools .

Lingbo

From: andrea.aime@anonymised.com03… [mailto:andrea.aime@anonymised.com] On Behalf Of Andrea Aime
Sent: Thursday, 12 March 2015 8:12 AM
To: Tey, Victor (Mineral Resources, Kensington)
Cc: Geoserver-devel; Jiang, Lingbo (Digital, Marsfield)
Subject: Re: [Geoserver-devel] Propose to add an option for latLongBoundingBox range checking before database query.

On Wed, Mar 11, 2015 at 6:11 AM, <Victor.Tey@anonymised.com> wrote:

Hi Andrea,

It seem like there might be some confusion with the different oracle issues floating around. The oracle version(https://jira.codehaus.org/browse/GEOT-4912 ) issue has nothing to do with the patch Lingbo is suggesting on implementing. However to clear up the confusion, I have advised Lingbo to create appropriate Jira task and submit a proposal instead so that the issue can be discussed accordingly around the proposal instead.

Yes, indeed that ticket has nothing to do with it (actually I don’t understand why you’re bringing it up? Maybe you wanted to link a different ticket?)

But earlier in this thread you said (copying and pasting):

What one of our developer have found with oracle enterprise edition is that the database actually optimize the query when a query is made outside the bounds of the dataset. However when we ran the same query on a oracle standard edition, the query crashed the database. We have logged a ticket with Oracle as part of our contract with them and its currently under investigation.

So, this statement makes me (and Jody) think your bbox problem problem is Oracle version specific.

In other words, can you show us how this new configuration would provide a significant benefit with a non

broken database query optimizer? (yes, a theoretical general benefit is evident, you don’t even access the data,

it has to be faster, but if we get only 0.1% speedup on the overall WMS/WFS request it’s not worth the effort and the maintenance of that code).

Cheers

Andrea

–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/NWWaa2 for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.