[QGIS-Developer] [Qgis-user] Bug with GeoPackage and Expressions?

Tue Aug 24 09:22:43 PDT 2021

Hi Calvin,

This definitely sounds like the sort of thing you want to be doing in 
the data provider and not in QGIS!

Maybe Fast Text Search would be of use. Problem is that I'm not sure if 
it's included in the GeoPackage spec or QGIS' SQLite install, but it's 
in the default pre-compiled SQLite. https://www.sqlite.org/fts5.html

Using that for textual searches and more conventional Operators for 
numerical should get you the speed of SQLite with the flexibility you 
desire.

Cheers,

Jonathan

On 29/07/2021 14:23, C Hamilton wrote:
> Ultimately what I am trying to do is to revamp the Search Layers 
> plugin (https://plugins.qgis.org/plugins/searchlayers/) to allow 
> searching using two different strings with NOT, AND, OR operators, 
> case dependent or independent, with qualifiers where the strings are 
> an exact match, within the string, begins with, and ends with. The 
> plugin allows for searching across all layers and all attributes, or 
> optionally a particular layer and attribute. The two strings may be 
> required to match within each attribute or match in any of the 
> attributes within the feature. I am trying to make it very flexible 
> and easy to use. I know that QGIS has the Locator interface, but the 
> users like the extra interactivity that Search Layers provides.
>
> Here is the issue: For some of the searches I am creating a regular 
> expression that matches the search parameters and iterating through 
> each feature and then each attribute using *re.search* to do the 
> comparison. Unfortunately, this is very slow. On a 2.2 million data 
> set it can take 5-10 minutes to do the search. This is way too slow. 
> The SQL search is less than 5 seconds. I am using a *QgsFeatureRequest 
> *and then setting *request.setFilterExpression*. I am only using this 
> when searching on a single layer and single attribute and I don't 
> think there is the flexibility that there is with the regular 
> expressions, but I don't really use SQL and don't know what is 
> possible. It sounds like the SQL searching may be limited by the 
> vector file type source.
>
> Are there any other ways for searching that I am unaware of? The 
> results of the search need to identify the feature in the layer and 
> what attribute or attributes matched for each of the two strings and 
> then I can apply the NOT, AND, OR operators. I am trying to 
> satisfy user's requests to make Search Layers into what they want it 
> to be and am finding it difficult to satisfy speed, vector file types, 
> and flexibility. I welcome any suggestions. Ultimately I might just 
> have to constrain the file types supported and say to convert the data 
> first.
>
> Thanks,
>
> Calvin
>
> On Thu, Jul 29, 2021 at 1:42 AM Nyall Dawson <nyall.dawson at gmail.com 
> <mailto:nyall.dawson at gmail.com>> wrote:
>
>     On Thu, 29 Jul 2021 at 15:31, Matthias Kuhn <matthias at opengis.ch
>     <mailto:matthias at opengis.ch>> wrote:
>     >
>     > Can you fill a bug for this? The expression compiler in QGIS
>     should not forward it to ogr/gpkg/sqlite in this case.
>
>     My interpretation is that Calvin is setting a layer filter, so QGIS
>     expressions aren't involved (i.e. no bug).
>
>     Nyall
>
>     >
>     > Matthias
>     >
>     > On Wed, Jul 28, 2021 at 10:08 PM C Hamilton
>     <adenaculture at gmail.com <mailto:adenaculture at gmail.com>> wrote:
>     >>
>     >> Thanks everyone for that explanation. This does complicate the
>     ability to set an expression filter in a plugin when it depends on
>     the data source type. I'm not sure what to do about that.
>     >>
>     >> Regards,
>     >> Calvin
>     >>
>     >> On Wed, Jul 28, 2021 at 8:00 PM Anita Graser
>     <anitagraser at gmx.at <mailto:anitagraser at gmx.at>> wrote:
>     >>>
>     >>> Hi Calvin,
>     >>>
>     >>> 28 Jul 2021 21:43:40 C Hamilton <adenaculture at gmail.com
>     <mailto:adenaculture at gmail.com>>:
>     >>> > If I use LIKE instead of ILIKE then it works. It is like
>     ILIKE was not implemented for GeoPackage. Can anyone verify this?
>     >>>
>     >>> Yes, there seems to be no iLike in sqlite:
>     >>>
>     >>>
>     https://stackoverflow.com/questions/15480319/case-sensitive-and-insensitive-like-in-sqlite
>     >>>
>     >>> And therefore neither in geopackage.
>     >>>
>     >>> Regards
>     >>> Anita
>     >>
>     >> _______________________________________________
>     >> QGIS-Developer mailing list
>     >> QGIS-Developer at lists.osgeo.org
>     <mailto:QGIS-Developer at lists.osgeo.org>
>     >> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>     >> Unsubscribe:
>     https://lists.osgeo.org/mailman/listinfo/qgis-developer
>     >
>     > _______________________________________________
>     > QGIS-Developer mailing list
>     > QGIS-Developer at lists.osgeo.org
>     <mailto:QGIS-Developer at lists.osgeo.org>
>     > List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>     > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20210824/b236c7a6/attachment.html>