Hi Roel,
I don't know the answer to the parametric SQL, but could maybe using UNION
or UNION ALL with an SQL Server View allow you to present all tables as a
single view? (I'm guessing SQL Server supports these).
So something like:
select * from (
select * from table_1
union
select * from table_2
union
...
) where colA = value
Just a thought.
Cheers,
Jonathan
On 26 August 2014 11:08, Roel De Nijs <roel.den...@aquafin.be> wrote:
> That would indeed be one of the options we are considering.
>
>
>
> But we also want to reduce network traffic. Instead of having a
> WMS-request for each layer (+- 30 in total) returning an image, we are
> looking if it's plausible to just send 1 WMS-request with all visible
> layers. Some of these layers are simple (sql server) table-based layers,
> other are parametric (geoserver) sql views. So the viewparams parameter
> should be able to accept multiple parameter maps. According to the
> documentation [1] it does (using a comma as separator). But how can you
> pass a variable number of view parameters? You don't want to define one
> parameter for each possible node-type. So thinking about using an
> IN-statement in the geoserver sql view (not sure how to define this
> statement correctly), but not sure if it's possible to pass an array to the
> viewparams parameter (eg. viewparams=types:['Type1', 'Type5', 'Type13']).
> Or maybe I can "simply" escape the comma values? Then I would end up with
> something like this:
>
> - The geoserver sql view: SELECT * FROM node WHERE node_type IN
> (%types%)
>
> - The viewparams parameter: types:'Type1'\,'Type5'\,'Type13'
>
> Reasonable or utterly craziness? Thoughts? Concerns?
>
>
>
> [1] http://docs.geoserver.org/stable/en/user/data/database/sqlview.html
>
>
>
> Kind regards,
>
> Roel
>
>
>
> *Van:* xurxos...@gmail.com [mailto:xurxos...@gmail.com] *Namens *Jorge
> Sanz
> *Verzonden:* maandag 25 augustus 2014 23:38
> *Aan:* Roel De Nijs
> *CC:* geoserver-users [geoserver-users@lists.sourcefo
> *Onderwerp:* Re: [Geoserver-users] Representing a SQL Server table with
> different types
>
>
>
> Why not using just one GeoServer SQL view with a parameter for your types
> and in openlayers a function to generate your layers.
>
> You could even generate a void GeoServer SQL layer with a SELECT DISTINCT
> query so you could get your types from a WFS query and make it more
> general.
>
> My 2cts
> --
> Jorge Sanz
> http://jorgesanz.net
>
> Sent from my tablet, sorry for my brevity, top posting, etc.
>
> El 25/08/2014 22:19, "Roel De Nijs" <roel.den...@aquafin.be> escribió:
>
> Hi list,
>
> We have a SQL Server spatial table node with a node_type column containing
> +-350K records. Currently we have +-15 different types; the records are not
> equally
> divided (the least common type 300 records vs the most common type 300K
> records). On different (zoom) levels of our map we need to display other
> node types and a user can decide to hide/show a given type.
> In order to meet this requirement the current setup is as follows:
> - A sql server view (based on table node) for each type (viewType1,
> viewType2, viewType3, ..., viewType15)
> - A geoserver layer for each sql server view (layerType1, layerType2,
> layerType3, ..., layerType15)
> - An OpenLayers layer switcher to control all these layers
> This approach works fine and meets all functional requirements.
>
> At this moment we start a redo of our application. And I was wondering if
> this approach (which was already in use before my arrival) could be
> replaced by another alternative. One of the reasons would be: if a change
> of the views is needed (e.g. filter out out-of-use nodes), I need to change
> all 15 views (e.g. adding the same where-clause). If a new node_type is
> added, you'll have to do some (easy, copy/paste) work. I'm a bit of a
> lazy developer, so I'm always looking to do less if possible :-)
> I guess I'm not the 1st one facing such a scenario and just wondering if
> there are other alternatives. Maybe creating a geoserver sql view once and
> publishing this view 15 times, each time with another type? Or creating
> just 1 geoserver sql view with an in-clause and using viewparams to pass
> all visible types in an array (if possible)? And what about performance of
> using a geoserver sql view vs a geoserver layer based on a sql server view?
>
>
> Kind regards,
> Roel
>
> [image: Afbeelding verwijderd door afzender. banner Chap-eau]
> <http://www.chap-eau.be>
> ------------------------------
>
>
> *Volg Aquafin op Facebook <https://www.facebook.com/AquafinNV> | Twitter
> <https://twitter.com/aquafinnv> | YouTube
> <http://www.youtube.com/channel/UCk_4P5BJ-MtEEDCkCsR_KqQ?feature=mhee> |
> LinkedIN <http://www.linkedin.com/company/aquafin/products> *
>
> Disclaimer: zie www.aquafin.be P Denk aan het milieu. Druk deze mail
> niet onnodig af.
>
>
>
> ------------------------------------------------------------------------------
> Slashdot TV.
> Video for Nerds. Stuff that matters.
> http://tv.slashdot.org/
> _______________________________________________
> Geoserver-users mailing list
> Geoserver-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>
>
> ------------------------------------------------------------------------------
> Slashdot TV.
> Video for Nerds. Stuff that matters.
> http://tv.slashdot.org/
> _______________________________________________
> Geoserver-users mailing list
> Geoserver-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>
>
--
This transmission is intended for the named addressee(s) only and may
contain confidential, sensitive or personal information and should be
handled accordingly. Unless you are the named addressee (or authorised to
receive it for the addressee) you may not copy or use it, or disclose it to
anyone else. If you have received this transmission in error please notify
the sender immediately. All email traffic sent to or from us, including
without limitation all GCSX traffic, may be subject to recording and/or
monitoring in accordance with relevant legislation.
------------------------------------------------------------------------------
Slashdot TV.
Video for Nerds. Stuff that matters.
http://tv.slashdot.org/
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
