Re: [Geoserver-users] GeoServer slowing down with bigger CQL filters
Hello Andrea! Thanks for your answer! We will also try a newer GeoServer in future. Just some measures with our postgres-DB containing around 100.000 entries in the table, filtering 1800 of it via IDs and GeoServer 2.7.1.1: Request without filter: ~150ms Request with a CQL Filter: ~1500ms Same request with FeatureID: ~210ms Best Regards, Bernhard Von: andrea.a...@gmail.com [mailto:andrea.a...@gmail.com] Im Auftrag von Andrea Aime Gesendet: Montag, 05. März 2018 10:11 An: Bernhard Kiselka Cc: Ian Turton; geoserver-users@lists.sourceforge.net Betreff: Re: [Geoserver-users] GeoServer slowing down with bigger CQL filters On Mon, Mar 5, 2018 at 9:52 AM, Bernhard Kiselka <bernhard.kise...@prisma-solutions.at<mailto:bernhard.kise...@prisma-solutions.at>> wrote: Hi Ian! Thanks for you answer! I understand that parsing 20KB takes some time. But why does in slow down exponentically (see digram in https://gis.stackexchange.com/a/82305 )? Could be a result of the algorithm doing the parsing, or the in memory representation building, or the filter simplification in the middle, hard to say. For sure none of the filter management in GeoTools was built with such a large filter in mind. However, it's open source, so you can propose an improvement, or use commercial support to have someone else to do it for you. I might be able to change the filter - but in fact this is a "simplification" for needing only the ID in the GeoServer layer (and not all attributes the filter relys on initially). I also had a look at the source code of GeoTools (guess https://github.com/geotools/geotools/blob/master/modules/library/main/src/main/java/org/geotools/filter/visitor/SimplifyingFilterVisitor.java is right), but I don't understand it completely. That simplifies filters post parsing, it's one of the things that might be scaling badly. Most of all I don't understand why GeoTools needs to parse a IN statement (and translates it to multiple ORs). Because the OGC standard filter model has a fixed list of filters, and "IN" is not one of them, pretty simple. In the most recent versions of GeoServer there is also a "in" function that can be encoded in SQL (in older version there is, but it is not recognized by the SQL encoder as thus it's executed in memory instead of being turned into SQL). 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 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. -- 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/
Re: [Geoserver-users] GeoServer slowing down with bigger CQL filters
Hi Ian! Thanks for you answer! I understand that parsing 20KB takes some time. But why does in slow down exponentically (see digram in https://gis.stackexchange.com/a/82305 )? I might be able to change the filter - but in fact this is a "simplification" for needing only the ID in the GeoServer layer (and not all attributes the filter relys on initially). I also had a look at the source code of GeoTools (guess https://github.com/geotools/geotools/blob/master/modules/library/main/src/main/java/org/geotools/filter/visitor/SimplifyingFilterVisitor.java is right), but I don't understand it completely. Most of all I don't understand why GeoTools needs to parse a IN statement (and translates it to multiple ORs). The only reason I can think of is to disable SQL injections. But again it should not slow down the request time exponentially. And interestingly if I don't use CQL_FILTER=id IN (1,2,…) but =fid1,fid2,… GeoServer scales much better! Conclusion: I might find an easier way for my filter. But a exponential speed reduction seems like a bug to me. As a workaround I will try to use FEATUREID instead of CQL_FILTER! Here is a publically available example (I must admit I don't know if the Medford data is stored on a database): 2 Buildings: https://demo.boundlessgeo.com/geoserver/medford/ows?service=WFS=1.0.0=GetFeature=medford:buildings_FILTER=key%20IN%20(18086%2C%2018087) takes 360ms https://demo.boundlessgeo.com/geoserver/medford/ows?service=WFS=1.0.0=GetFeature=medford:buildings=buildings.18086,buildings.18087 takes 460ms 20 Buildings https://demo.boundlessgeo.com/geoserver/medford/ows?service=WFS=1.0.0=GetFeature=medford:buildings_FILTER=key%20IN%20(1%2C2%2C3%2C4%2C5%2C6%2C7%2C8%2C9%2C10%2C11%2C12%2C13%2C14%2C15%2C16%2C17%2C18%2C19%2C20) takes approx. 800ms with CQL_FILTER and takes 480ms with FEATUREID (2+ time, first is ~700ms) 40 Buildings takes 1270ms with CQL_FILTER and 480ms with FEATUREID 230 Buildings: takes approx. 8000ms with CQL_FILTER Of course you need to use POST for more IDs… where as https://demo.boundlessgeo.com/geoserver/medford/ows?service=WFS=1.0.0=GetFeature=medford:buildings=230 takes only 250ms Best regards, Bernhard Von: Ian Turton [mailto:ijtur...@gmail.com] Gesendet: Donnerstag, 01. März 2018 16:14 An: Bernhard Kiselka Cc: geoserver-users@lists.sourceforge.net<mailto:geoserver-users@lists.sourceforge.net> Betreff: Re: [Geoserver-users] GeoServer slowing down with bigger CQL filters Parsing a CQL filter of that size is bound to take a while and then it has to be translated into an SQL statement which will also take a while. I suspect there must be an easier way of doing what ever you are doing with out the need for a 20Kb filter. Ian On 1 March 2018 at 13:14, Bernhard Kiselka <bernhard.kise...@prisma-solutions.at<mailto:bernhard.kise...@prisma-solutions.at>> wrote: Hi list! I did not do so much research as in https://gis.stackexchange.com/a/82305 but as my data increased, my CQL_FILTER containing IDs that shall be rendered increased from 600 to 1800 (20 kilobyte of filter string!) and GeoServer response time increased from 1 second to 5 seconds. Does someone has any guesses why GeoServer is slowing down exponentially? I assume that parsing the filter is slow, because the postgres database query is _not_ slowing down that much (the query itself takes less than 100ms). Thanks for any thoughts! Best regards, Bernhard -- 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<mailto:Geoserver-users@lists.sourceforge.net> https://lists.sourceforge.net/lists/listinfo/geoserver-users -- Ian Turton -- 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
[Geoserver-users] GeoServer slowing down with bigger CQL filters
Hi list! I did not do so much research as in https://gis.stackexchange.com/a/82305 but as my data increased, my CQL_FILTER containing IDs that shall be rendered increased from 600 to 1800 (20 kilobyte of filter string!) and GeoServer response time increased from 1 second to 5 seconds. Does someone has any guesses why GeoServer is slowing down exponentially? I assume that parsing the filter is slow, because the postgres database query is _not_ slowing down that much (the query itself takes less than 100ms). Thanks for any thoughts! Best regards, Bernhard -- 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
Re: [Geoserver-users] Possible bug rendering postgis data?
Hi! Thanks dracic and Andrea for all hints, I finally solved the problem. It was the PostGIS version 2.1.7 that caused the error. Obviously in this version the simplification collapsed small lines to points; https://trac.osgeo.org/postgis/ticket/2093#comment:32 might be the bugfix. After updating to 2.2.2 all works fine. Cheers, Bernhard -Ursprüngliche Nachricht- Von: Bernhard Kiselka Gesendet: Donnerstag, 29. Oktober 2015 16:29 An: geoserver-users@lists.sourceforge.net Betreff: Possible bug rendering postgis data? Dear list! I have some lines imported to postgres by osmosis and rendering the data with the default line style as wms in geoserver 2.7.1.1 fails for scales more zoomed out than 1:70K (though working zoomed more in), failing with the following message: org.geotools.data.DataSourceException: An exception occurred while parsing WKB data An exception occurred while parsing WKB data Invalid number of points in LineString (found 1 - must be 0 or = 2) WFS shows all data (just 3502 geometries) though. I had a look at the PostgreSQL 9.4.5 database with postgis 2.1.7: ST_IsValid returns true for all linestrings; also is the number of points for all lines >= 2 and the minimum length of all lines is 1.04m -- 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 Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
[Geoserver-users] Possible bug rendering postgis data?
Dear list! I have some lines imported to postgres by osmosis and rendering the data with the default line style as wms in geoserver 2.7.1.1 fails for scales more zoomed out than 1:70K (though working zoomed more in), failing with the following message: org.geotools.data.DataSourceException: An exception occurred while parsing WKB data An exception occurred while parsing WKB data Invalid number of points in LineString (found 1 - must be 0 or = 2) WFS shows all data (just 3502 geometries) though. I had a look at the PostgreSQL 9.4.5 database with postgis 2.1.7: ST_IsValid returns true for all linestrings; also is the number of points for all lines >= 2 and the minimum length of all lines is 1.04m That's the detail stacktrace: 2015-10-29 16:03:25,201 ERROR [ows] (http-0.0.0.0-8080-1) : org.geoserver.platform.ServiceException: Rendering process failed at org.geoserver.wms.map.RenderedImageMapOutputFormat.produceMap(RenderedImageMapOutputFormat.java:541) [:2.7.1.1] at org.geoserver.wms.map.RenderedImageMapOutputFormat.produceMap(RenderedImageMapOutputFormat.java:258) [:2.7.1.1] at org.geoserver.wms.map.RenderedImageMapOutputFormat.produceMap(RenderedImageMapOutputFormat.java:130) [:2.7.1.1] at org.geoserver.wms.GetMap.executeInternal(GetMap.java:504) [:2.7.1.1] at org.geoserver.wms.GetMap.run(GetMap.java:248) [:2.7.1.1] at org.geoserver.wms.GetMap.run(GetMap.java:119) [:2.7.1.1] at org.geoserver.wms.DefaultWebMapService.getMap(DefaultWebMapService.java:369) [:2.7.1.1] at sun.reflect.GeneratedMethodAccessor604.invoke(Unknown Source) [:1.7.0_80] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [:1.7.0_80] at java.lang.reflect.Method.invoke(Method.java:606) [:1.7.0_80] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:319) [:3.1.4.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) [:3.1.4.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) [:3.1.4.RELEASE] at org.geoserver.kml.WebMapServiceKmlInterceptor.invoke(WebMapServiceKmlInterceptor.java:34) [:2.7.1.1] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) [:3.1.4.RELEASE] at org.geoserver.gwc.wms.CacheSeedingWebMapService.invoke(CacheSeedingWebMapService.java:62) [:2.7.1.1] at org.geoserver.gwc.wms.CacheSeedingWebMapService.invoke(CacheSeedingWebMapService.java:36) [:2.7.1.1] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) [:3.1.4.RELEASE] at org.geoserver.gwc.wms.CachingWebMapService.invoke(CachingWebMapService.java:74) [:2.7.1.1] at org.geoserver.gwc.wms.CachingWebMapService.invoke(CachingWebMapService.java:55) [:2.7.1.1] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) [:3.1.4.RELEASE] at org.geoserver.ows.util.RequestObjectLogger.invoke(RequestObjectLogger.java:33) [:2.7.1.1] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) [:3.1.4.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) [:3.1.4.RELEASE] at com.sun.proxy.$Proxy274.getMap(Unknown Source) at sun.reflect.GeneratedMethodAccessor618.invoke(Unknown Source) [:1.7.0_80] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [:1.7.0_80] at java.lang.reflect.Method.invoke(Method.java:606) [:1.7.0_80] at org.geoserver.ows.Dispatcher.execute(Dispatcher.java:839) [:2.7.1.1] at org.geoserver.ows.Dispatcher.handleRequestInternal(Dispatcher.java:280) [:2.7.1.1] at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153) [:3.1.4.RELEASE] at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48) [:3.1.4.RELEASE] at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923) [:3.1.4.RELEASE] at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852) [:3.1.4.RELEASE] at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882) [:3.1.4.RELEASE] at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778) [:3.1.4.RELEASE] at javax.servlet.http.HttpServlet.service(HttpServlet.java:734) [:1.0.0.Final] at javax.servlet.http.HttpServlet.service(HttpServlet.java:847) [:1.0.0.Final] at
[Geoserver-users] Changed Axis Ordering with WFS 1.1.0 Filtering using URN-Projections and SRS styles documentation
Hi List! I upgraded from GeoServer 2.1.3 to 2.4.2 and experienced the following problems: 1) Changed Axis Ordering with WFS 1.1.0 Filtering using URN-Projections Worked and works only in GeoServer 2.1.3: Provide coordinates as latitude/longitude (y/x) Example: http://localhost:8080/geoserver/ows?service=wfsversion=1.1.0request=getfeaturetypeName=EDGEfilter=Filter xmlns:ogc=http://www.opengis.net/ogc; xmlns:gml=http://www.opengis.net/gml;BBOXPropertyNameSHAPE/PropertyNamegml:Envelope srsName=urn:x-ogc:def:crs:EPSG:31258gml:lowerCorner167426 524877/gml:lowerCornergml:upperCorner167573 525122/gml:upperCorner/gml:Envelope/BBOX/Filter Works only GeoServer 2.4.2: Provide coordinates as longitude/latitude (x/y) Example: http://localhost:8080/geoserver/ows?service=wfsversion=1.1.0request=getfeaturetypeName=EDGEfilter=Filter xmlns:ogc=http://www.opengis.net/ogc; xmlns:gml=http://www.opengis.net/gml;BBOXPropertyNameSHAPE/PropertyNamegml:Envelope srsName=urn:x-ogc:def:crs:EPSG:31258gml:lowerCorner524877 167426/gml:lowerCornergml:upperCorner525122 167573/gml:upperCorner/gml:Envelope/BBOX/Filter The GeoServer 2.1.3 behaviour is documented here http://docs.geoserver.org/stable/en/user/services/wfs/basics.html#axis-ordering but GeoServer 2.4.2 shows a wrong behaviour. Shall I file a JIRA-Ticket? My solution (I would say a work-around): Works with both GeoServer versions: Provide coordinates as longitude/latitude (x/y), but use srsName EPSG:31258 instead of urn:x-ogc:def:crs:EPSG:31258 http://localhost:8080/geoserver/ows?service=wfsversion=1.1.0request=getfeaturetypeName=EDGEfilter=Filter xmlns:ogc=http://www.opengis.net/ogc; xmlns:gml=http://www.opengis.net/gml;BBOXPropertyNameSHAPE/PropertyNamegml:Envelope srsName=EPSG:31258gml:lowerCorner524877 167426/gml:lowerCornergml:upperCorner525122 167573/gml:upperCorner/gml:Envelope/BBOX/Filter 2) SRS styles not documented (may be linked to 1) The documentation http://docs.geoserver.org/stable/en/user/webadmin/services/WFS.html does not match GeoServer 2.4.2: Documented is (and has been in GeoServer 2.1.3): Normal, XML, URN The list of SRS styles is now in GeoServer 2.4.2: EPSG Code, OGC HTTP URL, OGC Experimental URN, OGC URN, OGC HTTP URI What's the matching? (Shall I file a JIRA-Ticket?) Cheers, Bernhard -- CenturyLink Cloud: The Leader in Enterprise Cloud Services. Learn Why More Businesses Are Choosing CenturyLink Cloud For Critical Workloads, Development Environments Everything In Between. Get a Quote or Start a Free Trial Today. http://pubads.g.doubleclick.net/gampad/clk?id=119420431iu=/4140/ostg.clktrk ___ Geoserver-users mailing list Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users