PostgreSQL 图像搜索插件使用篇

1. 插件安装

依赖gd.h
yum install -y gd-devel
下载安装imgsmlr

$ git clone https://github.com/postgrespro/imgsmlr
$ cd imgsmlr
$ export PGHOME=/home/digoal/pgsql9.5
$ export PATH=$PGHOME/bin:$PATH:.

$ make USE_PGXS=1
$ make USE_PGXS=1 install

安装插件

$ psql
psql (9.5.3)
Type "help" for help.
postgres=# create extension imgsmlr;
CREATE EXTENSION

imgsmlr新增了两个数据类型

gist 索引方法(支持pattern和signature类型), 以及KNN操作符，可以用于搜索相似度

新增了几个函数
将图像的二进制转换为pattern类型，将pattern中存储的数据转换为signature类型

2.SQL的导入和测试

2.1 建立图片表
create table image (id serial, data bytea);

2.2 导入图片到数据库
insert into image(data) select pg_read_binary_file('文件路径');

由于我的图片并没有权限上传到PostgreSQL服务端，不能当成本地文件使用。

CREATE TABLE hexdump (hex text); 创建临时表
xxd -p 4.jpg | tr -d '\n' > 4.hex; 图片文件转为16进制文件
psql --host=xx.xx.xx.xx --port=1234 --username=root --dbname=postgres -c "COPY hexdump from STDIN" <4.hex; 通过输出流写入服务端的临时表
insert into image(data) SELECT decode(hex, 'hex') FROM hexdump; 将16进制转为area

2.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 
);

2.4 创建索引

ALTER TABLE pat ADD PRIMARY KEY (id);
CREATE INDEX pat_signature_idx ON pat USING gist (signature);

2.5 近似度查询，例如查询与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

这个smlr是越小越相似，可以试试下面这两张图片。

3.JDBC的导出和查询

3.1 注意二方库是和jre环境相关的，不然会报错。

<dependency>
           <groupId>org.postgresql</groupId>
           <artifactId>postgresql</artifactId>
           <version>42.1.4.jre7</version>
</dependency>

3.2 通过输入流写入到bytea类型的字段

Class.forName("org.postgresql.Driver");
connection= DriverManager.getConnection(url, user, password);
FileInputStream in = ImageUtil.readImage(path);
String insertSQL = "insert into image (id,data) values(?,?)";
PreparedStatement ps = connection.prepareStatement(insertSQL);
ps.setInt(1,1);
ps.setBinaryStream(2, in, in.available());
int count = ps.executeUpdate();

3.3 读取text字段

 String sql = "select * from hexdump limit 1";
 statement=connection.createStatement();
 ResultSet resultSet=statement.executeQuery(sql);
 while(resultSet.next()){
      ImageUtil.toFile(hexStringToBytes(resultSet.getString(1)),"/Users/work/1.jpg");
}

3.3.2 读取bytea类型的字段
实际上bytea类型getString 就是hexdump的类型的x+text，故通过流读取比string合理

 String sql = "select * from image limit 1";
 statement=connection.createStatement();
 ResultSet resultSet=statement.executeQuery(sql);
 while(resultSet.next()){
     ImageUtil.readBin2Image(resultSet.getBinaryStream(2), "/Users/work/2.jpg");
}

4.附录

安装文档：https://github.com/digoal/blog/blob/master/201611/20161126_01.md?file=20161126_01.md
如何插入文件：https://dba.stackexchange.com/questions/1742/how-to-insert-file-data-into-a-postgresql-bytea-column
图片流转换工具: http://blog.csdn.net/hikvision_java_gyh/article/details/52670469
hex转byte[]: http://blog.csdn.net/u010350809/article/details/41265379