[Geoserver-users] SLD with dynamic background color for a polygon

GeoServer GeoServer User
1

Hi all,

I have to implement the following scenario, with postgis as the backend db.

One table holds the geometries of my polygons (several thousands of them), something like

POLYGON_TABLE

ID_POLY | GEOM

I must generate tiles from these geometries, choosing the background color of the polygons from another table.
The problem is that each polygon can be differently painted, according to the user requesting it. So the colors are dynamic and
persisted in another table:

COLOR_TABLE

ID_POLY | ID_USER | COLOR

The background color of the same polygon could be yellow or green, according to the user requesting it.

I read some guides about choosing a color according to a fixed column on the same table of the geometry, or dinamically provide the sld
in the request:

http://osgeo-org.1560.x6.nabble.com/Random-colors-in-SLD-templates-td3790073.html
http://www.geo-solutions.it/blog/dynamic-wms-styling-with-geoserver-sld-and-library-mode/

However my scenario is a bit different, I guess I'd need to implement this by a CQL expression or something like that.

Is it possible to use GeoServer with SLD to generate tiles in this scenario? Would it be reasonably fast or would the query in the SLD really slow down things?

The other option would be to generate the tiles programmatically with geotools, but I'd like to avoid reinventing the wheel.

Thanks for any input,

Paolo

--
Paolo Crosato
Software engineer/Custom Solutions
e-mail: paolo.crosato@anonymised.com

2

Hi Paolo,
I think you can get what you need using parametric sqlviews.
Shortly you can define a sqlview, that is simply a sql query, directly in Geoserver to join the geometry table with COLOR_TABLE on ID_POLY. This sqlview can have a where filter on ID_USER, where the actual value of the user is given on the wms getmap request with the viewparams param.

The sqlview would be something like:

SELECT POLYGON_TABLE.ID_POLY,POLYGON_TABLE.GEOM,COLOR_TABLE.ID_USER,COLOR_TABLE.COLOR
FROM POLYGON_TABLE INNER JOIN COLOR_TABLE ON POLYGON_TABLE.ID_POLY = COLOR_TABLE.ID_POLY
WHERE ID_USER = %user%

and the viewparams param something like:

viewparams=user:1

Then you will use the COLOR attribute to get dynamic coloring in your SLD, with something like:

ogc:PropertyNameCOLOR</ogc:PropertyName>

In depth documentation on sqlviews can be found here: http://docs.geoserver.org/stable/en/user/data/database/sqlview.html

Mauro

···

2014-04-16 17:17 GMT+02:00 Paolo Crosato <paolo.crosato@anonymised.com50…>:

Hi all,

I have to implement the following scenario, with postgis as the backend db.

One table holds the geometries of my polygons (several thousands of
them), something like

POLYGON_TABLE

ID_POLY | GEOM

I must generate tiles from these geometries, choosing the background
color of the polygons from another table.
The problem is that each polygon can be differently painted, according
to the user requesting it. So the colors are dynamic and
persisted in another table:

COLOR_TABLE

ID_POLY | ID_USER | COLOR

The background color of the same polygon could be yellow or green,
according to the user requesting it.

I read some guides about choosing a color according to a fixed column on
the same table of the geometry, or dinamically provide the sld
in the request:

http://osgeo-org.1560.x6.nabble.com/Random-colors-in-SLD-templates-td3790073.html
http://www.geo-solutions.it/blog/dynamic-wms-styling-with-geoserver-sld-and-library-mode/

However my scenario is a bit different, I guess I’d need to implement
this by a CQL expression or something like that.

Is it possible to use GeoServer with SLD to generate tiles in this
scenario? Would it be reasonably fast or would the query in the SLD
really slow down things?

The other option would be to generate the tiles programmatically with
geotools, but I’d like to avoid reinventing the wheel.

Thanks for any input,

Paolo

–
Paolo Crosato
Software engineer/Custom Solutions
e-mail: paolo.crosato@anonymised.com.

Learn Graph Databases - Download FREE O’Reilly Book
“Graph Databases” is the definitive new guide to graph databases and their
applications. Written by three acclaimed leaders in the field,
this first edition is now available. Download your free book today!
http://p.sf.net/sfu/NeoTech

Geoserver-users mailing list
Geoserver-users@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

