[Geoserver-users] CQL_FILTER with IN

GeoServer GeoServer User
1

Hello,

I'm developing an web application based on OpenLayers 3 and Geoserver 2.9.0. In this application, the user can search in a database containing about 8000 entries of species with their locations. The search result is then displayed in a map with the locations of the found species.

To this end, I first do a database query based on the search parameters (using PHP), which returns a list of species ids. I then forward this list to Geoserver with a WFS request using CQL_FILTER such as

   CQL_FILTER=SpeciesID IN (16,17,18,40,41,...)

Geoserver then returns a map with only these species in the search result, which is fine. However, I run into performance problems when the list of ids becomes too large. For 8000 ids, it takes Geoserver about 2 minutes to draw the map which is way too long. The database query may return anything between 0 and 8000 results.

Is there another way to solve this problem more efficiently or some way I can optimize Geoserver for this? I would be glad for any help.

Sincerely,

Eva

2

Hi Eva,

Which database are you using? There may be an optimization which can be made for your database.

By chance is the data something which could be shared publicly? Another possibility is that the slowness is a bug in a GeoTools datastore implementation. If so, it'd be great to have a bug report.

It may be counterintuitive, but one really, really simple option may be to export your database as a CSV or Shapefile and skip using a database. Some of the fastest queries may become a little slower, but you'd never run into a 2 minute bottleneck.

Cheers,

Jim

On 06/27/2016 08:38 AM, emgerstner wrote:

Hello,

I'm developing an web application based on OpenLayers 3 and Geoserver
2.9.0. In this application, the user can search in a database containing
about 8000 entries of species with their locations. The search result is
then displayed in a map with the locations of the found species.

To this end, I first do a database query based on the search parameters
(using PHP), which returns a list of species ids. I then forward this
list to Geoserver with a WFS request using CQL_FILTER such as

    CQL_FILTER=SpeciesID IN (16,17,18,40,41,...)

Geoserver then returns a map with only these species in the search
result, which is fine. However, I run into performance problems when the
list of ids becomes too large. For 8000 ids, it takes Geoserver about 2
minutes to draw the map which is way too long. The database query may
return anything between 0 and 8000 results.

Is there another way to solve this problem more efficiently or some way
I can optimize Geoserver for this? I would be glad for any help.

Sincerely,

Eva

------------------------------------------------------------------------------
Attend Shape: An AT&T Tech Expo July 15-16. Meet us at AT&T Park in San
Francisco, CA to explore cutting-edge tech and listen to tech luminaries
present their vision of the future. This family event has something for
everyone, including kids. Get more information and register today.
http://sdm.link/attshape
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

3

Hi,

Probably Geoserver itself in not slow but if gets results from the data source slow. Where do you keep your data?

-Jukka Rahkonen-

Lähettäjä: emgerstner
Lähetetty: ‎27.‎6.‎2016 16:05
Vastaanottaja: geoserver-users@lists.sourceforge.net
Aihe: [Geoserver-users] CQL_FILTER with IN

Hello,

I’m developing an web application based on OpenLayers 3 and Geoserver
2.9.0. In this application, the user can search in a database containing
about 8000 entries of species with their locations. The search result is
then displayed in a map with the locations of the found species.

To this end, I first do a database query based on the search parameters
(using PHP), which returns a list of species ids. I then forward this
list to Geoserver with a WFS request using CQL_FILTER such as

CQL_FILTER=SpeciesID IN (16,17,18,40,41,…)

Geoserver then returns a map with only these species in the search
result, which is fine. However, I run into performance problems when the
list of ids becomes too large. For 8000 ids, it takes Geoserver about 2
minutes to draw the map which is way too long. The database query may
return anything between 0 and 8000 results.

Is there another way to solve this problem more efficiently or some way
I can optimize Geoserver for this? I would be glad for any help.

Sincerely,

Eva

Attend Shape: An AT&T Tech Expo July 15-16. Meet us at AT&T Park in San
Francisco, CA to explore cutting-edge tech and listen to tech luminaries
present their vision of the future. This family event has something for
everyone, including kids. Get more information and register today.
http://sdm.link/attshape

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

4

Hi Jukka,

i’m running PostgreSQL (with PostGIS) 9.2.14. From the Geoserver log file, I see that the call is converted into something like

