On Tue, Feb 7, 2012 at 10:04 PM, George, Curtis D (US SSA) <[email protected]> wrote: > Hello all, > > > > I was wondering why the current geoserver jdbc plugins do not support > calling stored procedures.
Because we're bad and like to make our users suffer :-p Seriously, 90% of the time the answer to this kind of question is the simplest one, "because nobody put resources to back this kind of use case" or put in other terms "because no developer ever needed it and nobody else sponsored a developer to do it" > We currently use geoserver and have a > scalability problem that our database engineer is convinced can be solved by > calling a store procedure. Our scalability problem is caused in part by an > oracle bug which has driven our physical table layout to a certain way > (inefficient for use with geoserver). Our database engineer says he can > work around the oracle bug by calling a stored procedure that he wrote, > which seems to require us writing a customized jdbc plugin to allow this in > geoserver. Before I ventured any further down this path I wanted to see if > anyone else had any thoughts on this, of if it was already attempted by > anyone already. GeoServer supports SQL views, which allow you to specify manually the SQL query acting as the data source: http://docs.geoserver.org/latest/en/user/data/sqlview.html As far as I know you can call a stored procedure that returns data in there. However mind, this source will be treated as a table, that is, all spatial indexes and other filters, as well as sorting and paging, will be added around that call, not inside. If you need to take into account the full Query object to adapt what your store procedure does (in order to efficiently execute the filters), you'll indeed have to write your own data store and honor property selection, all the filters, sorting and paging (typically stores split the query in two parts, one that can run inside the native engine, and a post-process one that does it memory what cannot be done in the database). The JDBC stores in GeoTools are probably the best pattern you might want to follow to get what you need done Cheers Andrea -- ------------------------------------------------------- Ing. Andrea Aime GeoSolutions S.A.S. Tech lead Via Poggio alle Viti 1187 55054 Massarosa (LU) Italy phone: +39 0584 962313 fax: +39 0584 962313 mob: +39 339 8844549 http://www.geo-solutions.it http://geo-solutions.blogspot.com/ http://www.youtube.com/user/GeoSolutionsIT http://www.linkedin.com/in/andreaaime http://twitter.com/geowolf ------------------------------------------------------- ------------------------------------------------------------------------------ Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d _______________________________________________ Geoserver-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/geoserver-devel
