使用SQL/MM静止图像将BLOB图像与存储为ORDImage的图像进行比较 [英] Compare a BLOB image to images stored as ORDImage using SQL/MM Still Image

问题描述

我正在使用 Oracle 11g r2 .

我有一个表，将图像存储为 ORDImage :

  PHOTOS(phot_id整数，phot_filename varchar2(256)，phot_source ordsys.ordimage) 

和另一个临时表，该表将用户上传的图像存储为 BLOB .

  INSERT_TEMP(itemp_id整数，itemp_source blob) 

我只想通过比较两个图像将BLOB图像移动到PHOTOS表(如果尚不存在).我需要使用 SQL/MM静态图像方法，因为Oracle 11g中不推荐使用ORDImageSignature方法.

这是代码:

 声明[...]开始[...]-从临时表中获取Blob(作为参数传递的in_id)从insert_temp中选择itemp_source到l_img_blob中，其中itemp_id = in_id；-从Blob构建stillimage对象l_img_obj:= new si_stillimage(l_img_blob);-获取图像特征并构建featureList对象l_avgcolor:= new si_averagecolor(l_img_obj);l_colorhist:=新的si_colorhistogram(l_img_obj);l_poscolor:=新的si_positionalcolor(l_img_obj);l_texture:=新的si_texture(l_img_obj);l_featurelist:=新的SI_FeatureList(l_avgcolor，1，l_colorhist，1，l_poscolor，1，l_texture，1);-检查是否已经存在相似的图像从照片p中选择count(*)到l_exist中，其中SI_ScoreByFtrList(l_featurelist，SI_MkStillImage1(p.phot_source.source.localdata))= 0;如果(l_exist> 0)，则out_message:= app_util.get_translated_message('ERR_SIMILAR_PHOTO_ALREADY_EXISTS');别的/*在这里，blob作为ORDImage成功插入到PHOTOS表中*/out_message:= app_util.get_translated_message('SUC_PHOTO_INSERTED');万一;结尾; 

如果我省略比较，则成功将图像作为ORDImage插入，否则使用DBMS_UTILITY.FORMAT_ERROR_BACKTRACE引发异常( sqlcode:1，sqerrm:用户定义的异常)，它告诉我以下内容:

ORA-06512:à"ORDSYS.SI_STILLIMAGE"，ligne 27
ORA-06512:à"ORDSYS.SI_MKSTILLIMAGE1"，ligne 6
ORA-06512:à"SURV.APP_CORE"，ligne 212

第212行是检查相似图像是否已经存在的行:

 选择count(*)到l_exist从照片p其中SI_ScoreByFtrList(l_featurelist，SI_MkStillImage1(p.phot_source.source.localdata))= 0; 

问题似乎在于它不接受 p.phot_source.source.localdata 作为参数.您对我如何解决这个问题有任何想法吗?

我也尝试过:

 选择count(*)到l_exist从照片p其中l_featurelist.si_score(new si_stillimage1(p.phot_source.source.localdata))= 0; 

谢谢！

解决方案

我终于回到问题上来，并使它正常工作.

问题很简单，我在 ORDImage 字段中有一些 null 值...

---

我尝试将 StillImage 对象直接存储到我的 PHOTOS 表中，发现了我的错误:

 更改表PHOTOS add phot_source2 SI_Stillimage;更新照片p集p.phot_source2 = si_stillimage(p.phot_source.source.localData)其中p.phot_id<10; 

，然后实施以下最小示例:

  DECLAREl_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_exist INTEGER;开始-从ordimage获取斑点选择p.phot_source.source.localdata从照片p进入l_blob其中phot_id = 2;-从Blob构建stillimage对象l_img_obj:= NEW si_stillimage(l_blob);-获取图像特征并构建featureList对象l_avgcolor:= NEW si_averagecolor(l_img_obj);l_colorhist:= NEW si_colorhistogram(l_img_obj);l_poscolor:=新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);-检查表中是否找到相似的图像选择1INTO l_exist从照片psi_scorebyftrlist(l_featurelist，p.phot_source2)= 0AND phot_id<10AND rownum = 1;-如果发现至少一张类似的照片，则显示消息如果(l_exist = 1)然后dbms_output.put_line('已找到相似的照片');万一;结尾;/ 

在将 phot_id 限制为10时，即使将 p.phot_source2 替换为 si_mkstillimage1(p.phot_source.source.localdata)，效果也很好(导致问题的原因).但是，当删除 phot_id 限制时，它失败了.因此，我终于明白，在 phot_source 列( ORDImage )中有一些 null 值可能会导致此问题.

实际上使用 null 参数调用 SI_StillImage()构造函数会导致以下错误消息:

  ORA-06510:PL/SQL:未处理的用户定义异常ORA-06512:在"ORDSYS.SI_STILLIMAGE"中，第27行ORA-06512:位于"ORDSYS.SI_MKSTILLIMAGE1"的第6行ORA-06512:在第24行 

我从 phot_source 列中删除了所有 null 值，现在一切正常:)

---

要进一步:

缺点是，与表中存储的所有图像进行比较需要花费很长时间( 1155秒(约20分钟)，持续 5000 >照片).因此，我尝试将图像功能直接存储到表中:

 更改表中的照片添加(phot_averagecolor si_averagecolor，phot_colorhistogram si_colorhistogram，phot_positionalcolor si_positionalcolor，phot_texture si_texture)更新照片p集p.phot_averagecolor = si_averagecolor(si_stillimage(p.phot_source.source.localData))，p.phot_colorhistogram = si_colorhistogram(si_stillimage(p.phot_source.source.localData))，p.phot_positionalcolor = si_positionalcolor(si_stillimage(p.phot_source.source.localData))，p.phot_texture = si_texture(si_stillimage(p.phot_source.source.localData))其中p.phot_id<10 