Request: getFeature
service = WFS
version = 1.0.0
baseUrl = http://localhost:8080/geoserver/
query[0]:
filter = [[ SpeciesID = 16 ] OR [ SpeciesID = 17 ] OR [ SpeciesID = 18 ] OR [ SpeciesID = 40 ] OR [ SpeciesID = 41 ] OR …

In the PostgreSQL log, I see the following query

SELECT “SpeciesID”,“Name”,encode(ST_AsEWKB(“geom”),‘base64’) as “geom” FROM “public”.“view_map” WHERE ((“SpeciesID” = 16 AND “SpeciesID” IS NOT NULL ) OR (“SpeciesID” = 17 AND “SpeciesID” IS NOT NULL ) OR (“SpeciesID” = 18 AND “SpeciesID” IS NOT NULL ) OR …

The query itself is quite slow (about 10 sec for 8000 ids), but the main performance loss appears to be with Geoserver. I’m not sure, but this seems to me an inefficient way to handle the CQL_FILTER request.

Sincerely,

Eva

···

Am 27.06.2016 um 15:31 schrieb Rahkonen Jukka (MML):

Hi,

Probably Geoserver itself in not slow but if gets results from the data source slow. Where do you keep your data?

-Jukka Rahkonen-

Lähettäjä: emgerstner
Lähetetty: ‎27.‎6.‎2016 16:05
Vastaanottaja: geoserver-users@lists.sourceforge.net
Aihe: [Geoserver-users] CQL_FILTER with IN

Hello,

I’m developing an web application based on OpenLayers 3 and Geoserver
2.9.0. In this application, the user can search in a database containing
about 8000 entries of species with their locations. The search result is
then displayed in a map with the locations of the found species.

To this end, I first do a database query based on the search parameters
(using PHP), which returns a list of species ids. I then forward this
list to Geoserver with a WFS request using CQL_FILTER such as

CQL_FILTER=SpeciesID IN (16,17,18,40,41,…)

Geoserver then returns a map with only these species in the search
result, which is fine. However, I run into performance problems when the
list of ids becomes too large. For 8000 ids, it takes Geoserver about 2
minutes to draw the map which is way too long. The database query may
return anything between 0 and 8000 results.

Is there another way to solve this problem more efficiently or some way
I can optimize Geoserver for this? I would be glad for any help.

Sincerely,

Eva

Attend Shape: An AT&T Tech Expo July 15-16. Meet us at AT&T Park in San
Francisco, CA to explore cutting-edge tech and listen to tech luminaries
present their vision of the future. This family event has something for
everyone, including kids. Get more information and register today.
http://sdm.link/attshape

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

5

Eva,

I assume you have an index on SpeciesID?

Ian

···

On 27 June 2016 at 15:31, emgerstner <eva-maria.gerstner@anonymised.com> wrote:

Hi Jukka,

i’m running PostgreSQL (with PostGIS) 9.2.14. From the Geoserver log file, I see that the call is converted into something like

Request: getFeature
service = WFS
version = 1.0.0
baseUrl = http://localhost:8080/geoserver/
query[0]:
filter = [[ SpeciesID = 16 ] OR [ SpeciesID = 17 ] OR [ SpeciesID = 18 ] OR [ SpeciesID = 40 ] OR [ SpeciesID = 41 ] OR …

In the PostgreSQL log, I see the following query

SELECT “SpeciesID”,“Name”,encode(ST_AsEWKB(“geom”),‘base64’) as “geom” FROM “public”.“view_map” WHERE ((“SpeciesID” = 16 AND “SpeciesID” IS NOT NULL ) OR (“SpeciesID” = 17 AND “SpeciesID” IS NOT NULL ) OR (“SpeciesID” = 18 AND “SpeciesID” IS NOT NULL ) OR …

The query itself is quite slow (about 10 sec for 8000 ids), but the main performance loss appears to be with Geoserver. I’m not sure, but this seems to me an inefficient way to handle the CQL_FILTER request.

Sincerely,

Eva

Am 27.06.2016 um 15:31 schrieb Rahkonen Jukka (MML):

Hi,

Probably Geoserver itself in not slow but if gets results from the data source slow. Where do you keep your data?

-Jukka Rahkonen-

Lähettäjä: emgerstner
Lähetetty: ‎27.‎6.‎2016 16:05
Vastaanottaja: geoserver-users@lists.sourceforge.net
Aihe: [Geoserver-users] CQL_FILTER with IN

Hello,

I’m developing an web application based on OpenLayers 3 and Geoserver
2.9.0. In this application, the user can search in a database containing
about 8000 entries of species with their locations. The search result is
then displayed in a map with the locations of the found species.

To this end, I first do a database query based on the search parameters
(using PHP), which returns a list of species ids. I then forward this
list to Geoserver with a WFS request using CQL_FILTER such as

CQL_FILTER=SpeciesID IN (16,17,18,40,41,…)

Geoserver then returns a map with only these species in the search
result, which is fine. However, I run into performance problems when the
list of ids becomes too large. For 8000 ids, it takes Geoserver about 2
minutes to draw the map which is way too long. The database query may
return anything between 0 and 8000 results.

Is there another way to solve this problem more efficiently or some way
I can optimize Geoserver for this? I would be glad for any help.

Sincerely,

Eva

Attend Shape: An AT&T Tech Expo July 15-16. Meet us at AT&T Park in San
Francisco, CA to explore cutting-edge tech and listen to tech luminaries
present their vision of the future. This family event has something for
everyone, including kids. Get more information and register today.
http://sdm.link/attshape

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

Attend Shape: An AT&T Tech Expo July 15-16. Meet us at AT&T Park in San
Francisco, CA to explore cutting-edge tech and listen to tech luminaries
present their vision of the future. This family event has something for
everyone, including kids. Get more information and register today.
http://sdm.link/attshape

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

–

Ian Turton

6

On Mon, Jun 27, 2016 at 4:31 PM, emgerstner <
eva-maria.gerstner@anonymised.com> wrote:

   SELECT "SpeciesID","Name",encode(ST_AsEWKB("geom"),'base64') as "geom"
FROM "public"."view_map" WHERE (("SpeciesID" = 16 AND "SpeciesID" IS NOT
NULL ) OR ("SpeciesID" = 17 AND "SpeciesID" IS NOT NULL ) OR ("SpeciesID" =
18 AND "SpeciesID" IS NOT NULL ) OR ...

The query itself is quite slow (about 10 sec for 8000 ids), but the main
performance loss appears to be with Geoserver. I'm not sure, but this seems
to me an inefficient way to handle the CQL_FILTER request.

The not null checks are redundant for sure, but there is no notion of "att
in (list of values)" in OGC filter, so we have nothing else to translate
into (e.g., we have
no way to represent it in our code, we'd have to go beyond the tools that
OGC provided us there, with some risk of becoming non compliant).
What version of GeoServer is that?

Cheers
Andrea

--

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

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

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

7

Hi Jim,

the data is proprietary at the moment, but the application will be launched in a few months.

I think the problem lies with Geoserver's handling of the CQL_FILTER=... IN (...) request. OpenLayers 2 has some client-side filtering, which appears to be faster, but map performance is much poorer than in OpenLayers 3.

i would like to keep the database, since the queries can be quite complex (such as looking for species names, species properties, geographical constraints and so on). Also data needs to be added or edited all the time.

Sincerely,

Eva

Am 27.06.2016 um 15:26 schrieb Jim Hughes:

Hi Eva,

Which database are you using? There may be an optimization which can be
made for your database.

By chance is the data something which could be shared publicly? Another
possibility is that the slowness is a bug in a GeoTools datastore
implementation. If so, it'd be great to have a bug report.

It may be counterintuitive, but one really, really simple option may be
to export your database as a CSV or Shapefile and skip using a
database. Some of the fastest queries may become a little slower, but
you'd never run into a 2 minute bottleneck.

Cheers,

Jim

On 06/27/2016 08:38 AM, emgerstner wrote:

Hello,

I'm developing an web application based on OpenLayers 3 and Geoserver
2.9.0. In this application, the user can search in a database containing
about 8000 entries of species with their locations. The search result is
then displayed in a map with the locations of the found species.

To this end, I first do a database query based on the search parameters
(using PHP), which returns a list of species ids. I then forward this
list to Geoserver with a WFS request using CQL_FILTER such as

     CQL_FILTER=SpeciesID IN (16,17,18,40,41,...)

Geoserver then returns a map with only these species in the search
result, which is fine. However, I run into performance problems when the
list of ids becomes too large. For 8000 ids, it takes Geoserver about 2
minutes to draw the map which is way too long. The database query may
return anything between 0 and 8000 results.

Is there another way to solve this problem more efficiently or some way
I can optimize Geoserver for this? I would be glad for any help.

Sincerely,

Eva

------------------------------------------------------------------------------
Attend Shape: An AT&T Tech Expo July 15-16. Meet us at AT&T Park in San
Francisco, CA to explore cutting-edge tech and listen to tech luminaries
present their vision of the future. This family event has something for
everyone, including kids. Get more information and register today.
http://sdm.link/attshape
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

------------------------------------------------------------------------------
Attend Shape: An AT&T Tech Expo July 15-16. Meet us at AT&T Park in San
Francisco, CA to explore cutting-edge tech and listen to tech luminaries
present their vision of the future. This family event has something for
everyone, including kids. Get more information and register today.
http://sdm.link/attshape
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

8

Hi Ian,

the problem is that I have a view, where i can’t have an index. I could try using a materialized view, but I think the main performance problem lies elsewhere, and I’m not sure if I’m using the right approach here.

Sincerely,

Eva

···

Am 27.06.2016 um 16:37 schrieb Ian Turton:

Eva,

I assume you have an index on SpeciesID?

Ian

On 27 June 2016 at 15:31, emgerstner <eva-maria.gerstner@anonymised.com> wrote:

Hi Jukka,

i’m running PostgreSQL (with PostGIS) 9.2.14. From the Geoserver log file, I see that the call is converted into something like

Request: getFeature
service = WFS
version = 1.0.0
baseUrl = http://localhost:8080/geoserver/
query[0]:
filter = [[ SpeciesID = 16 ] OR [ SpeciesID = 17 ] OR [ SpeciesID = 18 ] OR [ SpeciesID = 40 ] OR [ SpeciesID = 41 ] OR …

In the PostgreSQL log, I see the following query

SELECT “SpeciesID”,“Name”,encode(ST_AsEWKB(“geom”),‘base64’) as “geom” FROM “public”.“view_map” WHERE ((“SpeciesID” = 16 AND “SpeciesID” IS NOT NULL ) OR (“SpeciesID” = 17 AND “SpeciesID” IS NOT NULL ) OR (“SpeciesID” = 18 AND “SpeciesID” IS NOT NULL ) OR …

The query itself is quite slow (about 10 sec for 8000 ids), but the main performance loss appears to be with Geoserver. I’m not sure, but this seems to me an inefficient way to handle the CQL_FILTER request.

Sincerely,

Eva

Am 27.06.2016 um 15:31 schrieb Rahkonen Jukka (MML):

Hi,

Probably Geoserver itself in not slow but if gets results from the data source slow. Where do you keep your data?

-Jukka Rahkonen-

Lähettäjä: emgerstner
Lähetetty: ‎27.‎6.‎2016 16:05
Vastaanottaja: geoserver-users@lists.sourceforge.net
Aihe: [Geoserver-users] CQL_FILTER with IN

Hello,

I’m developing an web application based on OpenLayers 3 and Geoserver
2.9.0. In this application, the user can search in a database containing
about 8000 entries of species with their locations. The search result is
then displayed in a map with the locations of the found species.

To this end, I first do a database query based on the search parameters
(using PHP), which returns a list of species ids. I then forward this
list to Geoserver with a WFS request using CQL_FILTER such as

CQL_FILTER=SpeciesID IN (16,17,18,40,41,…)

Geoserver then returns a map with only these species in the search
result, which is fine. However, I run into performance problems when the
list of ids becomes too large. For 8000 ids, it takes Geoserver about 2
minutes to draw the map which is way too long. The database query may
return anything between 0 and 8000 results.

Is there another way to solve this problem more efficiently or some way
I can optimize Geoserver for this? I would be glad for any help.

Sincerely,

Eva

Attend Shape: An AT&T Tech Expo July 15-16. Meet us at AT&T Park in San
Francisco, CA to explore cutting-edge tech and listen to tech luminaries
present their vision of the future. This family event has something for
everyone, including kids. Get more information and register today.
http://sdm.link/attshape

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

Attend Shape: An AT&T Tech Expo July 15-16. Meet us at AT&T Park in San
Francisco, CA to explore cutting-edge tech and listen to tech luminaries
present their vision of the future. This family event has something for
everyone, including kids. Get more information and register today.
http://sdm.link/attshape

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

–

Ian Turton

9

Hi Andrea,

I’m running Geoserver 2.9.0. If I cannot use “attribute in (list of values)”, is there another way of setting up such a filter which may be more efficient?

Sincerely,

Eva

···

Am 27.06.2016 um 16:41 schrieb Andrea Aime:

On Mon, Jun 27, 2016 at 4:31 PM, emgerstner <eva-maria.gerstner@anonymised.com> wrote:

SELECT “SpeciesID”,“Name”,encode(ST_AsEWKB(“geom”),‘base64’) as “geom” FROM “public”.“view_map” WHERE ((“SpeciesID” = 16 AND “SpeciesID” IS NOT NULL ) OR (“SpeciesID” = 17 AND “SpeciesID” IS NOT NULL ) OR (“SpeciesID” = 18 AND “SpeciesID” IS NOT NULL ) OR …

The query itself is quite slow (about 10 sec for 8000 ids), but the main performance loss appears to be with Geoserver. I’m not sure, but this seems to me an inefficient way to handle the CQL_FILTER request.

The not null checks are redundant for sure, but there is no notion of “att in (list of values)” in OGC filter, so we have nothing else to translate into (e.g., we have
no way to represent it in our code, we’d have to go beyond the tools that OGC provided us there, with some risk of becoming non compliant).
What version of GeoServer is that?

Cheers
Andrea

–

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

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

10

On Mon, Jun 27, 2016 at 4:58 PM, emgerstner <
eva-maria.gerstner@anonymised.com> wrote:

Hi Ian,

the problem is that I have a view, where i can't have an index. I could
try using a materialized view, but I think the main performance problem
lies elsewhere, and I'm not sure if I'm using the right approach here.

If you have a view, I'd try the approach of setting a GeoServer parametric
sql view instead, make the list of speciesId a parameter (with the right
validation expression, mind sql injection attacks) and then pass them via
the view_params parameter.

See here:
http://docs.geoserver.org/stable/en/user/data/database/sqlview.html

Cheers
Andrea

--

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

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

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

11

Dear all,

we have troubles in cascading a WMS layer for the OSM of Italy, it is served as basemap to another server’s layer that is used for generating dynamic PDFs.

The architecture is made on two servers (both on Geoserver 2.6.2):

  • SERVER-A, is the map server to publish our replica of the Italian OSM. It works on top of a PostGIS that is synced periodically with OSM planet and it uses styles for points, polygons and lines.
  • SERVER-B, is the map server we provided our client; it uses the WMS rendered by SERVER-A as base layer for another WMS services used for PDF generation.
    Server A and B are in the same network on Digital Ocean. Moving the access to the data and the rendering to the SERVER-B is not an option for now.

We have two kinds of problems, we do not know whether they are related or not:

  • SERVER-B Tomcat crashes periodically, so far we were not able to find any message in the catalpa.out log. We are not sure it is the right place to check as we are not experts of Tomcat.
  • SERVER-A becomes very slow; when this happens the direct calls to WMS done through the SERVER-B and SERVER-A are still working, but the Geoserver admin panel cannot use or preview the SERVER-A basemap, the exception says that the underlying service does not exists.

We wonder whether there is some specific documentation, or somebody can give hints about tracking the errors.

More, we are sure the servers are under-dimensioned. Is there any specific reference to dimensioning and performance optimization on geoserver? Anybody with a similar experience? In your experience should the server chaining the WMS and overlaying few points to it be dimensioned similar to the server doing the OSM rendering?

Thanks for any help.

Simone

Smart technologies for bright ideas

Simone Gadenz
CEO

Mobile: +39 331 72 35 993
Office: +39 0577 588 408
Web: http://www.beecome.it

12

Hi Andrea,

the parametric sql view did the trick! Thank you very much for your advice,

Eva

···

Am 27.06.2016 um 17:07 schrieb Andrea Aime:

On Mon, Jun 27, 2016 at 4:58 PM, emgerstner <eva-maria.gerstner@anonymised.com> wrote:

Hi Ian,

the problem is that I have a view, where i can’t have an index. I could try using a materialized view, but I think the main performance problem lies elsewhere, and I’m not sure if I’m using the right approach here.

If you have a view, I’d try the approach of setting a GeoServer parametric sql view instead, make the list of speciesId a parameter (with the right
validation expression, mind sql injection attacks) and then pass them via the view_params parameter.

See here:
http://docs.geoserver.org/stable/en/user/data/database/sqlview.html

Cheers
Andrea

–

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

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

13

Hi Andrea,

We’ve hit similar issues in query planning for GeoMesa for queries like this.

If I understand correctly, “attr IN(value1, value2, …)” is part of ECQL. While I know that GeoTools/GeoServer could not depend on OpenLayers to integrate with GeoServer extensions, would there be any possibility to offer tools/library code to ‘collect’ up ORs like this for GeoTools datastores to leverage? It might be a fun activity for a OSGeo code sprint…

Cheers,

Jim

···

On 06/27/2016 10:41 AM, Andrea Aime wrote:

On Mon, Jun 27, 2016 at 4:31 PM, emgerstner <eva-maria.gerstner@anonymised.com> wrote:

SELECT “SpeciesID”,“Name”,encode(ST_AsEWKB(“geom”),‘base64’) as “geom” FROM “public”.“view_map” WHERE ((“SpeciesID” = 16 AND “SpeciesID” IS NOT NULL ) OR (“SpeciesID” = 17 AND “SpeciesID” IS NOT NULL ) OR (“SpeciesID” = 18 AND “SpeciesID” IS NOT NULL ) OR …

The query itself is quite slow (about 10 sec for 8000 ids), but the main performance loss appears to be with Geoserver. I’m not sure, but this seems to me an inefficient way to handle the CQL_FILTER request.

The not null checks are redundant for sure, but there is no notion of “att in (list of values)” in OGC filter, so we have nothing else to translate into (e.g., we have
no way to represent it in our code, we’d have to go beyond the tools that OGC provided us there, with some risk of becoming non compliant).
What version of GeoServer is that?

Cheers
Andrea

–

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

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

------------------------------------------------------------------------------
Attend Shape: An AT&T Tech Expo July 15-16. Meet us at AT&T Park in San
Francisco, CA to explore cutting-edge tech and listen to tech luminaries
present their vision of the future. This family event has something for
everyone, including kids. Get more information and register today.
[http://sdm.link/attshape](http://sdm.link/attshape)

_______________________________________________
Geoserver-users mailing list
[Geoserver-users@lists.sourceforge.net](mailto:Geoserver-users@lists.sourceforge.net)
[https://lists.sourceforge.net/lists/listinfo/geoserver-users](https://lists.sourceforge.net/lists/listinfo/geoserver-users)
14

On Mon, Jun 27, 2016 at 7:06 PM, Jim Hughes <jnh5y@anonymised.com> wrote:

Hi Andrea,

We've hit similar issues in query planning for GeoMesa for queries like
this.

If I understand correctly, "attr IN(value1, value2, ...)" is part of
ECQL. While I know that GeoTools/GeoServer could not depend on OpenLayers
to integrate with GeoServer extensions, would there be any possibility to
offer tools/library code to 'collect' up ORs like this for GeoTools
datastores to leverage? It might be a fun activity for a OSGeo code
sprint...

When it comes to databases, in theory, the following two are 100%
equivalent, and the query planner should figure it out (Oracle did not a
few years ago, but that should be fixed):

ATT in (v1, v2, ...., vn)
ATT = v1 or ATT = v2 or ... ATT = vn

The translation with the not null over properties is a bit strange, I
actually think there was a reason for that but right now it escapes me,
worth investigating.
Collecting ORs is simple enough, already has examples, see
SimplifyingFilterVisitor, no need to wait for a code sprint :slight_smile:

Cheers
Andrea

--

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

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

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

15

Dear all,

we have troubles in cascading a WMS layer for the OSM of Italy, it is served as basemap to another server’s layer that is used for generating dynamic PDFs.

The architecture is made on two servers (both on Geoserver 2.6.2):

  • SERVER-A, is the map server to publish our replica of the Italian OSM. It works on top of a PostGIS that is synced periodically with OSM planet and it uses styles for points, polygons and lines.
  • SERVER-B, is the map server we provided our client; it uses the WMS rendered by SERVER-A as base layer for another WMS services used for PDF generation.
    Server A and B are in the same network on Digital Ocean. Moving the access to the data and the rendering to the SERVER-B is not an option for now.

We have two kinds of problems, we do not know whether they are related or not:

  • SERVER-B Tomcat crashes periodically, so far we were not able to find any message in the catalpa.out log. We are not sure it is the right place to check as we are not experts of Tomcat.
  • SERVER-A becomes very slow; when this happens the direct calls to WMS done through the SERVER-B and SERVER-A are still working, but the Geoserver admin panel cannot use or preview the SERVER-A basemap, the exception says that the underlying service does not exists.

We wonder whether there is some specific documentation, or somebody can give hints about tracking the errors.

More, we are sure the servers are under-dimensioned. Is there any specific reference to dimensioning and performance optimization on geoserver? Anybody with a similar experience? In your experience should the server chaining the WMS and overlaying few points to it be dimensioned similar to the server doing the OSM rendering?

Thanks for any help.

Simone

Smart technologies for bright ideas

Simone Gadenz
CEO

Mobile: +39 331 72 35 993
Office: +39 0577 588 408
Web: http://www.beecome.it