sql-server - 古代 Microsoft DBMS 中的汉明距离

Question

问题

我想在 MS SQL Server 7 中查找重复图像和相似图像。

编辑

我使用 sql 游标让它运行 - 它很慢但它有效，再次感谢您的评论。请参阅我提出的解决方案的答案。

具体...

我有一个数据库，其中包含图像路径和在 this dhash algorithm 的帮助下计算的相关图像的指纹 .我使用的变体是在 BINARY(16) 列中为每个图像(水平和垂直渐变)存储 128 位。

我想做的是:

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

什么在起作用？

获得精确的副本很简单 - 只需使用 WHERE hash = @hash_to_compare 即可。

什么不起作用？

但是，我希望能够使用相似性度量 (Hamming Distance) 来解释小的操作/缺陷/压缩伪影等。我想出了一个实现距离测量的存储过程:

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

不幸的是，DBMS(SQL Server 7 - 无法升级/更改它)不允许我使用它来计算查询中的距离，并且这个 j*nk 不支持用户定义的函数。果然，我没有找到像 MySQL 的 BIT_COUNT 这样的东西，这对于 T-SQL 来说是轻而易举的事。

是否有希望让它在 SQL Server 7 上的 T-SQL 中工作？

非常感谢帮助!

Answer 1

我使用 SQL 游标和临时表让它运行起来。再次感谢@Tomalak

我提供我的实现以供将来引用，希望这对任何人都有用

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