There is another impact when using toLowerCase(myAttribute) LIKE 'blah%'
The use of a toLowerCase or toUpperCase on an sql column will prevent using sql indexes. The result will always be a table scan which hurts on large tables. I think this fact should be considered too. christian Andrea Aime writes: > Mauricio Pazos wrote: >> On Monday 23 February 2009 12:45:19 Nick Griffiths (JIRA) wrote: >>> There doesn't seem to be a way to do case-insensitive filtering through the >>> CQL/Filtering api, so I'm guessing the LIKE should be changed to ILIKE to >>> make the Postgis driver work like all the others. Not sure what CQL says >>> about case-sensitivity... >> I have a look in the specification but I did not find any thing about case- >> sensitivity in like statement. The general idea is that the CQL is a SQL >> like, >> so the semantic of like statement should be case-sensitive. >> >> Maybe we could add the ILIKE statement (under ECQL umbrella), thus: >> - LIKE generates (or builds) PropertyIsLike >> - ILIKE generates (or builds) ???? (org.opengis.filter has not a filter for >> ILIKE). > > To support case insensitive comparisons and like despite of the native > datastore (lack of) support for it I created two filter functions that > allow to lowercase/uppercase an expression. So you can make a case > insensitive like this way (with ECQL, not with CQL): > > toLowerCase(myAttribute) LIKE 'blah%' > > However, mind that those functions are not supported at all by the > sql encoders, so the filtering will be performed completely in memory. > > Another way is to go beyond the OGC filter specificatino and add > proper support for case insitive like comparisons, but that would > require some heavy lifting, we'd have to create a Geotools specific > PropertyIsLike interface that has a boolean isMatchingCase() method. > And then we'd have to modify the xml parsers to handle that extra, > out of spec attribute, provide an implementation that can work in > memory, and change the sql encoders to recognize it and make a best > effort to make a case insensitive comparison. > > You can open a "new feature" request on jira.codehaus.org, and > we can try to guide you in making the necessary changes to the codebase, > and eventually review and apply your patch. > > Cheers > Andrea > > ------------------------------------------------------------------------------ > Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA > -OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise > -Strategies to boost innovation and cut costs with open source participation > -Receive a $600 discount off the registration fee with the source code: SFAD > http://p.sf.net/sfu/XcvMzF8H > _______________________________________________ > Geotools-devel mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/geotools-devel ------------------------------------------------------------------------------ Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA -OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise -Strategies to boost innovation and cut costs with open source participation -Receive a $600 discount off the registration fee with the source code: SFAD http://p.sf.net/sfu/XcvMzF8H _______________________________________________ Geotools-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/geotools-devel
