In the previous two recipes, we ran basic statistics only on one raster tile. Though running operations on a specific raster is great, it is not very helpful for answering real questions. In this recipe, we will use geometries to filter, clip, and union raster tiles so that we can answer questions for a specific area.
We will use the San Francisco boundaries geometry previously imported into the sfpoly
table. If you have not imported the boundaries, refer to the first recipe of this chapter for instructions.
Since we are to look at rasters in the context of San Francisco, an easy question to ask is: what was the average temperature for January, 2012 in San Francisco?
SELECT ( ST_SummaryStats( ST_Union( ST_Clip(prism.rast, 2, ST_Transform(sf.geom, 4322), TRUE) ), 1 ) ).mean FROM prism JOIN sfpoly sf ON ST_Intersects(prism.rast...