4 Replies Latest reply: Jul 26, 2012 9:18 AM by Yann39 RSS

    ORDImageSignature to SQL/MM Still Image

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