Function st_clip(raster, integer, geometry) does not exist

Question

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.

Answer 1

As per the docs, ST_Clip doesn't provide the signature you use; without further evaluation of what you intend to do, the closest signature would be either

raster ST_Clip(raster rast, integer nband, geometry geom, double precision nodataval, boolean crop=TRUE);

or

raster ST_Clip(raster rast, integer nband, geometry geom, boolean crop);

Note that signatures with parameters having no default (=<value> suffix) requires an explicit input!

---

However, the docs explanations seem to disagree with the signatures, which may mean the docs have not been updated, or your installation is indeed not working...