Skip site navigation (1) Skip section navigation (2)

Re: Resizing images contained in oid fields

From: Juan Miguel <juanmime(at)ono(dot)com>
To: Michael Fuhr <mike(at)fuhr(dot)org>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Resizing images contained in oid fields
Date: 2005-03-24 08:15:09
Message-ID: 4242770D.9020905@ono.com (view raw or flat)
Thread:
Lists: pgsql-admin
Michael Fuhr wrote:

>On Mon, Mar 21, 2005 at 05:12:52PM +0100, juanmime(at)ono(dot)com wrote:
>  
>
>>I have a table where an oid field is used for saving images. I'm thinking
>>about getting a little snapshot of all images without downloading the full
>>images. I only want to download some entire images. I'm thinking in something
>>like this:
>>
>>create table images (
>>  id serial primary key,
>>  title varchar not null,
>>  photo_id oid
>>);
>>
>>select title, snapshot(photo_oid, 120, 120) as snap from images
>>
>>In this case, the snapshot function returns the resized snapshot of the original
>>image.
>>    
>>
>
>Why not store the resized image in another column?  Retrieval would
>probably be more efficient than running an algorithm over each image
>every time you wanted to fetch the "snapshots" (thumbnails?).
>
>  
>
>>I think that the core of function could be similar to this:
>>1) Obtain the image
>>2) Resize the Image to new size
>>3) Return the Resized Image
>>
>>Do you know if there exists a function like this ("snapshot")?
>>    
>>
>
>Not in the standard PostgreSQL installation.
>
>  
>
>>Otherwise, What is the type returned by the function ? What suitable procedure
>>language should I use ? What image library for the redimension ?
>>    
>>
>
>I'd probably make the return type bytea.  I'm sure several of the
>procedural languages (PL/Perl, PL/Tcl, PL/Python) have modules that
>interface to graphics libraries that can resize images; check their
>respective web sites (CPAN for Perl, etc.).  If I had to write this
>function I'd probably use C and ImageMagick, but I'd be more likely
>to generate the thumbnails on the client side and store them in
>another column.
>
>  
>
Tanks Michael,

This example is a simplification of the problem. I know that a good 
solution could be adding a column where storing the resized image. And I 
think that a solution, could be creating a trigger, lauched before 
insert or update, that autogenerate the thumbnail.

The new function could looks like this:
create or replace function createthumbnail (oid, integer, integer) 
returns oid as ' ......';

The params will be (oid of the big image, width, height) and returns the 
oid of the resized image.

and after. I will create the trigger that uses this function.

It is the best solution that I'm found.But there are two problems:
*) What happend if the size will be dinamically selected by the client 
application ?
*) We need two oids per image (big and resized).

Anyway, in my scene, the thumbnails dimensions will be constant, and the 
DB only will store a few hundred of images.

I think to program the image using the gd_lib library in "c". But I 
think that this library needs the image file for reading, or for 
writting. Because I'm using oids, I still don't know how to stablish 
this match...Phereaps I need a library that reads/writes from/to a 
buffer instead from/to a file.

Thanks Michael... If you are interented, I will send you the function/s 
when I will finish.


In response to

pgsql-admin by date

Next:From: Thilina GunasekaraDate: 2005-03-24 08:19:17
Subject: Re: Why this Query Plan is different
Previous:From: Tom LaneDate: 2005-03-24 05:59:29
Subject: Re: Why this Query Plan is different

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group