A previous article1 talked about vectorizing and analyzing drone images to populate a vector index with id, description and vector schema where the description was a JSONobject complete with the results of the analysis of object detection. This article customizes the search to answer questions posed by drone sensing applications by leveraging a SQL Server database to store the shredded description JSON with associated vectors and then using the built-in operators to query the objects associated with the vectors.

DROP TABLE IF EXISTS Production.ImageDescriptionEmbeddings;

GO

CREATE TABLE Production.ImageDescriptionEmbeddings

(

  ImageDescEmbeddingID INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED,

  ImageID INT NOT NULL,

  ImageDescriptionID INT NOT NULL,

  ImageModelID INT NOT NULL,

  CultureID nchar(6) NOT NULL,

  Embedding vector(1024)

);

-- Populate the table with the shredded json, written as a query but can also be used with a script and python odbc to insert as shown in references:

INSERT INTO Production.ImageDescriptionEmbeddings

SELECT p.ImageID, img.ImageDescriptionID, img.ImageModelID, img.CultureID,

AI_GENERATE_EMBEDDINGS(id.Description MODEL MyOllamaEmbeddingModel)

FROM Production.ImageModelImageDescriptionCulture img

JOIN Production Image p

ON img.ImageModelID = p.ImageModelID

JOIN Production.ImageDescription id

ON id.ImageDescriptionID = img.ImageDescriptionID

ORDER BY p.ImageID;

GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_ImageDescriptionEmbeddings_AlternateKey]

ON [Production].[ImageDescriptionEmbeddings]

# and this is how we vector_search

EXEC find_relevant_objects_vector_search

@prompt = N'Show me buildings with outdoor installations and fixtures',

@stock = 100,

@top = 20;

GO

CREATE DATABASE SCOPED CREDENTIAL [https://droneimagesopenai.openai.azure.com]

WITH IDENTITY = 'Managed Identity', SECRET - '{"resourceid": "https://cognitiveservices.azure.com"}';

GO

DROP EXTERNAL MODEL MyOpenAICompactEmbeddingModel

WITH (

     LOCATION = 'https://droneimages.centralus.cloudapp.azure.om/v1/embeddings',

     API_FORMAT = 'OpenAI',

     MODEL_TYPE = EMBEDDINGS,

     MODEL = 'nvidia/nv-embedqa-e5-v5-query',

     CREDENTIAL = [https://droneimabes.centralus.cloudapp.azure.com]

);

GO

Reference:

1. Image processing: https://1drv.ms/w/c/d609fb70e39b65c8/EWWmJNjERq1GqNkvyTYN1XkB8KxwYX4TpqI8IDbPZY59Ag?e=y4Odxs

2. previous articles: https://1drv.ms/w/c/d609fb70e39b65c8/EZQisMsUJqRHglAF6Mb2S7UB9qaUe2zd8uAs6qygmCXPYw?e=AYWB9r

3. Script to populate the image description embeddings table:

import requests

# Azure AI Search configuration

SEARCH_ENDPOINT = "https://<your-search-service>.search.windows.net"

SEARCH_API_KEY = "<your-api-key>"

INDEX_NAME = "index00"

# API headers

headers = {

    "Content-Type": "application/json",

    "api-key": SEARCH_API_KEY

}

# Query to fetch all entries and show only 'description'

query_url = f"{SEARCH_ENDPOINT}/indexes/{INDEX_NAME}/docs?api-version=2023-07-01&search=*"

response = requests.get(query_url, headers=headers)

# Process response

if response.status_code == 200:

    results = response.json()

    descriptions = [doc["description"] for doc in results.get("value", [])]

    return [desc for desc in descriptions]

else:

    print(f"Error: {response.status_code}, {response.text}")

    return []

4. Script to populate the table:

import pyodbc

server = 'tcp:<your_server.database.windows.net>'

database = 'DroneImagesDB'

username = '<your_username>'

password = '<your_password>'

port = '<your_port_number>'

cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+server+','+port+';DATABASE='+database+';ENCRYPT=yes;TrustServerCertificate=yes;UID='+username+';PWD='+ password)

cursor = cnxn.cursor()

print ('Inserting a new row into table')

#Insert Query

tsql = "INSERT INTO Production.ImageDescriptionEmbeddings (ImageID, ImageDescriptionID, ImageModelID, CultureID, Embedding) VALUES (?,?,?,?,?);"

with cursor.execute(id,description, generate_sha256_digest(description),'United States', vector):

    print ('Successfully Inserted!')