Opens your running SynapCores (Image with Metadata will be staged for a preview — nothing runs until you click Run). No instance yet? Install free in ~30s.
Share
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.
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;
-- 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;