A previous post described agentic retrieval against an Azure AI search knowledge base but it is especially beneficial to augment the data store with structured data in a relational table for all the objects detected in the drone world from aerial images:

1. Build a table for the detected objects as follows:

import mysql.connector

from mysql.connector import errorcode

# Connection parameters

config = {

    'user': 'your_username',

    'password': 'your_password',

    'host': 'your_server.mysql.database.azure.com',

    'database': 'droneworld',

    'ssl_ca': '/path/to/BaltimoreCyberTrustRoot.crt.pem',

    'ssl_verify_cert': True

}

# SQL to create the table

create_table_sql = """

CREATE TABLE IF NOT EXISTS drone_assets (

    name VARCHAR(255) NOT NULL,

    description TEXT,

    tags TEXT,

    boundingbox TEXT NOT NULL,

    sourcefile VARCHAR(255) NOT NULL,

    location POINT,

    created DATETIME,

    modified DATETIME,

    state VARCHAR(100),

    SPATIAL INDEX(location)

);

"""

try:

    cnx = mysql.connector.connect(**config)

    cursor = cnx.cursor()

    cursor.execute(create_table_sql)

    print("Table 'drone_assets' created successfully (if it didn't exist).")

except mysql.connector.Error as err:

    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:

        print("Access denied: Check your username or password.")

    elif err.errno == errorcode.ER_BAD_DB_ERROR:

        print("Database does not exist.")

    else:

        print(f"Error: {err}")

finally:

    if 'cursor' in locals():

        cursor.close()

    if 'cnx' in locals() and cnx.is_connected():

        cnx.close()

2. Integrate the table with the AI search, the code uploading the document vector can also upsert to the database.

3. Execute SQL queries with structured query operators and leverage location and timestamp for filtering as appropriate.