Search Image by Keyword¶

Tutorial Difficulty: ★☆☆☆☆
10 min read
Languages: SQL (100%)
File location: tutorial_en/thanosql_search/search_image_by_keyword.ipynb
References: Food Image and Nutrition Text Introduction Dataset

Tutorial Introduction¶

Understanding Keyword-Image Search

ThanoSQL provides image search using keywords. The search uses an image classification model to set a keyword as the target value, then adds an index column with the images updated from the trained model. In other words, the keyword-image search finds images that correspond to the desired target value(category).

The dictionary defines "search" as "finding the necessary materials in a book or computer according to its purpose." The ThanoSQL keyword-image search does not search for information that includes a specific word(keyword). Instead, it creates a model that predicts words from the features of an image and returns the image with the highest relevance.

The following are examples and applications of the ThanoSQL keyword image search algorithm.

Use shopping categories to create a model, and use it to create an index column. The index column combined with attributes such as image registration dates, provides more accurate searches.
You can create your own image search service by utilizing image-image search and text-image search from their respective tutorials.

In This Tutorial

👉 Use a combination of the "SEARCH" query and the "SELECT" query to search images using specific keywords.

Dataset Description

The Introduction to Food Images and Nutrition Information Text dataset was organized by the Ministry of Science and ICT and is supported by the Korea Intelligence Information Society Agency. It consists of 400 food items and 842,000 images. This tutorial uses only a few(10 types, 1,190 photos) images from that dataset.

0. Prepare Dataset¶

As mentioned in the ThanoSQL Workspace, you must create an API token and run the query below to execute the query of ThanoSQL.

In [ ]:

            
                Copied!
                
%load_ext thanosql
%thanosql API_TOKEN=<Issued_API_TOKEN>
%load_ext thanosql
%thanosql API_TOKEN=

Prepare Dataset¶

In [2]:

            
                Copied!
                
%%thanosql
GET THANOSQL DATASET diet_data
OPTIONS (overwrite=True)
%%thanosql
GET THANOSQL DATASET diet_data
OPTIONS (overwrite=True)

Success

Query Details

"GET THANOSQL DATASET" downloads the specified dataset to the workspace.
"OPTIONS" specifies the option values to be used for the GET THANOSQL DATASET clause.
- "overwrite": determines whether to overwrite a dataset if it already exists. If set as True, the old dataset is replaced with the new dataset (bool, optional, True|False, default: False)

In [3]:

            
                Copied!
                
%%thanosql
COPY diet 
OPTIONS (if_exists='replace')
FROM 'thanosql-dataset/diet_data/diet.csv'
%%thanosql
COPY diet 
OPTIONS (if_exists='replace')
FROM 'thanosql-dataset/diet_data/diet.csv'

Success

Query Details

"COPY" specifies the name of the dataset to be saved as a database table.
"OPTIONS" specifies the option values to be used for the COPY clause.
- "if_exists": determines how the function should handle the case where the table already exists, it can either raise an error, append to the existing table, or replace the existing table (str, optional, 'fail'|'replace'|'append', default: 'fail')

1. Check Dataset¶

To create a keyword-image search model, we use the diet table located in the ThanoSQL workspace database. Run the query below to check the contents of the table.

In [4]:

            
                Copied!
                
%%thanosql
SELECT * 
FROM diet
LIMIT 5
%%thanosql
SELECT * 
FROM diet
LIMIT 5

Out[4]:

	image_path	label
0	thanosql-dataset/diet_data/diet/백향과/0_A220148X...	백향과
1	thanosql-dataset/diet_data/diet/백향과/0_A220148X...	백향과
2	thanosql-dataset/diet_data/diet/백향과/1_A220148X...	백향과
3	thanosql-dataset/diet_data/diet/백향과/0_A220148X...	백향과
4	thanosql-dataset/diet_data/diet/백향과/0_A220148X...	백향과

Understanding the Data Table

The diet table contains the following information.

image_path: image path
label: image label

2. Build an Image Search Model¶

To create an image search model with the name diet_image_classification using the diet table, run the following query.
(Estimated duration of query execution: 3 min)

In [5]:

            
                Copied!
                
                    
                    
                
                

        
%%thanosql
BUILD MODEL diet_image_classification
USING ConvNeXt_Tiny
OPTIONS (
    image_col='image_path', 
    label_col='label', 
    max_epochs=1,
    overwrite=True
    )
AS 
SELECT *
FROM diet
%%thanosql
BUILD MODEL diet_image_classification
USING ConvNeXt_Tiny
OPTIONS (
    image_col='image_path', 
    label_col='label', 
    max_epochs=1,
    overwrite=True
    )
AS 
SELECT *
FROM diet

Success

Query Details

"BUILD MODEL" creates and trains a model named diet_image_classification.
"USING" specifies ConvNeXt_Tiny as the base model.
"OPTIONS" specifies the option values used to create a model.
- "image_col": the name of the column containing the image path (str, default: 'image_path')
- "label_col": the name of the column containing information about the target (str, default: 'label')
- "max_epochs": number of times to train with the training dataset (int, optional, default: 3)
- "overwrite": determines whether to overwrite a model if it already exists. If set as True, the old model is replaced with the new model (bool, optional, True|False, default: False)

3. Predict¶

To use the diet_image_classification model created in the previous step for prediction of diet, run the following query.

In [6]:

            
                Copied!
                
                    
                    
                
                

        
%%thanosql
PREDICT USING diet_image_classification
OPTIONS (
    image_col='image_path',
    result_col='predict_result'
    )
AS 
SELECT *
FROM diet
%%thanosql
PREDICT USING diet_image_classification
OPTIONS (
    image_col='image_path',
    result_col='predict_result'
    )
AS 
SELECT *
FROM diet

Out[6]:

	image_path	label	predict_result
0	thanosql-dataset/diet_data/diet/백향과/0_A220148X...	백향과	백향과
1	thanosql-dataset/diet_data/diet/백향과/0_A220148X...	백향과	백향과
2	thanosql-dataset/diet_data/diet/백향과/1_A220148X...	백향과	백향과
3	thanosql-dataset/diet_data/diet/백향과/0_A220148X...	백향과	백향과
4	thanosql-dataset/diet_data/diet/백향과/0_A220148X...	백향과	백향과
...	...	...	...
1185	thanosql-dataset/diet_data/diet/사과파이/0_A020511...	사과파이	사과파이
1186	thanosql-dataset/diet_data/diet/사과파이/0_A020511...	사과파이	사과파이
1187	thanosql-dataset/diet_data/diet/사과파이/1_A020511...	사과파이	사과파이
1188	thanosql-dataset/diet_data/diet/사과파이/0_A020511...	사과파이	사과파이
1189	thanosql-dataset/diet_data/diet/사과파이/0_A020511...	사과파이	사과파이

1190 rows × 3 columns

Query Details

"PREDICT USING" predicts the outcome using the diet_image_classification model.
"OPTIONS" specifies the option values used to predict with the model.
- "image_col": the name of the column containing the image path (str, default: 'image_path')
- "result_col": the column that contains the predicted results (str, optional, default: 'predict_result')

4. Search¶

To retrieve data with specific conditions, run a query using the "PREDICT USING", "SELECT", "WHERE" clauses. You can search data where the label is '사과파이'('apple pie') and where the prediction result is also '사과파이' by running the following query.

In [7]:

            
                Copied!
                
                    
                    
                
                

        
%%thanosql
SELECT *
FROM (
    PREDICT USING diet_image_classification
    AS
    SELECT *
    FROM diet
    )
WHERE label=predict_result
AND label LIKE '사과파이'
LIMIT 10
%%thanosql
SELECT *
FROM (
    PREDICT USING diet_image_classification
    AS
    SELECT *
    FROM diet
    )
WHERE label=predict_result
AND label LIKE '사과파이'
LIMIT 10

Out[7]:

	image_path	label	predict_result
0	thanosql-dataset/diet_data/diet/사과파이/0_A020511...	사과파이	사과파이
1	thanosql-dataset/diet_data/diet/사과파이/0_A020511...	사과파이	사과파이
2	thanosql-dataset/diet_data/diet/사과파이/0_A020511...	사과파이	사과파이
3	thanosql-dataset/diet_data/diet/사과파이/0_A020511...	사과파이	사과파이
4	thanosql-dataset/diet_data/diet/사과파이/0_A020511...	사과파이	사과파이
5	thanosql-dataset/diet_data/diet/사과파이/0_A020511...	사과파이	사과파이
6	thanosql-dataset/diet_data/diet/사과파이/0_A020511...	사과파이	사과파이
7	thanosql-dataset/diet_data/diet/사과파이/0_A020511...	사과파이	사과파이
8	thanosql-dataset/diet_data/diet/사과파이/0_A020511...	사과파이	사과파이
9	thanosql-dataset/diet_data/diet/사과파이/0_A020511...	사과파이	사과파이

Query Details

"SELECT * FROM (...)" selects all the results of the nested "PREDICT USING" query.
"WHERE" sets the selection condition. "AND" allows multiple conditions.
- "label=predict_result": queries only data where the label column and predict_result column are equal
- "label LIKE '사과파이'": queries data where the label value is 'apple pie'

5. In Conclusion¶

In this tutorial, we created an image search model to search for food images from the food image dataset using keywords. As this is a beginner-level tutorial, we focused on the process rather than accuracy. The model's accuracy can be improved by adjusting various options, such as increasing the epoch or dataset size. Furthermore, follow along with the image-image and image-text search tutorials to create your own search services.

Inquiries About Deploying a Model for Your Own Service

If you have any difficulties creating your own model using ThanoSQL or applying it to your services, please feel free to contact us below😊

For inquiries regarding building a keyword-image search models: contact@smartmind.team

Last update: 2023-08-31