We have a confession to make. We're not GIS analysts; we just play one at parties. Truth is the bread and butter of our business involves pretty
boring stuff like e-Commerce, pricing (venture capital, private equity, travel, pension management), project management, work force management and all that other stuff that would bore a real GIS analyst to tears. Somehow we've got a lot of pictures to deal with particularly with project management and e-commerce work. So I was elated when Bborie checked in this new function ST_FromGDALRaster. With this function you can do all resizing and other manipulations
right in the database with standard type images like PNGs, bitmaps and anything else users will throw at you.
As mentioned before, we like keeping our work related pictures in the database, but every once in a while, we'd like to manipulate them and it would be nice not to have to keep many sizes of one image in the database. Having to drag them out of the database to do stuff with them is kind of a pain or to keep extra sizes is also a pain. We'd like to keep the original format we were given intact, but all other custom sizes people ask for do on the fly.
For these operations, I'm using:
POSTGIS="2.1.0SVN r11230" 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
PostgreSQL 9.2.2, compiled by Visual C++ build 1600, 64-bit
Using the buildbot builds generated by Winnie PostGIS buildbot.
Output pics in different format
This example takes a raster in any GDAL supported format (for this particular table we've got a mix of BMPs and JPGS), converts it to PostGIS raster so you have the full PostGIS raster arsenal at your disposal, resizes it and then outputs back
as a PNG. Speed is pretty fast (like in milliseconds) and bottleneck is the network bandwidth to output a big image from databae to web server which is pretty linear with size of image.
SELECT ST_AsPNG(ST_Resize(rast,0.5,0.5)) As png
FROM (SELECT id, st_fromgdalraster(large_pic) As rast
FROM aircraft_pics
WHERE id = 1) As a;
We build applications mostly with ASP.NET and PHP. Well a lot of ASP.NET. So we just output it to a browser stream.
If you want to spot check, you can do from PSQL, by wrapping your query in () and using large object support as detailed in Raster output with PSQL
Just get basic info about images
If you want to get basic stats about your images like how big they are you can do something like this:
SELECT count(*) As num_pics
, MIN(ST_Width(rast)) As min_width, MIN(ST_Height(rast)) As min_height
, Max(ST_Width(rast)) As max_width, MAX(ST_Height(rast)) As max_height
FROM (SELECT id, st_fromgdalraster(large_pic) As rast
FROM aircraft_pics
ORDER BY id LIMIT 100
) As a;
I was expecting the above to be as slow as molasses given I've got some decent size images in there and presumably it has to convert from a common garden variety raster format to PostGIS raster before it can check dimensions. To my astonishment the speed
was pretty fast.
num_pics | min_width | min_height | max_width | max_height
----------+-----------+------------+-----------+------------
100 | 143 | 92 | 2816 | 2112
(1 row)
Time: 464.720 ms
