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:
CREATEFUNCTION [dbo].[ fn_CountsByZip]
(
-- Add the parameters for the function here
@ZIPCODE varchar(8000),
)
RETURNSTABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT tblZip.ID, tblZip.ZIPCODE, tblZip.GEOM,
tblCountsByZIP .CountsByZip
fromtblZip
join tblCountsByZIP tblZIP.ZIPCODE = tblCountsByZIP.ZIPCODE
--Note: fn_Split is a function that split comma delimited String
joinfn_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 <mtncl...@gmail.com>
To: Andrea Aime <andrea.a...@geo-solutions.it>
Cc: Meichun Li <pcloudy2...@yahoo.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.a...@geo-solutions.it>
wrote:
On Thu, Jan 31, 2013 at 6:27 PM, Meichun Li <pcloudy2...@yahoo.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
>--
>
>==
>Our support, Your Success! Visit http://opensdi.geo-solutions.it for more
>information.
>==
>
>
>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
>
>
------------------------------------------------------------------------------
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
