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 unite 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 March, 2017 in San Francisco? Have a look at the following code:
SELECT (ST_SummaryStats(ST_Union(ST_Clip(prism.rast, 1, ST_Transform(sf.geom, 4269), TRUE)), 1)).mean
FROM chp05.prism
JOIN chp05.sfpoly sf ON ST_Intersects(prism.rast, ST_Transform(sf.geom, 4269))
WHERE prism.month_year...