I'm trying to run an ST_Clip for a simple AOI geometry.

I'm using this WGS84 raster, imported into PostGIS like so:

raster2pgsql -t 100x100 -C -M -F -I -s 4326 Hansen_GFC-2016-v1.4_lossyear_20S_070W.tif hansen_temp | psql

I'm trying to clip this raster to my AOI (gist here).

I use ogr2ogr to insert this AOI into postgres:

ogr2ogr -f Postgresql PG:"dbname=charlie" aoi.geojson

I run the following SQL: CREATE TABLE ras_clip AS SELECT * FROM (SELECT ST_Union(ST_Clip(rast, wkb_geometry )) FROM hansen_temp, aoi WHERE ST_Intersects(rast, wkb_geometry)) foo;

This results in a raster that seems to follow my AOI - perhaps following the 100x100 tiles I set when importing the data using raster2pgsql. Here's a screenshot taken from PostGIS:

Example raster overlap

Is this the desired results of ST_Clip? Is there any way to constrain the output to only pixels that actually intersect the geometry of interest?

PostGIS version info: POSTGIS="2.4.4 r16526" PGSQL="100" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 5.0.1, April 1st, 2018" GDAL="GDAL 2.3.0, released 2018/05/04" LIBXML="2.9.7" LIBJSON="0.13.1" RASTER

  • Your query looks correct, other than the unecessary SELECT * FROM (SELECT ... which you could just replace with SELECT ST_Union(ST_Clip(rast, geom)..... It isn't very clear from your question how what you have done isn't working. A screen shot of the aoi, might be more useful than the gist. Commented May 26, 2018 at 11:14
  • 1
    It is possible you should be using the ST_Clip (rast, geom, no_data_value, true) form, ie, explicitly cropping which requires setting a no data value. Commented May 26, 2018 at 11:17
  • Hi @JohnPowellakaBarça - thanks so much for your response. You were exactly right - adding the no data value and explicitly cropping did the trick. Please post as an answer and I'll accept. Thanks! Commented May 29, 2018 at 13:53
  • Hi Charlie, sure, done. The Postgis raster functions are really great, but there are some quite non-obvious things. Commented May 30, 2018 at 6:12
  • Hi Charlie, I don't know if you read the answer in full, no problem either way :D, but the DumpAsPolygons or PixelAsPolygons might be of use to you. I don't know what analysis you are doing exactly, but for irregular areas where you just want to convert raster to vector, this works really well. Commented May 30, 2018 at 11:17

1 Answer 1


There are various forms of the ST_Clip function. To remove areas outside of your polygonal aoi, you should use one of the forms with an explicit no data value -- appropriate to whatever software you will be utilizing to view/analyse the clipped raster -- with clipped set to true, eg, this one:

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

Your query then becomes something like:

      ST_Union(ST_Clip(rast, wkb_geometry, -9999, true))
  FROM hansen_temp, aoi
 WHERE ST_Intersects(rast, wkb_geometry);

Note also, you don't need the subselect, as you are doing SELECT * FROM ( so you can just run a single select, although I am sure the query planner would do that automatically. Less typing is always good, though :D.

You might also want to look at ST_DumpAsPolygons or ST_PixelAsPolygons, depending on how you are doing your analysis. For raster coverages that are clipped to a very irregular polygon that cover little of the original raster coverage -- think rivers, train lines, etc -- it is sometimes more efficient to convert the output of ST_Clip to a polygonal coverage, as you don't have to store all the no data values.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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