[Qgis-user] Qgis / Postgis raster : select and display pixels are intersected a polygon

[celati Laurent]

Good morning,
I have in my postgis database, 1 raster and 1 vector polygon.
I would like to be able in qgis to display only pixels that are inside or
intersect my polygon.
I would like to do a "select" only and not a create table firstly. I was
thinking of using the SQL window of the BDD plugin for this.
Here is an example :





*Coded:SELECT temp_rast.*FROM public.temp_rast,
andra_human."FAVL_ZoneEtude_Po"WHERE
ST_Intersects(temp_rast.rast,"FAVL_ZoneEtude_Po".geom)*

When I run it works. But I can't load as a new layer. I put rid for unique
values. And I put rast for the geom column. FYI, My tables have indexes and
constraints (pk)

Here is the error message I get in the qgis logs:














*Coded:2023-06-14T10:32:38 WARNING Rast field ignored because type b is not
supported2023-06-14T10:32:38 WARNING An error occurred while retrieving
raster metadata for "public".."a", raster data parsing (which may be very
slow) continues: Unable to obtain the number of bands from the value: ''..
            Please consider adding raster constraints with the PostGIS
AddRasterConstraints function.2023-06-14T10:32:38 INFO No preview found,
performance may be affected for "public".."a"2023-06-14T10:40:17 WARNING
Bad query: SELECT 0, array_agg(DISTINCT st_srid("rast")::text || ':' ||
UPPER(geometrytype("rast")) || ':' | | ST_Zmflag("rast")) FROM (SELECT
"rast" FROM (SELECT temp_rast.* FROM public.temp_rast,
andra_human."FAVL_ZoneEtude_Po" WHERE
ST_Intersects(temp_rast.rast,"FAVL_ZoneEtude_Po".geom)              ) AS
"subQuery_0") AS _unused returned 7 [ERROR: function geometrytype(raster)
does not exist              LINE 1: ...gg(DISTINCT st_srid("rast")::text ||
':' || UPPER(geometryty...               ^              HINT: No function
matches given name and argument types.              You must add explicit
type conversions.              ]2023-06-14T10:40:17 WARNING Invalid
PostgreSQL layer*


*Thanks so much.*
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20230614/88fa2739/attachment.htm>