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

[Michael Gieding]

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

________________________________________
Von: Qgis-user <qgis-user-bounces at lists.osgeo.org> im Auftrag von Thayer Young <thayeray at yahoo.com>
Gesendet: Dienstag, 4. Mai 2021 00:22
An: qgis-user at lists.osgeo.org
Betreff: [Qgis-user] Loading filtered database table as temp layer

Hi Michael,

You may want to explain your question more, as I am not sure what you mean by temporary.
Are you sure this is a QGIS question and not a PostgreSQL user permissions question?
Are you trying to prevent your users from creating new views in the database or alter QGIS projects stored in the database, or something else?

If you use DB Manager the layers you load exist in your QGIS project not in your database. True they have access to the original data but it is not write access unless you set up triggers to allow the view to be editable.

-Thayer


> Date: Mon, 3 May 2021 18:12:16 +0000
> From: Michael Gieding <michael.gieding at live.de<mailto:michael.gieding at live.de>>
> To: QGIS-User Mailinglist <qgis-user at lists.osgeo.org<mailto:qgis-user at lists.osgeo.org>>
> Subject: [Qgis-user] Loading filtered database table as temp layer
> Message-ID <AS8P191MB1752DCD9FC45BA48FEAA9F57E55B9 at AS8P191MB1752.EURP191.PROD.OUTLOOK.COM<mailto:AS8P191MB1752DCD9FC45BA48FEAA9F57E55B9 at AS8P191MB1752.EURP191.PROD.OUTLOOK.COM>>

> Content-Type: text/plain; charset="iso-8859-1"

> Hallo erverybody,

> I'm looking for a solution where users can load the output of a sql statement from PostgreSQL as > temporary layer.
> The tool should be as easy as possible, as the target group is not very experienced in QGIS. Means > there should be some
> defined fields to enter values for filtering and an ok-button to create the desired layer.

> One idea I tried, is to create a workflow in the graphical modelbuilder in QGIS. In the moment it works > to create a new layer
> with the desired SQL-statement. I used the algorithms "PostgreSQL SQL execute and load" --> > "Load Layer to project".
> But I can't find an option to load the output of the statement as a temporary. The result is like a > loaded statement from
> the database manager. So the users would work with the original data within the database.
> Does anyone has got an idea to achieve this? Maybe I've overseen something.

> Of course a separate plugin would do the thing, but with my skills it's way more difficult to achieve.
> So I would be happy to solve the problem the "easy way".

> Any (other) hint is welcome.


> Many thanks in advance.

> Michael Gieding