Waiting for PostGIS 2.1: ST_Resize not just for GIS

To try the new ST_Resize feature out, I pointed raster2pgsql at my folder of pictures

	raster2pgsql -F -Y C:/pictures/views/*.JPG bos_pics | psql -U postgres -d testpostgis21 -h localhost -p 5432

For those who have a religious objection to keeping rasters in the database, you can use the -R switch during load and the files will not be imported and will just be referenced as using code like below:

raster2pgsql -F -Y -R C:/pictures/views/*.JPG bos_pics_outdb | psql -U postgres -d testpostgis21 -h localhost -p 5432

If you do keep the rasters outside of the db, you need to make sure the postgres daemon account has read access to the folder and the path you specified in the raster2pgsql commandline is one the postgres account can access.

The speed of the exercises I will demonstrate seem just as fast with outdb rasters.

Most of these pictures are around 1.5-2MB and a resolution of around 3000x2500 pixels for these, the resize and union operations are sufficiently fast that you can do these operations on the fly. They are even for the union one under 10 seconds on my Windows 7 64-bit 9.2 install.

The ST_Resize function can take percentages (0 to 1) or pixel width/heights but doesn't necessarily maintain aspect ratio unless you set percentages the same. So you can do something like:

-- 400px width --
SELECT ST_AsPNG(ST_Resize(rast ,least(1.0,400.00/ST_Width(rast))
,least(1.0,400.00/ST_Width(rast))))
   FROM bos_pics
 WHERE filename = 'P8160012.JPG';
 
  
To ensure the resulting width is at most 400px and the width/height ratio is maintained.
 -- 200 pixel width --
 SELECT ST_AsPNG(ST_Resize(rast ,least(1.0,200.00/ST_Width(rast))
,least(1.0,200.00/ST_Width(rast))))
   FROM bos_pics
 WHERE filename = 'P8160012.JPG';
  
 

Even more exciting, you can stitch your pictures together even overlapping them if you want by also employing the ST_Union function.

WITH wresized AS (SELECT ROW_NUMBER() OVER() As id
   , ST_Resize(rast ,least(1.0,250.00/ST_Width(rast)) 
    ,least(1.0,250.00/ST_Width(rast))) As rast
    FROM bos_pics
    LIMIT 4)
SELECT ST_AsPNG(ST_Union(ST_SetUpperLeft(rast,mod(id,2)*ST_Width(rast)
 , ST_Height(rast)*CASE WHEN id < 3 THEN 1 ELSE 2 END) ) )
FROM wresized;

And viola, I have me a picture mesh.

I hear the purists scoffing, spatial technology should not be repurposed to make picture murals. It should be used for GIS.