[gdal-dev] ogr2ogr (I)LIKE not caseINsensitive

[Jan Heckman]

Hi Even, all,
Thanks for the fast and helpful response!
So, if I need this shortly, I should compile a slightly modified version?
I can't see myself giving a true solution to the backward compatibility
issue ;)
My problem here is not large.
The main advantage of caseinsensitive is a compact and accurate query, I
was used to seeing if I could just omit the first (capital or not)
character.
But I ran into a case where this produced unwanted results and cmdline
gave ERROR 1: Destination buffer too small.
So I'll put the less compact SQL in a file and use -sql @file.sql for now.
Thanks,
Jan

On Thu, Nov 28, 2019 at 4:25 PM Even Rouault <even.rouault at spatialys.com>
wrote:

> On jeudi 28 novembre 2019 16:09:28 CET Jan Heckman wrote:
> > Hi everybody,
> > According to docs <https://gdal.org/user/ogr_sql_dialect.html>, LIKE and
> > ILIKE should be caseinsensitive.
> > However, they are not.
> > ogr2ogr plangebieden.shp -t_srs EPSG:28992 -dim 2 -spat 45000 405000
> 140000
> > 485000 WFS:"http://afnemers.ruimtelijkeplannen.nl/afnemers2012/services"
> > Bestemmingsplangebied -lco ENCODING=UTF-8 -where
> > "BeleidsmatigVerantwoordelijkeOverheid LIKE 'Geme%'" produces empty
> output.
> > The clue is that LIKE 'geme%'  (as in the DB 'gemeente'). does produce
> > output.
> > Replacing LIKE with ILIKE makes no difference.
>
> Jan,
>
> That's an interesting case.
>
> OGR SQL indeeds consider LIKE and ILIKE are identical and case insensitive.
>
> But... when issuing a -where to a WFS source, OGR turns the SQL into OGC
> Filter Encoding so that the filter is evaluated by the server for
> performance
> reasons. When doing so it uses the PropertyIsLike predicate, which is case
> sensitive by default. There's a matchCase attribute that could be set to
> false.
>
> So I can see 2 options:
>
> 1) change the WFS translation code to set matchCase=false. Quite easy to
> implement. Some backward compatibility consequence for users of WFS
> sources
> that would have dependended on the current behaviour (case sensitivity)
>
> 2) more work. for WFS, have LIKE and ILIKE having two different
> translations.
> But if we do so, as we would need to add a new SWQ_ILIKE operator, and we
> should also probably make LIKE and ILIKE be distinct operations on a pure
> OGR
> SQL evaluation (client side). Which has some backward compatibility
> implication.
>
> Even
>
> --
> Spatialys - Geospatial professional services
> http://www.spatialys.com
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20191128/4c1068e1/attachment.html>