Hi Jonathan,
So I was wrong with thinking that SDO_TUNE.EXTENT_OF would simply query the sdo_root_mbr from index metadata. But who knows how spatial index in Oracle really works and how risky it would be to use “select sdo_root_mbr from all_sdo_index_metadata where sdo_index_name=’GEOTABLE_SP_IDX’”? At least that query can’t be slow ever.
-Jukka-
···
Jonathan Moules wrote:
Hi Jukka,
Your mentioning SDO_TUNE.EXTENT_OF reminded me of a thread from a couple of years ago:
http://osgeo-org.1560.x6.nabble.com/SDO-TUNE-EXTENT-OF-Oracle-td5025640.html
The summary is – speed varies considerably depending on which version of Oracle you have (10g versus 11g) and maybe indexes and things too.
Cheers,
Jonathan
From: Rahkonen Jukka (MML) [mailto:jukka.rahkonen@anonymised.com]
Sent: Tuesday, March 17, 2015 8:53 AM
To: Geoserver-devel
Cc: VT@anonymised.com; Peter.Warren@anonymised.com
Subject: Re: [Geoserver-devel] Propose to add an option for latLongBoundingBox range checking before database query.
Hi,
If it should save some time when testing, these queries are fast for me and they return the same result.
-
select sdo_tune.extent_of(‘geotable’,‘geometry’) from dual;
-
select sdo_root_mbr from all_sdo_index_metadata where sdo_index_name=’GEOTABLE_SP_IDX’;
I guess that the tune.extent is doing about this:
select sdo_root_mbr from all_sdo_index_metadata where sdo_index_name=
(select INDEX_NAME from all_sdo_index_info where table_name='GEOTABLE’ and column_name=‘GEOMETRY’);
I believe that this query is always safe so that if the BBOX does not intersect with root_mbr there can’t be any geometries in the Oracle table to find. It may be that root_mbr is not shrinked if features are deleted and it may be too large sometimes but that is easy to test. However, the bounding box that is given for the layer in Geoserver is not updated automatically either.
If this is too slow to me performed with every query, perhaps Geotools can be made to cache the root_mbr?
-Jukka Rahkonen-
Rahkonen Jukka wrote:
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://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.
This message has been scanned for viruses by MailControl, a service from BlackSpider Technology
Click here to report this email as spam.
HR Wallingford and its subsidiaries uses faxes and emails for confidential and legally privileged business communications. They do not of themselves create legal commitments. Disclosure to parties other than addressees requires our specific consent. We are not liable for unauthorised disclosures nor reliance upon them.
If you have received this message in error please advise us immediately and destroy all copies of it.
HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099