[Qgis-user] PyQGIS to add PostGIS raster layer?

[Thayer Young]

 Thank you for your response Yann, 
I tried your idea in the QGIS Python Console in both 3.10.5 and 3.12.2.
I was able to create a one part connection string URI by copying the details from a manually added raster. Note that the two step layer addition procedure causes QGIS to crash. The one step iface method does not work either but at least it does not crash QGIS:
uri = QgsDataSourceUri("PG: dbname='my_database' host=my:host:address port=5432 sslmode=verify-full mode=2 schema='public' column='rast' table='my_raster_table'")uri.setConnection(' my:host:address', '5432', 'my_database', 'my_user_name', None, QgsDataSourceUri.SslVerifyFull, 'my_qgis_authentication_id')rlayer = iface.addRasterLayer(uri.uri(False), "my layer label", "postgres") # one step add
That resulted in the following:
Messages:
2020-04-30T16:02:01     CRITICAL    Invalid Layer : Raster layer Cannot instantiate the 'postgres' data provider

PostGIS Messages:
2020-04-30T16:02:01     WARNING    Field rast ignored, because of unsupported type b

2020-04-30T16:02:01     WARNING    NOTICE: row number 0 is out of range 0..-1

-Thayer
================================================
Message: 5
Date: Thu, 30 Apr 2020 09:59:04 +0200
From: Yann Voté <ygversil at lilo.org>
To: qgis-user at lists.osgeo.org
Subject: Re: [Qgis-user] PyQGIS to add PostGIS raster layer?
Message-ID: <cda24fb7-1832-b50f-649b-8f615ea50142 at lilo.org>
Content-Type: text/plain; charset=utf-8; format=flowed

Sorry, I read too fast. Didn't see it was about raster layers.

But here is a clue : load the raster layer manually, then look at the 
tooltip appearing when you hover the cursor on the layer. It will 
contain the connection string.


Le 30/04/2020 à 09:45, Yann Voté a écrit :
> Hi,
> 
> Something like this should do.
> 
> pg_layer = QgsVectorLayer(
>      "host=db.priv.example.org port=5432 authcfg=expauth srid=4326 "
>      "dbname=exampledb table='schema'.'table_name' (geometry) "
>      "type=MULTIPOLYGON sql=",
>      baseName='table_name',
>      providerLib='postgres',
> )
> QgsProject.instance().addMapLayer(pg_layer)
> 
> Of course, adapt with your values. In particular, authcfg is the 7-chars 
> identifier of the auth configuration that contains your login and 
> password (or cert) to authenticate to PostgreSQL.
> 
> See https://qgis.org/pyqgis/master/core/QgsVectorLayer.html
> 
> See also 
> https://qgis.org/pyqgis/master/core/QgsDataSourceUri.html#qgis.core.QgsDataSourceUri 
> for more options to build database connection strings.
> 
> Regards.
> 
> Yann
> 
> 
> Le 30/04/2020 à 00:24, thayeray a écrit :
>> Is it possible to add a PostGIS raster layer to a project using PyQGIS?
>> The PyQGIS Developer Cookbook mentions adding rasters from files,
>> geopackages and WCS services.
>> If I try using a QgsDataSourceUri by feeding it a raster column 
>> instead of a
>> geometry column, the PostGIS error that is generated shows that is not 
>> going
>> to work because raster is not a geometry:
>> WARNING    Erroneous query: SELECT 0, array_agg(DISTINCT
>> st_srid("rast")::text || ':' || UPPER(geometrytype("rast"))) FROM
>> "public"."indicators__g_economic_rebal_r_62ft" returned 7 [ERROR: 
>> function
>> geometrytype(raster) does not exist
>>
>> Any ideas of what I can do? Do I just have to give up on PostGIS 
>> rasters and
>> just turn it into WCS on the server?

  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20200430/0056ca9f/attachment.html>