Image Dimension Query

Objective

Use AIDB's DIMENSIONS function to query and filter images based on their width, height, and aspect ratio. This enables finding images that meet specific size requirements.

Step 1: Create Image Table

Create a table for storing images with dimension metadata.

CREATE TABLE image_repository (
    id INTEGER PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    image IMAGE(JPEG),
    category VARCHAR(50),
    width INTEGER,
    height INTEGER,
    aspect_ratio VARCHAR(10),
    megapixels DECIMAL(5, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert Sample Images

Add sample images with various dimensions.

INSERT INTO image_repository (id, filename, category, width, height) VALUES
    (1, 'landscape_wide.jpg', 'landscape', 3840, 2160),
    (2, 'portrait_tall.jpg', 'portrait', 1080, 1920),
    (3, 'square_post.jpg', 'social', 1080, 1080),
    (4, 'banner_ultra.jpg', 'marketing', 2560, 600),
    (5, 'thumbnail.jpg', 'web', 150, 150),
    (6, 'hd_photo.jpg', 'photography', 1920, 1080),
    (7, 'mobile_screen.jpg', 'mobile', 750, 1334),
    (8, 'desktop_bg.jpg', 'desktop', 2560, 1440),
    (9, 'social_story.jpg', 'social', 1080, 1920),
    (10, '4k_image.jpg', 'photography', 4096, 2160);

Step 3: Calculate Aspect Ratios

Update images with calculated aspect ratios.

UPDATE image_repository
SET aspect_ratio = CASE
    WHEN width * 9 = height * 16 THEN '16:9'
    WHEN height * 9 = width * 16 THEN '9:16'
    WHEN width * 3 = height * 4 THEN '4:3'
    WHEN height * 3 = width * 4 THEN '3:4'
    WHEN width = height THEN '1:1'
    WHEN width * 10 = height * 16 THEN '16:10'
    ELSE 'custom'
END,
megapixels = (width * height) / 1000000.0;

SELECT filename, width, height, aspect_ratio, megapixels
FROM image_repository
ORDER BY megapixels DESC;

Step 4: Extract Dimensions from Images

Use DIMENSIONS function on actual image data.

SELECT
    id,
    filename,
    DIMENSIONS(image) as image_dimensions
FROM image_repository
WHERE image IS NOT NULL
LIMIT 5;

Step 5: Find Images by Minimum Size

Query images meeting minimum dimension requirements.

-- Find images at least 1920x1080 (Full HD)
SELECT
    filename,
    width,
    height,
    category
FROM image_repository
WHERE width >= 1920 AND height >= 1080
ORDER BY width DESC, height DESC;

Step 6: Find Portrait vs Landscape

Separate images by orientation.

-- Portrait images
SELECT filename, width, height
FROM image_repository
WHERE height > width
ORDER BY height DESC;

-- Landscape images
SELECT filename, width, height
FROM image_repository
WHERE width > height
ORDER BY width DESC;

-- Square images
SELECT filename, width, height
FROM image_repository
WHERE width = height;

Step 7: Images by Aspect Ratio

Group and count images by aspect ratio.

SELECT
    aspect_ratio,
    COUNT(*) as image_count,
    AVG(megapixels) as avg_megapixels
FROM image_repository
GROUP BY aspect_ratio
ORDER BY image_count DESC;

Step 8: Find Social Media Sizes

Query images suitable for specific platforms.

-- Instagram square posts (1:1)
SELECT filename, width, height
FROM image_repository
WHERE width = height AND width >= 1080;

-- Instagram stories (9:16)
SELECT filename, width, height
FROM image_repository
WHERE aspect_ratio = '9:16' AND width >= 1080;

-- YouTube thumbnails (16:9)
SELECT filename, width, height
FROM image_repository
WHERE aspect_ratio = '16:9' AND width >= 1280;

Step 9: Resolution Categories

Categorize images by resolution tier.

SELECT
    filename,
    width,
    height,
    megapixels,
    CASE
        WHEN width >= 3840 THEN '4K Ultra HD'
        WHEN width >= 2560 THEN 'QHD'
        WHEN width >= 1920 THEN 'Full HD'
        WHEN width >= 1280 THEN 'HD'
        WHEN width >= 640 THEN 'SD'
        ELSE 'Low Resolution'
    END as resolution_tier
FROM image_repository
ORDER BY megapixels DESC;

Step 10: Find Images Needing Resize

Identify images that don't meet requirements.

-- Images too small for print (need > 3000px)
SELECT
    filename,
    width,
    height,
    'Needs higher resolution for print' as recommendation
FROM image_repository
WHERE width < 3000 AND height < 3000
  AND category = 'photography';

-- Images too large for web (should be < 2000px)
SELECT
    filename,
    width,
    height,
    'Consider resizing for web' as recommendation
FROM image_repository
WHERE width > 2000 AND category = 'web';

Step 11: Dimension Statistics

Generate dimension statistics for the repository.

SELECT
    COUNT(*) as total_images,
    AVG(width) as avg_width,
    AVG(height) as avg_height,
    MAX(width) as max_width,
    MAX(height) as max_height,
    MIN(width) as min_width,
    MIN(height) as min_height,
    SUM(megapixels) as total_megapixels
FROM image_repository;

Step 12: Custom Dimension Search

Find images within a dimension range.

SELECT
    filename,
    width,
    height,
    aspect_ratio
FROM image_repository
WHERE width BETWEEN 1000 AND 2000
  AND height BETWEEN 1000 AND 2000
ORDER BY width, height;

Cleanup (Optional)

DROP TABLE IF EXISTS image_repository;

Expected Outcomes

• DIMENSIONS function extracts image sizes
• Aspect ratios calculated and categorized
• Orientation filtering works correctly
• Resolution tiers identified
• Size-based queries return correct results

Common Aspect Ratios

Key Concepts Learned

• DIMENSIONS function usage
• Aspect ratio calculations
• Orientation-based filtering
• Resolution tier categorization
• Size requirement queries