Image Resize Operations

Objective

Use AIDB's RESIZE_IMAGE function to create resized versions of images. This enables creating thumbnails, optimized web versions, and responsive image variants.

Step 1: Create Original Images Table

Create a table for storing original high-resolution images.

CREATE TABLE original_images (
    id INTEGER PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    image IMAGE(JPEG),
    original_width INTEGER,
    original_height INTEGER,
    file_size BIGINT,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Resized Images Table

Create a table for storing resized variants.

CREATE TABLE resized_images (
    id INTEGER PRIMARY KEY,
    original_id INTEGER NOT NULL,
    size_name VARCHAR(50) NOT NULL,
    resized_image IMAGE(JPEG),
    target_width INTEGER NOT NULL,
    target_height INTEGER NOT NULL,
    actual_width INTEGER,
    actual_height INTEGER,
    file_size BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (original_id) REFERENCES original_images(id)
);

Step 3: Insert Sample Original Images

Add sample high-resolution images.

INSERT INTO original_images (id, filename, original_width, original_height, file_size) VALUES
    (1, 'product_hero_001.jpg', 4000, 3000, 8500000),
    (2, 'banner_main.jpg', 3840, 2160, 6200000),
    (3, 'team_photo.jpg', 5472, 3648, 12000000),
    (4, 'office_interior.jpg', 4096, 2730, 9800000),
    (5, 'event_photo.jpg', 6000, 4000, 15000000);

Step 4: Define Resize Presets

Create standard resize configurations.

CREATE TABLE resize_presets (
    preset_name VARCHAR(50) PRIMARY KEY,
    target_width INTEGER NOT NULL,
    target_height INTEGER NOT NULL,
    maintain_aspect BOOLEAN DEFAULT TRUE,
    quality INTEGER DEFAULT 85,
    use_case VARCHAR(100)
);

INSERT INTO resize_presets (preset_name, target_width, target_height, quality, use_case) VALUES
    ('thumbnail', 150, 150, 70, 'Grid thumbnails and previews'),
    ('small', 320, 240, 75, 'Mobile list views'),
    ('medium', 640, 480, 80, 'Tablet and small screens'),
    ('large', 1280, 960, 85, 'Desktop standard view'),
    ('xlarge', 1920, 1440, 90, 'Full HD displays'),
    ('social_square', 1080, 1080, 85, 'Social media square posts'),
    ('social_story', 1080, 1920, 85, 'Social media stories'),
    ('og_image', 1200, 630, 85, 'Open Graph sharing');

Step 5: Resize Images to Thumbnails

Create thumbnail versions using RESIZE_IMAGE.

INSERT INTO resized_images (id, original_id, size_name, target_width, target_height, file_size)
SELECT
    o.id,
    o.id,
    'thumbnail',
    150,
    150,
    o.file_size / 50
FROM original_images o;

UPDATE resized_images
SET resized_image = RESIZE_IMAGE(
    (SELECT image FROM original_images WHERE original_images.id = resized_images.original_id),
    150,
    150
)
WHERE size_name = 'thumbnail';

Step 6: Create Multiple Size Variants

Generate various size variants for responsive images.

INSERT INTO resized_images (id, original_id, size_name, target_width, target_height, file_size) VALUES
    -- Small variants
    (6, 1, 'small', 320, 240, 45000),
    (7, 2, 'small', 320, 180, 38000),
    (8, 3, 'small', 320, 213, 42000),
    -- Medium variants
    (9, 1, 'medium', 640, 480, 125000),
    (10, 2, 'medium', 640, 360, 98000),
    (11, 3, 'medium', 640, 427, 115000),
    -- Large variants
    (12, 1, 'large', 1280, 960, 380000),
    (13, 2, 'large', 1280, 720, 295000),
    (14, 3, 'large', 1280, 853, 350000);

Step 7: Query Available Sizes

Get all available sizes for an image.

SELECT
    o.filename,
    r.size_name,
    r.target_width,
    r.target_height,
    r.file_size
FROM original_images o
INNER JOIN resized_images r ON o.id = r.original_id
WHERE o.id = 1
ORDER BY r.target_width;

Step 8: Calculate Size Reduction

Analyze storage savings from resizing.

SELECT
    o.filename,
    o.file_size as original_size,
    SUM(r.file_size) as all_variants_size,
    o.file_size - SUM(r.file_size) as storage_difference
FROM original_images o
INNER JOIN resized_images r ON o.id = r.original_id
GROUP BY o.id, o.filename, o.file_size;

Step 9: Missing Size Variants

Find images missing specific sizes.

SELECT
    o.filename,
    p.preset_name,
    'Missing' as status
FROM original_images o
CROSS JOIN resize_presets p
LEFT JOIN resized_images r ON o.id = r.original_id AND r.size_name = p.preset_name
WHERE r.id IS NULL
ORDER BY o.filename, p.preset_name;

Step 10: Resize Queue

Create a queue for batch resizing.

CREATE TABLE resize_queue (
    id INTEGER PRIMARY KEY,
    original_id INTEGER NOT NULL,
    preset_name VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    priority INTEGER DEFAULT 5,
    queued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP,
    FOREIGN KEY (original_id) REFERENCES original_images(id)
);

INSERT INTO resize_queue (id, original_id, preset_name, priority)
SELECT
    ROW_NUMBER() OVER (),
    o.id,
    p.preset_name,
    CASE p.preset_name
        WHEN 'thumbnail' THEN 1
        WHEN 'small' THEN 2
        WHEN 'medium' THEN 3
        ELSE 5
    END
FROM original_images o
CROSS JOIN resize_presets p;

Step 11: Process Resize Queue

Update queue as images are processed.

UPDATE resize_queue
SET status = 'completed',
    completed_at = CURRENT_TIMESTAMP
WHERE original_id = 1 AND preset_name = 'thumbnail';

SELECT
    o.filename,
    rq.preset_name,
    rq.status,
    rq.priority
FROM resize_queue rq
INNER JOIN original_images o ON rq.original_id = o.id
ORDER BY rq.priority, rq.queued_at
LIMIT 10;

Step 12: Responsive Image Set Query

Get srcset data for responsive images.

SELECT
    o.filename,
    r.target_width || 'w' as size_descriptor,
    r.target_width,
    r.file_size
FROM original_images o
INNER JOIN resized_images r ON o.id = r.original_id
WHERE o.id = 1
ORDER BY r.target_width;

Cleanup (Optional)

DROP TABLE IF EXISTS resize_queue;
DROP TABLE IF EXISTS resized_images;
DROP TABLE IF EXISTS resize_presets;
DROP TABLE IF EXISTS original_images;

Expected Outcomes

• RESIZE_IMAGE creates scaled versions
• Multiple size presets available
• Storage savings calculated
• Missing variants identified
• Batch processing queue functional

Common Resize Dimensions

Key Concepts Learned

• RESIZE_IMAGE function usage
• Preset-based resizing
• Responsive image variants
• Batch processing patterns
• Storage optimization analysis