Waiting for PostGIS 2.1 - ST_Tile

For this exercise I was running with: a 32-bit windows 7 32-bit 9.2.2 install and a 64-bit windows 7 on 9.2.2 install both grabbed from winnie's builds.

POSTGIS="2.1.0SVN r11079" GEOS="3.4.0dev-CAPI-1.8.0 r0" 
PROJ="Rel. 4.8.0, 6 March 2012" 
GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER

Cutting up helpless paintings

The ST_Tile function you can think of as the reverse of ST_Union. It cuts up things you can glue back together with ST_Union. Here we have our test subject - Mona, a 2835x4289 pixel resolution 3.3 MB file.

Load her up as a single record:

Or if you prefer to keep her out of the database but just register her. In order to use the outdb feature of ST_Tile, you'll need PostGIS 2.1 r11079 or higher.

If we were to cut her up into about 250x250 pixel tiles, we'd have 216 game pieces at our disposal. All are not of equal size because we didn't choose a size divisible by our original painting size.

-- suprisingly fast: 285 ms on windows 32-bit pg 9.2
-- 150ms on my windows 64-bit windows 7 pg-64-bit -- 

SELECT Max(ST_Width(tiled_rast)) As  max_width
 , MIN(ST_Width(tiled_rast)) As min_width
 , orig_width, count(*) 
 FROM
(SELECT ST_Tile(rast,250,250) As tiled_rast, ST_Width(rast) As orig_width 
        FROM (SELECT rast FROM pics where filename='Mona_Lisa.jpg' )
        As foo ) As foofoo
GROUP By orig_width;

 max_width | min_width | orig_width | count
-----------+-----------+------------+-------
       250 |        85 |       2835 |   216

Running against my outdb version, the speed was even more mind blowing

 -- 10 ms on windows 64-bit 9.2 64-bit --
SELECT Max(ST_Width(tiled_rast)) As  max_width
 , MIN(ST_Width(tiled_rast)) As min_width
 , orig_width, count(*) 
 FROM
(SELECT ST_Tile(rast,250,250) As tiled_rast
 , ST_Width(rast) As orig_width 
        FROM 
         (SELECT rast 
           FROM pics_outdb WHERE filename='Mona_Lisa.jpg' ) 
         As foo ) As foofoo
GROUP By orig_width;

Let's return 2 randomish pieces to see what she's made of. I was surprised the ST_AsPNG added so much overhead.

-- takes 2467ms to output as PNG on my 32-bit and  1,484ms on my 64-bit --
-- without ST_AsPNG call takes 380 ms on 32-bit ad 340ms on 64-bit: bit surprising -- 
SELECT ST_AsPNG(tiled_rast) FROM
(SELECT ST_Tile(rast,250,250) As tiled_rast
        FROM (SELECT rast FROM pics WHERE filename='Mona_Lisa.jpg' ) 
         As foo ) As foofoo
LIMIT 2 OFFSET 64;

Performing the same exercise on outdb was suprisingly shocking in speed difference.

 -- removing the ST_AsPNG call 20 ms, with ST_AsPNG -- 1510 ms
SELECT ST_AsPNG(tiled_rast) FROM
(SELECT ST_Tile(rast,250,250) As tiled_rast
        FROM (SELECT rast 
     FROM pics_outdb WHERE filename='Mona_Lisa.jpg' ) 
      As foo ) As foofoo
LIMIT 2 OFFSET 64;

Seamless Vector / Raster integration

Raster functionality can not only be used to abuse rasters, you can also use it to abuse geometries by dragging a hapless geometry into raster space doing your bidding and then dragging it back out into vector space. I'm going to save those exercises for other articles. Suffice to say that there are a whole menu of simplification and intersection processes you can apply to your geometries by tweaking the dial on the size of your virtual canvas that are difficult to accomplish with a pure vector play.