All Projects → postgrespro → Imgsmlr

postgrespro / Imgsmlr

Licence: other
Similar images search for PostgreSQL

Programming Languages

c
50402 projects - #5 most used programming language

Projects that are alternatives of or similar to Imgsmlr

Pg stat monitor
PostgreSQL Statistics Collector
Stars: ✭ 145 (-19.89%)
Mutual labels:  postgresql, postgres
Node Express Postgresql Sequelize
Node.js, Express.js, Sequelize.js and PostgreSQL RESTful API
Stars: ✭ 148 (-18.23%)
Mutual labels:  postgresql, postgres
Sqlcell
SQLCell is a magic function for the Jupyter Notebook that executes raw, parallel, parameterized SQL queries with the ability to accept Python values as parameters and assign output data to Python variables while concurrently running Python code. And *much* more.
Stars: ✭ 145 (-19.89%)
Mutual labels:  postgresql, postgres
Laravel Scout Postgres
PostgreSQL Full Text Search Engine for Laravel Scout
Stars: ✭ 140 (-22.65%)
Mutual labels:  postgresql, postgres
Postgres Migrations
🐦 A Stack Overflow-inspired PostgreSQL migration library with strict ordering and immutable migrations
Stars: ✭ 161 (-11.05%)
Mutual labels:  postgresql, postgres
Serverless Pg
A package for managing PostgreSQL connections at SERVERLESS scale
Stars: ✭ 142 (-21.55%)
Mutual labels:  postgresql, postgres
Elephant Shed
PostgreSQL Management Appliance
Stars: ✭ 146 (-19.34%)
Mutual labels:  postgresql, postgres
Postgres Operator
Postgres operator creates and manages PostgreSQL clusters running in Kubernetes
Stars: ✭ 2,194 (+1112.15%)
Mutual labels:  postgresql, postgres
Doctrine Postgis
Spatial and Geographic Data with PostGIS and Doctrine.
Stars: ✭ 161 (-11.05%)
Mutual labels:  postgresql, postgres
Sqorn
A Javascript library for building SQL queries
Stars: ✭ 1,871 (+933.7%)
Mutual labels:  postgresql, postgres
Wal G
Archival and Restoration for Postgres
Stars: ✭ 1,974 (+990.61%)
Mutual labels:  postgresql, postgres
Postgresqltuner
Simple script to analyse your PostgreSQL database configuration, and give tuning advice
Stars: ✭ 2,214 (+1123.2%)
Mutual labels:  postgresql, postgres
Ozo
OZO is a C++17 Boost.Asio based header-only library for asyncronous communication with PostgreSQL DBMS.
Stars: ✭ 138 (-23.76%)
Mutual labels:  postgresql, postgres
Go Cache
This project encapsulates multiple db servers, redis、ledis、memcache、file、memory、nosql、postgresql
Stars: ✭ 143 (-20.99%)
Mutual labels:  postgresql, postgres
Masquerade
A Postgres Proxy to Mask Data in Realtime
Stars: ✭ 134 (-25.97%)
Mutual labels:  postgresql, postgres
Postgres Meta
A RESTful API for managing your Postgres. Fetch tables, add roles, and run queries
Stars: ✭ 146 (-19.34%)
Mutual labels:  postgresql, postgres
Timescaledb
An open-source time-series SQL database optimized for fast ingest and complex queries. Packaged as a PostgreSQL extension.
Stars: ✭ 12,211 (+6646.41%)
Mutual labels:  postgresql, postgres
Postgres Operator
Production PostgreSQL for Kubernetes, from high availability Postgres clusters to full-scale database-as-a-service.
Stars: ✭ 2,166 (+1096.69%)
Mutual labels:  postgresql, postgres
Psql2csv
Run a query in psql and output the result as CSV.
Stars: ✭ 153 (-15.47%)
Mutual labels:  postgresql, postgres
Pg hashids
Short unique id generator for PostgreSQL, using hashids
Stars: ✭ 164 (-9.39%)
Mutual labels:  postgresql, postgres

Build Status codecov GitHub license

ImgSmlr – similar images search for PostgreSQL

Introduction

ImgSmlr – is a PostgreSQL extension which implements similar images searching functionality.

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

Authors

Availability

ImgSmlr is released as an extension and not available in default PostgreSQL installation. It is available from github under the same license as PostgreSQL and supports PostgreSQL 9.1+.

Installation

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 built PostgreSQL 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;"

Usage

ImgSmlr offers two datatypes: pattern and signature.

Datatype Storage length Description
pattern 16388 bytes Result of Haar wavelet transform on the image
signature 64 bytes Short representation of pattern for fast search using GiST indexes

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.

Function Return type Description
jpeg2pattern(bytea) pattern Convert jpeg image into pattern
png2pattern(bytea) pattern Convert png image into pattern
gif2pattern(bytea) pattern Convert gif image into pattern
pattern2signature(pattern) signature Create signature from pattern
shuffle_pattern(pattern) pattern Shuffle pattern 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.

Operator Left type Right type Return type Description
<-> pattern pattern float8 Eucledian distance between two patterns
<-> signature signature float8 Eucledian distance between two signatures

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.

Example

Let us assume we have an image table with columns id and data where data column contains binary jpeg data. We can create pat table with patterns and signatures of given images using following query.

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 
);

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

ALTER TABLE pat ADD PRIMARY KEY (id);
CREATE INDEX pat_signature_idx ON 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.

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

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.

Note that the project description data, including the texts, logos, images, and/or trademarks, for each open source project belongs to its rightful owner. If you wish to add or remove any projects, please contact us at [email protected].