The thematic query
The below query uses the Raster ST_Union function to aggregate rasters (1 raster per state where value is aland (land area). State rasters are aligned
to our overall canvas so they can be unioned to form a single fatter raster. The fatter raster is 32 bit floating point. We then take the newly-formed single band
raster and convert to a viewable 4-band (RGBA) raster using ST_ColorMap. Then we convert the final to a PNG image.
canvas As ( SELECT
ST_SetSRID( ST_Extent(geom), 2163)
, '32BF'::text, 0, 0
) As rast
, '32BF'::text, aland, 0 )), 'bluered') )
FROM us_states CROSS JOIN canvas;
For those really interested in how the data was converted from NAD 83 long lat to National Atlas meters and also how we computed the bounds for the raster.
Here are the prep queries we ran.
ALTER TABLE us_states
ALTER COLUMN geom TYPE geometry(MultiPolygon,2163) USING ST_Transform(geom,2163);
SELECT ( Max( ST_YMax(geom) ) - Min( ST_YMin(geom) ) )
/ ( Max( ST_XMax(geom) ) - Min( ST_XMin(geom) ) ) As ratio
FROM us_states ;