Hi Jonathan,
I doubt if using UNION would be a good solution, mainly because performance
probably will be (very) poor.
After a little experiment with defining a geoserver sql view [1] I noticed the
view is tightly coupled with the layer itself, so publishing this geoserver sql
view several times (each time with another parameter value) is not an option.
So the only straightforward option left would be to define a geoserver sql view
for every type. So I don't need to create sql server views first. Just
wondering if there is a noticeable difference in performance between layers
based on a database sql view or a geoserver sql view.
Another experiment proves the parametric SQL works.
- With this geoserver sql view definition: SELECT * FROM node WHERE
node_type IN (%types%)
- You can add this to the getmap request:
viewparams=types:'Type1'\,'Type5'\,'Type13'
- And you'll get what you expected (an image with these 3 types)
But you have (obviously) to make sure that the types parameter has a valid
value to use in the IN-clause (which requires adding single quotes for strings
and using + escaping commas between the separate values)
[1]
http://osgeo-org.1560.x6.nabble.com/Publishing-a-geoserver-sql-view-more-than-once-td5159076.html
Kind regards,
Roel
Van: Jonathan Moules [mailto:jonathanmou...@warwickshire.gov.uk]
Verzonden: vrijdag 29 augustus 2014 9:10
Aan: Roel De Nijs
CC: Jorge Sanz; rcefo
Onderwerp: Re: [Geoserver-users] Representing a SQL Server table with different
types
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<mailto: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>
[mailto: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<mailto: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
[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<http://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<mailto: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<mailto: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
