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

[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?

[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?

[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?

[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

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

[Even Rouault]

SQLite, which is the underneath database engine for GeoPackage, doesn't 
support ILIKE. The SQLite LIKE operator is actually case insensitive for 
ASCII characters. See paragraph 5 "The LIKE, GLOB, REGEXP, and MATCH 
operators" of https://www.sqlite.org/lang_expr.html


Le 28/07/2021 à 21:43, C Hamilton a écrit :
I think I found a bug and was wondering if someone could verify this. 
I am on Windows 10 and tried this with both QGIS 3.16.9 and 3.20.1.


I tried the following filter expression on the Natural Earth data set 
ne_10m_populated_places 
(https://www.naturalearthdata.com/http//www.naturalearthdata.com/download/10m/cultural/ne_10m_populated_places.zip 
). 
Once loaded into QGIS right mouse click on the layer and select 
"Properties...", then click on the "Source" tab, click on "Query 
Builder" and then use this query


"NAME" ILIKE '%Bom%'

This works fine. If I export ne_10m_populated_places as a GeoPackage 
and try the same thing with the GeoPackage version of the layer, I get 
the error:


"An error occurred when executing the query, please check the 
expression syntax."


If I use LIKE instead of ILIKE then it works. It is like ILIKE was not 
implemented for GeoPackage. Can anyone verify this? I think you can 
use any vector layer for this test with one that is a shapefile and 
the other a GeoPackage.


Thanks,

Calvin


___
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


--
http://www.spatialys.com
My software is free, but my time generally not.

___
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] Bug with GeoPackage and Expressions?

[C Hamilton]

I think I found a bug and was wondering if someone could verify this. I am
on Windows 10 and tried this with both QGIS 3.16.9 and 3.20.1.

I tried the following filter expression on the Natural Earth data set
ne_10m_populated_places (
https://www.naturalearthdata.com/http//www.naturalearthdata.com/download/10m/cultural/ne_10m_populated_places.zip).
Once loaded into QGIS right mouse click on the layer and select
"Properties...", then click on the "Source" tab, click on "Query Builder"
and then use this query

"NAME" ILIKE '%Bom%'

This works fine. If I export ne_10m_populated_places as a GeoPackage and
try the same thing with the GeoPackage version of the layer, I get the
error:

"An error occurred when executing the query, please check the expression
syntax."

If I use LIKE instead of ILIKE then it works. It is like ILIKE was not
implemented for GeoPackage. Can anyone verify this? I think you can use any
vector layer for this test with one that is a shapefile and the other a
GeoPackage.

Thanks,

Calvin
___
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] Monitor QGIS (Server) memory consumption per project

[kimaidou]

Hey,

I updated my "audit qgis projects" processing algorithm with a
quick-and-dirty method to calculate the memory usage, before and after each
project reading
https://gist.github.com/mdouchin/cd89a259d3560635d4bb8708ad430caa#file-lizmapauditqgisprojects-py-L150

It is not very reliable and should not be used in a highly changing
environment (production server with high activity).
It could help nonetheless...

Regards,
Michaël

Le mer. 28 juil. 2021 à 12:08, kimaidou  a écrit :

> Hi all,
>
> We (@3liz.com) use QGIS Server since a long time to provide hosting
> services for our clients, with Lizmap Web Client (LWC) as the web map
> builder.
>
> Having hundred LWC instances, we pay obviously attention to metrics, and
> monitor the CPU, memory, requests response time, etc.
>
> We sometimes encounter heavy memory consumption for specific projects, for
> example :
> https://github.com/qgis/QGIS/issues/43787
> which put a lot of strain to the servers.
>
> It is often very hard to find the project(s) responsible for a high memory
> usage, in a production context, when many requests are run in parallel for
> many projects.
>
> I created a POC processing script to audit all the QGIS projects stored in
> a folder:
> https://gist.github.com/mdouchin/cd89a259d3560635d4bb8708ad430caa/
> which helps a bit to gather some key figures, such as the number of
> layers, the sum of the images used in map layouts, etc.
> This can help, but often the memory consumption is not related to the
> number of layers, and we use a "find a needle in the haystack" approach to
> find the problematic project(s).
>
> For example, I spent once a whole day finding that a too big JPEG used in
> a print layout causes the heavy memory usage:
> * look at the memory curve,
> * find the moment when the memory increased a lot
> * use the command line find  to get which QGIS project files has changed
> during this period (for example because it has been uploaded by the user)
> * look inside QGIS logs to find which project has been loaded during this
> period, and compare
> * if you are lucky, there are not so many projects involved...
> * download the project, open it in QGIS Desktop to observe and confirm the
> memory issue
> * try to understand which causes the issue
> * ...
>
> We are also building a REST API over py-qgis-server (
> https://github.com/3liz/py-qgis-server ) which will allow to get
> information on the loaded projects, to ease the auditing process in
> real-time. (It will be opensource).
>
> In the processing script, and in this API tool, we still miss a very
> important metric: how much each project consumes the system memory (RAM). I
> am not sure it could be easily done, but it will be very handy to:
>
> * find heavy memory consumer projects
> * see how the memory evolves
> * compare the memory usage for each QGIS versions (with the same project
> and layers)
> * help to find the possible memory leaks
>
> This message is not a feature request, I would like to get your experience
> and opinion regarding this memory usage issue. Any contribution welcome ;-)
>
> Thanks in advance
> Michaël
>
___
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] Monitor QGIS (Server) memory consumption per project

[kimaidou]

Hi all,

We (@3liz.com) use QGIS Server since a long time to provide hosting
services for our clients, with Lizmap Web Client (LWC) as the web map
builder.

Having hundred LWC instances, we pay obviously attention to metrics, and
monitor the CPU, memory, requests response time, etc.

We sometimes encounter heavy memory consumption for specific projects, for
example :
https://github.com/qgis/QGIS/issues/43787
which put a lot of strain to the servers.

It is often very hard to find the project(s) responsible for a high memory
usage, in a production context, when many requests are run in parallel for
many projects.

I created a POC processing script to audit all the QGIS projects stored in
a folder:
https://gist.github.com/mdouchin/cd89a259d3560635d4bb8708ad430caa/
which helps a bit to gather some key figures, such as the number of layers,
the sum of the images used in map layouts, etc.
This can help, but often the memory consumption is not related to the
number of layers, and we use a "find a needle in the haystack" approach to
find the problematic project(s).

For example, I spent once a whole day finding that a too big JPEG used in a
print layout causes the heavy memory usage:
* look at the memory curve,
* find the moment when the memory increased a lot
* use the command line find  to get which QGIS project files has changed
during this period (for example because it has been uploaded by the user)
* look inside QGIS logs to find which project has been loaded during this
period, and compare
* if you are lucky, there are not so many projects involved...
* download the project, open it in QGIS Desktop to observe and confirm the
memory issue
* try to understand which causes the issue
* ...

We are also building a REST API over py-qgis-server (
https://github.com/3liz/py-qgis-server ) which will allow to get
information on the loaded projects, to ease the auditing process in
real-time. (It will be opensource).

In the processing script, and in this API tool, we still miss a very
important metric: how much each project consumes the system memory (RAM). I
am not sure it could be easily done, but it will be very handy to:

* find heavy memory consumer projects
* see how the memory evolves
* compare the memory usage for each QGIS versions (with the same project
and layers)
* help to find the possible memory leaks

This message is not a feature request, I would like to get your experience
and opinion regarding this memory usage issue. Any contribution welcome ;-)

Thanks in advance
Michaël
___
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-psc] Grant report: Rework handling of multi-layer, mixed-format datasets

[kimaidou]

Thanks a lot !

Regards,
Michaël

Le mer. 28 juil. 2021 à 09:00, Tim Sutton  a écrit :

> Wow great work Nyall!
>
> Regards
>
> Tim
>
> Sent from my iPhone
>
> On 28 Jul 2021, at 07:43, Paolo Cavallini  wrote:
>
> Thanks a lot Nyall, I'm sure you did more and better work than it was
> expected. Our donors can be happy.
> Cheers.
>
> Il 28 luglio 2021 07:02:30 EEST, Nyall Dawson  ha
> scritto:
>>
>> Hi lists,
>>
>> Just reporting in the conclusion of the grant work: "Rework handling
>> of multi-layer, mixed-format datasets". You can read more about the
>> motivation behind this work at
>> https://github.com/qgis/QGIS-Enhancement-Proposals/issues/216
>>
>> While the work was partly motivated as an opportunity to clean up some
>> older parts of the QGIS codebase which were fragile and had low test
>> coverage, it has also resulted in many improvements and polish in the
>> QGIS user interface. Specifically:
>>
>> - There's a much improved dialog shown when a user has to pick from
>> multiple layers from a dataset dropped onto QGIS. The dialog will
>> populate in a background thread, avoiding the UI hangs older QGIS
>> versions experience when scanning for layers in large files. The
>> dialog shows a unified view of all layers in the dataset, regardless
>> of whether they are vector, raster or mesh layers. There's also a new
>> "filter" widget allowing users to quickly find matching layers in
>> larger datasets.
>>
>> - The browser view of files and file-based datasources has been
>> reworked. You'll no longer see duplicate entries for the same files
>> when these files can be read as either raster/vector/mesh layers (e.g.
>> for netcdf files, geopdf files and other mixed-format types). A lot of
>> consistency has been added to the right click actions you see when
>> interacting with file based items, with many bugs fixed along the way.
>>
>> Under the hood, we've now got a flexible API for querying sublayers of
>> a dataset which can be easily extended to new providers so that they
>> automatically participate in the unified sublayers dialog and browser
>> items. It's all protected by a huge number of new unit tests, the
>> majority of which cover critical functionality which was previously
>> not being tested.
>>
>> Thanks for funding this work!
>> Nyall
>> --
>> 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
>>
>>
> --
> Sorry for being short___
> Qgis-psc mailing list
> qgis-...@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/qgis-psc
>
> ___
> 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