更新时间:2022-10-17 23:41:17
我终于回到问题上来,并使它正常工作.
问题很简单,我在 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 :)
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.