360-Degree Product Views

Objective

Create a system for managing 360-degree product photography with multiple angles and interactive spin functionality. This provides immersive product exploration and increases customer confidence.

Step 1: Create 360 Products Table

Define products with 360 views.

CREATE TABLE spin_products (
    id INTEGER PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    has_360_view BOOLEAN DEFAULT TRUE,
    frame_count INTEGER DEFAULT 36,
    interaction_count INTEGER DEFAULT 0,
    avg_spin_duration DECIMAL(5, 2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Spin Sets Table

Store 360-degree image sets.

CREATE TABLE spin_sets (
    id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    set_name VARCHAR(100),
    view_type VARCHAR(50) DEFAULT 'horizontal',
    total_frames INTEGER NOT NULL,
    default_frame INTEGER DEFAULT 1,
    auto_rotate BOOLEAN DEFAULT FALSE,
    rotation_speed INTEGER DEFAULT 50,
    zoom_enabled BOOLEAN DEFAULT TRUE,
    max_zoom_level DECIMAL(3, 1) DEFAULT 2.0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES spin_products(id)
);

Step 3: Create Spin Frames Table

Store individual frame images.

CREATE TABLE spin_frames (
    id INTEGER PRIMARY KEY,
    spin_set_id INTEGER NOT NULL,
    frame_number INTEGER NOT NULL,
    frame_image IMAGE(JPEG),
    frame_image_hd IMAGE(JPEG),
    hotspot_data TEXT,
    angle_degrees INTEGER,
    file_size INTEGER,
    width INTEGER,
    height INTEGER,
    is_loaded BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (spin_set_id) REFERENCES spin_sets(id)
);

Step 4: Create Hotspots Table

Define interactive points on frames.

CREATE TABLE spin_hotspots (
    id INTEGER PRIMARY KEY,
    spin_set_id INTEGER NOT NULL,
    frame_start INTEGER NOT NULL,
    frame_end INTEGER NOT NULL,
    hotspot_name VARCHAR(100),
    x_position DECIMAL(5, 2),
    y_position DECIMAL(5, 2),
    tooltip_text VARCHAR(255),
    link_url VARCHAR(500),
    icon_type VARCHAR(50) DEFAULT 'info',
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (spin_set_id) REFERENCES spin_sets(id)
);

Step 5: Create Spin Interactions Table

Track user interactions.

CREATE TABLE spin_interactions (
    id INTEGER PRIMARY KEY,
    spin_set_id INTEGER NOT NULL,
    session_id VARCHAR(100),
    interaction_type VARCHAR(50),
    start_frame INTEGER,
    end_frame INTEGER,
    duration_seconds DECIMAL(6, 2),
    zoom_used BOOLEAN DEFAULT FALSE,
    hotspots_clicked INTEGER DEFAULT 0,
    device_type VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (spin_set_id) REFERENCES spin_sets(id)
);

Step 6: Insert Sample Products

Add products with 360 views.

INSERT INTO spin_products (id, sku, name, category, frame_count, interaction_count, avg_spin_duration) VALUES
    (1, 'SHOE-RUN-360', 'ProRunner Elite Shoes', 'Footwear', 36, 15000, 12.5),
    (2, 'WATCH-LUX-360', 'Luxury Chronograph', 'Watches', 72, 12000, 18.2),
    (3, 'BAG-TRAVEL-360', 'Executive Travel Bag', 'Bags', 36, 8500, 14.8),
    (4, 'HEADPHONES-360', 'Studio Headphones Pro', 'Electronics', 36, 11000, 10.5),
    (5, 'CHAIR-OFF-360', 'Ergonomic Office Chair', 'Furniture', 48, 6500, 22.3),
    (6, 'CAMERA-360', 'Mirrorless Camera Body', 'Electronics', 72, 9200, 25.6),
    (7, 'JACKET-360', 'All-Weather Jacket', 'Clothing', 36, 7800, 11.2);

Step 7: Insert Spin Sets

Create 360 view configurations.

INSERT INTO spin_sets (id, product_id, set_name, view_type, total_frames, default_frame, auto_rotate, rotation_speed, zoom_enabled, max_zoom_level) VALUES
    -- Shoes - horizontal spin
    (1, 1, 'Main 360 View', 'horizontal', 36, 1, TRUE, 60, TRUE, 3.0),
    -- Watch - horizontal with high frame count
    (2, 2, 'Watch Detail View', 'horizontal', 72, 1, FALSE, 40, TRUE, 4.0),
    -- Bag - horizontal spin
    (3, 3, 'Bag 360 View', 'horizontal', 36, 1, TRUE, 50, TRUE, 2.5),
    -- Headphones - horizontal
    (4, 4, 'Headphones View', 'horizontal', 36, 18, FALSE, 50, TRUE, 3.0),
    -- Chair - horizontal with more frames
    (5, 5, 'Chair Full View', 'horizontal', 48, 1, TRUE, 45, TRUE, 2.0),
    (6, 5, 'Chair Seat Detail', 'detail', 24, 1, FALSE, 30, TRUE, 4.0),
    -- Camera - high detail
    (7, 6, 'Camera Body View', 'horizontal', 72, 36, FALSE, 35, TRUE, 5.0);

Step 8: Insert Sample Frames

Add frame metadata (images would be stored separately).

INSERT INTO spin_frames (id, spin_set_id, frame_number, angle_degrees, width, height, file_size) VALUES
    -- Shoes frames (36 frames = 10 degrees each)
    (1, 1, 1, 0, 1200, 800, 125000),
    (2, 1, 2, 10, 1200, 800, 128000),
    (3, 1, 3, 20, 1200, 800, 122000),
    (4, 1, 4, 30, 1200, 800, 130000),
    (5, 1, 5, 40, 1200, 800, 127000),
    (6, 1, 6, 50, 1200, 800, 124000),
    (7, 1, 7, 60, 1200, 800, 126000),
    (8, 1, 8, 70, 1200, 800, 129000),
    (9, 1, 9, 80, 1200, 800, 131000),
    (10, 1, 10, 90, 1200, 800, 125000),
    -- Watch frames (first 10 of 72)
    (11, 2, 1, 0, 1500, 1500, 185000),
    (12, 2, 2, 5, 1500, 1500, 187000),
    (13, 2, 3, 10, 1500, 1500, 182000),
    (14, 2, 4, 15, 1500, 1500, 189000),
    (15, 2, 5, 20, 1500, 1500, 184000);

Step 9: Insert Hotspots

Add interactive points.

INSERT INTO spin_hotspots (id, spin_set_id, frame_start, frame_end, hotspot_name, x_position, y_position, tooltip_text, link_url, icon_type) VALUES
    -- Shoes hotspots
    (1, 1, 1, 5, 'Breathable Mesh', 45.5, 30.2, 'Engineered mesh for maximum airflow', '/features/mesh-technology', 'info'),
    (2, 1, 8, 12, 'Cushioning System', 50.0, 70.5, 'Responsive foam for impact absorption', '/features/cushioning', 'info'),
    (3, 1, 18, 22, 'Heel Counter', 55.0, 45.0, 'Structured heel support', '/features/heel-support', 'info'),
    -- Watch hotspots
    (4, 2, 1, 10, 'Sapphire Crystal', 50.0, 50.0, 'Scratch-resistant sapphire glass', '/features/sapphire', 'detail'),
    (5, 2, 18, 28, 'Crown', 85.0, 50.0, 'Screw-down crown for water resistance', '/features/crown', 'detail'),
    (6, 2, 36, 45, 'Case Back', 50.0, 50.0, 'Exhibition case back showing movement', '/features/movement', 'detail'),
    -- Camera hotspots
    (7, 7, 1, 15, 'Sensor', 50.0, 45.0, '45MP full-frame sensor', '/specs/sensor', 'tech'),
    (8, 7, 30, 42, 'LCD Screen', 50.0, 50.0, '3.2" tilting touchscreen', '/specs/display', 'tech'),
    (9, 7, 55, 65, 'Control Dial', 70.0, 30.0, 'Customizable control dial', '/specs/controls', 'tech');

Step 10: Insert Interactions

Record user behavior.

INSERT INTO spin_interactions (id, spin_set_id, session_id, interaction_type, start_frame, end_frame, duration_seconds, zoom_used, hotspots_clicked, device_type, created_at) VALUES
    (1, 1, 'sess_001', 'manual_spin', 1, 36, 15.5, TRUE, 2, 'desktop', '2024-07-01 10:30:00'),
    (2, 1, 'sess_002', 'auto_play', 1, 36, 6.0, FALSE, 0, 'mobile', '2024-07-01 11:15:00'),
    (3, 2, 'sess_003', 'manual_spin', 1, 72, 25.3, TRUE, 3, 'desktop', '2024-07-01 12:00:00'),
    (4, 1, 'sess_004', 'manual_spin', 10, 25, 8.2, FALSE, 1, 'tablet', '2024-07-01 14:30:00'),
    (5, 7, 'sess_005', 'manual_spin', 1, 72, 32.5, TRUE, 4, 'desktop', '2024-07-02 09:00:00'),
    (6, 4, 'sess_006', 'manual_spin', 18, 36, 12.8, TRUE, 0, 'mobile', '2024-07-02 10:45:00'),
    (7, 5, 'sess_007', 'auto_play', 1, 48, 10.0, FALSE, 0, 'desktop', '2024-07-02 13:20:00'),
    (8, 2, 'sess_008', 'manual_spin', 36, 72, 18.7, TRUE, 2, 'desktop', '2024-07-02 15:00:00');

Step 11: Get Product Spin Configuration

Retrieve 360 view settings.

SELECT
    sp.name as product,
    ss.set_name,
    ss.total_frames,
    ss.view_type,
    ss.auto_rotate,
    ss.rotation_speed,
    ss.zoom_enabled,
    ss.max_zoom_level,
    COUNT(sh.id) as hotspot_count
FROM spin_products sp
INNER JOIN spin_sets ss ON sp.id = ss.product_id
LEFT JOIN spin_hotspots sh ON ss.id = sh.spin_set_id AND sh.is_active = TRUE
WHERE ss.is_active = TRUE
GROUP BY sp.id, sp.name, ss.id, ss.set_name, ss.total_frames, ss.view_type, ss.auto_rotate, ss.rotation_speed, ss.zoom_enabled, ss.max_zoom_level
ORDER BY sp.interaction_count DESC;

Step 12: Get Frames for Spin Set

Load frames for rendering.

SELECT
    sf.frame_number,
    sf.angle_degrees,
    sf.width,
    sf.height,
    sf.file_size / 1024 as size_kb
FROM spin_frames sf
WHERE sf.spin_set_id = 1
ORDER BY sf.frame_number;

Step 13: Interaction Analytics

Analyze 360 view engagement.

SELECT
    sp.name as product,
    COUNT(si.id) as total_interactions,
    AVG(si.duration_seconds) as avg_duration,
    CAST(COUNT(CASE WHEN si.zoom_used THEN 1 END) AS DECIMAL) / COUNT(*) * 100 as zoom_usage_percent,
    SUM(si.hotspots_clicked) as total_hotspot_clicks,
    AVG(si.hotspots_clicked) as avg_hotspots_per_session
FROM spin_products sp
INNER JOIN spin_sets ss ON sp.id = ss.product_id
INNER JOIN spin_interactions si ON ss.id = si.spin_set_id
GROUP BY sp.id, sp.name
ORDER BY total_interactions DESC;

Step 14: Device Engagement Report

Compare engagement by device.

SELECT
    si.device_type,
    COUNT(*) as sessions,
    AVG(si.duration_seconds) as avg_duration,
    CAST(COUNT(CASE WHEN si.zoom_used THEN 1 END) AS DECIMAL) / COUNT(*) * 100 as zoom_rate,
    AVG(si.hotspots_clicked) as avg_hotspots
FROM spin_interactions si
GROUP BY si.device_type
ORDER BY sessions DESC;

Step 15: Hotspot Performance

Track hotspot engagement.

SELECT
    sp.name as product,
    sh.hotspot_name,
    sh.tooltip_text,
    sh.frame_start || '-' || sh.frame_end as visible_frames,
    COUNT(si.id) as views_in_range
FROM spin_hotspots sh
INNER JOIN spin_sets ss ON sh.spin_set_id = ss.id
INNER JOIN spin_products sp ON ss.product_id = sp.id
LEFT JOIN spin_interactions si ON ss.id = si.spin_set_id
    AND si.start_frame <= sh.frame_end
    AND si.end_frame >= sh.frame_start
WHERE sh.is_active = TRUE
GROUP BY sh.id, sp.name, sh.hotspot_name, sh.tooltip_text, sh.frame_start, sh.frame_end
ORDER BY views_in_range DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS spin_interactions;
DROP TABLE IF EXISTS spin_hotspots;
DROP TABLE IF EXISTS spin_frames;
DROP TABLE IF EXISTS spin_sets;
DROP TABLE IF EXISTS spin_products;

Expected Outcomes

• 360 views configured
• Frames organized
• Hotspots defined
• Interactions tracked
• Device analytics captured

View Types

Key Concepts Learned

• 360 photography management
• Frame sequencing
• Interactive hotspots
• User interaction tracking
• Multi-device optimization