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