Oracle8i Visual Information Retrieval User's Guide and Reference
Release 8.1.5






Prev Next

Visual Information Retrieval Examples

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

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:

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

   image     ORDSYS.ORDVIR;
   IdNum     NUMBER;
   -- 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),
   SELECT photo INTO image FROM stockphotos WHERE photo_id = IdNum FOR UPDATE;
   -- Set property attributes for the image data.
   -- Read the data into the database.
   -- Generate the 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;

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

INTO TABLE stockphotos
  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)

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

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

   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(, compare_sig, 
                 'globalcolor="0.2" localcolor="0.3" texture="0.1"
structure="0.4"', threshold)=1; BEGIN -- Create BLOB object SELECT 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 
                                '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
                  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:

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

   image     ORDSYS.ORDVIR;
   -- 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.
    -- 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;


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, 
    'some text describing the image');

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


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

  update my_example set an_image=myimage;

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 
             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.


Copyright © 1999 Oracle Corporation.

All Rights Reserved.