Here is an example.
Enviroments:
GeoServer2.2.2
Database: SQL SERVER 2008
Table tblZip stores geography data, and another table tblCountsByZip stores counts by zip code.
tblZip has columns: ID, ZIPCODE, GEOM (data type is GEOMETRY ), etc
tblCountsByZip contains columns: ZIPCODE, CountsByZIP. This table’s data are updated frequently.
I tried to do a description Layer that shows counts by Zip area. I created the layer using GeoServer SQL view. For security issue and easy of maintainance, I don’t want to use straight SQL statement in the SQL view. usally I used Stored Procedure in my applications but stored procedure doesn’t work in GeoServer SQL view. I tried SQL SERVER table-valued functions and that works.
I created a table-valued function fn_CountsByZip in the database.
Example codes:
CREATE FUNCTION [dbo].[ fn_CountsByZip]
(
– Add the parameters for the function here
@ZIPCODE varchar(8000),
)
RETURNS TABLE
AS
RETURN
(
– Add the SELECT statement with parameter references here
SELECT tblZip.ID, tblZip.ZIPCODE, tblZip.GEOM,
tblCountsByZIP .CountsByZip
from tblZip
join tblCountsByZIP tblZIP.ZIPCODE = tblCountsByZIP.ZIPCODE
–Note: fn_Split is a function that split comma delimited String
join fn_Split(@ZIPCODE, ‘,’) z tblZIP.ZIPCODE= z.items
)
GO
Example query to select from the function:
Select * from fn_CountsByZip(‘27514,27516’);
I created a map layer using SQL View following the instructions on http://docs.geoserver.org/stable/en/user/data/database/sqlview.html)
In SQL view, I used the query below and set up a parameter %ZIPCODE%
Select * from fn_CountsByZip(%ZIPCODE%)
You can see that the query is simple in SQL View, since all the logic is handled in the function fn_CountsByZip.
By doing this, we can get a dynamic map layer of counts by zip code which fetches real-time data from the database.
Using WMS, we can limit the results by zipcodes, using viewparams such as &viewparams= ZIPCODE:27514
or &viewparams= ZIPCODE:27514, 27516.
Hope this helps.
Thanks!
Meichun
From: Martin Davis mtnclimb@anonymised.com
To: Andrea Aime andrea.aime@anonymised.com
Cc: Meichun Li pcloudy2005@anonymised.com; “geoserver-users@lists.sourceforge.net” geoserver-users@lists.sourceforge.net
Sent: Thursday, January 31, 2013 1:34 PM
Subject: Re: [Geoserver-users] Passing multiple values to parameters / SQL Server Stored Procedure
Yes, it would be nice to see this example.
Perhaps these kinds of things could start to be captured in a “SQL Views Tips and Tricks” section in the User Guide?
On Thu, Jan 31, 2013 at 10:24 AM, Andrea Aime <andrea.aime@anonymised.com> wrote:
On Thu, Jan 31, 2013 at 6:27 PM, Meichun Li <pcloudy2005@anonymised.com> wrote:
Even though I can’t use SQL Server Stored Procedure in SQL view, I figured out how to use table-valued functions which worked great! Also,I can pass multi-value parameters to a function.
Ah interesting, this is going to be useful for other users, thanks for letting us know.
Could you share the example of the table valued function?
Cheers
Andrea
–
Ing. Andrea Aime
@geowolf
Technical Lead
GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549
http://www.geo-solutions.it
http://twitter.com/geosolutions_it
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_jan
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users