Database Reference
In-Depth Information
How to do it...
LetussaythatacolleagueasksforthemonthlyminimumtemperaturedataforSan
Francisco during the summer months as a single raster file. This entails restricting
our PRISM rasters to June, July, and August, clipping each monthly raster to San
Francisco'sboundaries,creatingonerasterwitheachmonthlyrasterasaband,and
thenoutputtingthecombinedrastertoaportablerasterformat.Wewillconvertthe
combined raster to the GeoTIFF format.
WITH months AS ( -- extract monthly rasters
clipped to San Francisco
SELECT
prism.month_year,
ST_Union(
ST_Clip(prism.rast, 2,
ST_Transform(sf.geom,
4322), TRUE)
) AS rast
FROM prism
JOIN sfpoly sf
ON ST_Intersects(prism.rast,
ST_Transform(sf.geom,
4322))
WHERE prism.month_year BETWEEN
'2012-06-01'::date AND '2012-08-01'::date
GROUP BY prism.month_year
ORDER BY prism.month_year
), summer AS ( -- new raster with each monthly
raster as a band
SELECT
ST_AddBand(NULL::raster,
array_agg(rast)) AS rast
FROM months
)
SELECT -- export as GeoTIFF
ST_AsTIFF(rast) AS content
FROM summer;
