|Oracle Visual Information Retrieval User's Guide and Reference
Part Number A85335-01
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.
Example 3-1 contains a script that creates the tablespaces needed for some of the examples in this chapter, creates a vir1 user with password vir1, identifies the default and temporary tablespace for this user's sessions, grants the necessary privileges to the vir1 user, and then connects as vir1 user and creates the ordvirdir directory.
CONNECT system/<system-password>; SET SERVEROUTPUT ON SET ECHO ON -- create tablespace tbs_1 CREATE TABLESPACE tbs_1 DATAFILE 'tbs_1.dbf' SIZE 20M MINIMUM EXTENT 64K DEFAULT STORAGE (INITIAL 64K NEXT 128K) LOGGING; -- create tablespace tbs_2 CREATE TABLESPACE tbs_2 DATAFILE 'tbs_2.dbf' SIZE 20M MINIMUM EXTENT 64K DEFAULT STORAGE (INITIAL 64K NEXT 128K) LOGGING; CREATE USER user vir1 IDENTIFIED BY vir1 DEFAULT TABLESPACE tbs_1 TEMPORARY TABLESPACE temp; GRANT CONNECT, RESOURCE, CREATE LIBRARY TO vir1; GRANT CREATE ANY DIRECTORY TO vir1; CONNECT vir1/vir1; CREATE OR REPLACE DIRECTORY ordvirdir as 'e:\oracle\ord\vir\demo'; GRANT READ ON DIRECTORY ordvirdir TO PUBLIC WITH GRANT OPTION;
If the ordvirdir directory will be used by any other schema, you must grant read access privilege to it as shown in the last line of Example 3-1.
Example 3-2 creates a new table of photographic images that includes the following information for each photograph:
CONNECT vir1/vir1; SET SERVEROUTPUT ON SET ECHO ON CREATE TABLE stockphotos (photo_id NUMBER, photographer VARCHAR2(64), annotation VARCHAR2(255), photo ORDSYS.ORDVir);
The SQL DESCRIBE table statement shows the following description:
DESCRIBE stockphotos; Column Name Null? Type ------------------------------ -------- ---- PHOTO_ID NUMBER PHOTOGRAPHER VARCHAR2(64) ANNOTATION VARCHAR2(255) PHOTO ORDSYS.ORDVIR
This example modifies an existing table to store an image with each row. Assume that the table in Section 3.2 already exists, but the table does not include the actual photographic images (the photo column).
Example 3-3 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.
CONNECT vir1/vir1; SET SERVEROUTPUT ON SET ECHO ON ALTER TABLE stockphotos ADD (photo ORDSYS.ORDVir);
Example 3-4 shows how to load two external images and import them into the database. This example assumes the image files are located in the ordvirdir directory. This directory name must be specified in uppercase in the INSERT statement.
CONNECT vir1/vir1; SET SERVEROUTPUT ON SET ECHO ON DECLARE image ORDSYS.ORDVIR; IdNum NUMBER; ctx RAW(4000) :=NULL; 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', 'Beach scene, balls on water', ORDSYS.ORDVIR.init('FILE','ORDVIRDIR','virdemo1.dat)); SELECT photo INTO image FROM stockphotos WHERE photo_id = IdNum FOR UPDATE; -- Import calls setProperties by default. -- Read the data into the database and set properties. image.import(ctx); -- 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; COMMIT; IdNum := 2; INSERT INTO stockphotos VALUES (IdNum, 'Martha Gray', 'Beach scene, balls on water', ORDSYS.ORDVIR.init('FILE','ORDVIRDIR','virdemo2.dat')); SELECT photo INTO image FROM stockphotos WHERE photo_id = IdNum FOR UPDATE; -- Import calls setProperties by default. -- Read the data into the database and set properties. image.import(ctx); -- 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; COMMIT; END; /
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-5 is an example of a control file (load1.ctl) used to load images into a table of ORDVir objects. This example assumes that the two images are located on drive e:\ordvirdir\ and that record 4 in the BEGINDATA section ends with a carriage return.
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)
TERMINATED BY EOF ) ) ) ) BEGINDATA 3, "John Doe", "vir image 3", e:\ordvirdir\virdemo1.dat, 4, "Jane Doe", "vir image 4", e:\ordvirdir\virdemo2.dat, E:\Scripts\ORDVir>sqlldr userid=vir1/vir1 control=load1.ctl log=load1.log SQL*Loader: Release 220.127.116.11.0 - Production on Thu May 25 10:14:02 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Commit point reached - logical record count 2
SQL*Loader does not set the properties of the images for you automatically. If you know the properties of the images ahead of time, you can specify the respective column names in the control file and the respective properties in each data row. In addition, you must also invoke the analyze method to generate an image signature for each image loaded and update the row. Example 3-6 shows a PL/SQL script that does both of these tasks.
DECLARE image ORDSYS.ORDVIR; BEGIN FOR I IN 3..4 LOOP SELECT photo INTO image FROM stockphotos WHERE photo_id = I FOR UPDATE; -- Set property attributes for the image data. image.SetProperties; -- 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 = I; COMMIT; END loop; END; /
Example 3-7 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.
CONNECT vir1/vir1; SET SERVEROUTPUT ON SET ECHO ON DECLARE image ORDSYS.ORDVIR; myid INTEGER :=1; BEGIN -- Select the desired photograph from the stockphotos table. SELECT photo INTO image FROM stockphotos WHERE photo_id = myid; END; /
Example 3-8 performs content-based retrieval; it finds images that are similar to an image chosen for comparison.
The program segment performs the following operations:
compare_sig) of the comparison image (
compare_img). Note: The program must have previously placed the comparison image in
CONNECT vir1/vir1; SET SERVEROUTPUT ON SET ECHO ON 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 AND photo_id <> 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-9 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( ). In this example, three of the three images compared to the comparison image were identical to the comparison image and showed a score of zero (0).
CONNECT vir1/vir1; SET SERVEROUTPUT ON SET ECHO ON SELECT Q.photo_id, ORDSYS.VIRScore(123) SCORE FROM stockphotos Q, stockphotos S WHERE S.photo_id=1 AND Q.photo_id != S.photo_id AND ORDSYS.VIRSimilar(Q.photo.signature, S.photo.signature, 'texture=1', 20.0, 123)=1; PHOTO_ID SCORE --------- --------- 2 0 3 0 4 0
To improve performance, you can create a domain index on the image signature attribute. Example 3-10 creates an index called imgindex.
CONNECT vir1/vir1; SET SERVEROUTPUT ON SET ECHO ON 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:
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.
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.7 for examples of retrieving similar images. As in the example, be sure to specify the query signature as the second parameter.
Example 3-11 converts an image from its current format to GIF format for display on a Web page. The program segment performs the following operations:
photo_id = 1) and places it in an image storage area.
photocolumn with content of the converted image.
CONNECT vir1/vir1; SET SERVEROUTPUT ON SET ECHO ON DECLARE image ORDSYS.ORDVIR; BEGIN -- Select the desired photograph from the stockphotos table. SELECT photo INTO image FROM stockphotos WHERE photo_id = 1 FOR UPDATE; -- Use Process method to perform the conversion. image.Process('fileFormat=GIFF'); -- Update the photo column with the contents of image. UPDATE stockphotos SET photo = image WHERE photo_id = 1; COMMIT; END; /
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.2 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-12 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.
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) -- CONSTRUCTORS STATIC FUNCTION init RETURN AnnotatedImage, STATIC FUNCTION init(srcType IN VARCHAR2, srcLocation IN VARCHAR2, srcName IN VARCHAR2, annotation IN VARCHAR2) RETURN AnnotatedImage --. --. --. ); / CREATE TYPE BODY AnnotatedImage AS MEMBER PROCEDURE SetProperties(SELF IN OUT AnnotatedImage) IS BEGIN SELF.image.setProperties; END SetProperties; MEMBER PROCEDURE Copy(dest IN OUT AnnotatedImage) IS BEGIN SELF.image.copy(dest.image); dest.annotation := SELF.annotation; END Copy; MEMBER PROCEDURE ProcessCopy(command in VARCHAR2, dest IN OUT AnnotatedImage) IS BEGIN SELF.image.processCopy(command,dest.image); dest.annotation := SELF.annotation; END ProcessCopy; -- CONSTRUCTORS STATIC FUNCTION init RETURN AnnotatedImage IS tmpImage ORDSYS.ORDVir; BEGIN tmpImage := ORDSYS.ORDVir.init; RETURN AnnotatedImage( tmpImage, -- ORDVir NULL); -- annotation END init; STATIC FUNCTION init( srcType IN VARCHAR2, srcLocation IN VARCHAR2, srcName in VARCHAR2, annotation IN VARCHAR2) RETURN AnnotatedImage IS tmpImage ORDSYS.ORDVir; BEGIN tmpImage := ORDSYS.ORDVir.init(srcType,srcLocation,srcName); RETURN AnnotatedImage( tmpImage, -- ORDVir annotation); -- annotation END init; -- . -- . -- . END; / SHOW ERRORS
After creating the new type, you can use it as you would any other type. Notice the static constructor function, init( ), in Example 3-13: newType(ORDSYS.ORDVir.init(). 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 or using the ORDVir.init( ) method.
CONNECT vir1/vir1; SET SERVEROUTPUT ON SET ECHO ON CREATE TABLE table my_example (id NUMBER,an_image AnnotatedImage); INSERT INTO my_example VALUES ( 1, AnnotatedImage.init('FILE','ORDVIRDIR','virdemo1.dat'), '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.annotation); UPDATE my_example SET an_image=myimage; END; / This image has a description of This is an example of using the VIR object as a subtype PL/SQL procedure successfully completed.
Just as a view is a virtual table, an object view is a virtual object table.
Oracle Corporation 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.
In Example 3-14, consider the following relational table (containing no ORDVir objects).
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));
CREATE OR REPLACE VIEW object_images_v AS SELECT id, ORDSYS.ORDVir( ORDSYS.ORDImage( ORDSYS.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. Object views also provide a way to use replication when your application uses objects. You can create an object view containing one or more object columns and also use replication. See Oracle8i Concepts for more information on defining, using, and updating object views.
The following scripts create and load a Visual Information Retrieval image table from a BFILE data source.
The following set of scripts:
The fifth script (setup_virschema.sql) automates this entire process by running each script in the required order. The last script (readvimage.sql) creates a stored procedure that performs a SELECT operation to read a specified amount of image data from the BLOB beginning at a particular offset until all the image data is read. To successfully load the image data, you must have a virdir directory created on your system containing the virdemo1.dat and virdemo2.dat files, and this directory and disk drive must be specified in the CREATE DIRECTORY statement in the create_viruser.sql file.
This script creates the virdemo tablespace with a data file named virdemo.dbf of 200 MB in size, with an initial extent of 64 K, a next extent of 128 K, and turns on table logging. Next, the viruser user is created and given connect, resource, create library, and create directory privileges followed by creating the image data load directory.
You must edit the create_viruser.sql file and either enter the system password in the connect statement or comment out the connect statement, and run this file in the system account. You must specify the disk drive in the CREATE DIRECTORY statement. Also, create the temp temporary tablespace if you have not already created it, otherwise this file will not run.
-- create_viruser.sql -- Connect as admin connect system/<system password>; -- Edit this script and either enter your system password here -- to replace <system password> or comment out this connect -- statement and connect as system before running this script. set serveroutput on; set echo on; -- Need system manager privileges to delete a user. -- Note: There is no need to delete viruser user, if you do not delete -- the virdemo tablespace, therefore comment out next line. -- drop user viruser cascade; -- Need system manager privileges to delete a directory. If there is no need -- to really delete it, then comment out next line. -- drop directory virdir; -- Delete then create tablespace. -- Note: It is better to not delete and create tablespaces, -- so comment this next line out. The create tablespace statement -- will fail because it already exists. -- drop tablespace virdemo including contents; -- If you do not make the preceding a comment and really want to delete the -- virdemo tablespace, remember to manually delete the virdemo.dbf -- file to complete the operation. Otherwise, you cannot create -- the virdemo tablespace again because the virdemo.dbf file already -- exists. Therefore, it might be best to create this tablespace -- once and not delete it. -- Create tablespace. create tablespace virdemo datafile 'virdemo.dbf' size 200M minimum extent 64K default storage (initial 64K next 128K) logging; -- Create viruser user. create user viruser identified by viruser default tablespace virdemo temporary tablespace temp; -- Note: If you do not have a temp tablespace already defined, you will -- have to create it first for this script to work. grant connect, resource, create library to viruser; grant create any directory to viruser; -- Note: If this user already exists, you get an error message -- when you try to create this user again. -- Connect as viruser. connect viruser/viruser -- Create the virdir load directory; this is the directory where -- the image files are residing. create or replace directory virdir as 'e:\oracle\ord\vir\demo'; grant read on directory virdir to public with grant option; -- Note: If this directory already exists, an error message will -- be returned stating so, and the operation will fail; -- ignore the message.
This script creates the VIR image table and then performs an insert operation to initialize the column object to empty for two rows. Initializing the column object creates the BLOB locator that is required for loading each row with BLOB data in a subsequent data load operation.
-- create_virtable.sql connect viruser/viruser; set serveroutput on; set echo on; drop table virtable; create table virtable (id number, virImage ordsys.ordvir); -- Insert a row with empty BLOB. insert into virtable values(1,ORDSYS.ORDVIR.init()); -- Insert a row with empty BLOB. insert into virtable values(2,ORDSYS.ORDVIR.init()); commit;
This script performs a SELECT FOR UPDATE operation to load the VIR image data by first setting the source for loading the VIR image data from a file, importing the data, setting the properties for the BLOB data, updating the row, and committing the transaction. To successfully run this script, you must copy two VIR image files to your VIRDIR directory using the names specified in this script, or modify this script to match the file names of your image files, point VIRDIR to the Visual Information Retrieval demo directory.
-- importvirimg.sql set serveroutput on set echo on -- Import the two files into the database. DECLARE obj ORDSYS.ORDVIR; ctx RAW(4000) := NULL; BEGIN -- This imports the image file virdemo1.dat from the VIRDIR directory -- on a local file system (srcType=FILE) and sets the properties. select virImage into obj from virtable where id = 1 for update; obj.setSource('FILE','VIRDIR','virdemo1.dat'); obj.import(ctx); update virtable set virimage = obj where id = 1; commit; -- This imports the image file virdemo2.dat from the VIRDIR directory -- on a local file system (srcType=FILE) and sets the properties. select virImage into obj from virtable where id = 2 for update; obj.setSource('FILE','VIRDIR','virdemo2.dat'); obj.import(ctx); update virtable set virimage = obj where id = 2; commit; END; /
This script performs a SELECT operation of the rows of the VIR image table, then gets the image characteristics of the BLOB data to check that the BLOB data is in fact loaded.
-- chkprop.sql set serveroutput on; --Connect viruser/viruser. --Query virtable for ORDSYS.ORDVir. DECLARE vimage ORDSYS.ORDVir; idnum integer; properties_match BOOLEAN; BEGIN FOR I IN 1..2 LOOP SELECT id into idnum from virtable where id=I; dbms_output.put_line('image id: '|| idnum); SELECT virImage into vimage from virtable where id=I for update; properties_match := vimage.checkProperties; IF properties_match THEN DBMS_OUTPUT.PUT_LINE('Check Properties Succeeded'); END IF; dbms_output.put_line('image height: '|| vimage.getHeight); dbms_output.put_line('image width: '|| vimage.getWidth); dbms_output.put_line('image MIME type: '|| vimage.getMimeType); dbms_output.put_line('image file format: '|| vimage.getFileFormat); dbms_output.put_line('BLOB Length: '|| TO_CHAR(vimage.getContentLength)); dbms_output.put_line('-------------------------------------------'); END loop; END; /
The folllowing are the results from running the script chkprop.sql:
SQL> @chkprop.sql image id: 1 Check Properties Succeeded image height: 400 image width: 600 image MIME type: image/bmp image file format: BMPF BLOB Length: 720054 ------------------------------------------- image id: 2 Check Properties Succeeded image height: 400 image width: 600 image MIME type: image/bmp image file format: BMPF BLOB Length: 720054 ------------------------------------------- PL/SQL procedure successfully completed.
This script runs each of the previous four scripts in the correct order to automate this entire process.
-- setup_virschema.sql -- Create viruser user, tablespace, and load directory to -- hold the files. @create_viruser.sql -- Create VIR imame table. @create_virtable.sql --Import 2 images and set properties. @importvirimg.sql --Check the properties of the images. @chkprop.sql --exit;
This script performs a SELECT operation to read a specified amount of image data from the BLOB, beginning at a particular offset until all the image data is read.
-- readvimage.sql set serveroutput on; set echo on; create or replace procedure readvimage as -- Note: ORDVir has no readFromSource method like ORDAudio -- and ORDVideo; therefore, you must use the DBMS_LOB package to -- read image data from a BLOB. buffer RAW (32767); src BLOB; obj ORDSYS.ORDVir; amt BINARY_INTEGER := 32767; pos integer := 1; read_cnt integer := 1; BEGIN Select t.virimage.getcontent into src from virtable t where t.id = 1; Select virimage into obj from virtable t where t.id = 1; DBMS_OUTPUT.PUT_LINE('Content length is: '|| TO_CHAR(obj.getContentLength)); LOOP DBMS_LOB.READ(src,amt,pos,buffer); DBMS_OUTPUT.PUT_LINE('start position: '|| pos); DBMS_OUTPUT.PUT_LINE('doing read '|| read_cnt); pos := pos + amt; read_cnt := read_cnt + 1; -- Note: Add your own code here to process the image data being read; -- this routine just reads data into the buffer 32767 bytes -- at a time, then reads the next chunk, overwriting the first -- buffer full of data. END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('----------------'); DBMS_OUTPUT.PUT_LINE('End of data '); END; / show errors
To execute the stored procedure, enter the following SQL statements: