Image Retrieval

​

Introduction

wget https://raw.githubusercontent.com/smartmind-team/mintlify-docs/main/examples/Image_Retrieval.ipynb

​

Dataset

​

Goals

1. Obtain photo files and their metadata (e.g., photo dimensions, photographer names).
2. Perform advanced queries on both structured and unstructured data using ThanoSQL to retrieve the desired metadata.

​

Install Required Libraries

!pip install pillow matplotlib

​

Displaying ThanoSQL Query Results in Jupyter Notebooks

from IPython.display import display

def check_query_result(query_result):
    if query_result.error_result:
        print(query_result.error_result)
    else:
        if query_result.records is not None and len(query_result.records.data) > 0:
            df = query_result.records.to_df()
            display(df)
        else:
            print("Query executed successfully")

​

Displaying Images in Jupyter Notebooks

import requests
from PIL import Image
from io import BytesIO
import matplotlib.pyplot as plt
import pandas as pd

def show_images(input, image_column_name=None):
    """
    Display images from URLs. The input can be either a single URL or a DataFrame containing multiple URLs.
    vs
    Args:
        input (str or DataFrame): Single image URL or DataFrame containing image URLs.
        image_column_name (str): The column name containing image URLs if input is a DataFrame.
    """
    # Check if the input is a DataFrame
    if isinstance(input, pd.DataFrame):
        df = input
        num_images = len(df)
        fig, axes = plt.subplots(1, num_images, figsize=(15, 5))
        
        if num_images == 1:
            axes = [axes]
        
        for idx, (image_url, ax) in enumerate(zip(df[image_column_name], axes)):
            response = requests.get(image_url)
            response.raise_for_status()
            image = Image.open(BytesIO(response.content))
            ax.imshow(image)
            ax.axis('off')
    
    # If the input is a single URL
    elif isinstance(input, str):
        fig, ax = plt.subplots(figsize=(5, 5))
        response = requests.get(input)
        response.raise_for_status()
        image = Image.open(BytesIO(response.content))
        ax.imshow(image)
        ax.axis('off')

    else:
        raise ValueError("Input must be either a DataFrame or a single image URL (str).")
    
    plt.tight_layout()
    plt.show()

​

Procedure

0. Download Datasets:
   • First, we will load the datasets into pandas DataFrames and display their shapes to understand the structure and size of the data.
   Copy

   import pandas as pd
   unsplash_embed_sample = pd.read_csv("unsplash_embed_sample.csv")
   unsplash_meta_sample = pd.read_csv("unsplash_meta_sample.csv")   
   
   print("unsplash_meta_sample:", unsplash_meta_sample.shape, "unsplash_embed_sample:", unsplash_embed_sample.shape)
   

   On execution, we get:
   • This step loads the datasets into pandas DataFrames and prints their shapes to give an overview of the data size and structure.
1. Import ThanoSQL Library:
   • Next, we need to import the ThanoSQL library and create a client instance. This client will be used to interact with the ThanoSQL engine.
   You can find your API Token and Engine URL by following these steps:

   1. Go to your workspace’s settings page.
   2. Navigate to the “Developer” tab.
   3. Locate and copy your API Token and Engine URL.
   Copy

   from thanosql import ThanoSQL
   client = ThanoSQL(api_token="your_api_token", engine_url="engine_url")
   

2. Upload Data to Tables:
   • We upload the unsplash_embed_sample table to ThanoSQL. This table contains image paths and their corresponding embeddings.
   Copy

   table = client.table.upload('unsplash_embed_sample', 'unsplash_embed_sample.csv', if_exists='replace')
   table.get_records(limit=10).to_df()
   

   On execution, we get:
   • This step uploads the unsplash_embed_sample data to ThanoSQL and retrieves the first 10 records to confirm the upload.
   • Similarly, we upload the unsplash_meta_sample table which contains detailed metadata about the images.
   Copy

   table = client.table.upload('unsplash_meta_sample', 'unsplash_meta_sample.csv', if_exists='replace')
   table.get_records(limit=10).to_df()
   

   On execution, we get:
   • This step uploads the unsplash_meta_sample data to ThanoSQL and retrieves the first 10 records to confirm the upload.
3. Create Embedding Column:
   • To store the embedding vectors, we need to create an embedding column in the unsplash_embed_sample table.
   Copy

   query_result = client.query.execute(
       """
       CREATE EXTENSION IF NOT EXISTS vector;
   
       ALTER TABLE unsplash_embed_sample
       ADD COLUMN IF NOT EXISTS embedding vector(512)
       """
   )
   check_query_result(query_result)
   

   On execution, we get:
   • This step ensures that the vector extension is available and adds an embedding column to the unsplash_embed_sample table if it does not already exist.
