I am trying to isolate the pixels in a floating point raster (loaded in PostGIS) that intersect my MultipolygonZ table (also loaded in PostGIS) and whose values are less than or equal to the z values of the MultipolygonZ table - essentially a 3d intersection.
I am running PostGIS 2.1.5. SELECT postgis_full_version() returns:
"POSTGIS="2.1.5 r13152" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.1, released 2014/09/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER"
Here's what I have tried so far:
1) ST_Intersects(rast, geom) - this simply calculates a 2d intersection between the polygons and the raster - the z values of the MultipolygonZ are ignored.
SELECT (ST_DumpAsPolygons(rast)).* FROM pgz_tab, dem WHERE ST_Intersects(rast, geom)
2) ST_3dIntersects(rast, geom) - invalid arguments - apparently this only works with points and linestrings - http://postgis.net/docs/manual-2.1/ST_3DIntersects.html
SELECT (ST_DumpAsPolygons(rast)).* FROM pgz_tab, dem WHERE ST_3dIntersects(rast, geom)
3) Converting the MultipolygonZ to a floating point raster and then subtracting the two rasters. I can't seem to get the arguments right with the ST_AsRaster function - http://postgis.net/docs/manual-2.1/RT_ST_AsRaster.html even after reading A guide to the rasterization of vector coverages in PostGIS.