[Qgis-user] Loading filtered database table as temp layer

[Thayer Young]

 Hi Michael,
I think l can see where the problem lies with model builder. Temporary tables only exist in the database connection that created them. So the difficulty is passing the connection from one tool to the next in your model.  I am not sure there is a way to do that. If you want to stick with model builder you may want to consider having your model create a table using the filters, and populate it with the filtered data. Then the user can edit it as they see fit and when they are done, whatever code you were planning to use to make the changes permanent would also drop the "not so temporary" table. Obviously you would need to periodically find and drop old tables that did not get dropped by their creators.
Python should be able to work with temporary tables, but you might have to build the whole thing in python to maintain the connection. I do not know enough about how QGIS interacts with the database, if each layer has a connection that is maintained until the QGIS project is closed, or if the connection is reestablished periodically, for example when the extent changes.
-Thayer

Date: Tue, 4 May 2021 08:50:16 +0000
From: Michael Gieding <michael.gieding at live.de>
To: "qgis-user at lists.osgeo.org" <qgis-user at lists.osgeo.org>
Subject: Re: [Qgis-user] Loading filtered database table as temp layer
Message-ID:
    <AS8P191MB17524CF81C1E020295B1D705E55A9 at AS8P191MB1752.EURP191.PROD.OUTLOOK.COM>
    
Content-Type: text/plain; charset="us-ascii"

Hello Thayer,

thank you for your reply. I try to make it a bit more clear.

I've got a PostgreSQL-table with lots of points which have attributes like "name" and "date".
In our scenario the users must be able to work with a filtered subset of points from this table.
Ideally they should be able to delete or edit these points without changing the original data.
So my idea of a workflow is to add the database table to a QGIS project, add a flilter like
"name='xyz' and date='2021-05-01', mark all loaded points, copy them and insert them as
a new temporary layer.
With that you can safely work with that new layer until you have got the desired result. No
changes in the databse are necessary.
In my opinion this is nothing for unexperienced users and needs to much clicks. So I'm looking
for a solution where the user can open a kind of gui where she can add the filter parameters
and with "ok" the temporary layer is automatically generated.
To prevent to program an own plugin I tried to solve this with the modelbuilder. In the moment
it works to create a new layer which is the original filtered database table. So it's more like a
sqlstatement loaded to QGIS from the database manager. But I was not able to create a
temporary layer as output from the modelscript.
Maybe there is already a plugin out there which does something like this or could be a good
starting point for an own plugin.



Thanks,

Michael Gieding


  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20210504/52aacdd9/attachment.html>