然后进行如下比较:

 -从ordimage获取斑点选择p.phot_source.source.localdata从照片p进入l_blob其中phot_id = 2;-从Blob构建stillimage对象l_img_obj:= NEW si_stillimage(l_blob);-获取图像特征并构建featureList对象l_avgcolor:= si_averagecolor(l_img_obj);l_colorhist:= si_colorhistogram(l_img_obj);l_poscolor:= si_positionalcolor(l_img_obj);l_texture:= si_texture(l_img_obj);l_featurelist:= NEW si_featurelist(l_avgcolor，1，l_colorhist，1，l_poscolor，1，l_texture，1);-检查表中是否找到相似的图像选择1INTO l_exist从照片p在哪里p.phot_averagecolor = l_avgcolorAND p.phot_colorhistogram = l_colorhistAND p.phot_positionalcolor = l_poscolorAND p.phot_texture = l_textureAND p.phot_id<10AND rownum = 1; 

但是会出现以下错误，因为似乎无法使用 = 运算符直接比较图像特征:

  ORA-22901:无法比较对象类型的VARRAY或LOB属性ORA-06512:在第24行 

我认为解决方案是将图像特征存储为数值，但是我阅读了整个 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 :

 select count(*) into l_exist
 from photos p 
 where SI_ScoreByFtrList(l_featurelist, SI_MkStillImage1(p.phot_source.source.localdata)) = 0;

It seems the problem is that it does not accept p.phot_source.source.localdata as parameter. Do you have any idea on how I can solve this ?

I have also tried :

 select count(*) into l_exist 
 from photos p
 where l_featurelist.si_score(new si_stillimage1(p.phot_source.source.localdata)) = 0;

Thank you !

解决方案

I finally came back to the problem, and get it to work.

The problem was simply that I had some null values in the ORDImage field...

---

I found my error by trying to store the StillImage object directly into my PHOTOS table :

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;

and then implementing the following minimal example :

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_exist     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 1
    INTO l_exist
    FROM photos p
    WHERE si_scorebyftrlist(l_featurelist, p.phot_source2) = 0
    AND phot_id < 10
    AND rownum = 1;
    -- show message if at least one similar photo has been found
    IF (l_exist = 1) THEN       
        dbms_output.put_line('A similar photo has been found');
    END IF;
END;
/ 

It was working fine when restricting the phot_id to 10, even by replacing p.phot_source2 with si_mkstillimage1(p.phot_source.source.localdata) (wich was causing the problem). But it failed when removing the phot_id restriction. So I finally understood that I had some null values in the phot_source column (ORDImage) that can cause the problem.

And indeed calling SI_StillImage() constructor with a null parameter leads to the following error message :

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

I removed all null values from the phot_source column and all is working fine now :)

---

To go further:

The downside of this is that it takes a very long time to do the comparison with all the images stored in the table (1155 seconds (arround 20 min) for 5000 photos). So I have tried to store images features directly into the table :

alter table photos add (
    phot_averagecolor si_averagecolor,
    phot_colorhistogram si_colorhistogram,
    phot_positionalcolor si_positionalcolor,
    phot_texture si_texture
)

update photos p set
    p.phot_averagecolor = si_averagecolor(si_stillimage(p.phot_source.source.localData)),
    p.phot_colorhistogram = si_colorhistogram(si_stillimage(p.phot_source.source.localData)),
    p.phot_positionalcolor = si_positionalcolor(si_stillimage(p.phot_source.source.localData)),
    p.phot_texture = si_texture(si_stillimage(p.phot_source.source.localData))
where p.phot_id < 10

And then do the comparison like this :

-- 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    := si_averagecolor(l_img_obj);
l_colorhist   := si_colorhistogram(l_img_obj);
l_poscolor    := si_positionalcolor(l_img_obj);
l_texture     := 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 1
INTO l_exist
FROM photos p
WHERE p.phot_averagecolor = l_avgcolor
AND p.phot_colorhistogram = l_colorhist
AND p.phot_positionalcolor = l_poscolor
AND p.phot_texture = l_texture
AND p.phot_id < 10
AND rownum = 1;

But it gives the following error as it seems not possible to compare image features directly using the = operator :

ORA-22901: cannot compare VARRAY or LOB attributes of an object type
ORA-06512: at line 24

I thought a solution would be to store image features as numeric values, but I read the entire documentation and I have not found any way to get any corresponding numeric value from an image feature.

Luckily, SI_score functions are provided for each image feature, so we can use the following to compare the images :

DECLARE
    l_img_obj   si_stillimage;
    l_blob      BLOB;
    l_exist     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);
    -- check if a similar image is found in the table
    SELECT 1
    INTO l_exist
    FROM photos p
    WHERE p.phot_averagecolor.SI_Score(l_img_obj) = 0
    AND p.phot_colorhistogram.SI_Score(l_img_obj) = 0
    AND p.phot_positionalcolor.SI_Score(l_img_obj) = 0
    AND p.phot_texture.SI_Score(l_img_obj) = 0
    AND rownum = 1;
    -- show message
    dbms_output.put_line(l_count || ' similar photo(s) found');
END;
/

I reduced the time from 1155 seconds (arround 20 min) to 226 seconds (less than 3 min) for 5000 images.

I know, it is still very slow, but I can't find another way to improve performances..., if anyone has an idea do not hesitate to share.

这篇关于使用SQL/MM静止图像将BLOB图像与存储为ORDImage的图像进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！