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

[Mauro B. <mau...@ge...>]

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 <pao...@ta...>:

>  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 <pao...@ta...>:
>
>> 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: pao...@ta...
>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>> 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
>> Geo...@li...
>> 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: pao...@ta...
>
> 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.
>
>


-- 
==
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

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