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

[Yann Voté]

As Alessandro pointed out, with QGS 3.12, you can use 'postgresraster' 
instead of 'postgres' as the providerType argument. That is:

iface.addRasterLayer(uri.uri(), 'my layer label', 'postgresraster')

You can find out these parameters (URI and provider type) with further 
introspection: when you have loaded the layer manually, select it, open 
a Python console, and you can get the layer Python object with:

 >>> db_layer = iface.activeLayer()

Then you can get the source URI with:

 >>> db_layer.source():

and the provider type with:

 >>> db_layer.providerType()

With these parameters you can feed iface.addRasterLayer() method or 
QgsRasterLayer() init method.

With QGIS 3.10 you can introspect this way too to find out the URI while 
'gdal' is the provider.

Regards.

Yann

Le 30/04/2020 à 22:41, Thayer Young a écrit :
> 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 <mailto:ygversil at lilo.org>>
> To: qgis-user at lists.osgeo.org <mailto: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 
> <mailto: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?
> 
> 
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
>