Repository files navigation

ImgSmlr – is a PostgreSQL extension which implements similar images searchingfunctionality.

ImgSmlr method is based on Haar wavelet transform. The goal of ImgSmlr is notto provide most advanced state of art similar images searching methods. ImgSmlrwas written as sample extension which illustrate how PostgreSQL extendabilitycould cover such untypical tasks for RDBMS as similar images search.

• Alexander Korotkovaekorotkov@gmail.com, Postgres Professional, Moscow, Russia

ImgSmlr is released as an extension and not available in default PostgreSQLinstallation. It is available fromgithubunder the same license asPostgreSQLand supports PostgreSQL 9.1+.

Before build and install ImgSmlr you should ensure following:

• PostgreSQL version is 9.1 or higher.
• You have development package of PostgreSQL installed or you builtPostgreSQL from source.
• You have gd2 library installed on your system.
• Your PATH variable is configured so that pg_config command available.

Typical installation procedure may look like this:

$ git clone https://github.com/postgrespro/imgsmlr.git$ cd imgsmlr$ make USE_PGXS=1$ sudo make USE_PGXS=1 install$ make USE_PGXS=1 installcheck$ psql DB -c "CREATE EXTENSION imgsmlr;"

ImgSmlr offers two datatypes: pattern and signature.

There is set of functions *2pattern(bytea) which converts bynary data in given format into pattern. Convertion into pattern consists of following steps.

• Decompress image.
• Make image black&white.
• Resize image to 64x64 pixels.
• Apply Haar wavelet transform to the image.

Pattern could be converted into signature and shuffled for less sensitivity to image shift.

Both pattern and signature datatypes supports<-> operator for eucledian distance. Signature also supports GiST indexing with KNN on<-> operator.

The idea is to find top N similar images by signature using GiST index. Then find top n (n < N) similar images by pattern from top N similar images by signature.

Let us assume we have animage table with columnsid anddata wheredata column contains binary jpeg data. We can createpat table with patterns and signatures of given images using following query.

CREATETABLEpatAS (SELECTid,shuffle_pattern(pattern)AS pattern, pattern2signature(pattern)AS signatureFROM (SELECT id, jpeg2pattern(data)AS patternFROM image) x );

Then let's create primary key forpat table and GiST index for signatures.

ALTERTABLE pat ADDPRIMARY KEY (id);CREATEINDEXpat_signature_idxON pat USING gist (signature);

Prelimimary work is done. Now we can search for top 10 similar images to given image with specified id using following query.

SELECTid,smlrFROM(SELECTid,pattern<-> (SELECT patternFROM patWHERE id= :id)AS smlrFROM patWHERE id<> :idORDER BYsignature<-> (SELECT signatureFROM patWHERE id= :id)LIMIT100) xORDER BYx.smlrASCLIMIT10

Inner query selects top 100 images by signature using GiST index. Outer query search for top 10 images by pattern from images found by inner query. You can adjust both of number to achieve better search results on your images collection.