ST_Clip tiled raster using geometry results in out of geometry pixels

Question

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:

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

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:

CREATE TABLE ras_clip AS 
SELECT 
      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.