-1

Sorry for my bad english, i have a small database that contains hashes of photos, when I try to find similar photos to the one below:

for which the following hash was calculated: "0f3f2764ecc482c2" using the method average_hash()

imagehash.average_hash(Image.open(imagePath))

The system finds a very large number of collisions, below is an example of photos that were identified as completely identical:

The table in which I store hashes of photos:

CREATE TABLE IF NOT EXISTS photos(id_photo BIGINT PRIMARY  KEY,  photo_hash TEXT,  FOREIGN KEY (id_photo) REFERENCES users (id));

Adding hash:

cur.execute('''INSERT INTO photos(id_photo, photo_hash) VALUES(%s, %s)''', id, str(result_image_recognition['photo_hash'])])

SQL Query by which I calculate the Euclidean distance between my hash and stored hashes:

SELECT id_photo, BIT_COUNT(photo_hash ^ '0f3f2764ecc482c2') FROM photos;

The photos table contains 7889 photos, 959 of them are mistakenly determined by this query as completely identical (Euclidean distance is 0). About a week I can not solve this problem please someone help me.

user3391185
  • 33
  • 1
  • 1
  • 5
  • You are using 64-bit hashes. I would try with 160-bit hashes at least using SHA1 or even SHA256-512 to reduce collisions. – The Impaler Oct 03 '21 at 15:01

1 Answers1

1

You need to convert the hex strings to integers before doing xor operations

SELECT id_photo, BIT_COUNT(CONV(photo_hash, 16, 10) ^ CONV('0f3f2764ecc482c2', 16, 10)) FROM photos;

because all strings the first character of which is not 1-9 are converted to 0.

mysql> select 'abc' ^ 'def'; # -> 0
mysql> select CONV('abc', 16, 10) ^ CONV('def', 16, 10);  # -> 1875
umitu
  • 2,423
  • 1
  • 10
  • 28
  • Hello again) Сan you please tell me the easiest way to compare two faces in python? I want to get(from photo) and store biometrics of the faces in my database and then using SQL Query to search for similar faces? i found the module for python 'face_recognition' but nothing worked, then I asked the question here) https://stackoverflow.com/questions/69606556/%d0%a1ompare-two-faces-using-python3-module-face-recognition but I'm afraid the person who answered me did not understand) – user3391185 Oct 18 '21 at 16:54