599      技術社區[雲棲]

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
圖片流轉換工具: https://blog.csdn.net/hikvision_java_gyh/article/details/52670469
hex轉byte[]: https://blog.csdn.net/u010350809/article/details/41265379

最後更新：2017-10-23 15:34:32

  上一篇：  如何準備Joomla3.8?

  下一篇：  Amazon Aurora 讀後感