Re: [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  im Auftrag von Thayer Young 

Gesendet: Dienstag, 4. Mai 2021 00:22
An: qgis-user@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 +
> From: Michael Gieding 
> mailto:michael.gied...@live.de>>
> To: QGIS-User Mailinglist 
> mailto:qgis-user@lists.osgeo.org>>
> Subject: [Qgis-user] Loading filtered database table as temp layer
> Message-ID 
> mailto:as8p191mb1752dcd9fc45ba48feaa9f57e5...@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
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

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

[Richard McDonnell]

Hi Michael,

To add to Thayer's message.

There is currently no way to do exactly what you are looking for, but, I would 
suggest the following as an alternative, which is simple enough to follow...



There are three ways to load a filtered layer into QGIS

Firstly, You can load a Layer using SQL Filter using the either the Data Source 
Manager or DB Manager.

1.   Data Source Manager will allow you to use the Set Filter option on 
your selected Table

2.   DB Manager will do the same when you highlight the Layer you want to 
load and click on the SQL Window [cid:image002.png@01D740CC.14351C50]

3.   Load the Layer in in its entirety, then use the Filter option by right 
clicking on the layer and selecting Filter.



Whichever of the above you use, the Filtered Layers can be temporally saved out 
as whatever format you would like while being worked on. This can be achieved 
by right clicking on the layer, selecting Export => Save Feature As



I Hope that helps in some way.



Regards,



Richard







--
Richard McDonnell MSc GIS, FME Certified Professional
FRM Data Management

--
Oifig na nOibreacha Poibl?
Office of Public Works

Sr?id Jonathan Swift, Baile ?tha Troim, Co na M?, C15 NX36
Jonathan Swift Street, Trim, Co Meath, C15 NX36
--
M +353 87 688 5964 T +353 46 942 2409
https://gov.ie/opw

--
To send me files larger than 30MB, please use the link below 
https://filetransfer.opw.ie/filedrop/richard.mcdonn...@opw.ie

Email Disclaimer: 
https://www.gov.ie/en/organisation-information/439daf-email-disclaimer/

--
MSc GIS, FME Certified Professional

--
Oifig na nOibreacha Poibl?
Office of Public Works

Sr?id Jonathan Swift, Baile ?tha Troim, Co na M?, C15 NX36
Jonathan Swift Street, Trim, Co Meath, C15 NX36
--
M +353 87 688 5964 T +353 46 942 2409
https://www.opw.ie

--
Email Disclaimer: https://www.opw.ie/en/disclaimer/

> -Original Message-

> From: Qgis-user  On Behalf Of Michael

> Gieding

> Sent: 03 May 2021 19:12

> To: QGIS-User Mailinglist 

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

>

> 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

> ___

> Qgis-user mailing list

> Qgis-user@lists.osgeo.org

> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user

> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Please note that the existing 01-6476XXX / 046-9426XXX numbering ranges will no 
longer be available after 30th April 2021.
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

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

[Michael Gieding]

Hello Richard,

thank you for the addition. But I fear, that this is not suitable for my target 
group.
Difficult enough for them to get the basics in QGIS. So it would be nice to 
offer a simple
tool for their work.
Maybe a good chance for me to dive into plugin programming. Seems to be a 
reachable
goal.


Kind regards,
Michael Gieding



Von: Richard McDonnell 
Gesendet: Dienstag, 4. Mai 2021 10:58
An: Michael Gieding; QGIS-User Mailinglist
Cc: thaye...@yahoo.com
Betreff: RE: Loading filtered database table as temp layer

Hi Michael,

To add to Thayer's message.

There is currently no way to do exactly what you are looking for, but, I would 
suggest the following as an alternative, which is simple enough to follow…



There are three ways to load a filtered layer into QGIS

Firstly, You can load a Layer using SQL Filter using the either the Data Source 
Manager or DB Manager.

1.   Data Source Manager will allow you to use the Set Filter option on 
your selected Table

2.   DB Manager will do the same when you highlight the Layer you want to 
load and click on the SQL Window [cid:image002.png@01D740CC.14351C50]

3.   Load the Layer in in its entirety, then use the Filter option by right 
clicking on the layer and selecting Filter.



Whichever of the above you use, the Filtered Layers can be temporally saved out 
as whatever format you would like while being worked on. This can be achieved 
by right clicking on the layer, selecting Export => Save Feature As



I Hope that helps in some way.



Regards,



Richard







——
Richard McDonnell MSc GIS, FME Certified Professional
FRM Data Management

——
Oifig na nOibreacha Poiblí
Office of Public Works

Sráid Jonathan Swift, Baile Átha Troim, Co na Mí, C15 NX36
Jonathan Swift Street, Trim, Co Meath, C15 NX36
——
M +353 87 688 5964 T +353 46 942 2409
https://gov.ie/opw
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

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

[Karl Magnus Jönsson]

Hi!
Wouldn't it be possible to use the "Execute SQL" algorithm under "Vector 
general" instead of the "PostgreSQL SQL execute and load" in the Model 
Designer? It outputs a (temporary) layer if you choose that. I'm not sure how 
to use it correctly though. 

Karl-Magnus Jönsson


-Ursprungligt meddelande-
Från: Qgis-user  För Michael Gieding
Skickat: den 4 maj 2021 11:18
Till: QGIS-User Mailinglist 
Ämne: Re: [Qgis-user] Loading filtered database table as temp layer

Hello Richard,

thank you for the addition. But I fear, that this is not suitable for my target 
group.
Difficult enough for them to get the basics in QGIS. So it would be nice to 
offer a simple tool for their work.
Maybe a good chance for me to dive into plugin programming. Seems to be a 
reachable goal.


Kind regards,
Michael Gieding



Von: Richard McDonnell 
Gesendet: Dienstag, 4. Mai 2021 10:58
An: Michael Gieding; QGIS-User Mailinglist
Cc: thaye...@yahoo.com
Betreff: RE: Loading filtered database table as temp layer

Hi Michael,

To add to Thayer's message.

There is currently no way to do exactly what you are looking for, but, I would 
suggest the following as an alternative, which is simple enough to follow.



There are three ways to load a filtered layer into QGIS

Firstly, You can load a Layer using SQL Filter using the either the Data Source 
Manager or DB Manager.

1.   Data Source Manager will allow you to use the Set Filter option on 
your selected Table

2.   DB Manager will do the same when you highlight the Layer you want to 
load and click on the SQL Window [cid:image002.png@01D740CC.14351C50]

3.   Load the Layer in in its entirety, then use the Filter option by right 
clicking on the layer and selecting Filter.



Whichever of the above you use, the Filtered Layers can be temporally saved out 
as whatever format you would like while being worked on. This can be achieved 
by right clicking on the layer, selecting Export => Save Feature As



I Hope that helps in some way.



Regards,



Richard







--
Richard McDonnell MSc GIS, FME Certified Professional FRM Data Management

--
Oifig na nOibreacha Poiblí
Office of Public Works

Sráid Jonathan Swift, Baile Átha Troim, Co na Mí, C15 NX36 Jonathan Swift 
Street, Trim, Co Meath, C15 NX36 -- M +353 87 688 5964 T +353 46 942 2409 
https://gov.ie/opw ___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

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

[pathmapper]

Hi Michael,

if it's just about creating a temporay layer as model output, you could 
try to use the "Drop field(s)" algorithm after "PostgreSQL SQL execute 
and load" in your model.


Just choose for the field "Input layer" the algorithm output of the 
"PostgreSQL SQL execute and load" algorithm, leave the "Fields to drop" 
field empty and enter a custom name for "Remaining fields".


You should also set a algorithm dependecy there (so the "Drop field(s)" 
algorithm is executed after "PostgreSQL SQL execute and load").


This way the "Drop field(s)" algorithm doesn't remove any fields but you 
get the option to create a tempory layer.


Hope this helps.

Bjoern


___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

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

[Michael Gieding]

Hello everybody,

@Karl: "execute SQL" seems not to be an option, as I found no way to pass the 
output to load it as a new layer.

@Bjoern: your hint looks promising. In the end there are two layers. One is the 
output of "PostgreSQL execute
and load". Seems it's "bypassing" the rest of the model.  The second is the 
temporary layer which is generated
through "drop fields" --> "load layer into project". Maybe I find a way to 
supress the first layer so only the
temporary one is left. In a first step it does (nearly) what I want.


Many thanks to you both,

Michael


Von: pathmapper 
Gesendet: Dienstag, 4. Mai 2021 12:16
An: Michael Gieding
Cc: QGIS-User Mailinglist
Betreff: Re: [Qgis-user] Loading filtered database table as temp layer

Hi Michael,

if it's just about creating a temporay layer as model output, you could
try to use the "Drop field(s)" algorithm after "PostgreSQL SQL execute
and load" in your model.

Just choose for the field "Input layer" the algorithm output of the
"PostgreSQL SQL execute and load" algorithm, leave the "Fields to drop"
field empty and enter a custom name for "Remaining fields".

You should also set a algorithm dependecy there (so the "Drop field(s)"
algorithm is executed after "PostgreSQL SQL execute and load").

This way the "Drop field(s)" algorithm doesn't remove any fields but you
get the option to create a tempory layer.

Hope this helps.

Bjoern


___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Windows BATCH-File Start

[Asmus]

Hi Bo,
 
thanks for the reply and sorry for answer so late.
That is exactly what I want to do and what doesn't work for me.

But now I have the trick, if I choose the option "capture output" on it works.
I don't know what this option do, I didn't used it before because I don't 
undestand what it is for, but it works.
If the option is on, no command window is open, but there opens a QGIS window 
which shows the output of the BATCH, and the work of teh BATCH will be done. 
Because of the missing command window, I thought it do not work.

Can anybody explain, what the option "capture output" do, and why the Windows 
BATCH do the work if the option is on, and don't do the work, if the option is 
off?

Thank you very much
Asmus




Am 28. April 2021 11:23:11 MESZ schrieb Bo Victor Thomsen 
:
>Hi, late to the party..
>
>However, this works on my Windows-10 64 bit / QGIS 3.18 64 bit:
>
>the "d:/tmp/start.cmd" contains only 1 line:
>
>@echo "%1"
>
>You should probably change the action type from "Open" to "Generic"
>
>Med venlig hilsen / Kind regards
>
>Bo Victor Thomsen
>
>Den 23-04-2021 kl. 13:40 skrev Asmus Harder:
>> Hi there,
>> I tried to use in QGIS 3.16 on Windows10 a old layer-action from QGIS
>
>> 2.18.
>> If I cklick on one object the action should start an BATCH-file and 
>> gives some attributes as parameter to the batch-file. But it doesn't 
>> work, nothing happens.
>> In QGIS2.18 it works fine.
>> If I use "Open" as type and only the BATCH-File without parameter
>than 
>> the BATCH opens. If I add an parameter than nothing happens.
>> For example this work:
>> "d:\Test_Batch_Aufruf.bat"
>> but this doesn't work:
>> "d:\Test_Batch_Aufruf.bat" "Hello"
>> If I use a exe-file it works with a parameter.
>> For example this work fine:
>> "c:\Program Files\IrfanView\i_view64.exe" "D:\50342_col.tif"
>> Any idea why the BATCH-file doesn't start with parameter?
>> greetings
>> Asmus
>>
>> ___
>> Qgis-user mailing list
>> Qgis-user@lists.osgeo.org
>> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
>> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

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

[chris hermansen]

Michael and list,

On Tue, May 4, 2021 at 2:18 AM Michael Gieding 
wrote:

> Hello Richard,
>
> thank you for the addition. But I fear, that this is not suitable for my
> target group.
> Difficult enough for them to get the basics in QGIS. So it would be nice
> to offer a simple
> tool for their work.
> Maybe a good chance for me to dive into plugin programming. Seems to be a
> reachable
> goal.
>

If you're going to start programming, maybe consider the idea of having the
user interaction result in the definition of a view, rather than filtering
the data after it's received on the client.  That way all the filtering
will happen on the back end, which should improve the performance for your
users, and the views can be shared between users (instead of needing to
share SQL directly).


-- 
Chris Hermansen · clhermansen "at" gmail "dot" com

C'est ma façon de parler.
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [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 +
From: Michael Gieding 
To: "qgis-user@lists.osgeo.org" 
Subject: Re: [Qgis-user] Loading filtered database table as temp layer
Message-ID:
    

    
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


  ___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user