The Power of Visual Data

Concepts are a powerful way to categorize, analyze, and extract insights from your visual data. By leveraging SQL queries, you can efficiently explore, filter, and structure your data based on key concepts detected in images and videos.

By the end of this tutorial, you’ll be able to query and interpret concept data in Coactive, enabling you to turn unstructured visual content into actionable insights.

Retrieve all Instances of a Concept

Retrieve all rows from the coactive_table where a specific concept, such as sports, has been identified.

-- Retrieve rows where the 'sports' concept takes place
SELECT
  *
FROM
  coactive_table
WHERE
  sports = 1;

Explaination

Retrieve all rows from the coactive_table where the sports column is set to 1. Since the sports column acts as a binary flag (0 or 1) to indicate whether sports are occurring in the corresponding video or keyframe, this query will return all instances where something related to sports has been identified in the dataset.

Find the minimum and maximum probability for a concept

Identify the range of probabilities for a given concept, which helps to determine potential thresholds.

-- Find the minimum and maximum probabilities for the 'sports' concept
SELECT
  MIN(sports_prob) AS min_probability,
  MAX(sports_prob) AS max_probability
FROM
  coactive_table_adv;

Explanation

This query gives you a range of the min and max values of a concept’s probabilities. MIN retrieves the lowest probability, while MAX retrieves the highest. From there, you can incorporate threshold probabilities.

Filter keyframes by concept probability threshold

Filter keyframes where a concept’s probability exceeds a specific threshold, sorted by probability.

-- Retrieve keyframes with a 'sports_prob' above a threshold
SELECT
  sports_prob,
  keyframe_time_ms,
  coactive_video_id,
  coactive_image_id
FROM
  coactive_table_adv
WHERE
  sports_prob > 0.5 -- Adjust the threshold as needed
ORDER BY
  sports_prob DESC;

Explanation

This query will display the threshold results in descending order of highest to lowest threshold with a threshold cutoff at 0.5 probability for the sports concept.

Thresholds help refine results by filtering out low-confidence matches. Choosing an appropriate threshold ensures more accurate outcomes, as different concepts have unique probability distributions. Experimenting with threshold values can optimize results for specific use cases.

Calculate concept probabilities as percentages

Calculate the probability as a percentage and display it for keyframes with a concept probability above a threshold.

-- Calculate probabilities as percentages for easy interpretation
WITH Concept AS (
  SELECT
    coactive_image_id,
    sports_prob AS concept_probability
  FROM
    coactive_table_adv
)
SELECT
  coactive_image_id,
  concept_probability,
  CONCAT(ROUND(concept_probability * 100), '%') AS concept_percentage_probability
FROM
  Concept
WHERE
  concept_probability > 0.50
ORDER BY
  concept_probability DESC;

Explanation

The query is selecting only the keyframes with concept scores above the threshold 0.5 and then ordering the outputs in descending order.

Retrieve Transcription Text from Audio Segments

This query extracts the transcription text for audio segments from the coactive_table_audio table. Transcriptions are crucial for analyzing speech data and identifying key audio content.

-- Retrieve transcription text from the audio table
SELECT
  coactive_audio_segment_id,
  audio_segment_speech_to_text_transcription AS transcription,
  audio_segment_start_time_ms,
  audio_segment_end_time_ms
FROM
  coactive_table_audio
WHERE
  audio_segment_speech_to_text_transcription IS NOT NULL
ORDER BY
  audio_segment_start_time_ms ASC;

Explanation

The audio_segment_speech_to_text_transcription column contains the transcription for each audio segment.

The query filters out rows without transcriptions (IS NOT NULL) and orders results by the start time for chronological analysis.