PolarDB的云原生架构提供高性价比的数据存储、可扩展的灵活性、高效的并行计算能力以及快速的数据搜索和处理能力。PolarDB通过结合计算算法，挖掘业务数据的价值，将其转化为生产力。
本文介绍如何使用开源PolarDB和imgsmlr存储图像特征值，并快速进行图像相似度搜索。
本演示的测试环境为 macOS + Docker。关于部署PolarDB的详细说明，请参考以下文章：
PolarDB的简单部署
原则
有多种方法可以对图像进行数字化，例如将其划分为正方形，并用原色和灰度表示每个正方形。然后将图像逐层逐渐缩小（例如，从 81 个正方形缩小到 9 个正方形）并压缩成一个较小的正方形，形成另一个正方形阵列。N^2N^2
在图像相似性搜索过程中，将比较两个方形数组之间的向量距离。N^2
通过使用GIST索引接口，可以实现向量相似性搜索的快速收敛。这涉及使用中心点作为存储桶的数据分区，并采用多层缩略图压缩搜索算法（请参阅本文的后面部分）。
本文介绍如何使用开源的PolarDB和imgsmlr来存储图像特征值，并高效进行图像相似度搜索。
1. 介绍两种数据类型：细节向量和特征向量。特征向量占用的空间更小，查询效率更高，通常用于初始数据过滤，而细节向量则用于更细致的过滤。
| 数据类型 | 存储长度 | 描述 |
| 模式 | 16388 字节 | Haar小波变换在图像上的结果 |
| 签名 | 64 字节 | 使用 GiST 索引进行快速搜索的模式的简短表示 |
2、介绍几种图像转换功能接口。
| 功能 | 返回类型 | 描述 |
| jpeg2pattern（bytea） | 模式 | 将jpeg图像转换为图案 |
| png2pattern（bytea） | 模式 | 把png图像转换为图案 |
| GIF2模式（bytea） | 模式 | 将gif图像转换为图案 |
| pattern2signature（模式） | 签名 | 从模式创建签名 |
| shuffle_pattern（模式） | 模式 | 随机播放模式，降低对图像偏移的敏感度 |
3. 引入两种向量距离计算算子和索引排序支持。
| 算子 | 左型 | 正确类型 | 返回类型 | 描述 |
| <-> | 模式 | 模式 | 浮点8 | 两种模式之间的欧克里得距离 |
| <-> | 签名 | 签名 | 浮点8 | 两个特征之间的欧克里底距离 |
在PolarDB上部署imgsmlr
1. 安装 png 和 jpeg 的图片库依赖。
sudo yum install -y libpng-devel sudo yum install -y libjpeg-turbo-devel sudo vi /etc/ld.so.conf # add /usr/lib64 sudo ldconfig
2. 安装gd库，用于jpeg、png、gif等图片格式的序列化转换。
git clone --depth 1 https://github.com/libgd/libgd cd libgd/ mkdir build cd build cmake -DENABLE_PNG=1 -DENABLE_JPEG=1 .. make sudo make install ... -- Installing: /usr/local/lib64/libgd.so.3.0.16 -- Installing: /usr/local/lib64/libgd.so.3 ... sudo vi /etc/ld.so.conf # add /usr/local/lib64 sudo ldconfig export LD_LIBRARY_PATH=/usr/local/lib64:$LD_LIBRARY_PATH
3. 安装 imgsmlr。
git clone --depth 1 https://github.com/postgrespro/imgsmlr cd imgsmlr/ USE_PGXS=1 make USE_PGXS=1 make install ldd /home/postgres/tmp_basedir_polardb_pg_1100_bld/lib/imgsmlr.so linux-vdso.so.1 => (0x00007ffc25d52000) libgd.so.3 => /usr/local/lib64/libgd.so.3 (0x00007fd7a4463000) libc.so.6 => /lib64/libc.so.6 (0x00007fd7a3ee5000) libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007fd7a3bdd000) libm.so.6 => /lib64/libm.so.6 (0x00007fd7a38db000) libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fd7a36c5000) /lib64/ld-linux-x86-64.so.2 (0x00007fd7a42b3000)
4. 加载插件。
psql create extension imgsmlr ;
场景模拟和架构设计实践
生成测试图像。
cd imgsmlr USE_PGXS=1 make installcheck
图像导入、矢量化和图像相似性搜索测试。
psql -- Create a plug-in. CREATE EXTENSION imgsmlr; -- Create a table that stores the binary of the original image. CREATE TABLE image (id integer PRIMARY KEY, data bytea); -- Create a temporary table for import. CREATE TABLE tmp (data text); -- Import images. \copy tmp from 'data/1.jpg.hex' INSERT INTO image VALUES (1, (SELECT decode(string_agg(data, ''), 'hex') FROM tmp)); TRUNCATE tmp; \copy tmp from 'data/2.png.hex' INSERT INTO image VALUES (2, (SELECT decode(string_agg(data, ''), 'hex') FROM tmp)); TRUNCATE tmp; \copy tmp from 'data/3.gif.hex' INSERT INTO image VALUES (3, (SELECT decode(string_agg(data, ''), 'hex') FROM tmp)); TRUNCATE tmp; \copy tmp from 'data/4.jpg.hex' INSERT INTO image VALUES (4, (SELECT decode(string_agg(data, ''), 'hex') FROM tmp)); TRUNCATE tmp; \copy tmp from 'data/5.png.hex' INSERT INTO image VALUES (5, (SELECT decode(string_agg(data, ''), 'hex') FROM tmp)); TRUNCATE tmp; \copy tmp from 'data/6.gif.hex' INSERT INTO image VALUES (6, (SELECT decode(string_agg(data, ''), 'hex') FROM tmp)); TRUNCATE tmp; \copy tmp from 'data/7.jpg.hex' INSERT INTO image VALUES (7, (SELECT decode(string_agg(data, ''), 'hex') FROM tmp)); TRUNCATE tmp; \copy tmp from 'data/8.png.hex' INSERT INTO image VALUES (8, (SELECT decode(string_agg(data, ''), 'hex') FROM tmp)); TRUNCATE tmp; \copy tmp from 'data/9.gif.hex' INSERT INTO image VALUES (9, (SELECT decode(string_agg(data, ''), 'hex') FROM tmp)); TRUNCATE tmp; \copy tmp from 'data/10.jpg.hex' INSERT INTO image VALUES (10, (SELECT decode(string_agg(data, ''), 'hex') FROM tmp)); TRUNCATE tmp; \copy tmp from 'data/11.png.hex' INSERT INTO image VALUES (11, (SELECT decode(string_agg(data, ''), 'hex') FROM tmp)); TRUNCATE tmp; \copy tmp from 'data/12.gif.hex' INSERT INTO image VALUES (12, (SELECT decode(string_agg(data, ''), 'hex') FROM tmp)); TRUNCATE tmp; -- Convert the original image into an image feature vector and an image signature, and import it into a new table. CREATE TABLE pat AS ( SELECT id, shuffle_pattern(pattern)::text::pattern AS pattern, pattern2signature(pattern)::text::signature AS signature FROM ( SELECT id, (CASE WHEN id % 3 = 1 THEN jpeg2pattern(data) WHEN id % 3 = 2 THEN png2pattern(data) WHEN id % 3 = 0 THEN gif2pattern(data) ELSE NULL END) AS pattern FROM image ) x ); -- Add a PK. ALTER TABLE pat ADD PRIMARY KEY (id); -- Create an index in the image signature field. ALTER TABLE pat ADD PRIMARY KEY (id); -- Self-correlate and query image similarity (Euclidean distance). SELECT p1.id, p2.id, round((p1.pattern <-> p2.pattern)::numeric, 4) FROM pat p1, pat p2 ORDER BY p1.id, p2.id; SELECT p1.id, p2.id, round((p1.signature <-> p2.signature)::numeric, 4) FROM pat p1, pat p2 ORDER BY p1.id, p2.id; -- Use the index to quickly search for similar images. SET enable_seqscan = OFF; SELECT id FROM pat ORDER BY signature <-> (SELECT signature FROM pat WHERE id = 1) LIMIT 3; SELECT id FROM pat ORDER BY signature <-> (SELECT signature FROM pat WHERE id = 4) LIMIT 3; SELECT id FROM pat ORDER BY signature <-> (SELECT signature FROM pat WHERE id = 7) LIMIT 3; SELECT id FROM pat ORDER BY signature <-> (SELECT signature FROM pat WHERE id = 10) LIMIT 3;
结果：
SELECT p1.id, p2.id, round((p1.signature <-> p2.signature)::numeric, 4) FROM pat p1, pat p2 ORDER BY p1.id, p2.id; id | id | round ----+----+-------- 1 | 1 | 0.0000 1 | 2 | 0.5914 1 | 3 | 0.6352 1 | 4 | 1.1431 1 | 5 | 1.3843 1 | 6 | 1.5245 1 | 7 | 3.1489 1 | 8 | 3.4192 1 | 9 | 3.4571 1 | 10 | 4.0683 1 | 11 | 3.3551 1 | 12 | 2.4814 2 | 1 | 0.5914 2 | 2 | 0.0000 2 | 3 | 0.7785 2 | 4 | 1.1414 2 | 5 | 1.2839 2 | 6 | 1.4373 2 | 7 | 3.2969 2 | 8 | 3.5381 2 | 9 | 3.5788 2 | 10 | 4.4256 2 | 11 | 3.6138 2 | 12 | 2.7975 3 | 1 | 0.6352 3 | 2 | 0.7785 3 | 3 | 0.0000 3 | 4 | 1.0552 3 | 5 | 1.3885 3 | 6 | 1.4925 3 | 7 | 3.0224 3 | 8 | 3.2555 3 | 9 | 3.2907 3 | 10 | 4.0521 3 | 11 | 3.2095 3 | 12 | 2.4304 4 | 1 | 1.1431 4 | 2 | 1.1414 4 | 3 | 1.0552 4 | 4 | 0.0000 4 | 5 | 0.5904 4 | 6 | 0.7594 4 | 7 | 2.6952 4 | 8 | 2.9019 4 | 9 | 2.9407 4 | 10 | 3.8655 4 | 11 | 2.9710 4 | 12 | 2.1766 5 | 1 | 1.3843 5 | 2 | 1.2839 5 | 3 | 1.3885 5 | 4 | 0.5904 5 | 5 | 0.0000 5 | 6 | 0.7044 5 | 7 | 2.9206 5 | 8 | 3.1147 5 | 9 | 3.1550 5 | 10 | 4.0454 5 | 11 | 3.2023 5 | 12 | 2.3612 6 | 1 | 1.5245 6 | 2 | 1.4373 6 | 3 | 1.4925 6 | 4 | 0.7594 6 | 5 | 0.7044 6 | 6 | 0.0000 6 | 7 | 2.8572 6 | 8 | 3.0659 6 | 9 | 3.1054 6 | 10 | 3.7803 6 | 11 | 2.7595 6 | 12 | 2.0282 7 | 1 | 3.1489 7 | 2 | 3.2969 7 | 3 | 3.0224 7 | 4 | 2.6952 7 | 5 | 2.9206 7 | 6 | 2.8572 7 | 7 | 0.0000 7 | 8 | 0.6908 7 | 9 | 0.7082 7 | 10 | 4.3939 7 | 11 | 3.5039 7 | 12 | 3.2914 8 | 1 | 3.4192 8 | 2 | 3.5381 8 | 3 | 3.2555 8 | 4 | 2.9019 8 | 5 | 3.1147 8 | 6 | 3.0659 8 | 7 | 0.6908 8 | 8 | 0.0000 8 | 9 | 0.0481 8 | 10 | 4.6824 8 | 11 | 3.7398 8 | 12 | 3.5689 9 | 1 | 3.4571 9 | 2 | 3.5788 9 | 3 | 3.2907 9 | 4 | 2.9407 9 | 5 | 3.1550 9 | 6 | 3.1054 9 | 7 | 0.7082 9 | 8 | 0.0481 9 | 9 | 0.0000 9 | 10 | 4.6921 9 | 11 | 3.7523 9 | 12 | 3.5913 10 | 1 | 4.0683 10 | 2 | 4.4256 10 | 3 | 4.0521 10 | 4 | 3.8655 10 | 5 | 4.0454 10 | 6 | 3.7803 10 | 7 | 4.3939 10 | 8 | 4.6824 10 | 9 | 4.6921 10 | 10 | 0.0000 10 | 11 | 1.8252 10 | 12 | 2.0838 11 | 1 | 3.3551 11 | 2 | 3.6138 11 | 3 | 3.2095 11 | 4 | 2.9710 11 | 5 | 3.2023 11 | 6 | 2.7595 11 | 7 | 3.5039 11 | 8 | 3.7398 11 | 9 | 3.7523 11 | 10 | 1.8252 11 | 11 | 0.0000 11 | 12 | 1.2933 12 | 1 | 2.4814 12 | 2 | 2.7975 12 | 3 | 2.4304 12 | 4 | 2.1766 12 | 5 | 2.3612 12 | 6 | 2.0282 12 | 7 | 3.2914 12 | 8 | 3.5689 12 | 9 | 3.5913 12 | 10 | 2.0838 12 | 11 | 1.2933 12 | 12 | 0.0000 (144 rows) postgres=# SELECT id FROM pat ORDER BY signature <-> (SELECT signature FROM pat WHERE id = 1) LIMIT 3; id ---- 1 2 3 (3 rows) postgres=# SELECT id FROM pat ORDER BY signature <-> (SELECT signature FROM pat WHERE id = 4) LIMIT 3; id ---- 4 5 6 (3 rows) postgres=# SELECT id FROM pat ORDER BY signature <-> (SELECT signature FROM pat WHERE id = 7) LIMIT 3; id ---- 7 8 9 (3 rows) postgres=# SELECT id FROM pat ORDER BY signature <-> (SELECT signature FROM pat WHERE id = 10) LIMIT 3; id ---- 10 11 12 (3 rows) postgres=# explain SELECT id FROM pat ORDER BY signature <-> (SELECT signature FROM pat WHERE id = 10) LIMIT 3; QUERY PLAN ------------------------------------------------------------------------------------ Limit (cost=8.29..10.34 rows=3 width=8) InitPlan 1 (returns $0) -> Index Scan using pat_pkey on pat pat_1 (cost=0.14..8.15 rows=1 width=64) Index Cond: (id = 10) -> Index Scan using pat_signature_idx on pat (cost=0.13..8.37 rows=12 width=8) Order By: (signature <-> $0) (6 rows)
