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)
Best Answer