Video Frame Extraction

Objective

Extract frames from videos for creating thumbnails, preview images, and enabling image-based analysis. This pattern supports video preview generation and visual content indexing.

Step 1: Create Video Source Table

Create a table for source videos.

CREATE TABLE video_sources (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    video_file VIDEO(MP4),
    duration_seconds INTEGER,
    frame_rate DECIMAL(5, 2) DEFAULT 30.00,
    resolution VARCHAR(20),
    total_frames INTEGER,
    frames_extracted BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Extracted Frames Table

Create a table for storing extracted frames.

CREATE TABLE video_frames (
    id INTEGER PRIMARY KEY,
    video_id INTEGER NOT NULL,
    frame_number INTEGER NOT NULL,
    timestamp_seconds DECIMAL(10, 3),
    frame_image IMAGE(JPEG),
    frame_type VARCHAR(20) DEFAULT 'regular',
    is_keyframe BOOLEAN DEFAULT FALSE,
    is_thumbnail BOOLEAN DEFAULT FALSE,
    scene_description TEXT,
    extracted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (video_id) REFERENCES video_sources(id)
);

Step 3: Insert Sample Videos

Add sample video sources.

INSERT INTO video_sources (id, title, duration_seconds, frame_rate, resolution, total_frames) VALUES
    (1, 'Product Demo', 300, 30.00, '1920x1080', 9000),
    (2, 'Tutorial Video', 600, 30.00, '1920x1080', 18000),
    (3, 'Interview Clip', 180, 24.00, '1920x1080', 4320),
    (4, 'Event Highlight', 120, 60.00, '3840x2160', 7200),
    (5, 'Animated Explainer', 90, 30.00, '1920x1080', 2700);

Step 4: Insert Extracted Frames

Add extracted frame records.

INSERT INTO video_frames (id, video_id, frame_number, timestamp_seconds, frame_type, is_keyframe, is_thumbnail, scene_description) VALUES
    -- Video 1 frames
    (1, 1, 1, 0.000, 'start', TRUE, TRUE, 'Opening logo animation'),
    (2, 1, 150, 5.000, 'regular', TRUE, FALSE, 'Product on display'),
    (3, 1, 450, 15.000, 'regular', TRUE, FALSE, 'Feature demonstration'),
    (4, 1, 900, 30.000, 'regular', TRUE, FALSE, 'Close-up of interface'),
    (5, 1, 2700, 90.000, 'regular', TRUE, FALSE, 'User interaction demo'),
    (6, 1, 9000, 300.000, 'end', TRUE, FALSE, 'Call to action screen'),
    -- Video 2 frames
    (7, 2, 1, 0.000, 'start', TRUE, TRUE, 'Tutorial introduction'),
    (8, 2, 300, 10.000, 'regular', TRUE, FALSE, 'Step 1 explanation'),
    (9, 2, 900, 30.000, 'regular', TRUE, FALSE, 'Step 2 demonstration'),
    (10, 2, 1800, 60.000, 'regular', TRUE, FALSE, 'Step 3 walkthrough'),
    (11, 2, 5400, 180.000, 'regular', TRUE, FALSE, 'Advanced tips'),
    (12, 2, 18000, 600.000, 'end', TRUE, FALSE, 'Summary screen'),
    -- Video 3 frames
    (13, 3, 1, 0.000, 'start', TRUE, TRUE, 'Interview setup'),
    (14, 3, 720, 30.000, 'regular', TRUE, FALSE, 'Speaker introduction'),
    (15, 3, 2160, 90.000, 'regular', TRUE, FALSE, 'Key discussion point'),
    (16, 3, 4320, 180.000, 'end', TRUE, FALSE, 'Closing remarks');

Step 5: Update Extraction Status

Mark videos as having frames extracted.

UPDATE video_sources
SET frames_extracted = TRUE
WHERE id IN (1, 2, 3);

SELECT title, frames_extracted FROM video_sources;

Step 6: Get Video Thumbnails

Retrieve thumbnail frames for each video.

SELECT
    vs.title,
    vf.frame_number,
    vf.timestamp_seconds,
    vf.scene_description
FROM video_sources vs
INNER JOIN video_frames vf ON vs.id = vf.video_id
WHERE vf.is_thumbnail = TRUE
ORDER BY vs.title;

Step 7: Get Timeline Preview Frames

Get evenly distributed frames for timeline preview.

SELECT
    vs.title,
    vf.frame_number,
    vf.timestamp_seconds,
    vf.scene_description,
    vf.frame_type
FROM video_sources vs
INNER JOIN video_frames vf ON vs.id = vf.video_id
WHERE vs.id = 1
ORDER BY vf.frame_number;

Step 8: Count Frames by Video

Get frame count statistics per video.

SELECT
    vs.title,
    vs.total_frames,
    COUNT(vf.id) as extracted_frames,
    SUM(CASE WHEN vf.is_keyframe THEN 1 ELSE 0 END) as keyframe_count
FROM video_sources vs
LEFT JOIN video_frames vf ON vs.id = vf.video_id
GROUP BY vs.id, vs.title, vs.total_frames
ORDER BY vs.title;

Step 9: Find Scene Changes

Identify keyframes marking scene changes.

SELECT
    vs.title,
    vf.frame_number,
    vf.timestamp_seconds,
    vf.scene_description
FROM video_sources vs
INNER JOIN video_frames vf ON vs.id = vf.video_id
WHERE vf.is_keyframe = TRUE
  AND vf.frame_type = 'regular'
ORDER BY vs.title, vf.frame_number;

Step 10: Frame Density Analysis

Analyze frame extraction coverage.

SELECT
    vs.title,
    vs.duration_seconds,
    COUNT(vf.id) as frames_stored,
    vs.duration_seconds / NULLIF(COUNT(vf.id), 0) as seconds_per_frame,
    CAST(COUNT(vf.id) AS DECIMAL) / vs.duration_seconds * 60 as frames_per_minute
FROM video_sources vs
LEFT JOIN video_frames vf ON vs.id = vf.video_id
GROUP BY vs.id, vs.title, vs.duration_seconds
ORDER BY frames_per_minute DESC;

Step 11: Videos Without Frames

Find videos needing frame extraction.

SELECT
    id,
    title,
    duration_seconds,
    total_frames,
    'Needs extraction' as status
FROM video_sources
WHERE frames_extracted = FALSE
ORDER BY duration_seconds DESC;

Step 12: Frame Timeline Query

Get frames for video scrubbing preview.

SELECT
    frame_number,
    timestamp_seconds,
    CAST(timestamp_seconds / 60 AS INTEGER) as minute,
    CAST(timestamp_seconds AS INTEGER) % 60 as second,
    frame_type,
    scene_description
FROM video_frames
WHERE video_id = 2
ORDER BY frame_number;

Cleanup (Optional)

DROP TABLE IF EXISTS video_frames;
DROP TABLE IF EXISTS video_sources;

Expected Outcomes

• Frames extracted and stored
• Thumbnails identified
• Timeline preview available
• Scene detection tracked
• Frame statistics calculated

Frame Types

Key Concepts Learned

• Frame extraction storage
• Thumbnail selection
• Timeline preview queries
• Keyframe identification
• Scene change tracking