Hi Paul, Have you already set the logging level to GEOTOOLS_DEVELOPER and had a look at the SQL that Geoserver generates? Another option is to log the statements on Oracle side.
-Jukka Rahkonen- Lähettäjä: Andrea Aime [mailto:[email protected]] Lähetetty: 4. marraskuuta 2018 17:35 Vastaanottaja: Wittle, Paul <[email protected]> Kopio: GeoServer Mailing List List <[email protected]> Aihe: Re: [Geoserver-users] Database views referencing other databases Hi Paul, as predicted, there is no much feedback, I have not much either. Just wanted to report that GeoServer figures out the structure of views using JDBC standard methods to get the list of columns (JDBCMetadata class), and has no idea that the table is linked to a remote database. It's probably something happening at the JDBC driver level Cheers Andrea On Wed, Oct 31, 2018 at 2:53 PM Paul Wittle via Geoserver-users <[email protected]<mailto:[email protected]>> wrote: Hi, This is almost definitely really niche but I thought I’d send the message in case it is relevant for any reason. We have a view in our Oracle database which takes a spatial table (source of GeoServer store) and joins it with non-spatial data from another Oracle database which is not in our control. The view is slow as you would expect however it cannot be loaded into GeoServer because GeoServer is discovering the reference to an alternate database somehow. The reason I thought I’d email is that it does work if you view it in a viewer’s such as Toad but it also works fine in other GIS capable applications such QGIS and FME. Please note, the reference to the other database should be in the database view not an SQL view in GeoServer. I think it is logical that GeoServer may not accept that sort of thing but I’m confused by why GeoServer even knows the view is referencing another database, I would have expected it to just query the view in the source database and let the database handle the joins but I think it is something to do with the way GeoServer gets the metadata (although I’m guessing). I’m hoping it would be easy to check in other database types, just create a view which references another database / schema and then see if the view can be published in GeoServer and rendered by QGIS. It is probably too niche to log it as a bug and so I’m not necessarily expecting that we do anything but I thought I’d ask round to see if others can easily recreate the situation to see if it is just me as usual. Best Regards, Paul Wittle "This e-mail is intended for the named addressee(s) only and may contain information about individuals or other sensitive information and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this email in error, kindly disregard the content of the message and notify the sender immediately. Please be aware that all email may be subject to recording and/or monitoring in accordance with relevant legislation." _______________________________________________ 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]<mailto:[email protected]> https://lists.sourceforge.net/lists/listinfo/geoserver-users -- Regards, Andrea Aime == GeoServer Professional Services from the experts! Visit http://goo.gl/it488V 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 http://twitter.com/geosolutions_it ------------------------------------------------------- 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
