Hi, What is the best way to compare to Images in the database? I tried to compare them (@Image is the type Image):
Select * from Photos
where [Photo] = @Image
But receives the error "The data types image and image are incompatible in the equal to operator".
Since the Image data type is a binary and huge space for storing data, IMO, the easiest way to compare Image fields is hash comparison. So you need to store hash of the Photo column on your table.
If you need to compare the image you should retrive all the images from the database and do that from the language that you use for accessing the database. This is one of the reasons why it's not a best practice to store images or other binary files in a relational database. You should create unique file name every time when you want to store an image in a database. Rename the file with this unique file name, store the image on the disk and insert in your database it's name on the disk and eventually the original name of the file or the one provided by the user of your app.
It depends on how accurate you want to be and how many images you need to compare. You can use various functions like DATALENGTH and SUBSTRING or READTEXT to do some comparisons. Alternatively, you could write code in the CLR and implement it through a stored procedure to do comparisons.
Comparing images falls under a specific category of Computer science, that is called image processing. You should search some libraries that provide image processing capabilities. Using that you can compare upto what ratio given two images are same or identical. you can have 2 images matching each other upto 50% or more or less. There are mathematical algorithm that define the comparison formulae which returns the ratio.
Hope this gives you a direction to work further on your Problem..
Hi there,
If you are using SQL Server, then you might want to give a try to Volpet's Table Diff:
You can try a fully-functional copy for 30 days.
Please let me know for anything.
Thank you,
Giammarco
Generally, as it's been mentioned already, you need to use dedicated algorithms from the image processing shelve.
Moreover, it's hard to give precise answer because the question is too general. Two images may be considered as different or not based on number of properties.
For instance, you can have one image of a flower 100x100 pixels and image of the same flower but resized to 50x50 pixels. For some purposes and applications these two will be considered as similar (regardless of different dimensions), but will be different images for other purposes.
You may want to check how image comparison is realised by some tools and learn how it works:
If you don't want to compare image content but just check if two binary streams (image files, other binary files, binary object with image content) are equivalent, then you can compare MD5 checksums of images.