Hi Matteo,
there is a tutorial on writing functions here:
http://docs.geotools.org/latest/userguide/tutorial/function.html
About translating them down in SQL, that gets trickier (hence my "easier
said than done").
If the tablesample declaration can be written anywhere in the WHERE clause
(like any filter)
then this would be the place, there is already support for encoding a few
functions (has to be enabled
via datastore config parameter):
https://github.com/geotools/geotools/blob/master/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/FilterToSqlHelper.java
If instead it has to be at the end of the SELECT, like a group by, order by
and so on, heh... it gets harder.
It would have to be implemented somewhere in here, with dialect support I
believe (the class is shared among all
RDBMS bases stores):
https://github.com/geotools/geotools/blob/master/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStore.java
If this is the case, maybe some of the other avenues I've pointed at might
be easier.
Hope this helps
Cheers
Andrea
On Sun, May 27, 2018 at 10:11 AM, Matteo Cusmai <cusmaimat...@gmail.com>
wrote:
> Ciao Andrea,
> Thanks for the quick answer.
>
> Yes, I known that I can't use sql view parameter from sld, and however, I
> don't like use it for security reason.
>
> So I was thinking about a wps, but I have to check if it is possible.
>
> But the best solution, I think is to implement your second idea, with
> filter function. Where I can see some code example?
>
> On 27 May 2018 09:04, "Andrea Aime" <andrea.a...@geo-solutions.it> wrote:
>
> Hi Matteo,
> I don't think there is a way to do what you want from the SLD, and using
> that function.
>
> In order to run that SQL, you have to setup a SQL View.
> So that "10" could be a parameter in the parametric sql view.
> But SLD cannot specificy those view parameters, only the client in the
> GetMap can.
>
> I'm also not sure how well the optimizer would interact with the actual
> query running the sql view,
> e.g.:
>
> select columns from (<your sql here>) where <filters from request>
>
> I can propose some alternatives.
>
> The first one, add a new column to your table, as an integer, with values
> from one to 10
> (see also https://stackoverflow.com/questions/1400505/
> generate-a-random-number-in-the-range-1-10)
> Then in the SLD, based on the scale, choose which subset of those random
> numbers you want to show
> using a simple filter (e.g., myRandomNumber < 5)... you might want to
> index that column too.
> This approach works fully from SLD, and is also fully repeatable
> (tablesample can be too, there is an
> extra param you have to pass).
>
> The second one, write code for a "tablesample" filter function, that would
> do the work in memory
> for any type of data source and would allow to express the concept in SLD
> via:
>
> <Filter>
> <PropetyIsEqualTo>
> <Function name="tablesample">
> <Literal>10</Literal>
> <Literal>Repeatable</Literal>
> </Function>
> </PropertyIsEqualTo>
> </Filter>
>
> and then have the PostGIS data store recognize it, and rewrite the query
> to use tablesample
> (easier said than done, but I guess feasible).
>
> There are other possible code change based avenues too, like modifying the
> way sql views work and
> allow setting viewparams from SLD, adding a vendor option somewhere i the
> SLD to specify the sampling
> (e.g., at the FeatureTypeStyle level), and likely others I did not think
> about.
>
> For these approaches see also:
> https://github.com/geoserver/geoserver/wiki/Successfully-
> requesting-and-integrating-new-features-and-improvements-in-GeoServer
>
> Cheers
> Andrea
>
>
> On Sat, May 26, 2018 at 6:09 PM, Matteo Cusmai <cusmaimat...@gmail.com>
> wrote:
>
>> Hi all,
>> I have very big layer based on tables with about 10 millions of records.
>> I am trying to define a great sld to limit the number of record to be
>> queried at low zoom level, but there isn't a field with a good enough
>> meaning.
>>
>> I have found a great postgresql feature that was introduced in version
>> 9.5, it is tablesample that give us the ability to retrieve a percentage of
>> records in tables.
>>
>> I would like to use this capabilities to show, for example, the 1% at low
>> zoom level, 10% at medium and 100% at high zoom level.
>>
>> SELECT * FROM big_table TABLESAMPLE SYSTEM(10);
>>
>> I am going mad to figure out how I can use sld with zoom level to set the
>> percentage.
>>
>> Anyone could help me?
>>
>>
>> Matteo Cusmai
>>
>> ------------------------------------------------------------
>> ------------------
>> Check out the vibrant tech community on one of the world's most
>> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
>> _______________________________________________
>> Geoserver-users mailing list
>>
>> Please make sure you read the following two resources before posting to
>> this list:
>> - Earning your support instead of buying it, but Ian Turton:
>> http://www.ianturton.com/talks/foss4g.html#/
>> - The GeoServer user list posting guidelines: http://geoserver.org/comm/
>> userlist-guidelines.html
>>
>> If you want to request a feature or an improvement, also see this:
>> https://github.com/geoserver/geoserver/wiki/Successfully-
>> requesting-and-integrating-new-features-and-improvements-in-GeoServer
>>
>>
>> Geoserver-users@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>>
>>
>
>
> --
>
> Regards, Andrea Aime == GeoServer Professional Services from the experts!
> Visit http://goo.gl/it488V for more information. == Ing. Andrea Aime
> @geowolf Technical Lead GeoSolutions S.A.S. Via di Montramito 3/A 55054
> Massarosa (LU) phone: +39 0584 962313 fax: +39 0584 1660272 mob: +39 339
> 8844549 http://www.geo-solutions.it http://twitter.com/geosolutions_it
> ------------------------------------------------------- *Con riferimento
> alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 -
> Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni
> circostanza inerente alla presente email (il suo contenuto, gli eventuali
> allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i
> destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per
> errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le
> sarei comunque grato se potesse darmene notizia. This email is intended
> only for the person or entity to which it is addressed and may contain
> information that is privileged, confidential or otherwise protected from
> disclosure. We remind that - as provided by European Regulation 2016/679
> “GDPR” - copying, dissemination or use of this e-mail or the information
> herein by anyone other than the intended recipient is prohibited. If you
> have received this email by mistake, please notify us immediately by
> telephone or e-mail.*
>
>
>
--
Regards, Andrea Aime == GeoServer Professional Services from the experts!
Visit http://goo.gl/it488V for more information. == Ing. Andrea Aime
@geowolf Technical Lead GeoSolutions S.A.S. Via di Montramito 3/A 55054
Massarosa (LU) phone: +39 0584 962313 fax: +39 0584 1660272 mob: +39 339
8844549 http://www.geo-solutions.it http://twitter.com/geosolutions_it
------------------------------------------------------- *Con riferimento
alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 -
Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni
circostanza inerente alla presente email (il suo contenuto, gli eventuali
allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i
destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per
errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le
sarei comunque grato se potesse darmene notizia. This email is intended
only for the person or entity to which it is addressed and may contain
information that is privileged, confidential or otherwise protected from
disclosure. We remind that - as provided by European Regulation 2016/679
“GDPR” - copying, dissemination or use of this e-mail or the information
herein by anyone other than the intended recipient is prohibited. If you
have received this email by mistake, please notify us immediately by
telephone or e-mail.*
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Geoserver-users mailing list
Please make sure you read the following two resources before posting to this
list:
- Earning your support instead of buying it, but Ian Turton:
http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines:
http://geoserver.org/comm/userlist-guidelines.html
If you want to request a feature or an improvement, also see this:
https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users