Dear List, In case it helps anyone, I had to disable the spatial index on a SQL Server table when using a bounding box query in light of
“This method may produce a false positive return, and the exact result may be plan-dependent” https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/cc645883(v=sql.105)?redirectedfrom=MSDN Can I please confirm this is understood to be the current behaviour for GeoServer 2.17/2.18? I think this may have been related to http://osgeo-org.1560.x6.nabble.com/Spatial-Index-not-being-Used-in-SQL-Server-Layer-td5011400.html ; Technically I was using a SQL view on the table, and it still didn’t return only the bound points. Does anyone know if PostGIS makes the same approximation? I expect that if I want to reenable the table index I probably need to fund the review or possible change to use STIntersects(). Much appreciated, Russell. From: Andrea Aime <[email protected]> Sent: Tuesday, 1 December 2020 6:46 PM To: mark <[email protected]> Cc: GeoServer Mailing List List <[email protected]> Subject: Re: [Geoserver-users] Instance box on SQL Server plugin On Wed, Nov 25, 2020 at 8:40 PM mark <[email protected]<mailto:[email protected]>> wrote: actually, the table hints are added to the query here: https://github.com/geotools/geotools/blob/f3fd3149a7e9c5b1ff12fdb91b7aed5284325073/modules/plugin/jdbc/jdbc-sqlserver/src/main/java/org/geotools/data/sqlserver/SQLServerDialect.java#L901-L947<https://protect-au.mimecast.com/s/YyHrCOMx3MHKDM3TEvtV1?domain=github.com> Yeah, they are there to force usage of spatial indexes, regardless what the query optimizer might think. It's something one should not have to use, but it seems the query optimizer in SQL Server does a poor job at guessing the selectivity of a spatial index, and sometimes needs to be forced into the right direction. -- Regards, Andrea Aime == GeoServer Professional Services from the experts! Visit http://goo.gl/it488V<https://protect-au.mimecast.com/s/fVf7CP7y37fxBPQT07gFE?domain=goo.gl> for more information. == Ing. Andrea Aime @geowolf Technical Lead GeoSolutions S.A.S. Via di Montramito 3/A 55054 Massarosa (LU) phone: +39 0584 962313 fax: +39 0584 1660272 mob: +39 339 8844549 http://www.geo-solutions.it<https://protect-au.mimecast.com/s/WcxMCQnz9ncWnY7TM5Llc?domain=geo-solutions.it> http://twitter.com/geosolutions_it<https://protect-au.mimecast.com/s/WfAFCROA3OSLwxNcON4PQ?domain=twitter.com> ------------------------------------------------------- Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia. This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail.
_______________________________________________ Geoserver-users mailing list Please make sure you read the following two resources before posting to this list: - Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/ - The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer [email protected] https://lists.sourceforge.net/lists/listinfo/geoserver-users
