Waiting for PostGIS 2.1: ST_SetValues - The road to thematic maps

BostonGIS BostonGIS Home About Postgres OnLine Journal PostGIS in Action

Saturday, March 09. 2013

Posted by Regina Obe in postgis postgresql, postgis_raster, waiting_postgis_21

Comments (0)
Trackbacks (0)

Waiting for PostGIS 2.1: ST_SetValues - The road to thematic maps

With all the cool new raster functions coming in postGIS 2.1, it's really hard to pick a favorite. One of the functions I feel like I've been waiting eons for is ability to burn a set of geometry values in a raster with one SQL statement. My dream came true with ST_SetValues. Check this out:

A side note, people have asked how we visualize these things. We used the ad hoc web viewer we had created that we described in Minimalist Web based ASP.NET PostGIS 2+ and brother version PHP viewer version.

We do a lot of ASP.NET development and for using the ASP.NET viewer, we just set the credentials, in web.config, install the helper function in our test database running PostGIS, and then right-click View in Browser the postgis_viewer.htm with Visual Studio or Visual Web Express. The PHP version is setup much the same and in fact the postgis_viewer.htm are identical except the php version calls a PHP handler file and the ASP.NET one calls an ASP.NET http handler. The client side has some jquery magic that feeds that posts the query to the handler. The handler does the query and outputs the results which are then injected via jquery call.

Which I generated with the code below. Admittedly I could have come up with a more interesting topic than land and water area, but hopefully you get the point.

-- 7,254ms on Wndows 2003 64-bit running PostGIS 2.1.0SVN
-- PostgreSQL 9.2.3, compiled by Visual C++ build 1600
-- , 64-bit POSTGIS="2.1.0SVN r11008" 
WITH usext AS (SELECT ST_Extent(ST_Transform(geom_4269,2163)) As ext
        , Max(aland) As max_aland, MIN(aland) As min_aland
        , MAX(awater) As max_awater,  MIN(awater) As min_awater
 FROM   staging.tl_2012_state)
 , canvas AS (SELECT ST_AddBand(
                ST_AddBand(
            ST_MakeEmptyRaster( ( (ST_XMax(ext) - ST_XMin(ext))/20000)::integer
              , ( (ST_YMax(ext) - ST_YMin(ext))/20000)::integer
            , ST_XMin(ext), ST_YMax(ext),20000, -20000, 0, 0, 2163) 
            , '8BUI'::text, 0, 0 ) 
            , '8BUI'::text, 0, 0)
            As rast 
        FROM usext)
, heatmap As (
    SELECT array_agg(
        (ST_Transform(geom_4269,2163)
         , ((aland - min_aland)/(max_aland - min_aland)*255)::integer)::geomval
                ) As gvals_land
    , array_agg(
        (ST_Transform(geom_4269,2163)
         , ((awater - min_awater)/(max_awater- min_awater)*255)::integer)::geomval
                ) As gvals_water
    FROM staging.tl_2012_state As s CROSS JOIN usext
     )
SELECT 
   ST_AddBand(ST_SetValues(
        ST_SetValues(rast 
        , 1, heatmap.gvals_land)
                , 2, heatmap.gvals_water)
        , '8BUI'::text,  0,0) As rast 
FROM canvas CROSS JOIN heatmap;

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

No comments

Add Comment

Name
Email
Homepage
In reply to
Comment	E-Mail addresses will not be displayed and will only be used for E-Mail notifications. To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly. Enter the string from the spam-prevention image above:
	Remember Information? Subscribe to this entry