4. Embed Images:
   • Now, we calculate and update the embedding vectors for the images in the unsplash_embed_sample table using a pre-trained model.
   Copy

   query_result = client.query.execute(
       """
       -- This query calculates embeddings for each photo URL using a specified model and saves it to the specified table
       WITH calculated_embeddings AS (
           -- Step 1: Calculate embeddings
           SELECT
               photo_url,
               thanosql.embed(
                   engine := 'huggingface',           
                   input := photo_url,                
                   model := 'openai/clip-vit-base-patch32',
                   model_args := '{"device_map": "cpu"}'
               ) AS embedding                        
                   -- Alias the result of the embedding function as 'embedding'
           FROM
               unsplash_embed_sample                
       )
       -- Step 2: Update the original table with the calculated embeddings
       UPDATE unsplash_embed_sample
       SET 
           embedding = calculated_embeddings.embedding 
               -- Set the 'embedding' column with the calculated embeddings
       FROM 
           calculated_embeddings                    
               -- Use the 'calculated_embeddings' as the source for the new values
       WHERE
           unsplash_embed_sample.photo_url = calculated_embeddings.photo_url;
               -- Ensure that each row in 'unsplash_embed_sample' is matched with the corresponding row in 'calculated_embeddings' 
               -- by matching the 'photo_url' column in both tables
       """
   )
   check_query_result(query_result)
   

   On execution, we get:
5. Search by Image:
   • In this section, we’ll perform a similarity search using an example image. We will retrieve the embedding vector for an image of a coastal cliff and then use that vector to find similar images.
   • This calls the show_images function with a single image URL of a coastal cliff which will be used to search similar images from the sample dataset. It will display the image inline in the notebook.
   Copy

   show_images('https://images.unsplash.com/photo-1506887419028-47afaf38c27e')
   

   On execution, we get:
   • This query retrieves the three most similar images to the example image based on their embeddings with the detailed metadata for the images.
   Copy

   query_result = client.query.execute(
       """
       -- This query calculates the embedding for a specific image URL, finds the top 3 similar images in the database,
       -- and retrieves their metadata from the 'unsplash_meta_sample' table.
       WITH calculated_embeddings AS (
           -- Step 1: Calculate embedding for the specific image URL
           SELECT 
               thanosql.embed(
                   engine := 'huggingface',           
                   input := 'https://images.unsplash.com/photo-1506887419028-47afaf38c27e', 
                   model := 'openai/clip-vit-base-patch32',
                   model_args := '{"device_map": "cpu"}' 
               ) AS embedding                        
                   -- Alias the result of the embedding function as 'embedding'
       )
       -- Step 2: Select metadata of the top 3 similar images
       SELECT 
           photo_id,                      
           photo_image_url,               
           photo_submitted_at,            
           photo_width,                   
           photo_height,                  
           photographer_username,         
           photographer_first_name,       
           photographer_last_name,        
           exif_camera_make,              
           exif_camera_model,             
           exif_iso,                      
           exif_aperture_value,           
           exif_focal_length,             
           exif_exposure_time             
       FROM 
           unsplash_meta_sample
       WHERE 
           photo_id IN (
               -- Step 3: Retrieve the top 3 similar images based on embedding similarity
               SELECT 
                   photo_id
               FROM 
                   unsplash_embed_sample
               ORDER BY 
                   embedding <-> (
                       SELECT embedding FROM calculated_embeddings 
                           -- Calculate distance to the specific image's embedding
                   )
               LIMIT 3                    
                   -- Limit to the top 3 similar images
           );
       """
   )
   df = query_result.records.to_df()
   df
   

   On execution, we get:
   • We can now display the retrieved images using the metadata.
   Copy

   show_images(df, 'photo_image_url')
   

   On execution, we get:
6. Search by Text:
   • We can also perform a similarity search using a text description. Here, we search for the image that is most similar to the description “a photo of the desert”.
   Copy

   query_result = client.query.execute(
       """
       -- This query calculates the embedding for a description of an image ('a photo of the desert'), finds the most similar image in the database,
       -- and retrieves its metadata from the 'unsplash_meta_sample' table.
       WITH calculated_embeddings AS (
           -- Step 1: Calculate embedding for the given description
           SELECT 
               thanosql.embed(
                   engine := 'huggingface',           
                   input := 'a photo of the desert',  
                   model := 'openai/clip-vit-base-patch32',
                   model_args := '{"device_map": "cpu"}' 
               ) AS embedding                        
                   -- Alias the result of the embedding function as 'embedding'
       )
       -- Step 2: Select metadata of the most similar image
       SELECT 
           photo_id,                      
           photo_image_url,               
           photo_submitted_at,            
           photo_width,                   
           photo_height,                  
           photographer_username,         
           photographer_first_name,       
           photographer_last_name,        
           exif_camera_make,              
           exif_camera_model,             
           exif_iso,                      
           exif_aperture_value,           
           exif_focal_length,             
           exif_exposure_time             
       FROM 
           unsplash_meta_sample
       WHERE 
           photo_id IN (
               -- Step 3: Retrieve the most similar image based on embedding similarity
               SELECT 
                   photo_id
               FROM 
                   unsplash_embed_sample
               ORDER BY 
                   embedding <-> (
                       SELECT embedding FROM calculated_embeddings 
                           -- Calculate distance to the embedding of the description
                   )
               LIMIT 1                    
                   -- Limit to the single most similar image
           );
       """
   )
   df = query_result.records.to_df()
   df
   

   On execution, we get:
   • Finally, we display the retrieved image.
   Copy

   show_images(df, 'photo_image_url')
   

   On execution, we get:
   • This code fetches and displays the image using its URL from the metadata.

​

Conclusion