I am trying to replicate this example by alphabetasoup for summarizing pixel values according to census polygons, but am getting this error.
ERROR: function st_clip(raster, integer, geometry) does not exist LINE 8: FROM (SELECT sa_id, ST_SummaryStats(ST_Clip(rast,1,geom)) ...
HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 176
The code is only changed slightly to reflect my particular schemas, the results I'm querying, and to make it more legible.
WITH polys AS ( SELECT id As sa_id, geom FROM prac4.small_areas ), poly_stats AS ( SELECT sa_id, (stats).* FROM (SELECT sa_id, ST_SummaryStats(ST_Clip(rast,1,geom)) As stats FROM prac4.no2_raster INNER JOIN polys ON ST_Intersects(geom,rast) ) As foo ) SELECT sa_id, SUM(mean*count)/SUM(count) As avg_pval FROM poly_stats GROUP BY sa_id ORDER BY sa_id;
A similar question has been posted here but I think the answers there had to do with incorrect SQL. I'm probably missing something obvious.
I'm on postgis 3.0.2., postgresql 12.4. The postgis_raster extension has been added to the database.