1、依赖gd.h
yum install -y gd-devel
2、下载插件
git clone https://github.com/postgrespro/imgsmlr
cd imgsmlr
export PGHOME=/opt/pg11.5/
export PATH=$PGHOME/bin:$PATH:.
make USE_PGXS=1
make USE_PGXS=1 install
3、解决错粗 FALSE未声明
在imgsmlr.h头文件中定义一个宏:
#ifndef FALSE
#define FALSE (0)
#endif
#ifndef TRUE
#define TRUE (!FALSE)
#endif
4、再次编译安装
make USE_PGXS=1
make USE_PGXS=1 install
5、编译成功后进入pg数据库,创建扩展:
create extension imgsmlr;
二、使用
6、建立图片表
create table image (id serial, data bytea);
导入图片到数据库
insert into image(data) select pg_read_binary_file('文件路径');
将图片转换成 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
);
创建索引
ALTER TABLE pat ADD PRIMARY KEY (id);
CREATE INDEX pat_signature_idx ON pat USING gist (signature);
近似度查询,例如查询与id = :id的图像相似的图像,按相似度排行,取出前10条
SELECT
id,
smlr
FROM
(
SELECT
id,
pattern <-> (SELECT pattern FROM pat WHERE id = :id) AS smlr
FROM pat
WHERE id <> :id
ORDER BY
signature <-> (SELECT signature FROM pat WHERE id = :id)
LIMIT 100
) x
ORDER BY x.smlr ASC
LIMIT 10
这里可以用到KNN索引,快速按相似度排行输出结果。
最后修改于 2020-08-11 17:15:31
如果觉得我的文章对你有用,请随意赞赏
扫一扫支付

