SQL Functions Guide

Introduction

This guide will show you how to use PostPic to create, query and process images.
The rest of this document assumes that you have successfully compiled and installed PostPic in your database, and you have created example tables running the script create_example_tables_and_funcs.sql under examples.

Creating images

There are several ways to create an image value.

• Using postpic_import

postpic_import is an utility that can easy import multiple files from a client to the database. Usage:

postpic_import [connection options] [additional options]
Connection options:
        -U username
        -P password
        -h host
        -d database
Additional options: -c callback [ -u userdata ]  <filename> [<filename> ...]
        -c callback     callback is a stored procedure to call with each
                         eg. to insert the image in a table. The signature needs to be:
                         callback(i image, imgpath varchar, usrdata varchar)
        -u usrdata      optional userdata to pass to callback function

Suppose we want to import all the jpegs in the current directory, and add them to the images table, we can to this way:

postpic_import -U myuser -d mydb -c postpic_import_callback *.jpg

You just need to replace the connection information with the ones you use with psql. The sample callback provided takes care of inserting all the imported images in the ‘images’ table, using the filename as the ‘name’ field.

If you got no error messages, you can verify your images have been imported with psql. Eg.

postgres=# select count(*) from images;
 count
-------
    12
(1 row)

• From a large object: use image_from_large_object. Eg.

select lo_import('/path/to/file.jpg'); -- this returns the new_oid
insert into images (name, the_img) values ('a pic', image_from_large_object(new_loid));
select lo_unlink (new_loid) -- no more needed

Note: server-side lo_import can be used only by admin (database) users, and file must be readable by the server

• From a bytea value: use the image_from_bytea function

Works like image_from_large_object but expexts a bytea value.

Querying image attributes

A number of fuctions allows you to obtain quickly some basic information about an image:

	FUNCTION		width ( image )		RETURNS INT
	FUNCTION		height ( image )		RETURNS INT
	FUNCTION		date ( image )		RETURNS TIMESTAMP
	FUNCTION		iso ( image )		RETURNS INT
	FUNCTION		exposure_time ( image )		RETURNS FLOAT4
	FUNCTION		f_number ( image )		RETURNS FLOAT4

select width(the_img), height(the_img), size(the_img) -- image dimensions and largest size
select date(the_img) -- EXIF shoot date as a timestamp if present, else NULL
select exposure_time(the_img), f_number(the_img) -- shutter speed and aperture, if available

Image processing functions

Getting a thumbnail

	FUNCTION		thumbnail(i image, size INT)		RETURNS image
	FUNCTION		square(i image, size INT)		RETURNS image

‘size’ is the largest dimension of the resulting thumbnail. thumbnail computes the other dimension to preserve the aspect ratio, while square returns a ‘size x size’ thumbnail by cropping the image.

Resize

FUNCTION

resize(i image, w INT, h INT)

RETURNS image

Rotate

	FUNCTION		rotate(i image, degrees FLOAT4)		RETURNS image
	FUNCTION		rotate_left(i image)		RETURNS image
	FUNCTION		rotate_right(i image)		RETURNS image

Crop

FUNCTION

crop(i image, x INT, y INT, w INT, h INT)

RETURNS image

Drawing into an image

	FUNCTION		draw_text(i image, label VARCHAR)		RETURNS image
	FUNCTION		draw_text(i image, label VARCHAR, x INT, y INT)		RETURNS image
	FUNCTION		draw_text(i image, label VARCHAR, x INT, y INT, font-family VARCHAR, font-size INT)		RETURNS image
	FUNCTION		draw_text(i image, label VARCHAR, x INT, y INT, font-family VARCHAR, font-size INT, c color)		RETURNS image

FUNCTION

draw_rect(i image, rect BOX, c color)

RETURNS image

Other functions

The index function builds a montage (overview image) from an array of images.

FUNCTION

index(images image[], title VARCHAR, cols INT)

RETURNS image

A convenient way to build the array is via the aggregate function array_agg:

SELECT index (array_agg(square(the_img, 64)), 'My Images', 5) from images ;

The example builds an index with all the images, five on each row and thumbnailed.

Other references

The complete list of functions is available in the postpic.sql file used to create them.