1,建立图片表
create table image (id serial,data bytea);
insert into image(data) select pg_read_binary_file('文件全路径');
错误：
ERROR: absolute path not allowed
拷贝文件到该目录后
insert into image(data) select pg_read_binary_file('./2/1K7A9699.JPG');
3,
将图片转换成 patten 和 signature,很耗时
CREATE TABLE pat AS (SELECT id,shuffle_pattern(pattern) AS pattern,pattern2signature(pattern) AS signature FROM ( SELECT id,jpeg2pattern(data) AS pattern FROM image) x );
4,创建索引
ALTER TABLE pat ADD PRIMARY KEY (id);CREATE INDEX pat_signature_idx ON pat USING gist (signature);
5,相似度排行
SELECT id,smlr FROM ( SELECT id,pattern <-> (SELECT pattern FROM pat WHERE id = 74) AS smlr FROM pat WHERE id <> 74 ORDER BY signature <-> (SELECT signature FROM pat WHERE id = 74) LIMIT 100) x ORDER BY x.smlr ASC LIMIT 10;
视频去重
抽取视频中的关键帧
图片表
图片到数据库
3,255);">将图片转换成 patten 和 signature
CREATE TABLE pat AS ( SELECT id,movie_id,jpeg2pattern(data) AS pattern FROM image )
x
);
create tabletarger_tableas select * fromsource_table
是会复制表结构+表数据，
SELECT name FROM (SELECT name FROM agentinformation) 这种是不对的
You need to alias the subquery.
SELECT name FROM (SELECT name FROM agentinformation) a
or to be more explicit
SELECT
a.name FROM (SELECT name FROM agentinformation) a
即
select x.id,x.movie_id...
4.
计算不同视频的相似度
select t1.movie_id,t1.id,t1.signature<->t2.signature from
pat t1 join pat t2 on (t1.movie_id<>t2.movie_id)
order by t1.signature<->t2.signature desc
or
select t1.movie_id,t1.signature<->t2.signature from
pat t1 join pat t2 on (t1.movie_id<>t2.movie_id)
where t1.signature<->t2.signature > 0.9
order by t1.signature<->t2.signature desc
多上面红色那一行，其它一样
报错：
ERROR: function jpeg2pattern(bytea) does not exist
这个是因为扩展
imgsmlr安装不正确
\dx命令查看所有已安装的扩展模块
imgsmlr
$ cd imgsmlr$ export PGHOME=/usr/pgsql-9.6$ export PATH=$PGHOME/bin:$PATH:.$ make USE_PGXS=1$ make USE_PGXS=1 install
then exeute the sql.
create extension imgsmlr;
查看下
\dx命令查看所有已安装的扩展模块
然后执行
记得结束加分号
The idea is to find top N similar images by signature using GiST index. Then find top n (n < N) similar images by pattern from top N similar images by signature.
参考
