PostGIS Cookbook - Second Edition

How to do it...

Let's adapt part of the query used in the last recipe to find out the average minimum temperature in San Francisco. We replace ST_SummaryStats() with ST_DumpAsPolygons(), and then return the geometries as WKT:

WITH geoms AS (SELECT ST_DumpAsPolygons(ST_Union(ST_Clip(prism.rast, 1, ST_Transform(sf.geom, 4269), TRUE)), 1 ) AS gv 
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 ) 
SELECT (gv).val, ST_AsText((gv).geom) AS geom 
FROM geoms; 

The output is as follows:

Now, replace the ST_DumpAsPolygons() function with ST_PixelsAsPolyons():