PolarDB:imgsmlr

Prerequisites

The feature is supported on PolarDB for PostgreSQL (Compatible with Oracle) clusters that run PolarDB for PostgreSQL (Compatible with Oracle) 2.0 (revision version 2.0.14.18.0 or later).

SHOW polar_version;

Usage

Data type

imgsmlr provides the pattern and signature data types.

Function

imgsmlr provides functions to convert various types of images into the pattern type. It also provides functions to create signatures for patterns for search purposes.

Operator

Both pattern and signature data types support the <-> operator for Euclidean distance. The signature data type also supports GiST indexing on the <-> operator.

Examples

Create the imgsmlr extension

CREATE EXTENSION imgsmlr;

Create an image feature value table

In this example, a table named image contains the id and data columns. The data column contains binary data of JPEG images. Execute the following SQL statements to create a table that contains the patterns and signatures of specified images.

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 that are the most similar to the image with the specified id, run the subquery to query the top 100 images by signature using the GiST index. Then run the outer query to query 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

Remove the extension

DROP EXTENSION imgsmlr;