How to select in SQL by LSH distance?

Asked Jul 14 '22 at 15:15

Active Jul 14 '22 at 15:51

Viewed 154 times

Say, I have a table in Postgres (or MySQL), one of the columns called the_lsh holds a 70 char LSH in hex. And now I'd like to select. I have my 70 char LSH. Can I select all rows where the distance from each the_lsh to my LSH is no farther than 11 units? In js I have a function "findDiffBetweenTwoLSH", but to use it, I would need to select ALL the rows and compare LSHs one by one, but I need a very quick response.

Maybe I should store my LSH not as one column of 70 chars, but maybe split it into 35 columns one byte each, then perform the comparison? Maybe there is an LSH DIFF function already built-in or written by someone? Please help, thank you!

Here is an algorithm: https://github.com/NattapongSiri/tlsh_ts/blob/master/src/index.ts#L363 it's a moderately small func, bunch of calculations, nothing fancy, but not 3 lines of code still.

edited Jul 14 '22 at 15:51

asked Jul 14 '22 at 15:15

shal

2,854
4
20
31

2

If you are able to share your distance algorithm, somebody here might be able to evaluate whether this can be done efficiently with SQL. – Mike Organek Jul 14 '22 at 15:31
1

You're right, done – shal Jul 14 '22 at 15:51

How to select in SQL by LSH distance?

0 Answers0