On Tue, Feb 7, 2012 at 10:04 PM, George, Curtis D (US SSA)
<curtis.george@anonymised.com> 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
-------------------------------------------------------