This discussion is archived
4 Replies Latest reply: Jul 26, 2012 7:18 AM by Yann39 RSS

ORDImageSignature to SQL/MM Still Image

Yann39 Journeyer
Currently Being Moderated
Hi,

We have migrated our application from Oracle 10g to Oracle 11g.
All our images are stored in the database as ORDImage.
And I was using ORDImageSignature for image comparison.

But as ORDImageSignature methods are depreciated in Oracle 11g, it seems I have to use SQL/MM Still Image standard methods to do the image comparison.

<li>Here is how I did the image comparison using ORDImageSignature :
procedure link_photo_to_point(in_pt_id in number, in_img_id in number, out_message out varchar2) is
     l_exist number := 0;
begin
     select count(*) into l_exist 
     from photos inner join photo_components on phco_phot_id = phot_id
     where ordsys.imgsimilar(phot_signature, (select phot_signature from photos where phot_id = in_img_id),'color=1,texture=1,shape=1,location=1', 0, 1) = 1
     and phco_spatial_id = in_pt_id;
     if (l_exist > 0) then
          dbms_output.put_line('Error : a similar photo is already linked to the point' || in_pt_id);
     else
          insert into photo_components(phco_spatial_id, phco_phot_id) values (in_pt_id, in_img_id);
          dbms_output.put_line('The photo was successfully linked to the point' || in_pt_id);
     end if;
end;
<li>And here is how I try to do the same using SQL/MM Still Image :
procedure link_photo_to_point(in_pt_id in number, in_img_id in number, out_message out varchar2) is
     l_exist number := 0;
     l_score double precision;
     l_img_obj si_stillimage;
     l_img_blob blob;
     l_featurelist si_featurelist;
begin
     select p.phot_source.source.localdata into l_img_blob from photos p where p.phot_id = in_img_id;
     l_img_obj := new si_stillimage(l_img_blob);
     select count(*) into l_exist from photos p inner join photo_components on phco_phot_id = phot_id
     where l_featurelist.si_score(new si_stillimage(p.phot_source.source.localdata)) = l_score
     and phco_spatial_id = in_pt_id;
     if (l_exist > 0) then
          dbms_output.put_line('Error : a similar photo is already linked to the point' || in_pt_id);
     else
          insert into photo_components(phco_spatial_id, phco_phot_id) values (in_pt_id, in_img_id);
          dbms_output.put_line('The photo was successfully linked to the point' || in_pt_id);
     end if;
end;
And of course I get the following message as I don't know how to populate the si_featurelist variable so it can contains the image features...
ORA-30625: method dispatch on NULL SELF argument is disallowed
I think the documentation is not clear about how to do that.

Any help would be much appreciated.

Thanks.

