How to load images one by one from a postgres database?

5 views (last 30 days)
Dear MATLAB users,
For an image analysis project I want to load images from a postgres db stored as datatype LO (Large Object). The RGB images of plants are taken every day for a certain period and other (meta-)data as weigth is also stored in this db. Is there a way to get the images one by one out of the database, load it into a matrix (like the result after an imread command; I guess it's stored as binaries, so it must be reshaped) so I can do some analysis on a single image at a time? I don't want to export them to a folder and read them in one by one afterwards, because the amount of images is huge (up to tens thousand) which takes a lot of storage cpacity, it quickly can become a mesh with multiple folder structures and you have to connect the other data in the db to the image again. I am on a client side and my login has superuser priviliges at the server side. Below the code I use, including a SQL statement for extraction of normal data (numbers), which works for all data, and a couple of the SQL statements I have tried so far to load an image. If you need more information, please let me know. Thank you all in advance for your help.
%%Set environment
setDBPref % Other function to set preferences with setdbprefs
%%Make connection to database.
% Using JDBC driver.
conn=database('<name_db>','<user>','<pasword>','org.postgresql.Driver','jdbc:postgresql://<ip db server>:5432/');
%%Retrieve data from database.
id_tag='00000001';
% Next SQL statement to extract only numbers works perfect:
sqlstring=sprintf('SELECT snapshot.weight_before, time_stamp FROM public.snapshot WHERE snapshot.id_tag = ''%s'' ORDER BY time_stamp ASC', id_tag);
output = fetch(exec(conn,sqlstring));
% but this does not work--------------------------
%%Retrieve image from database
id_tag='00000001';
% none of the statements below works:
sqlstring=sprintf('SELECT lo_open(conn,image_oid FROM tile WHERE tile.id= ''%s'', INV_READ)',id_tag);
sqlstring=sprintf('SELECT (lo_read(image_oid)) FROM tile WHERE tile.id= ''%s''',id_tag);
sqlstring=sprintf('SELECT lo_open(image_oid) FROM tile WHERE tile.id= ''%s''',id_tag);
sqlstring=sprintf('SELECT lo_export(image_oid, ''C:/tmp'' ) FROM tile WHERE tile.id= ''%s''',id_tag);
sqlstring=sprintf('SELECT (lo_read(image_oid)) FROM tile WHERE tile.id= ''%s''',id_tag);
images = fetch(exec(conn,sqlstring));
%%Close database connection
close(conn)

Accepted Answer

Quattro
Quattro on 26 Aug 2011
Loading an image directly into Matlab without writing it (temporarily) to a local folder on the server side seems to be impossible. This means you have to make sure your account has the right priviliges on the server side for exporting the images, as well for the database as for the folder to save the images (temporarily).
This is how the query must look like:
% EXPORT IMAGE AND LOAD INTO MATLAB
sqlstringImage=sprintf('SELECT lo_export(tile.image_oid, ...
''/tmp/image_view/%s/%i.png'') FROM tile WHERE ...
image_oid = (select image_oid from tile where ...
id = ''%i'')',<name_db>,ID,ID);
exec(conn,sqlstringImage);
folder=sprintf('Y:/%s',<name_db>);
imageList = dir(fullfile(folder,'*.png'));
I=imread(fullfile(folder,imageList.name)));
% Do your image analysis
....
% Clean temporary file
delete(fullfile(folder,imageList.name)));
The result of the query is only one image, thus imageList in this case only contains one cell array. Probably not the best solution, but it works.

More Answers (0)

Products

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!