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索引，快速按相似度排行输出结果。