I have a Java application that includes an OpenLayers map with WMS tiles pulled from Geoserver. Geoserver and the Java app are looking at the same MySQL database. In the Java application I have a complex filtering system to select data and I would like only the selected data to be shown on the map.

I have tried to do this by passing a CQL filter of the form "ID in (123,3000,6001,5464, etc...)" unfortunately Geoserver 2.3 takes around 12 seconds to render each tile as the list of ID's is so long and I can expect this to get worse as more data is added.

I cannot pass through the SQL query that my Java app is using as it is a complex join that pulls in data from tables not available to geoserver. Also there are security issues as I do not want to expose anything more than the geometry and the feature ID to the public internet via geoserver. Is there a better way of passing the ID's across that is faster?

  • I'm pretty sure filtering by ID should not cause such a slow-down if your ID field is indexed, even with thousands of IDs. Are you sure the problem is not in rendering the features themselves? Also beware very long queries. Eventually your DBMS will refuse to process the query that GeoServer gives it (1 MB default max in MySQL) – tomfumb Jan 6 '14 at 18:03
  • I thought that as well, but my benchmarking shows otherwise. Without a CQL filter tiles take an average of 0.52s to render 3062 points. With a CQL filter with all ID's included in the list takes an average of 8.8s to render the exact same 3062 points. Adding or removing indexes on the table made no noticeable difference to the speed. – Ralph Jan 7 '14 at 9:35
  • You should check the generated SQL query, it can be done by setting the logging level to "geotools developer logging". – Andrea Aime Apr 8 '15 at 16:00

For the moment I have solved this via a bodge. The Java side of the application that is generating the CQL knows how many ID's are in the table in total so if the number of ID's selected by the user are less than half the total in the table a CQL 'IN ()' query is generated as normal.

If the number of ID's selected by the user is more than half the total, the CQL query is inverted to a 'NOT IN ()' query, with the comma separated list populated with the ID's I don't want instead. This has halved the number of items in my CQL query down to about ~1500 points. This takes 2.3s on average to render instead of 8.8s so is a temporary fix.

Update: Brief benchmarking of my application gave the following rendering times for different length CQL filters. Note that using 'NOT IN ()' is slower than using IN. I hope this helps others with similar application designs.

Graph of tile rendering time by CQL length and type

| improve this answer | |

Mayba a solution can be to use FEATUREID instead of CQL_FILTER, if you are really only using the IDs: http://docs.geoserver.org/latest/en/user/services/wms/vendor.html#featureid

This seems to be much faster!

See my findings: http://osgeo-org.1560.x6.nabble.com/GeoServer-slowing-down-with-bigger-CQL-filters-td5356244.html

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.