Combining geometries with rasters for analysis

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.

Getting ready

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.

How to do it...

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...