I have a confession to make. I'm one of those folk who likes keeping my pictures in the database. File paths are just too annoying
and when some network guy/gal decides to do some spring cleaning, all the pictures referenced in your database, associated with important projects are misplaced. On top of that paths aren't as easily accessible across firewall connections as database connections, vary depending on OS/network and are just ancient. I can go on and on about why I like
my pictures in the database along side the data they pictorially describe, and some wise ass will tell me how wrong I am, so I'll stop here.
One thing I have always wanted to do is do all my cropping and resizing with the terseness and beauty that is SQL. With latest changes in PostGIS I can enjoy the same features with non-georeferenced pictures that I can with georeferenced ones. Thanks Bborie. We now have an ST_Resize function that doesn't require a known spatial reference system and many of the other gdal functions e.g. ST_ReSample have been changed in a similar fashion.
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))
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))
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
, ST_Height(rast)*CASE WHEN id < 3 THEN 1 ELSE 2 END) ) )
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.
In a previous article Waiting for PostGIS 2.1 ST_Resize we demonstrated a new raster function ST_Resize that is available in upcoming 2.1. In this article we'll talk about another function called ST_Tile, which similar to ST_Resize can work with bot
Tracked: Feb 05, 06:22