How to create a Postgresql database query in Django with BitXor bitwise comparison and filtering by Hamming distance value?

My task is to develop a function for searching images in a database using perseptual hash and Hamming distance comparison. The code below works, but due to the fact that filtering occurs in the Django environment and Python, the code runs extremely slowly, on average the execution time is more than 7 seconds with 1,500,000 records in the database, but the number of records on the production server will be much larger and the request processing speed will drop even more.

models.py

# models.py
from django.db import models

class Fids(models.Model):
    """
    The model stores records with computed image hashes (>6000000 records)
    picture = 'https://ae01.alicdn.com/kf/Sc835208f704548b7b230fd12f72850bfU.jpg'
    hs8 = '9aca3edfc0c23538'
    bin8 = '1001101011001010001111101101111111000000110000100011010100111000'
    """
    picture = models.URLField()
    hs8 = models.CharField(max_length=50)
    bin8 = models.CharField(max_length=64)

service.py

# service.py
import requests
import json
import imagehash
from PIL import Image
from main.models import Fids


def hamming_distance(binary_str1, binary_str2):
    """
    The function calculates the Hamming distance
    example input:
    binary_str1 = '1001101011001010001111101101111111000000110000100011010100111000'
    binary_str2 = '1001101011001010001111101101111111000000110000100011010100111000'
    """
    if len(binary_str1) != len(binary_str2):
        return -1
    xor_result = int(binary_str1, 2) ^ int(binary_str2, 2)
    hamming_distance = bin(xor_result).count('1')
    return hamming_distance


def search(url=None, hamming=None):
    """
    Search query processing function
    
    example input:
    url = 'https://ae01.alicdn.com/kf/Sc835208f704548b7b230fd12f72850bfU.jpg'
    hamming = 8

    output:
    delay: 8-15 sec
    """
    if url is None:
        return -1
    hamming = 0 if hamming is None else int(hamming)
    try:
        res = requests.get(url, stream=True).raw
        hash = imagehash.phash(Image.open(res), hash_size=8)
        str_hash = str(hash)
        binary = bin(int(str_hash, 16))[2:].zfill(len(str_hash)*4)
    except Exception as er:
        return json.dumps({'search': url, 'error': str(er)})
    result = []
    queryset = Fids.objects.all().values('picture', 'bin8')
    for item in queryset:
        bin8 = item['bin8']
        bin8 = '' if bin8 is None else bin8
        distance = hamming_distance(binary, bin8)
        if 0 <= distance <= hamming:
            result.append({'url': item['picture']})
    return json.dumps({'search': url, 'results': result})

I think that you need to correctly compose the SQL query so that filtering is carried out using the Postgreql-14 database. I found the BitXor aggregation function in Django: from django.contrib.postgres.aggregates import BitXor

I believe that it performs a bitwise comparison, but I don’t understand how to apply it, there are very few examples on the Internet, Moreover, you also need to filter the request by Hamming disorder.

SQL also has the # operator, which also performs a bitwise comparison. Something like: SELECT X'eb35b232b08ac33e' # X'eb35b232b08ac33e'; But I don’t understand how to apply this to my problem.

Help me create the correct query using ORM Django or SQL query to perform search and filtering using bitwise comparison and comparing the result with a given Hamming distance value.

As a result of searching for a solution, the following SQL query was obtained:

SELECT * FROM main_fids WHERE int7 # 521635964142742 = 1;

I will have to add an int7 field and store the hash in decimal form and in a smaller size, but I get an error due to the large size of the number. I couldn't figure out how to compare binary and hexadecimal values that I stored as Char. All that remains is to figure out how to form such a request in Django and pass the search hash value instead of 521635964142742 and the Hamming distance value defined by the request instead of 1.

To solve the problem, I found this solution:

SELECT id, hsi7 
FROM main_fids 
WHERE length(regexp_replace(((hsi7 # 524383500174776)::bit(64))::text, '0', '', 'g')) < 8 ;

I convert the result of bit XOR to a binary type, then convert it to a string, remove zeros, count the number of characters in the resulting string, thus obtaining the Hamming distance, compare it with the specified value (in example 8). The query is still very slow; it might be worth changing the query or table structure somehow, or converting the data to a different type. If possible, please recommend a more elegant and efficient solution. Please.

Back to Top