Database Reference
In-Depth Information
).mean
FROM prism
JOIN sfpoly sf
ON ST_Intersects(prism.rast,
ST_Transform(sf.geom, 4322))
GROUP BY prism.month_year
ORDER BY prism.month_year;
TheonlychangefromthepriorSQLqueryistheremovalofthe
WHERE
clauseand
theadditionofa
GROUP BY
clause.Since
ST_Union()
isanaggregatefunction,we
need to group the clipped rasters by
month_year
.
The output is as follows:
month_year |
mean------------+------------------2012-01-01 |
7.351250052452092012-02-01 |
7.961250007152562012-03-01 |
8.047499954700472012-04-01 |
9.43375003337862012-05-01 |
9.716250061988832012-06-01 |
10.69249999523162012-07-01 |
11.59749996662142012-08-01 |
11.718752012-09-01 | 11.15124988555912012-10-01
| 12.52500009536742012-11-01 |
11.08250010013582012-12-01 | 8.63750004768372
Basedontheresults,thelatesummermonthsof2012werethewarmest,thoughnot
by a huge margin.
How it works...
Byusingageometrytofiltertherastersintheprismtable,onlyasmallsetofrasters
neededclippingwiththegeometryandunioningtocomputethemean.Thismaxim-
izedthequeryperformanceand,moreimportantly,providedtheanswertoourques-
tion.
