Database Reference
In-Depth Information
Combining geometries with rasters for
analysis
In the previous two recipes, we ran basic statistics only on one raster tile. Though
runningoperationsonaspecificrasterisgreat,itisnotveryhelpfulforansweringreal
questions.Inthisrecipe,wewillusegeometriestofilter,clip,andunionrastertilesso
that we can answer questions for a specific area.
Getting ready
WewillusetheSanFranciscoboundariesgeometrypreviouslyimportedintothe
sf-
poly
table. If you have not imported the boundaries, refer to the first recipe of this
chapter for instructions.
How to do it...
SincewearetolookatrastersinthecontextofSanFrancisco,aneasyquestionto
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,
ST_Transform(sf.geom, 4322))
WHERE prism.month_year = '2012-01-01'::date;
In the preceding SQL query, there are four items to pay attention to, as follows:
