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.*
------------------------------------------------------------------------------
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
