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.

   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.

  • The ST_Clip function postgis.net/docs/RT_ST_Clip.html takes just arguments raster, integer, and geometry. Are you sure that the installation of the raster extension was successful and you have ST_Clip available? – user30184 Jan 21 at 10:53
  • @user30184 on the contrary, it requires at least one more parameter, or an INT[]. – geozelot Jan 21 at 11:04
  • @user30184 although the description states otherwise... – geozelot Jan 21 at 11:11
  • I was looking at raster ST_Clip(raster rast, integer nband, geometry geom, boolean crop); and later If crop is not specified, true is assumed meaning the output raster is cropped to the intersection of the geom and rast extents. – user30184 Jan 21 at 12:25

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);


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...

  • Thanks so much! This did the trick! I must say, the error message is not particularly enlightening. So, for anybody else who is as uninitiated about what a "signature" is as me (took me a while), changing "ST_Clip(rast,1,geom))" (as in alphabetasoup's example) to "ST_Clip(rast,1,geom,TRUE))" resolves the issue. Just for future reference, how could I check whether I have any particular function available @user30184? – gregorhuh Jan 21 at 17:31
  • 1
    You can find the functions with PgAdmin from "Functions". With SQL try SELECT * from pg_proc where proname like '%st_clip%'. I do not have raster extension installed and for me the query found only "st_clipbybox2d". – user30184 Jan 22 at 9:17
  • Excellent - thanks a million! – gregorhuh Jan 25 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.