3
Visual Information Retrieval Examples

This chapter provides examples of common operations with Visual Information Retrieval Cartridge. These operations include:

creating a new table with a column based on the cartridge
modifying an existing table to add a column based on the cartridge
loading images into the table (includes generating the signatures)
retrieving images (simple read operation; no content-based retrieval)
retrieving images similar to a comparison image (content-based retrieval)
converting an image from one format to another
extending the cartridge with new object types
using the cartridge with object views

The examples in this chapter use a table of photographs. For each photograph, a photo ID, the photographer's name, a descriptive annotation, and the photographic image are stored.

Reference information on the functions used in these examples is presented in Chapter 4.

3.1 Create a New Table Containing an Image

This example creates a new table of photographic images that includes, for each photograph, the following information:

photo ID number
photographer's name (variable text, up to 64 characters)
descriptive annotation (variable text, up to 255 characters)
photographic image (defined using the cartridge, stored in the database rather than referred to as an external file)

Example 3-1 creates the table.

Example 3-1 Create a New Table Containing an Image

CREATE TABLE stockphotos (photo_id NUMBER, photographer VARCHAR2(64),
             annotation VARCHAR2(255), photo ORDSYS.ORDVIRB);

The SQL DESCRIBE TABLE statement shows the following description:

Column Name                    Null?    Type
------------------------------ -------- ----
PHOTO_ID                                NUMBER
PHOTOGRAPHER                            VARCHAR2(64)
ANNOTATION                              VARCHAR2(255)
PHOTO                                   ADT(2880)

3.2 Add an Image Column to an Existing Table

This example modifies an existing table to store an image with each row. Assume that the table in Section 3.1 already exists, but does not include the actual photographic images.

Example 3-2 adds the photo column to the stockphotos table, storing the photographic image in the database itself rather than as a reference to the external file.

Example 3-2 Add an Image Column to an Existing Table

ALTER TABLE stockphotos ADD (photo ORDSYS.ORDVIRB);

3.3 Load Images into BLOBs From External Files

Loading images into BFILEs is easy. Loading images into BLOBs requires a few extra steps. First, create an empty BLOB, then load the image into a BFILE, and finally, copy the image from the BFILE to the BLOB.

Create a table to use as a temporary location for storing BFILEs as follows:

CREATE TABLE imgfiles (id NUMBER, photof ORDVIRF);

Next, create an alias for the directory where the images are stored:

CREATE DIRECTORY tempdir as 'C:\Images';

Example 3-3 loads a file image into a BLOB in the stockphotos table. The program segment performs these operations:

Generates an ID number for the row.
Inserts a row into the database with the intended values for all columns except photo, which is initialized as an empty BLOB.
Selects the newly inserted row for update.
Inserts the image into a BFILE.
Copies the image from the BFILE to the empty BLOB.
Sets the property attributes for the image.
Generates the signature.
Updates the photo column with content of the new image.
Example 3-3 Load Images into a Table

DECLARE
   buffer    RAW(2000);
   offset    NUMBER;
   amount    NUMBER;
   IdNum     NUMBER
   image     ORDSYS.ORDVIRB;
   imagef    ORDSYS.ORDVIRF;  
BEGIN
   -- Generate a photo ID and insert a row into the table.
   -- Note: empty_blob() is the initializer for the BLOB attribute.
   IdNum := 1;
   INSERT INTO stockphotos VALUES (IdNum, 'Janice Gray', 
          'Living room, full-length drapes, modern furniture',
          ORDSYS.ORDVIRB(empty_blob(),NULL,NULL,NULL,NULL,NULL,NULL,NULL));

   -- Move image into a temporary BFILE.
   INSERT INTO imgfiles VALUES (IdNum,

      ORDSYS.ORDVIRF(bfilename('TEMPDIR','1097_SAMP.JPG'),NULL,NULL,NULL,NULL,

       NULL,NULL,NULL));

   -- Select the BFILE image.
   SELECT photof INTO imagef FROM imgfiles a WHERE a.id=IdNum;
   -- Select the empty BLOB row for update.
   SELECT photo INTO image FROM stockphotos b WHERE b.photo_id = IdNum
      FOR UPDATE;

   -- Move image from the BFILE.
      imagef.CopyContent(image.photo);

   -- Set property attributes for the image data.
      image.SetProperties();

   -- Generate the image signature.
      ORDSYS.VIR.Analyze(image.content, image.signature);

   -- Update the photo column with the contents of image.
   -- This also stores the signature and other image-specific attributes.
   UPDATE stockphotos SET photo = image WHERE photo_id = IdNum;
END;

3.4 Retrieve an Image

Example 3-4 reads an image from the table and prepares it to be passed along, either directly to the end user or to the application for further processing. The program segment performs these operations:

Selects the desired photograph (where photo_id = myid) and places it in an image storage area.
Prepares the image for output.
Example 3-4 Retrieve an Image (Simple Read)

DECLARE
   image     ORDSYS.ORDVIRB;
   myid      INTEGER;
BEGIN
   
   -- Select the desired photograph from the stockphotos table.
   SELECT photo INTO image FROM stockphotos 
          WHERE photo_id = myid;
END;

3.5 Retrieve Images Similar to a Comparison Image

Example 3-5 performs content-based retrieval: it finds images that are similar to an image chosen for comparison. The program segment performs these operations:

Defines a cursor to perform the matching. The cursor sets the following weight values:
- global color: 0.5
- local color: 0.7
- texture: 0.1
- structure: 0.9
Generates the signature (compare_sig) of the comparison image (compare_img). Note: The program must have previously placed the comparison image content into compare_img.
Sets the threshold value at 75.
Selects the matching images, using the cursor.
Example 3-5 Retrieve Images Similar to a Comparison Image