3

Hi,

thank you very much for the suggestion, I had forgotten about sqlviews. This should work indeed.

Do you think performances would still be ok or could there be any major performance hit with sqlviews?

I only need to render a single polygon layer, with a border and a half transparent background color, however the geometry set
is pretty big, around 300k features.

Paolo

Il 16/04/2014 18:06, Mauro Bartolomeoli ha scritto:

Hi Paolo,
I think you can get what you need using parametric sqlviews.
Shortly you can define a sqlview, that is simply a sql query, directly in Geoserver to join the geometry table with COLOR_TABLE on ID_POLY. This sqlview can have a where filter on ID_USER, where the actual value of the user is given on the wms getmap request with the viewparams param.

The sqlview would be something like:

SELECT POLYGON_TABLE.ID_POLY,POLYGON_TABLE.GEOM,COLOR_TABLE.ID_USER,COLOR_TABLE.COLOR
FROM POLYGON_TABLE INNER JOIN COLOR_TABLE ON POLYGON_TABLE.ID_POLY = COLOR_TABLE.ID_POLY
WHERE ID_USER = %user%

and the viewparams param something like:

viewparams=user:1

Then you will use the COLOR attribute to get dynamic coloring in your SLD, with something like:

<cssParameter name="fill"><ogc:PropertyName>COLOR</ogc:PropertyName></cssParameter>

In depth documentation on sqlviews can be found here: http://docs.geoserver.org/stable/en/user/data/database/sqlview.html

Mauro

2014-04-16 17:17 GMT+02:00 Paolo Crosato <paolo.crosato@anonymised.com <mailto:paolo.crosato@anonymised.com>>:

    Hi all,

    I have to implement the following scenario, with postgis as the
    backend db.

    One table holds the geometries of my polygons (several thousands of
    them), something like

    POLYGON_TABLE

    ID_POLY | GEOM

    I must generate tiles from these geometries, choosing the background
    color of the polygons from another table.
    The problem is that each polygon can be differently painted, according
    to the user requesting it. So the colors are dynamic and
    persisted in another table:

    COLOR_TABLE

    ID_POLY | ID_USER | COLOR

    The background color of the same polygon could be yellow or green,
    according to the user requesting it.

    I read some guides about choosing a color according to a fixed
    column on
    the same table of the geometry, or dinamically provide the sld
    in the request:

    http://osgeo-org.1560.x6.nabble.com/Random-colors-in-SLD-templates-td3790073.html
    http://www.geo-solutions.it/blog/dynamic-wms-styling-with-geoserver-sld-and-library-mode/

    However my scenario is a bit different, I guess I'd need to implement
    this by a CQL expression or something like that.

    Is it possible to use GeoServer with SLD to generate tiles in this
    scenario? Would it be reasonably fast or would the query in the SLD
    really slow down things?

    The other option would be to generate the tiles programmatically with
    geotools, but I'd like to avoid reinventing the wheel.

    Thanks for any input,

    Paolo

    --
    Paolo Crosato
    Software engineer/Custom Solutions
    e-mail: paolo.crosato@anonymised.com
    <mailto:paolo.crosato@anonymised.com>

    ------------------------------------------------------------------------------
    Learn Graph Databases - Download FREE O'Reilly Book
    "Graph Databases" is the definitive new guide to graph databases
    and their
    applications. Written by three acclaimed leaders in the field,
    this first edition is now available. Download your free book today!
    http://p.sf.net/sfu/NeoTech
    _______________________________________________
    Geoserver-users mailing list
    Geoserver-users@lists.sourceforge.net
    <mailto:Geoserver-users@lists.sourceforge.net>
    https://lists.sourceforge.net/lists/listinfo/geoserver-users

--

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

Dott. Mauro Bartolomeoli
@mauro_bart
Senior Software Engineer

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

-------------------------------------------------------

--
Paolo Crosato
Software engineer/Custom Solutions
e-mail: paolo.crosato@anonymised.com
Office phone: +3904221722825

UBIEST S.p.A.
........................................................................................
www.ubiest.com
Via E. Reginato, 85/H - 31100 Treviso- ITALY Tel [+39] 0422 210 194 - Fax [+39] 0422 210 270 ........................................................................................
This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.

4

