In PostGIS, I have a set of (large, U.S.-wide) rasters at high (30m) resolution. For my application, I need to take an arbitrary area of interest provided by the user, likely ranging in size from dozens to hundreds of raster cells, and then get back out a table containing the values for each cell, in each raster. What I'm ultimately looking for is essentially a normalized table, something like this:
I don't necessarily need each cell to have a unique identifier, but it's important that I know, for example, that the value for raster 1 in a given cell was 0.152, and the value for raster 2 in that same cell was 9.3. I'll ultimately be doing some math to multiply each of those raster values by a user-defined factor, with each of those raster values for a given cell, to get a resulting value for each cell (which could be just tabular info).
I know I can use ST_Value or ST_PixelAsPoint to get the value for a particular pixel, given point input, and I've seen examples for getting summary stats for raster values within a polygon, but no examples that will give back a table or list of values for each raster, for all cells intersecting a polygon, on a cell by cell basis.