Representing a SQL Server table with different types

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Representing a SQL Server table with different types

Roel De Nijs
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
banner Chap-eau


Volg Aquafin op Facebook | Twitter | YouTube | LinkedIN

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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Representing a SQL Server table with different types

Jorge Sanz (OSGeo)

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" <[hidden email]> 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
banner Chap-eau


Volg Aquafin op Facebook | Twitter | YouTube | LinkedIN

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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users


------------------------------------------------------------------------------
Slashdot TV.  
Video for Nerds.  Stuff that matters.
http://tv.slashdot.org/
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Representing a SQL Server table with different types

Roel De Nijs

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: [hidden email] [mailto:[hidden email]] Namens Jorge Sanz
Verzonden: maandag 25 augustus 2014 23:38
Aan: Roel De Nijs
CC: geoserver-users ‎[[hidden email]
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" <[hidden email]> 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



Volg Aquafin op Facebook | Twitter | YouTube | LinkedIN

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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users


------------------------------------------------------------------------------
Slashdot TV.  
Video for Nerds.  Stuff that matters.
http://tv.slashdot.org/
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Representing a SQL Server table with different types

Jonathan Moules-2
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 <[hidden email]> 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: [hidden email] [mailto:[hidden email]] Namens Jorge Sanz
Verzonden: maandag 25 augustus 2014 23:38
Aan: Roel De Nijs
CC: geoserver-users [[hidden email]
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" <[hidden email]> 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



Volg Aquafin op Facebook | Twitter | YouTube | LinkedIN

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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users


------------------------------------------------------------------------------
Slashdot TV.
Video for Nerds.  Stuff that matters.
http://tv.slashdot.org/
_______________________________________________
Geoserver-users mailing list
[hidden email]
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Representing a SQL Server table with different types

Roel De Nijs

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:[hidden email]]
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 <[hidden email]> 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: [hidden email] [mailto:[hidden email]] Namens Jorge Sanz
Verzonden: maandag 25 augustus 2014 23:38
Aan: Roel De Nijs
CC: geoserver-users [[hidden email]
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" <[hidden email]> 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



Volg Aquafin op Facebook | Twitter | YouTube | LinkedIN

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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users


------------------------------------------------------------------------------
Slashdot TV.
Video for Nerds.  Stuff that matters.
http://tv.slashdot.org/
_______________________________________________
Geoserver-users mailing list
[hidden email]
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users