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 at 3:19
  • I should add that though it returned the expected results, it was very slow. – kflaw Aug 17 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 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. – Andrea Aime Aug 19 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. – Andrea Aime Aug 21 at 12:14

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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