Hamming distance in ancient Microsoft DBMS

Question

The Problem

I want to find image duplicates and similar images in MS SQL Server 7.

Edit

I got it running using sql cursors - it's slow but it works, thanks again for the comment. See my answer for the solution I came up with.

Specifically ...

I have a database containing image paths and the fingerprints of the associated images computed with the help of this dhash algorithm. I'm using a variation where I'm storing 128 bits per image (horizontal and vertical gradient) in a BINARY(16) column.

What I want to be able to do is something in the lines of:

DECLARE @id INT
SET @id = ...

DECLARE @given_hash BINARY(16)
SET @given_hash = ...

SELECT TOP 10 file_path,hash,
    (hamming_distance(hash, @given_hash)) AS distance
FROM my_table
WHERE distance <= 20
ORDER BY distance ASC

What is working?

It is trivial to obtain exact duplicates - just use a WHERE hash = @hash_to_compare and that's it.

What's not working?

However, I want to be able to use a similarity measure (Hamming Distance) to account for small manipulations/defects/compression artifacts etc. I've come up with a stored procedure implementing the distance measure:

CREATE PROCEDURE hamming_distance128
    @hash BINARY(16),
    @supplied BINARY(16)
AS
    DECLARE @i INT, @j INT
    SET @i = 1
    SET @j = 1

    DECLARE @count INT
    SET @count = 0

    DECLARE @byte TINYINT

    DECLARE @length TINYINT
    SET @length = 16

    WHILE @i <= @length
    BEGIN
        SET @j = 1
        SET @byte = CAST((ASCII(SUBSTRING(@hash,@i,1)) ^ ASCII(SUBSTRING(@supplied,@i,1))) AS TINYINT)

        WHILE @j < 256
        BEGIN
            SET @count = @count + (CASE (@byte & @j) WHEN 0 THEN 0 ELSE 1 END)
            SET @j = @j * 2
        END

        SET @i = @i + 1
    END

    SELECT @count
GO

Unfortunately the DBMS (SQL Server 7 - it's impossible to upgrade/change this) won't let me use it to compute distances within a query and this piece of j*nk doesn't support user defined functions. Sure enough I didn't find anything like MySQL's BIT_COUNT that would make this a no brainer for T-SQL.

Is there hope of getting this to work in T-SQL on SQL Server 7?

Help very much appreciated!

Answer 1

I got it running using SQL cursors and a temporary table. Thanks again @Tomalak

I provide my implementation for future reference, hope this is useful to anyone

CREATE PROCEDURE hamming_distance128
    @hash BINARY(16),
    @supplied BINARY(16),
    @distance TINYINT OUTPUT
AS
    DECLARE @i INT, @j INT
    SET @i = 1
    SET @j = 1

    DECLARE @count TINYINT
    SET @count = 0

    DECLARE @byte TINYINT

    DECLARE @length TINYINT
    SET @length = 16

    WHILE @i <= @length
    BEGIN
        SET @j = 1
        SET @byte = CAST((ASCII(SUBSTRING(@hash,@i,1)) ^ ASCII(SUBSTRING(@supplied,@i,1))) AS TINYINT)

        WHILE @j < 256
        BEGIN
            SET @count = @count + (CASE (@byte & @j) WHEN 0 THEN 0 ELSE 1 END)
            SET @j = @j * 2
        END

        SET @i = @i + 1
    END

    SET @distance = @count
GO


DECLARE @min_similarity FLOAT
SET @min_similarity = 0.85 -- | 85%

DECLARE @supplied_hash BINARY(16)
SET @supplied_hash = 0x392929295B4B13371B0301272D2B2509

IF OBJECT_ID('tempdb..#distances') IS NOT NULL
BEGIN DROP TABLE #distances END

CREATE TABLE #distances
(id INT NOT NULL,
 similarity FLOAT NOT NULL)

DECLARE
    @tmp_id INT,
    @dhash BINARY(16),
    @distance TINYINT,
    @similarity FLOAT

DECLARE rowCursor CURSOR
LOCAL FORWARD_ONLY READ_ONLY
FOR
    SELECT id,dhash_value
    FROM hashes
    OPEN rowCursor
        FETCH NEXT FROM rowCursor
        INTO @tmp_id,@dhash

        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXECUTE hamming_distance128 @dhash, @supplied_hash, @distance OUTPUT

            IF @distance < 128
            BEGIN
                SET @similarity = CAST(128 - @distance AS FLOAT) / 128.0

                IF @similarity >= @min_similarity
                BEGIN
                    INSERT INTO #distances (id,similarity)
                    VALUES (@tmp_id,@similarity)
                END
            END

            FETCH NEXT FROM rowCursor
            INTO @tmp_id,@dhash
        END
    CLOSE rowCursor
DEALLOCATE rowCursor

SELECT hashes.id,#distances.similarity
FROM #distances
    INNER JOIN hashes
    ON hashes.id = #distances.id
ORDER BY #distances.similarity DESC

DROP TABLE #distances