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

2021-08-24 Thread Jonathan Moules 

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 > wrote:


On Thu, 29 Jul 2021 at 15:31, Matthias Kuhn mailto:matth...@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
mailto:adenacult...@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
mailto:anitagra...@gmx.at>> wrote:
>>>
>>> Hi Calvin,
>>>
>>> 28 Jul 2021 21:43:40 C Hamilton mailto:adenacult...@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@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@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-u...@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
___
QGIS-Developer mailing l

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

2021-07-29 Thread C Hamilton 
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  wrote:

> On Thu, 29 Jul 2021 at 15:31, Matthias Kuhn  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 
> 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 
> wrote:
> >>>
> >>> Hi Calvin,
> >>>
> >>> 28 Jul 2021 21:43:40 C Hamilton :
> >>> > 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@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@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@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

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

2021-07-29 Thread Andrea Giudiceandrea 

Il 29/07/2021 07:30, Matthias Kuhn ha scritto:

Can you fill a bug for this? The expression compiler in QGIS should not
forward it to ogr/gpkg/sqlite in this case.

Matthias


The Query Builder documentation [1] states that:

"The filter is made at the data provider (OGR, PostgreSQL, MSSQL…) 
level. So the syntax depends on the data provider (DateTime is for 
instance not supported for the ESRI Shapefile format)"


So I think the reported behaviour is expected.

Regards.

Andrea

[1] 
https://docs.qgis.org/3.16/en/docs/user_manual/working_with_vector/vector_properties.html#query-builder

___
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

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

2021-07-28 Thread Matthias Kuhn 
Thanks for pointing that out.

In this case a sqlite compatible version (I think something like
`lower(NAME) LIKE '%bom%'` should work) is required.

Hope this helps
Matthias

On Thu, Jul 29, 2021 at 7:56 AM Andrea Giudiceandrea 
wrote:

> Il 29/07/2021 07:30, Matthias Kuhn ha scritto:
> > Can you fill a bug for this? The expression compiler in QGIS should not
> > forward it to ogr/gpkg/sqlite in this case.
> >
> > Matthias
>
> The Query Builder documentation [1] states that:
>
> "The filter is made at the data provider (OGR, PostgreSQL, MSSQL…)
> level. So the syntax depends on the data provider (DateTime is for
> instance not supported for the ESRI Shapefile format)"
>
> So I think the reported behaviour is expected.
>
> Regards.
>
> Andrea
>
> [1]
>
> https://docs.qgis.org/3.16/en/docs/user_manual/working_with_vector/vector_properties.html#query-builder
> ___
> QGIS-Developer mailing list
> QGIS-Developer@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@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

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

2021-07-28 Thread Nyall Dawson 
On Thu, 29 Jul 2021 at 15:31, Matthias Kuhn  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  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  wrote:
>>>
>>> Hi Calvin,
>>>
>>> 28 Jul 2021 21:43:40 C Hamilton :
>>> > 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@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@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@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

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

2021-07-28 Thread Matthias Kuhn 
Can you fill a bug for this? The expression compiler in QGIS should not
forward it to ogr/gpkg/sqlite in this case.

Matthias

On Wed, Jul 28, 2021 at 10:08 PM C Hamilton  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  wrote:
>
>> Hi Calvin,
>>
>> 28 Jul 2021 21:43:40 C Hamilton :
>> > 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@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@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

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

2021-07-28 Thread C Hamilton 
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  wrote:

> Hi Calvin,
>
> 28 Jul 2021 21:43:40 C Hamilton :
> > 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@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer