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新增了兩個資料型別

資料型別	儲存長度	描述
pattern	16388 bytes	圖片的哈爾小波變換結果
signature	64 bytes	模式的快速索引(GiST)

gist 索引方法(支援pattern和signature型別), 以及KNN操作符，可以用於搜尋相似度

操作符	左型別	右型別	返回型別	描述
<->	pattern	pattern	float8	兩個模式的歐幾里得距離
<->	signature	signature	float8	兩個特徵的歐幾里得距離

新增了幾個函式
將影像的二進位制轉換為pattern型別，將pattern中儲存的資料轉換為signature型別

函式	返回型別	描述
jpeg2pattern(bytea)	pattern	生產jpeg圖片的模式
png2pattern(bytea)	pattern	生產png圖片的模式
gif2pattern(bytea)	pattern	生產gif圖片的模式
pattern2signature(pattern)	signature	從模式中提取特徵
shuffle_pattern(pattern)	pattern	洗牌模式，不敏感影像的轉變

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 `
` > 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