Hi Paolo,
I think it mostly depends on indexing correctly the columns on POLYGON_TABLE and COLOR_TABLE. If the join id is indexed on both tables performance should be good enough.

Mauro

···

2014-04-17 9:15 GMT+02:00 Paolo Crosato <paolo.crosato@anonymised.com0…>:

Hi,

thank you very much for the suggestion, I had forgotten about sqlviews. This should work indeed.

Do you think performances would still be ok or could there be any major performance hit with sqlviews?

I only need to render a single polygon layer, with a border and a half transparent background color, however the geometry set
is pretty big, around 300k features.

Paolo

Il 16/04/2014 18:06, Mauro Bartolomeoli ha scritto:

Hi Paolo,
I think you can get what you need using parametric sqlviews.
Shortly you can define a sqlview, that is simply a sql query, directly in Geoserver to join the geometry table with COLOR_TABLE on ID_POLY. This sqlview can have a where filter on ID_USER, where the actual value of the user is given on the wms getmap request with the viewparams param.

The sqlview would be something like:

SELECT POLYGON_TABLE.ID_POLY,POLYGON_TABLE.GEOM,COLOR_TABLE.ID_USER,COLOR_TABLE.COLOR
FROM POLYGON_TABLE INNER JOIN COLOR_TABLE ON POLYGON_TABLE.ID_POLY = COLOR_TABLE.ID_POLY
WHERE ID_USER = %user%

and the viewparams param something like:

viewparams=user:1

Then you will use the COLOR attribute to get dynamic coloring in your SLD, with something like:

ogc:PropertyNameCOLOR</ogc:PropertyName>

In depth documentation on sqlviews can be found here: http://docs.geoserver.org/stable/en/user/data/database/sqlview.html

Mauro

–

==
Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

Dott. Mauro Bartolomeoli
@mauro_bart
Senior Software Engineer

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272

http://www.geo-solutions.it
http://twitter.com/geosolutions_it


-- 
Paolo Crosato
Software engineer/Custom Solutions
e-mail: [paolo.crosato@anonymised.com](mailto:paolo.crosato@anonymised.com)

Office phone: +3904221722825

UBIEST S.p.A.
........................................................................................
[www.ubiest.com](http://www.ubiest.com)
Via E. Reginato, 85/H - 31100 Treviso- ITALY Tel [+39] 0422 210 194 - Fax [+39] 0422 210 270 ........................................................................................
This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.

–

==
Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

Dott. Mauro Bartolomeoli
@mauro_bart
Senior Software Engineer

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

2014-04-16 17:17 GMT+02:00 Paolo Crosato <paolo.crosato@anonymised.com>:

Hi all,

I have to implement the following scenario, with postgis as the backend db.

One table holds the geometries of my polygons (several thousands of
them), something like

POLYGON_TABLE

ID_POLY | GEOM

I must generate tiles from these geometries, choosing the background
color of the polygons from another table.
The problem is that each polygon can be differently painted, according
to the user requesting it. So the colors are dynamic and
persisted in another table:

COLOR_TABLE

ID_POLY | ID_USER | COLOR

The background color of the same polygon could be yellow or green,
according to the user requesting it.

I read some guides about choosing a color according to a fixed column on
the same table of the geometry, or dinamically provide the sld
in the request:

http://osgeo-org.1560.x6.nabble.com/Random-colors-in-SLD-templates-td3790073.html
http://www.geo-solutions.it/blog/dynamic-wms-styling-with-geoserver-sld-and-library-mode/

However my scenario is a bit different, I guess I’d need to implement
this by a CQL expression or something like that.

Is it possible to use GeoServer with SLD to generate tiles in this
scenario? Would it be reasonably fast or would the query in the SLD
really slow down things?

The other option would be to generate the tiles programmatically with
geotools, but I’d like to avoid reinventing the wheel.

Thanks for any input,

Paolo

–
Paolo Crosato
Software engineer/Custom Solutions
e-mail: paolo.crosato@anonymised.com

Learn Graph Databases - Download FREE O’Reilly Book
“Graph Databases” is the definitive new guide to graph databases and their
applications. Written by three acclaimed leaders in the field,
this first edition is now available. Download your free book today!
http://p.sf.net/sfu/NeoTech

Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users