1. 插件安装
依赖gd.hyum 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 '\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