[QGIS-Developer] PostgreSQL execute and load SQL in Processing Modeler

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[QGIS-Developer] PostgreSQL execute and load SQL in Processing Modeler

Pedro Venâncio-2
Dear all,

I'm developing a Processing Model and I want to run an algorithm (Union) on a specific PostGIS layer. The idea is to have this input layer hardcoded to the Union algorithm.

If this layer is stored in a system file (SHP, or any other vector layer format), it is simple, just pointing Union input field to that file. But as it is stored in PostgreSQL, I thought to use "PostgreSQL execute and load SQL" algorithm to call the layer and use it in Model, as input to Union.

In fact this works well, but when running the Model, the PostGIS layer is loaded to QGIS canvas.

I think this is happening because "PostgreSQL execute and load SQL" won’t create any new layer, and load the PostGIS layer itself, as a result of the query, just like it's explained in the docs:

https://docs.qgis.org/testing/en/docs/user_manual/processing_algs/qgis/database.html#postgresql-execute-and-load-sql

So, anyone have any hint to call a PostGIS layer and use it as hardcoded input to a Model algorithm, without python coding?

Thank you very much!

Best regards,
Pedro Venâncio

_______________________________________________
QGIS-Developer mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL execute and load SQL in Processing Modeler

DelazJ
Hi Pedro,

Regards,
Harrissou

Le mer. 31 juil. 2019 à 18:31, Pedro Venâncio <[hidden email]> a écrit :
Dear all,

I'm developing a Processing Model and I want to run an algorithm (Union) on a specific PostGIS layer. The idea is to have this input layer hardcoded to the Union algorithm.

If this layer is stored in a system file (SHP, or any other vector layer format), it is simple, just pointing Union input field to that file. But as it is stored in PostgreSQL, I thought to use "PostgreSQL execute and load SQL" algorithm to call the layer and use it in Model, as input to Union.

In fact this works well, but when running the Model, the PostGIS layer is loaded to QGIS canvas.

I think this is happening because "PostgreSQL execute and load SQL" won’t create any new layer, and load the PostGIS layer itself, as a result of the query, just like it's explained in the docs:

https://docs.qgis.org/testing/en/docs/user_manual/processing_algs/qgis/database.html#postgresql-execute-and-load-sql

So, anyone have any hint to call a PostGIS layer and use it as hardcoded input to a Model algorithm, without python coding?

Thank you very much!

Best regards,
Pedro Venâncio
_______________________________________________
QGIS-Developer mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

_______________________________________________
QGIS-Developer mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL execute and load SQL in Processing Modeler

Pedro Venâncio-2
Hi Harrissou,

"PostgreSQL execute SQL" can not be input to another algorithm in Processing Modeler, because it has no output. It is only used to execute SQL directly in the database.

"PostgreSQL execute and load SQL" do the same, but load the result of the SQL query in QGIS, and this one already has the possibility to be used as input in another Modeler algorithm. The problem is just that it is loaded to QGIS, and my goal is to hardcode a PostGIS layer input to a Modeler algorithm. This is really easy with system based files, but I can't find an easy solution to PostGIS layers.

Thanks anyway Harrissou!

Best regards,
Pedro



DelazJ <[hidden email]> escreveu no dia quarta, 31/07/2019 à(s) 18:15:
Hi Pedro,

Regards,
Harrissou

Le mer. 31 juil. 2019 à 18:31, Pedro Venâncio <[hidden email]> a écrit :
Dear all,

I'm developing a Processing Model and I want to run an algorithm (Union) on a specific PostGIS layer. The idea is to have this input layer hardcoded to the Union algorithm.

If this layer is stored in a system file (SHP, or any other vector layer format), it is simple, just pointing Union input field to that file. But as it is stored in PostgreSQL, I thought to use "PostgreSQL execute and load SQL" algorithm to call the layer and use it in Model, as input to Union.

In fact this works well, but when running the Model, the PostGIS layer is loaded to QGIS canvas.

