3
Visual Information Retrieval Examples

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

• Creating a new table with an image column (based on the ORDVir object type)

• Modifying an existing table to add an image column

• 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)

• Creating a domain index

• Retrieving images similar to a comparison image using indexing operations (indexed content-based retrieval)

• Converting an image from one format to another

• Extending the product with new object types

• Using 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 the following information for each photograph:

• Photo ID number

• Photographer's name (variable text, up to 64 characters)

• Descriptive annotation (variable text, up to 255 characters)

• Photographic image (defined using Visual Information Retrieval and 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

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

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. Later, when the images are loaded, they can be stored either in the database itself or as references to external files.

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

ALTER TABLE stockphotos ADD (photo ORDSYS.ORDVir);

3.3 Load Images from External Files

Example 3-3 shows how to load external images and import them into the database.

Example 3-3 Load Images into a Table

DECLARE
   image     ORDSYS.ORDVIR;
   IdNum     NUMBER;
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.ORDVIR(ORDSYS.ORDImage( ORDSYS.ordsource(
              empty_blob(),'FILE','ORDVIRDIR','virdemo1.dat',sysdate, 0),
              NULL, NULL, NULL, NULL, NULL,NULL,NULL), NULL));
   SELECT photo INTO image FROM stockphotos WHERE photo_id = IdNum FOR UPDATE;
 
   -- Set property attributes for the image data.
      image.SetProperties;
   -- Read the data into the database.
      image.import(NULL);
 
   -- Generate the image signature.
      image.Analyze;
 
   -- 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 Load Images from External Files Using SQL*Loader

Oracle8i database utilities provide support for loading BLOBs using SQL*Loader. This utility can be used to load external image files into image tables in the database. For more information on SQL*Loader, see Oracle8i Utilities.

Example 3-4 is an example of a control file (filename.ctl) used to load images into a table of ORDVir objects.

Example 3-4 Load Image Using SQL*Loader

LOAD DATA
INFILE *
INTO TABLE stockphotos
APPEND
FIELDS TERMINATED BY '.'
(
  photo_id, photographer, annotation,
  photo column object
     (image column object
        (source column object
           (localData_fname FILLER CHAR(4000),
            localData LOBFILE(photo.image.source.localData_fname) RAW

                 TERMINATED BY EOF)
            )
         )
       )
 )
BEGINDATA
1, "John Doe", "vir demo image 1", virdemo1.dat,
2, "Jane Doe", "vir demo image 2", virdemo2.dat,

3.5 Retrieve an Image

Example 3-5 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 selects the desired photograph (where photo_id = myid) and places it in an image storage area.

Example 3-5 Retrieve an Image (Simple Read)

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

3.6 Retrieve Images Similar to a Comparison Image

Example 3-6 performs content-based retrieval: it finds images that are similar to an image chosen for comparison.

The program segment performs these operations:

1. Defines a cursor to perform the matching. The cursor sets the following weight values:
   • Global color: 0.2

   • Local color: 0.3

   • Texture: 0.1

   • Structure: 0.4

2. Generates the signature (compare_sig) of the comparison image (compare_img). Note: The program must have previously placed the comparison image in compare_img.

3. Sets the threshold value at 25.

4. Selects the matching images, using the cursor.

Example 3-6 Retrieve Images Similar to a Comparison Image

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

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

BEGIN
 -- Create BLOB object
   SELECT s.photo INTO compare_img FROM stockphotos s 

     WHERE photo_id = 1;

 -- Generate signature of comparison image, which resides in compare_img.
   compare_img.Analyze;
   compare_sig:= compare_img.signature;

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

   -- Retrieve rows for matching images.
   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;

Example 3-7 finds the photo_id and score of the image that is most similar to a comparison image with respect to texture. None of the other image characteristics is considered. This example uses the VIRScore( ) operator, which is an ancillary operator used in conjunction with the VIRSimilar( ) operator. The parameter passed to VIRScore( ) (123 in this example) is a reference to the same parameter passed in the call to VIRSimilar( ).

Example 3-7 Find photo_id and Score of Similar Image

SELECT Q.photo_id,  
       ORDSYS.VIRScore(123) SCORE 
       FROM stockphotos Q, stockphotos S 
       WHERE S.photo_id=1234 AND Q.photo_id != S.photo_id AND 
             ORDSYS.VIRSimilar(Q.photo.signature, S.photo.signature, 
                                'texture=1', 20.0, 123)=1;

3.7 Create a Visual Information Retrieval Domain Index

To improve performance, you can create a domain index on the image signature column. Example 3-8 creates an index called imgindex.

Example 3-8 Creating a VIR Index

