Using ST_ColorMap for Thematic Maps

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.

-- total time 560ms (~0.5 secs)
-- POSTGIS="2.2.0dev r11808" GEOS="3.4.1-CAPI-1.8.0 r3895" 
-- PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.7.8" 
-- LIBJSON="UNKNOWN" RASTER PostgreSQL 9.3rc1, compiled by Visual C++ build 1600, 64-bit
WITH
 canvas As (  SELECT 
    ST_AsRaster(
         ST_SetSRID( ST_Extent(geom), 2163)
    , 500 --width is 500 pixels
    , (500*0.70256)::integer --height proportional to width
    , '32BF'::text, 0, 0 --set initial value and nodata to 0 
      ) As rast 
 FROM   us_states)
SELECT  ST_AsPNG(
         ST_ColorMap(
          ST_Union(ST_AsRaster(geom, canvas.rast
                , '32BF'::text, aland, 0 )), 'bluered') )
            As rast 
        FROM us_states CROSS JOIN canvas;

Just prep

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.

-- convert from NAD 83 long lat to national atlas meters --
ALTER TABLE us_states 
 ALTER COLUMN geom TYPE geometry(MultiPolygon,2163) USING ST_Transform(geom,2163);

-- used this query to compute 0.70256
SELECT  ( Max( ST_YMax(geom) ) - Min( ST_YMin(geom) ) ) 
  / ( Max( ST_XMax(geom) ) - Min( ST_XMin(geom) ) )  As ratio 
FROM us_states ;