I think this is happening because "PostgreSQL execute and load SQL" won’t create any new layer, and load the PostGIS layer itself, as a result of the query, just like it's explained in the docs:

https://docs.qgis.org/testing/en/docs/user_manual/processing_algs/qgis/database.html#postgresql-execute-and-load-sql

So, anyone have any hint to call a PostGIS layer and use it as hardcoded input to a Model algorithm, without python coding?

Thank you very much!

Best regards,
Pedro Venâncio
_______________________________________________
QGIS-Developer mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

_______________________________________________
QGIS-Developer mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL execute and load SQL in Processing Modeler

Nyall Dawson
On Thu, 1 Aug 2019 at 08:17, Pedro Venâncio <[hidden email]> wrote:
>
> Hi Harrissou,
>
> "PostgreSQL execute SQL" can not be input to another algorithm in Processing Modeler, because it has no output. It is only used to execute SQL directly in the database.
>
> "PostgreSQL execute and load SQL" do the same, but load the result of the SQL query in QGIS, and this one already has the possibility to be used as input in another Modeler algorithm. The problem is just that it is loaded to QGIS, and my goal is to hardcode a PostGIS layer input to a Modeler algorithm. This is really easy with system based files, but I can't find an easy solution to PostGIS layers.

Hmm - you're 100%. There's a gap here which can't be solved in current
Processing framework.

There's two approaches which could be taken here:
1. Add a flag to QgsProcessingContext reflecting whether the algorithm
is being run as part of a larger model. In the
PostGISExecuteAndLoadSQL.py script, check for this flag and if it's
present then skip the "addLayerToLoadOnCompletion" step.

or

2. (better, more complicated, but of value EVERYWHERE in processing)
Allow postgres layer sources to be used directly as input values for
vector layer parameters.

I'm happy to mentor the changes if you want to give a PR as shot, or
email me privately if you'd prefer to directly sponsor either
approach.

Nyall

>
> Thanks anyway Harrissou!
>
> Best regards,
> Pedro
>
>
>
> DelazJ <[hidden email]> escreveu no dia quarta, 31/07/2019 à(s) 18:15:
>>
>> Hi Pedro,
>> Using "PostgreSQL execute SQL" https://docs.qgis.org/testing/en/docs/user_manual/processing_algs/qgis/database.html#postgresql-execute-sql? without the load part
>>
>> Regards,
>> Harrissou
>>
>> Le mer. 31 juil. 2019 à 18:31, Pedro Venâncio <[hidden email]> a écrit :
>>>
>>> Dear all,
>>>
>>> I'm developing a Processing Model and I want to run an algorithm (Union) on a specific PostGIS layer. The idea is to have this input layer hardcoded to the Union algorithm.
>>>
>>> If this layer is stored in a system file (SHP, or any other vector layer format), it is simple, just pointing Union input field to that file. But as it is stored in PostgreSQL, I thought to use "PostgreSQL execute and load SQL" algorithm to call the layer and use it in Model, as input to Union.
>>>
>>> In fact this works well, but when running the Model, the PostGIS layer is loaded to QGIS canvas.
>>>
>>> I think this is happening because "PostgreSQL execute and load SQL" won’t create any new layer, and load the PostGIS layer itself, as a result of the query, just like it's explained in the docs:
>>>
>>> https://docs.qgis.org/testing/en/docs/user_manual/processing_algs/qgis/database.html#postgresql-execute-and-load-sql
>>>
>>> So, anyone have any hint to call a PostGIS layer and use it as hardcoded input to a Model algorithm, without python coding?
>>>
>>> Thank you very much!
>>>
>>> Best regards,
>>> Pedro Venâncio
>>> _______________________________________________
>>> QGIS-Developer mailing list
>>> [hidden email]
>>> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>>> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>
> _______________________________________________
> QGIS-Developer mailing list
> [hidden email]
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
_______________________________________________
QGIS-Developer mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer