I work with scientists who analyze pictures. These pictures have a limited number of colors and a main component of their analysis is to understand how particular pixels are distributed within each image. So basically what I need to save for a full analysis is the position and color of each of the pixels. The images that I need to process are things similar to these:

sample image

Note that the shapes are often not squares or rectangles, but could be any shape. However, what is always the case is that:

  • There is one background color that will dominate 60-90% of the image
  • There are only 6 different colors in use
  • Images vary in size from 100x100 pixels to 1500x1000 pixels

We will be processing hundreds of thousands of images. So, this will quickly becomes huge if I want to store every pixel's color (large images have 1.5 million pixels). I haven't dealt with databases of this size before, nor have I dealt with storing pixel data from images, so I was wondering if you can give any advice with regards to:

  • Database recommendations (favoring PostgreSQL but any open source solution can be considered)
  • Tricks to store the data without things becoming so huge

One thing I'm thinking is to store the main background color for each image, and that way I only have to store those pixels that are NOT the background color. It would help, but I wonder if there's more that can be done...


Relational databases are not specially good for storing information that you would say "have very variable structure".

A fully normalized structure would probably represent an image of your kind with two tables:

  1. one for pictures (picture_id,background_color)
  2. and another for non_background_pixels_of_pictures (picture_id, x, y, color).

But this would probably be terribly space consuming and inconvenient for image processing. It all depends on which kind of "queries" you would be performing, and which tools you'd be using.

Alternatives (based on PostgreSQL):

  1. Store a reference to a standard image file (the path of the file). If this is done for "scientific" purposes, I guess you want a lossless storage format. This will allow to use very many libraries or programs that work directly with standard image formats (TIFF, JPEG, PNG, ...).
  2. Store the standard image data format as binary data (a "blob", using the bytea type in PostgreSQL, for instance; or the Large Objects Facility).
  3. Use some specific types or extensions for images.

For instance, with certain versions of PostgreSQL, you can use:

a. PostPic:

PostPic is an extension for the open source PostgreSQL dbms that enables image processing inside the database, like PostGIS does for spatial data. It adds the new 'image' type to the SQL, and several functions to process images and to extract their attributes.

b. PostgreSQL-IE

[...] To facilitate the development of new CBIR systems, we propose an image-handling extension to the relational database management system (RDBMS) PostgreSQL. This extension, called PostgreSQL-IE, is independent of the application and provides the advantage of being open source and portable. The proposed system extends the functionalities of the structured query language SQL with new functions that are able to create new feature extraction procedures, new feature vectors as combinations of previously defined features, and new access methods, as well as to compose similarity queries. PostgreSQL-IE makes available a new image data type, which permits the association of various images with a given unique image attribute.

NOTE: this seems to have been a doctoral thesis or some other research program... from 2006. Check also their download page.

c. pg_image, A bitmap image datatype for JPEG and PNG images.

This extension provides an IMAGE datatype for the storage of bitmap images, which verifies that the image data is in a recognized format (PNG or JPEG) and provides accessor functions for the image metadata (format, width, height) as well as the image data itself. Planned features include conversion between formats, cropping and scaling, annotating with text, and compositing of multiple images. Depends on the GD library for image loading and manipulation. Compatible with PostgreSQL 9.2 and 9.3beta2.

NOTE: Looks like it's not being maintained.

Other references:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.