Thank you all for your responses, the conversation so far has been very
helpful. I think I may have confused the issue a little by asking about
stored procedures, and the solution outlined by Andrea below would be a
perfect match for what I need.
We have been successfully displaying statistics in thematic maps but we
have been constantly getting suggestions for a new dimension to the
data. An example might be historical months or seeing population by
males vs. females and then inside those two groups - what is the
population by age group or hair color, what is the income for each
gender, age group and hair color. I have accomplished the dynamically
regeneration of thematic maps so far by creating feature types off of
views specific to each data cut, but this has only allowed me to cut the
data by geographic region (state, county, MSA) and historical trend
(current month through 6 months of history). Creating new views for
each cut is not a scalable solution so I was thinking that the ability
to pre-calculate all my cuts and filter them using SQL would allow me to
add new statistics without creating new feature types and also reduce
the number of styles I need.
Reading the documentation I have been able to accomplish some of my
goals through filters in the styles but this also leads to the
generation of numerous SLD files (much like the numerous views).
To sum up, Andrea has outlined what I believe to be a perfect solution
both in passing some number of parameters through the GET string and
having those parameters show up in the query. There would no reason to
address the stored procedure issue if this were available.
Nate
-----Original Message-----
From: Andrea Aime [mailto:aaime@anonymised.com]
Sent: Thursday, June 11, 2009 4:01 AM
To: Justin Deoliveira
Cc: Den Herder, Nate P; Marghescu, Silviu; Berry, Megan C;
geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Oracle Stored Procedure
Justin Deoliveira ha scritto:
Interesting. So this seems to fall into the "virtual feature type"
idea
that has been on the table for some time. A common request is to
publish
a feature type backed by an SQL query, rather than table or a view. A
stored procedure is more or less along the same lines.
Andrea and I agreed more or less on an approach to code this, but
unfortunately its by no means trivial, something on the order of
weeks.
So until someone has a mandate for this feature, it probably won't be
implemented.
Hooking up parameters might be a bit tricky as well. It might be good
to
have an idea of what sort of things you would want to parameterize.
Agreed on all the line, calling a stored procedure looks like a
special case of executing a random query with parameters.
Parametrization wise, I would suggest the query (sp call) is
parametrized with well known markers with default values, so
that the query can be executed also in absence of them, something
like:
SELECT * FROM TABLE WHERE ATT1 BETWEEN $minval,0$ AND $maxval,100$
assuming $ is a good choice for a marker symbol (we probably want
that to be customizable as well)
And then the WMS/WFS request could look like:
...&request=GetMap&...&dataParams=minVal:10;maxVal:20
The important thing here is that the param usage should never
alter the structure or the record set returned by the query.
Having default values is crucial because we need to run the
query an inspect the results to determine the structure
of the feature type.
Oh, this jira has some more discussion:
http://jira.codehaus.org/browse/GEOT-2123
An interesting bit we discussed is how to support further
filtering without having to do that in memory by using
sub-queries (the typical wms request adds at the very least
a bbox filter to the query).
I'm not sure that would work for stored procedures thought
(never tried, maybe it does), a quick scan over the internet
seems to suggest that support is the privilege of few
databases (sql server, not mysql, probaly not oracle nor
postgres).
Cheers
Andrea
--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.