<html><head></head><body><div class="ydp3fd05626yahoo-style-wrap" style="font-family: Helvetica Neue, Helvetica, Arial, sans-serif; font-size: 13px;"><div style="font-size: 13px;"></div>
<div dir="ltr" data-setdir="false" style=""><font size="2">Thank you for your response Yann, </font></div><div dir="ltr" data-setdir="false" style=""><font size="2"><br></font></div><div dir="ltr" data-setdir="false" style=""><font size="2">I tried your idea in the QGIS Python Console in both 3.10.5 and 3.12.2.</font></div><div dir="ltr" data-setdir="false" style=""><font size="2"><br></font></div><div dir="ltr" data-setdir="false" style=""><font size="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:</font></div><div dir="ltr" data-setdir="false" style=""><font size="2"><br></font></div><div dir="ltr" data-setdir="false" style=""><div><div><font size="2">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'")</font></div><div dir="ltr" data-setdir="false"><font size="2">uri.setConnection(' <span><span style="font-family: Helvetica Neue, Helvetica, Arial, sans-serif;">my:host:address</span></span>', '5432', 'my_database', 'my_user_name', None, QgsDataSourceUri.SslVerifyFull, 'my_qgis_authentication_id')</font></div><div dir="ltr" data-setdir="false"><font size="2">rlayer = iface.addRasterLayer(uri.uri(False), "my layer label", "postgres") # one step add</font></div></div><div><font size="2"><br></font></div><div dir="ltr" data-setdir="false"><font size="2">That resulted in the following:</font></div><div><font size="2"><br></font></div><div dir="ltr" data-setdir="false"><font size="2">Messages:</font></div><font size="2"><span><p style="white-space: pre-wrap; margin: 0px;">2020-04-30T16:02:01 CRITICAL Invalid Layer : Raster layer Cannot instantiate the 'postgres' data provider</p></span><br></font></div><div dir="ltr" data-setdir="false" style=""><font size="2">PostGIS Messages:</font></div><div dir="ltr" data-setdir="false" style=""><div><p style="white-space: pre-wrap; margin: 0px;"><font size="2">2020-04-30T16:02:01 WARNING Field rast ignored, because of unsupported type b</font></p><p style="white-space: pre-wrap; margin: 0px;"><font size="2">2020-04-30T16:02:01 WARNING NOTICE: row number 0 is out of range 0..-1</font></p></div><font size="2"><br></font></div><div dir="ltr" data-setdir="false" style=""><font size="2">-Thayer</font></div><div dir="ltr" data-setdir="false" style="font-size: 13px;"><br></div><div dir="ltr" data-setdir="false" style="font-size: 13px;">================================================</div></div><div id="ydpc6be9681yahoo_quoted_8997373339" class="ydpc6be9681yahoo_quoted"><div style="font-family:'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13px;color:#26282a;"><div><div dir="ltr"><br></div><div dir="ltr">Message: 5<br></div><div dir="ltr">Date: Thu, 30 Apr 2020 09:59:04 +0200<br></div><div dir="ltr">From: Yann Voté <<a href="mailto:ygversil@lilo.org" rel="nofollow" target="_blank">ygversil@lilo.org</a>><br></div><div dir="ltr">To: <a href="mailto:qgis-user@lists.osgeo.org" rel="nofollow" target="_blank">qgis-user@lists.osgeo.org</a><br></div><div dir="ltr">Subject: Re: [Qgis-user] PyQGIS to add PostGIS raster layer?<br></div><div dir="ltr">Message-ID: <<a href="mailto:cda24fb7-1832-b50f-649b-8f615ea50142@lilo.org" rel="nofollow" target="_blank">cda24fb7-1832-b50f-649b-8f615ea50142@lilo.org</a>><br></div><div dir="ltr">Content-Type: text/plain; charset=utf-8; format=flowed<br></div><div dir="ltr"><br></div><div dir="ltr">Sorry, I read too fast. Didn't see it was about raster layers.<br></div><div dir="ltr"><br></div><div dir="ltr">But here is a clue : load the raster layer manually, then look at the <br></div><div dir="ltr">tooltip appearing when you hover the cursor on the layer. It will <br></div><div dir="ltr">contain the connection string.<br></div><div dir="ltr"><br></div><div dir="ltr"><br></div><div dir="ltr">Le 30/04/2020 à 09:45, Yann Voté a écrit :<br></div><div dir="ltr">> Hi,<br></div><div dir="ltr">> <br></div><div dir="ltr">> Something like this should do.<br></div><div dir="ltr">> <br></div><div dir="ltr">> pg_layer = QgsVectorLayer(<br></div><div dir="ltr">> "host=db.priv.example.org port=5432 authcfg=expauth srid=4326 "<br></div><div dir="ltr">> "dbname=exampledb table='schema'.'table_name' (geometry) "<br></div><div dir="ltr">> "type=MULTIPOLYGON sql=",<br></div><div dir="ltr">> baseName='table_name',<br></div><div dir="ltr">> providerLib='postgres',<br></div><div dir="ltr">> )<br></div><div dir="ltr">> QgsProject.instance().addMapLayer(pg_layer)<br></div><div dir="ltr">> <br></div><div dir="ltr">> Of course, adapt with your values. In particular, authcfg is the 7-chars <br></div><div dir="ltr">> identifier of the auth configuration that contains your login and <br></div><div dir="ltr">> password (or cert) to authenticate to PostgreSQL.<br></div><div dir="ltr">> <br></div><div dir="ltr">> See <a href="https://qgis.org/pyqgis/master/core/QgsVectorLayer.html" rel="nofollow" target="_blank">https://qgis.org/pyqgis/master/core/QgsVectorLayer.html</a><br></div><div dir="ltr">> <br></div><div dir="ltr">> See also <br></div><div dir="ltr">> <a href="https://qgis.org/pyqgis/master/core/QgsDataSourceUri.html#qgis.core.QgsDataSourceUri " rel="nofollow" target="_blank">https://qgis.org/pyqgis/master/core/QgsDataSourceUri.html#qgis.core.QgsDataSourceUri </a><br></div><div dir="ltr">> for more options to build database connection strings.<br></div><div dir="ltr">> <br></div><div dir="ltr">> Regards.<br></div><div dir="ltr">> <br></div><div dir="ltr">> Yann<br></div><div dir="ltr">> <br></div><div dir="ltr">> <br></div><div dir="ltr">> Le 30/04/2020 à 00:24, thayeray a écrit :<br></div><div dir="ltr">>> Is it possible to add a PostGIS raster layer to a project using PyQGIS?<br></div><div dir="ltr">>> The PyQGIS Developer Cookbook mentions adding rasters from files,<br></div><div dir="ltr">>> geopackages and WCS services.<br></div><div dir="ltr">>> If I try using a QgsDataSourceUri by feeding it a raster column <br></div><div dir="ltr">>> instead of a<br></div><div dir="ltr">>> geometry column, the PostGIS error that is generated shows that is not <br></div><div dir="ltr">>> going<br></div><div dir="ltr">>> to work because raster is not a geometry:<br></div><div dir="ltr">>> WARNING Erroneous query: SELECT 0, array_agg(DISTINCT<br></div><div dir="ltr">>> st_srid("rast")::text || ':' || UPPER(geometrytype("rast"))) FROM<br></div><div dir="ltr">>> "public"."indicators__g_economic_rebal_r_62ft" returned 7 [ERROR: <br></div><div dir="ltr">>> function<br></div><div dir="ltr">>> geometrytype(raster) does not exist<br></div><div dir="ltr">>><br></div><div dir="ltr">>> Any ideas of what I can do? Do I just have to give up on PostGIS <br></div><div dir="ltr">>> rasters and<br></div><div dir="ltr">>> just turn it into WCS on the server?<br></div><div dir="ltr"><br></div></div>
</div>
</div></body></html>