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 = '2017-03-01'::date; 

In the preceding SQL query, there are four items to pay attention to, which are as follows:

• ST_Transform(): This method converts the geometry's coordinates from one spatial reference system to another. Transforming a geometry is typically faster than transforming a raster. Transforming a raster requires the pixel values to be resampled, a compute-intensive process, and one that could introduce undesirable results. If possible, always transform a geometry before transforming a raster, because spatial joins need to use the same SRID.
• ST_Intersects(): The ST_Intersects() method found in the JOIN ON clause tests if the raster tile and the geometry spatially intersect. It will use any available spatial indexes. Depending on the installed version of PostGIS, ST_Intersects() will implicitly convert the input geometry to a raster (PostGIS 2.0), or the input raster to a geometry (PostGIS 2.1), before comparing the two inputs.
• ST_Clip(): This method trims each intersecting raster tile only to the area that intersects the geometry. It eliminates the pixels that are not spatially part of the geometry. Like ST_Intersects(), the geometry is implicitly converted to a raster before clipping.
• ST_Union(): This method aggregates and merges the clipped raster tiles into one raster for further processing.

The following output shows the average minimum temperature for San Francisco:

San Francisco was really cold in March, 2017. So, how does the rest of 2017 look? Is San Francisco always cold?

SELECT prism.month_year, (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)) 
GROUP BY prism.month_year 
ORDER BY prism.month_year; 

The only change from the prior SQL query is the removal of the WHERE clause and the addition of a GROUP BY clause. Since ST_Union() is an aggregate function, we need to group the clipped rasters by month_year.

The output is as follows:

Based on the results, the late summer months of 2017 were the warmest, though not by a huge margin.