imgsmlr is a third-party plug-in supported by PolarDB for PostgreSQL (Compatible with Oracle). It provides similar image search capabilities. The imgsmlr plug-in is based on the Haar wavelet transform algorithm. It can extract feature values from images such as PNG and GIF, and retrieve similar images through indexes.
Applicable scope
Supported versions of PolarDB for PostgreSQL (Compatible with Oracle): Oracle syntax compatibility 2.0, and the minor engine version must be 2.0.14.18.0 or later.
You can view the minor engine version in the console or by running the SHOW polardb_version; statement. If the minor engine version does not meet the requirements, upgrade the minor engine version。
Usage
Data types
The imgsmlr plug-in provides two data types: pattern and signature.
Data type | Storage size | Description |
pattern | 16,388 bytes | The result of applying the Haar wavelet transform to an image. |
signature | 64 bytes | A compact representation of a pattern. A GiST index can be used for fast searches. |
Functions
The imgsmlr plug-in provides a variety of functions that can convert images of various types to the pattern type, and also provides functions that create a signature for a pattern-type image for retrieval.
Function | Return type | Description |
jpeg2pattern(bytea) | pattern | Converts a JPEG image to the pattern type. |
png2pattern(bytea) | pattern | Converts a PNG image to the pattern type. |
gif2pattern(bytea) | pattern | Converts a GIF image to the pattern type. |
pattern2signature(pattern) | signature | Creates a signature for a pattern-type image. |
shuffle_pattern(pattern) | pattern | Randomizes a pattern to reduce sensitivity to image offsets. |
Operators
Both the pattern and signature types support the <-> operator for Euclidean distance. The signature type also supports a GiST index on the <-> operator.
Operator | Left type | Right type | Return type | Description |
<-> | pattern | pattern | float8 | Calculates the Euclidean distance between two pattern-type images. |
<-> | signature | signature | float8 | Calculates the Euclidean distance between two signature-type images. |
Examples
Install the plug-in
CREATE EXTENSION imgsmlr;Create an image feature table
Assume that you already have an image table that contains an id column and a data column, where the data column contains binary JPEG data. Use the following SQL statements to create a table that contains the pattern and signature of a given image.
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
);Create a GiST index
ALTER TABLE pat ADD PRIMARY KEY (id);
CREATE INDEX pat_signature_idx ON pat USING gist (signature);Search for similar images
To query the top 10 images most similar to the image with a specified id, you can use a GiST index in a subquery to select the top 100 images by signature, and then the outer query searches the top 10 images by pattern from the images found by the subquery.
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;Uninstall the plug-in
DROP EXTENSION imgsmlr;