CREATE INDEX imgindex ON stockphotos(photo.signature) 
     INDEXTYPE IS ordsys.ordviridx
     PARAMETERS ('ORDVIR_DATA_TABLESPACE = tbs_1, 
                  ORDVIR_INDEX_TABLESPACE = tbs_2');

As with any index, the tablespace (tbs_1 and tbs_2) must be created first.

The following recommendations are good starting points for further index tuning:

• ORDVIR_DATA_TABLESPACE -- Each signature requires around 350 bytes in this tablespace. The tablespace should be at least 350 times the number of signatures in the table.

• ORDVIR_INDEX_TABLESPACE -- The size of the tablespace should be 100 times the size of the initial and final extents specified. For example, if an extent is 10KB, the tablespace size should be 1MB. The initial and final extents should be equal to each other. The size of the tablespace should also be approximately equal to the size of ORDVIR_DATA_TABLESPACE.

• Typically, it will be much faster if you create the index after the images have been loaded into the database and analyzed.

  Note that if a signature is corrupt, or is in incorrect byte order for your system, the CREATE INDEX statement exits and index creation is not completed. This incomplete index is not usable. Delete the index, delete the corrupt signature or correct the byte order (see the Convert( ) Operator), and re-create the index.

  To prevent wasting time when creating an index for large tables, ensure that the signatures are in the correct format before creating the index. This can be accomplished by generating the signatures on the same system as they are used, or by using the Convert( ) operator.

• Creating an index for large tables can be very time consuming. When importing a large number of images, you should postpone index creation until after the import completes. Do this by specifying the following parameters to the import statement: INDEXES=N and INDEXNAME=<filename>. See Oracle8i Utilities for details.

• Rollback segments of an appropriate size are required. The size depends on the size of your transactions, such as, how many signatures are indexed at one time.

• Analyze the new index. See Section 2.4.

  Note: A domain index cannot be created on a facial image signature column in this release of Visual Information Retrieval.

3.8 Retrieve Images Similar to a Comparison Image Using Index Operations

Queries for indexed and nonindexed comparisons are identical. The Oracle optimizer uses the domain index if it determines that the first argument passed to the VIRSimilar( ) operator is a domain-indexed column. Otherwise, the optimizer invokes a functional implementation of the operator that compares the query signature with the stored signatures, one row at a time.

See Section 3.6 for examples of retrieving similar images. As in the example, be sure to specify the query signature as the second parameter.

3.9 Convert an Image to a Different Format

Example 3-9 converts an image from its current format to GIF format for display on a Web page. The program segment performs these operations:

1. Selects the desired photograph (where photo_id = 1234) and places it in an image storage area.

2. Uses the process( ) method to convert the format to "GIFF". (You do not need to know the current image format.)

3. Updates the photo column with content of the converted image.

Example 3-9 Convert an Image to a Different Format

DECLARE
   image     ORDSYS.ORDVIR;
   
BEGIN
   
   -- Select the desired photograph from the stockphotos table.
   SELECT photo INTO image FROM stockphotos WHERE photo_id = 1234 FOR UPDATE;
      
   -- Use Process method to perform the conversion.
   image.Process('fileFormat=GIFF');
    -- 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.10 Extend the Object Type

You can use the ORDVir type as the basis for a new type of your own creation.

For example, the original table created in Section 3.1 had a column called annotation. You could move that annotation into a new object encapsulating the ORDVir type. This new type can have additional attributes and methods.

To simulate subtyping (which is not supported in this release), you can define wrappers for the ORDVir methods and access functions. Example 3-10 adds the annotation column to the ORDVir type and defines wrappers for three procedures, making a new type called AnnotatedImage. The ellipses in this example indicate that more methods could be included, but they are not being shown.

Example 3-10 Extending the ORDVir Type

create type AnnotatedImage as object 

   (  image ordsys.ordvir,  

       annotation varchar2(2000),

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

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

  MEMBER PROCEDURE Copy(dest IN OUT AnnotatedImage) IS
  BEGIN
    SELF.image.copy(dest.image);
    dest.annotation := SELF.annotation;
  END CopyContent;

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

After creating the new type, you can use it as you would any other type. Notice the encapsulation structure in Example 3-11: newType(ORDVir(OrdImage(Ord
Source( )))). Because user-defined constructors are not supported in this release, inserting into a simulated subtype is only possible by being aware of the full encapsulation hierarchy.

Example 3-11 Using an Extended Type

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.ordvir(
       ordsys.ordimage( 
        ordsys.ordsource(empty_blob(),'file','TEST_DIR','test1.jpg',sysdate,0),
        NULL,NULL,NULL,NULL,NULL,NULL,NULL),
      NULL),
    'some text describing the image');
commit;

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

  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.11 Use Image Type 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 does not have attributes containing sensitive data and does not 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,
   localData     BLOB,
   srcType       varchar2(4000),
   srcLocation   varchar2(4000),
   srcName       varchar2(4000),
   updateTime    date,
   local         number,
   height        integer,
   width         integer,
   contentLength integer,
   fileFormat    varchar2(4000),
   contentFormat varchar2(4000),
   compressionFormat varchar2(4000),
   mimeType      varchar2(4000)
   signature     raw(2000)
   );

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

create or replace view object_images_v as 
  select
      id,
      ordsys.ORDVir(
        ordsys.ORDImage(
          ordSource(
             T.localData, T.srcType, T.srcLocation, T.srcName, 

              T.updateTime, T.local),
          T.height,
          T.width,
          T.contentLength,
          T.fileFormat,
          T.contentFormat
          T.compressionFormat,
          T.mimeType),
        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. You can use different in-memory object representations for different applications without changing the way you store the data in the database. See Oracle8i Concepts for more information on defining, using, and updating object views.

---