DECLARE
   threshold    NUMBER;
   compare_sig  RAW(2000);
   compare_img  BLOB;
   photo_id     NUMBER;
   photographer VARCHAR2(64);
   annotation   VARCHAR2(255);
   photo        ORDSYS.ORDVIRB;
   
-- Define cursor for matching. Set weights for the visual attributes.
CURSOR getphotos IS
   SELECT photo_id, photographer, annotation, photo FROM stockphotos T
   WHERE ORDSYS.VIR.Similar(T.photo.Signature, compare_sig, 
                 'globalcolor="0.5" localcolor="0.7" texture="0.1"

                    structure="0.9"', threshold)=1;

BEGIN
   -- Generate signature of comparison image, which resides
   -- in compare_img.
   ORDSYS.VIR.Analyze(compare_img, compare_sig);

   -- Set the threshold value.
   threshold := 75;

   -- Retrieve rows for matching images.
   SELECT OPEN getphotos
   LOOP
      FETCH getphotos INTO photo_id, photographer, annotation, photo;
      EXIT WHEN getphotos%NOTFOUND;
      -- Display or store the results.
      .
      .
   END LOOP;
   CLOSE getphotos;
END;

3.6 Convert an Image to a Different Format

Example 3-6 converts an image from its current format to Windows bitmap (BMP) format. The program segment performs these operations:

Selects the desired photograph (where photo_id = 1234) and places it in an image storage area.
Uses the Process method to convert the format to BMP. (You do not need to know the current image format.)
Updates the photo column with content of the converted image.
Example 3-6 Convert an Image to a Different Format

DECLARE
   image     ORDSYS.ORDVIRB;
   
BEGIN
   
   -- Select the desired photograph from the stockphotos table.
   SELECT photo INTO image FROM stockphotos WHERE photo_id = 1234;
      
   -- Use Process method to perform the conversion.
   image.Process('fileFormat=BMPF');

   -- Update the photo column with the contents of image.
   -- This also stores the signature and other image-specific attributes.
   UPDATE stockphotos SET photo = image WHERE photo_id = 1234;

END;

3.7 Extend the Cartridge With a New Type

You can use the ORDVirF and ORDVirB types as the basis for a new type of your own creation. This task requires the Objects Option of Oracle8 Enterprise Edition.

create type AnnotatedImage as object 

   (  image ordsys.ordvirF,  

       description varchar2(2000),

       MEMBER PROCEDURE SetProperties(SELF IN OUT AnnotatedImage),
       MEMBER PROCEDURE CopyContent(dest IN OUT BLOB),
       MEMBER PROCEDURE ProcessCopy(command in VARCHAR2, dest IN OUT BLOB)
    );
/

create type body AnnotatedImage as 
  MEMBER PROCEDURE SetProperties(SELF IN OUT AnnotatedImage) IS
  BEGIN
    SELF.image.setProperties;
    SELF.description := 
 'This is an example of using the VIR cartridge as a subtype';
  END SetProperties;

  MEMBER PROCEDURE CopyContent(dest IN OUT BLOB) IS
  BEGIN
    SELF.image.copyContent(dest);
  END CopyContent;

  MEMBER PROCEDURE ProcessCopy(command in VARCHAR2, dest IN OUT BLOB) IS
  BEGIN
    SELF.image.processCopy(command,dest);
  END ProcessCopy;
END;
/

After creating the new type, you can use it as you would any other type. For example:

create or replace directory TEST_DIR as 'C:\TESTS';

create table my_example (id number,an_image AnnotatedImage);

insert into my_example values ( 1, 
    AnnotatedImage(
      ordsys.ordvirf( 
        bfilename('TEST_DIR','test1.jpg'),
        NULL,NULL,NULL,NULL,NULL,NULL,NULL),
      NULL)
    );
commit;

declare
  myimage AnnotatedImage;
begin
  select an_image into myimage from my_example;

  myimage.setProperties;

  dbms_output.put_line('This image has a description of '); 
  dbms_output.put_line( myimage.description);

  update my_example set an_image=myimage;
end;

3.8 Use Image Types With Object Views

Just as a view is a virtual table, an object view is a virtual object table.

Oracle provides object views as an extension of the basic relational view mechanism. By using object views, you can create virtual object tables from data-- of either built-in or user-defined types -- stored in the columns of relational or object tables in the database.

Object views provide the ability to offer specialized or restricted access to the data and objects in a database. For example, you might use an object view to provide a version of an employee object table that doesn't have attributes containing sensitivedata and doesn't have a deletion method. Object views also allow you to try object-oriented programming without permanently converting your tables. Using object views, you can convert data gradually and transparently from relational tables to object-relational tables.

Consider the following non-object image table:

create table flat (
   id            number,
   content       bfile,
   height        number,
   width         number,
   contentLength number,
   fileFormat    varchar2(64),
   contentFormat varchar2(64),
   compressionFormat varchar2(64),
   signature     raw(2000)
   );

You can create an object view on the flat table as follows:

create or replace view object_images_v as 
  select
      id,
      ordsys.ORDVirF(
         T.content,
         T.height,
         T.width,
         T.contentLength,
         T.compressionFormat,
         T.signature) IMAGE
   from flat T;

Object views provide the flexibility of looking at the same relational or object data in more than one way. Thus you can use different in-memory object representations for different applications without changing the way you store the data in the database. See the Oracle8 Concepts manual for more information on defining, using, and updating object views.

3 Visual Information Retrieval Examples