Yann.
  • 1. Re: ORDImageSignature to SQL/MM Still Image
    Yann39 Journeyer
    Currently Being Moderated
    OK I finally managed to make it work.

    I was working with this 11g documentation, that does not contain any example : http://isu.ifmo.ru/docs/doc112/appdev.112/e10776/ap_stimgref.htm#CHDICDFB
    But I have finally found that old 10g documentation that is very clearer ! http://docs.oracle.com/cd/B14117_01/appdev.101/b10829/mm_stimgref006.htm

    So here is a solution, if it can help :
    procedure link_photo_to_point(in_pt_id in number, in_img_id in number, out_message out varchar2) is
         l_exist number := 0;
         l_score double precision;
         l_img_obj si_stillimage;
         l_img_blob blob;
         l_featurelist si_featurelist;
         l_avgcolor si_averagecolor;
         l_colorhist si_colorhistogram;
         l_poscolor si_positionalcolor;
         l_texture si_texture;
    begin
         select p.phot_source.source.localdata into l_img_blob from photos p where p.phot_id = in_img_id;
         l_img_obj := new si_stillimage(l_img_blob);
         l_avgcolor := new si_averagecolor(l_img_obj);
         l_colorhist := new si_colorhistogram(l_img_obj);
         l_poscolor := new si_positionalcolor(l_img_obj);
         l_texture := new si_texture(l_img_obj);
         l_featurelist := new SI_FeatureList(l_avgcolor,1,l_colorhist,1,l_poscolor,1, l_texture,1);
         select count(*) into l_exist from photos p inner join photo_components on phco_phot_id = phot_id
         where l_featurelist.si_score(new si_stillimage(p.phot_source.source.localdata)) = 0
         and phco_spatial_id = in_pt_id;
         if (l_exist > 0) then
              dbms_output.put_line('Error : a similar photo is already linked to the point' || in_pt_id);
         else
              insert into photo_components(phco_spatial_id, phco_phot_id) values (in_pt_id, in_img_id);
              dbms_output.put_line('The photo was successfully linked to the point' || in_pt_id);
         end if;
    end;
    Big thanks to me :D
  • 2. Re: ORDImageSignature to SQL/MM Still Image
    896774 Newbie
    Currently Being Moderated
    Hi Yann,
    Can you explain what you are trying to do ?
    From my understanding,
    photos: Table were resides all images
    photo_components: Table linking images to points. All those images should not be similar.
    in_img_id: Image Id
    in_pt_id: Point Id
    You will link image in_img_id to point in_pt_id if there are no images already linked to point in_pt_id which are already similar to image in_img_id
    Is that what you are doing ?
    Can you tell me if similarity in your context mean in fact equal ? Because you are looking only for the following results. imgsimilar = 1 and si_score = 0
    Why are you adding parameters 0 and 1 when calling imgsimilar ? What will does parameters do exactly ?
    Thanks for your answer.Hi Yann,
    Can you explain what you are trying to do?
    From my understanding,
    photos: Table were resides all images
    photo_components: Table linking images to points. All those images should not be similar.
    in_img_id: Image Id
    in_pt_id: Point Id
    You will link image in_img_id to point in_pt_id if there are no images already linked to point in_pt_id which are already similar to image in_img_id
    Is that what you are doing?
    Can you tell me if similarity in your context mean in fact equal? You are looking only for the following results. imgsimilar = 1 and si_score = 0
    Why are you adding parameters 0 and 1 when calling imgsimilar ? What will does parameters do exactly?
    Thanks for your answer.
  • 3. Re: ORDImageSignature to SQL/MM Still Image
    Yann39 Journeyer
    Currently Being Moderated
    Hi,

    sorry I just see your answer...

    My last code above DOES NOT WORK, we have temporarily removed the image comparison until now (we had more urgent things to do).
    I only come back to the problem, to try to really make it work this time !
    From my understanding,
    photos: Table were resides all images
    photo_components: Table linking images to points. All those images should not be similar.
    in_img_id: Image Id
    in_pt_id: Point Id
    You will link image in_img_id to point in_pt_id if there are no images already linked to point in_pt_id which are already similar to image in_img_id
    Is that what you are doing ?>

    Yes,
    - PHOTOS is the table that holds photos as ORDImage.
    - COMPONENTS is the table that holds points.
    - PHOTO_COMPONENTS is the table linking photos to points.

    I need to link an image to a point only if the specified image is not already linked to the specified point.
    In the PHOTOS table, 2 images can be exactly the same but with a different filename... It is why I need to compare the images.
    Can you tell me if similarity in your context mean in fact equal ? Because you are looking only for the following results. imgsimilar = 1 and si_score = 0
    Why are you adding parameters 0 and 1 when calling imgsimilar ? What will does parameters do exactly ?>

    I'm not using imgsimilar anymore as I have to use SQL/MM Still Image methods.
    In my case, similarity means exactly the same image, it is why I am comparing averagecolor, colorhistogram, positionalcolor and texture and set precision to 1 :
    l_featurelist := new SI_FeatureList(l_avgcolor,1,l_colorhist,1,l_poscolor,1, l_texture,1)
    If l_featurelist.si_score(image) returns 0, it means image is exactly the same.

    The problem now is that an exception is raised ( sqlcode: 1, sqlerrm: User-defined Exception ) when the comparison is performed.
    Using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE it tells me the following :
    ORA-06512: à "ORDSYS.SI_STILLIMAGE", ligne 27
    ORA-06512: à "ORDSYS.SI_MKSTILLIMAGE1", ligne 6
    ORA-06512: à "SURV.APP_CORE", ligne 212
    Line 212 is the line that checks if a similar image already exists.
    It seems the problem is that it does not accept p.phot_source.source.localdata as parameter.

    I will continue investigating...
  • 4. Re: ORDImageSignature to SQL/MM Still Image
    Yann39 Journeyer
    Currently Being Moderated
    OK, I think I have found my error. Here is what I did:

    I first tried to add a new column to the PHOTOS table to store directly the StillImage object:
    alter table PHOTOS add phot_source2 SI_Stillimage;
    update photos p set p.phot_source2 = si_stillimage(p.phot_source.source.localData) where p.phot_id < 10;
    It worked.

    Then I have implemented a minimal example, that is comparing image 2 to the first 10 images from the PHOTOS table :
    DECLARE
         l_img_obj     si_stillimage;
         l_avgcolor     si_averagecolor;
         l_colorhist     si_colorhistogram;
         l_poscolor     si_positionalcolor;
         l_texture     si_texture;
         l_featurelist     si_featurelist;
         l_blob          BLOB;
         l_count          INTEGER;
    BEGIN
         -- get the blob from the ordimage
         SELECT p.phot_source.source.localdata
         INTO l_blob FROM photos p
         WHERE phot_id = 2;
         -- build the stillimage object from the blob
         l_img_obj := NEW si_stillimage(l_blob);
         -- get image features and build the featureList object
         l_avgcolor    := NEW si_averagecolor(l_img_obj);
         l_colorhist   := NEW si_colorhistogram(l_img_obj);
         l_poscolor    := NEW si_positionalcolor(l_img_obj);
         l_texture     := NEW si_texture(l_img_obj);
         l_featurelist := NEW si_featurelist(l_avgcolor, 1, l_colorhist, 1, l_poscolor, 1, l_texture, 1);
         -- check if a similar image is found in the table
         SELECT COUNT(1)
         INTO l_count
         FROM photos p
         WHERE si_scorebyftrlist(l_featurelist, p.phot_source2) = 0
         AND phot_id < 10;
         -- show message
         dbms_output.put_line(l_count || ' similar photo(s) found');
    END;
    /
    It worked.

    Then I tried to replace <font face="courier">p.phot_source2</font> with <font face="courier">si_mkstillimage1(p.phot_source.source.localdata)</font> (wich was causing the problem), and it worked too !

    Then I tried to change <font face="courier">phot_id &lt; 10</font> to <font face="courier">phot_id &lt; 20</font>, and finally it failed.

    So I finally understood that I had some null values in the ORDImage column... that was causing the problem.

    Indeed calling SI_StillImage() with a null parameter gives the following error message in the above program :

    >
    ORA-06510: PL/SQL: unhandled user-defined exception
    ORA-06512: at "ORDSYS.SI_STILLIMAGE", line 27
    ORA-06512: at "ORDSYS.SI_MKSTILLIMAGE1", line 6
    ORA-06512: at line 24
    >

    All is working fine now.
    I hope this thread can help somebody else :)

    Bye.

    Yann.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points