Image with Metadata

Objective

Store images with comprehensive metadata including dimensions, file size, and EXIF data. Using JSON columns allows flexible storage of varied metadata structures.

Step 1: Create Image Table with Metadata

Create a table with image and JSON metadata columns.

CREATE TABLE images_with_metadata (
    id INTEGER PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    image IMAGE(JPEG),
    width INTEGER,
    height INTEGER,
    file_size BIGINT,
    mime_type VARCHAR(50) DEFAULT 'image/jpeg',
    exif_data JSON,
    custom_metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert Images with EXIF Data

Insert images with detailed EXIF metadata in JSON format.

INSERT INTO images_with_metadata (id, filename, width, height, file_size, exif_data, custom_metadata) VALUES
(1, 'sunset_beach.jpg', 4032, 3024, 4500000,
    '{"camera": {"make": "Canon", "model": "EOS R5"}, "lens": "RF 24-70mm f/2.8", "settings": {"aperture": "f/8", "shutter_speed": "1/250", "iso": 100, "focal_length": "35mm"}, "gps": {"latitude": 34.0522, "longitude": -118.2437}, "date_taken": "2024-03-15T18:45:00Z"}',
    '{"tags": ["sunset", "beach", "landscape"], "rating": 5, "edited": true, "editor": "Lightroom"}'),

(2, 'portrait_studio.jpg', 3648, 5472, 8200000,
    '{"camera": {"make": "Sony", "model": "A7R IV"}, "lens": "85mm f/1.4 GM", "settings": {"aperture": "f/2.0", "shutter_speed": "1/200", "iso": 400, "focal_length": "85mm"}, "flash": true, "date_taken": "2024-02-20T14:30:00Z"}',
    '{"tags": ["portrait", "studio", "professional"], "rating": 4, "edited": true, "editor": "Capture One"}'),

(3, 'wildlife_bird.jpg', 6000, 4000, 12000000,
    '{"camera": {"make": "Nikon", "model": "Z9"}, "lens": "600mm f/4 TC VR S", "settings": {"aperture": "f/5.6", "shutter_speed": "1/2000", "iso": 1600, "focal_length": "600mm"}, "gps": {"latitude": 36.7783, "longitude": -119.4179}, "date_taken": "2024-04-05T07:15:00Z"}',
    '{"tags": ["wildlife", "bird", "nature"], "rating": 5, "edited": false}');

Step 3: Query Basic Image Info

Retrieve basic image information.

SELECT
    filename,
    width,
    height,
    file_size,
    (width * height) as total_pixels,
    CASE
        WHEN width > height THEN 'Landscape'
        WHEN height > width THEN 'Portrait'
        ELSE 'Square'
    END as orientation
FROM images_with_metadata
ORDER BY file_size DESC;

Step 4: Calculate Aspect Ratios

Analyze image dimensions and aspect ratios.

SELECT
    filename,
    width,
    height,
    CASE
        WHEN width * 9 = height * 16 THEN '16:9'
        WHEN width * 3 = height * 4 THEN '4:3'
        WHEN width * 2 = height * 3 THEN '3:2'
        WHEN width = height THEN '1:1'
        ELSE 'Custom'
    END as aspect_ratio
FROM images_with_metadata;

Step 5: Create Extended Metadata Table

Create a more comprehensive metadata structure.

CREATE TABLE photo_archive (
    id INTEGER PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    photo IMAGE(JPEG),
    dimensions JSON,
    camera_info JSON,
    location_data JSON,
    editing_history JSON,
    tags JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO photo_archive (id, filename, dimensions, camera_info, location_data, editing_history, tags) VALUES
(1, 'city_night.jpg',
    '{"width": 5472, "height": 3648, "megapixels": 20.0, "dpi": 300}',
    '{"make": "Sony", "model": "A7 III", "lens": "24-105mm f/4", "aperture": "f/11", "shutter": "30s", "iso": 100}',
    '{"city": "New York", "country": "USA", "lat": 40.7128, "lng": -74.0060, "altitude": 10}',
    '{"edited": true, "software": ["Lightroom", "Photoshop"], "adjustments": ["exposure", "contrast", "color_grade"]}',
    '["cityscape", "night", "long_exposure", "urban"]'),

(2, 'mountain_hike.jpg',
    '{"width": 4000, "height": 6000, "megapixels": 24.0, "dpi": 300}',
    '{"make": "Fujifilm", "model": "X-T5", "lens": "16-55mm f/2.8", "aperture": "f/8", "shutter": "1/500", "iso": 200}',
    '{"city": "Denver", "country": "USA", "lat": 39.7392, "lng": -104.9903, "altitude": 3500}',
    '{"edited": true, "software": ["Capture One"], "adjustments": ["exposure", "shadows", "highlights"]}',
    '["mountain", "hiking", "landscape", "nature"]');

Step 6: Search by Metadata

Query images based on metadata values.

-- Find high-resolution images
SELECT filename, dimensions
FROM photo_archive;

-- Find images by location
SELECT filename, location_data
FROM photo_archive;

-- Find edited images
SELECT filename, editing_history
FROM photo_archive;

Step 7: Metadata Statistics

Analyze metadata across the collection.

SELECT
    COUNT(*) as total_images,
    SUM(file_size) as total_storage,
    AVG(width) as avg_width,
    AVG(height) as avg_height,
    MAX(file_size) as largest_file
FROM images_with_metadata;

Step 8: Find Images by Size Category

Categorize images by resolution.

SELECT
    filename,
    width,
    height,
    (width * height) as pixels,
    CASE
        WHEN (width * height) >= 20000000 THEN 'Ultra High Res (20MP+)'
        WHEN (width * height) >= 12000000 THEN 'High Res (12-20MP)'
        WHEN (width * height) >= 8000000 THEN 'Medium Res (8-12MP)'
        ELSE 'Low Res (<8MP)'
    END as resolution_category
FROM images_with_metadata
ORDER BY pixels DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS images_with_metadata;
DROP TABLE IF EXISTS photo_archive;

Expected Outcomes

• Images stored with rich metadata
• EXIF data preserved in JSON
• Custom metadata in flexible JSONB
• Dimension calculations work correctly
• Metadata queries filter effectively

Key Concepts Learned

• Combining IMAGE type with metadata
• JSON for flexible EXIF storage
• Calculating dimensions and ratios
• Categorizing by resolution
• Metadata-based searching