I am querying a WFS housed in geoserver - the data itself lives in a postgresql database. I would like to run case insensitive queries against the WFS and see there is an option to specify strToLowerCase on the field I am querying, but it doesn't appear to take advantage of the lowercase index I have created in postgresql. The table I am querying has several million records, and when using the strToLowerCase I run into memory issues. Is there a way I can do a case insensitive query on the data in the WFS and take advantage of the lowercase index that exists?


Looking at the GeoTools source code (FilterTOSqlHelper) it looks like your strToLower should be converted into a postgresql lower function and passed to the database.

So, I would turn the logging level upto GeoTools Debug and check that the SQL query really does have lower in it (if not raise a bug). Then paste that into pgadmin and do an explain on it to check if the index is being used.

You might also try the tip I give in this blog post to speed up case insensitive like queries.

  • Thanks for your reply. I reviewed the log and strangely in the query the clause where strToLowerCase is applied doesn't show up. But I can see from the results that it worked. I just don't see any corresponding SQL for that part of the query.
    – kflaw
    Aug 17 '18 at 3:19
  • I should add that though it returned the expected results, it was very slow.
    – kflaw
    Aug 17 '18 at 3:48
  • was just reading this docs.geoserver.org/stable/en/user/filter/… which says that most functions aren't understood by data stores and the expressions are evaluated in memory
    – kflaw
    Aug 17 '18 at 16:08
  • 2
    Unless you are using a very recent version of GeoServer, the datastore does won't encode functions unless instructed to. Check your store configuration and see if the function encoding flag is enabled. Aug 19 '18 at 19:32
  • 1
    You are going beyond the limits of what stackexchange support can offer. Please hop on the geoserver-users list instead, more people can help and we can have a discussion, here the format is just 1 question, 1 answer. Aug 21 '18 at 12:14

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.