New ILIKE statement - STEMLab/geotools GitHub Wiki

• Motivation: Case insensitive search need to be improved to take advantage of store implementations

• Contact: Mauricio Pazos

• Tracker: https://osgeo-org.atlassian.net/browse/GEOT-2359

• Tagline: improving the search conditions

Nowadays, the CQL module does not implement the ILIKE statement. The CQL specification only talks about the LIKE statement so it is case-sensitive. Moreover, the GeoTool infrastructure (datastore, sql encoders, etc) saves this limitations with the case insensitive functions which allow to lowercase/uppercase an expression.

toLowerCase(myAttribute) LIKE 'blah%'

Those functions are not supported at all by the sql encoders, so the filtering is performed completely in memory. This approach saves the problem and works well for small datasets. Additionally, the use of a toLowerCase or toUpperCase on an sql column will prevent using sql index with the resultant low performance problem.

To take advantages of this new feature some geotools components should be changed.

To begin the impact analysis I copy below some comment taken from our e-mail list.

• extend filter Interface (PropertyIsILike filter).
• extend the interfaces to add matchCase to Like filters,
• change filter parsers,
• sql encoders
• ....

This work has completed, voting results were not recorded:

• Andrea Aime

• Ian Turton

• Justin Deoliveira

• Jody Garnett

• Martin Desruisseaux

• Simone Giannecchini

      | :white_check_mark: | :no_entry: | :warning:               | :negative_squared_cross_mark: |
  

------------|--------------------|------------|-------------------------|-------------------------------| no progress | done | impeded | lack mandate/funds/time | volunteer needed |

1. ✅ PropertyIsLike.isMatchingCase() method available
2. ✅ Update default implementation LikeFilterImpl
3. ✅ Update the CQL or ECQL parser: GEOT-2359
4. ⚠️ Change the SQL Encoders: GEOT-2426
5. ✅ Update the user guide example

Technical debt:

• SQL Encoding for LikeFilter