Motion Detection Events

Objective

Create a system for recording and analyzing motion detection events from security cameras, enabling pattern analysis, alert generation, and incident investigation.

Step 1: Create Motion Cameras Table

Store cameras with motion detection.

CREATE TABLE motion_cameras (
    id INTEGER PRIMARY KEY,
    camera_id VARCHAR(50) NOT NULL UNIQUE,
    camera_name VARCHAR(200),
    location VARCHAR(200),
    zone VARCHAR(100),
    sensitivity_level INTEGER DEFAULT 50,
    detection_enabled BOOLEAN DEFAULT TRUE,
    min_motion_duration_ms INTEGER DEFAULT 500,
    cooldown_seconds INTEGER DEFAULT 5,
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Motion Detection Zones Table

Define detection areas within camera view.

CREATE TABLE detection_zones (
    id INTEGER PRIMARY KEY,
    camera_id INTEGER NOT NULL,
    zone_name VARCHAR(100),
    zone_type VARCHAR(50),
    coordinates TEXT,
    sensitivity_override INTEGER,
    is_active BOOLEAN DEFAULT TRUE,
    alert_on_motion BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (camera_id) REFERENCES motion_cameras(id)
);

Step 3: Create Motion Events Table

Record detected motion.

CREATE TABLE motion_events (
    id INTEGER PRIMARY KEY,
    camera_id INTEGER NOT NULL,
    zone_id INTEGER,
    event_uid VARCHAR(100) UNIQUE,
    event_time TIMESTAMP NOT NULL,
    duration_ms INTEGER,
    motion_score DECIMAL(5, 2),
    pixel_change_percent DECIMAL(5, 2),
    bounding_box TEXT,
    snapshot IMAGE(JPEG),
    video_clip VIDEO(MP4),
    classification VARCHAR(50),
    confidence_score DECIMAL(3, 2),
    is_acknowledged BOOLEAN DEFAULT FALSE,
    acknowledged_by VARCHAR(100),
    acknowledged_at TIMESTAMP,
    FOREIGN KEY (camera_id) REFERENCES motion_cameras(id),
    FOREIGN KEY (zone_id) REFERENCES detection_zones(id)
);

Step 4: Create Motion Patterns Table

Store recurring patterns.

CREATE TABLE motion_patterns (
    id INTEGER PRIMARY KEY,
    camera_id INTEGER NOT NULL,
    pattern_name VARCHAR(100),
    day_of_week INTEGER,
    hour_of_day INTEGER,
    avg_events DECIMAL(8, 2),
    std_deviation DECIMAL(8, 2),
    baseline_date DATE,
    is_anomaly_baseline BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (camera_id) REFERENCES motion_cameras(id)
);

Step 5: Create Motion Alerts Table

Track generated alerts.

CREATE TABLE motion_alerts (
    id INTEGER PRIMARY KEY,
    event_id INTEGER NOT NULL,
    alert_type VARCHAR(50),
    alert_priority VARCHAR(20),
    alert_message TEXT,
    alert_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notification_sent BOOLEAN DEFAULT FALSE,
    notification_time TIMESTAMP,
    resolved BOOLEAN DEFAULT FALSE,
    resolved_by VARCHAR(100),
    resolved_at TIMESTAMP,
    resolution_notes TEXT,
    FOREIGN KEY (event_id) REFERENCES motion_events(id)
);

Step 6: Insert Motion Cameras

Add cameras with motion detection.

INSERT INTO motion_cameras (id, camera_id, camera_name, location, zone, sensitivity_level, detection_enabled, min_motion_duration_ms, cooldown_seconds) VALUES
    (1, 'MOT-CAM-001', 'Front Entrance Camera', 'Main Entrance', 'Entry Zone', 60, TRUE, 500, 5),
    (2, 'MOT-CAM-002', 'Parking Lot A', 'Parking Area', 'Vehicle Zone', 45, TRUE, 1000, 10),
    (3, 'MOT-CAM-003', 'Back Door Camera', 'Rear Entrance', 'Exit Zone', 70, TRUE, 300, 3),
    (4, 'MOT-CAM-004', 'Server Room', 'IT Area', 'Restricted Zone', 80, TRUE, 200, 2),
    (5, 'MOT-CAM-005', 'Lobby Camera', 'Main Lobby', 'Common Area', 40, TRUE, 800, 8),
    (6, 'MOT-CAM-006', 'Warehouse Floor', 'Warehouse', 'Operations Zone', 50, TRUE, 1500, 15),
    (7, 'MOT-CAM-007', 'Emergency Exit North', 'Stairwell N', 'Emergency Zone', 75, TRUE, 300, 3),
    (8, 'MOT-CAM-008', 'Loading Dock', 'Shipping Area', 'Delivery Zone', 55, TRUE, 1000, 10);

Step 7: Insert Detection Zones

Define areas of interest.

INSERT INTO detection_zones (id, camera_id, zone_name, zone_type, coordinates, sensitivity_override, alert_on_motion) VALUES
    (1, 1, 'Door Area', 'entry_point', '{"x":100,"y":200,"w":300,"h":400}', 70, TRUE),
    (2, 1, 'Sidewalk', 'approach', '{"x":0,"y":300,"w":640,"h":180}', 50, FALSE),
    (3, 2, 'Entrance Lane', 'vehicle_lane', '{"x":200,"y":100,"w":200,"h":380}', 55, TRUE),
    (4, 2, 'Parking Spaces', 'parking', '{"x":0,"y":0,"w":640,"h":480}', 40, FALSE),
    (5, 4, 'Server Racks', 'critical', '{"x":50,"y":50,"w":540,"h":380}', 90, TRUE),
    (6, 4, 'Entry Door', 'access_point', '{"x":500,"y":100,"w":140,"h":380}', 85, TRUE),
    (7, 6, 'Aisle A', 'walkway', '{"x":0,"y":200,"w":150,"h":280}', 45, TRUE),
    (8, 6, 'Storage Area', 'inventory', '{"x":150,"y":0,"w":490,"h":480}', 50, TRUE);

Step 8: Insert Motion Events

Add detected motion.

INSERT INTO motion_events (id, camera_id, zone_id, event_uid, event_time, duration_ms, motion_score, pixel_change_percent, bounding_box, classification, confidence_score, is_acknowledged) VALUES
    (1, 1, 1, 'EVT-20240115-001', '2024-01-15 08:15:32', 2500, 78.5, 12.3, '{"x":150,"y":220,"w":80,"h":180}', 'person', 0.92, TRUE),
    (2, 1, 1, 'EVT-20240115-002', '2024-01-15 08:18:45', 3200, 82.1, 15.8, '{"x":140,"y":215,"w":90,"h":185}', 'person', 0.95, TRUE),
    (3, 2, 3, 'EVT-20240115-003', '2024-01-15 08:22:10', 8500, 95.2, 45.2, '{"x":180,"y":120,"w":240,"h":320}', 'vehicle', 0.98, TRUE),
    (4, 4, 5, 'EVT-20240115-004', '2024-01-15 02:45:18', 1500, 65.3, 8.5, '{"x":200,"y":180,"w":60,"h":140}', 'person', 0.88, FALSE),
    (5, 3, NULL, 'EVT-20240115-005', '2024-01-15 09:30:22', 2100, 71.8, 10.2, '{"x":100,"y":150,"w":75,"h":170}', 'person', 0.91, TRUE),
    (6, 6, 7, 'EVT-20240115-006', '2024-01-15 10:15:45', 4500, 68.9, 18.5, '{"x":20,"y":220,"w":120,"h":200}', 'person', 0.89, TRUE),
    (7, 6, 8, 'EVT-20240115-007', '2024-01-15 10:16:30', 6200, 72.4, 22.1, '{"x":300,"y":150,"w":150,"h":180}', 'forklift', 0.94, TRUE),
    (8, 1, 1, 'EVT-20240115-008', '2024-01-15 12:30:15', 1800, 55.2, 8.8, '{"x":160,"y":230,"w":70,"h":160}', 'person', 0.86, TRUE),
    (9, 7, NULL, 'EVT-20240115-009', '2024-01-15 03:22:05', 900, 45.8, 5.2, '{"x":200,"y":100,"w":40,"h":90}', 'unknown', 0.45, FALSE),
    (10, 4, 6, 'EVT-20240115-010', '2024-01-15 14:30:00', 2800, 88.5, 14.2, '{"x":510,"y":120,"w":85,"h":180}', 'person', 0.96, TRUE);

Step 9: Insert Motion Patterns

Add baseline patterns.

INSERT INTO motion_patterns (id, camera_id, pattern_name, day_of_week, hour_of_day, avg_events, std_deviation, baseline_date) VALUES
    -- Front Entrance weekday patterns
    (1, 1, 'Weekday Morning Rush', 1, 8, 45.5, 12.3, '2024-01-01'),
    (2, 1, 'Weekday Morning Rush', 1, 9, 62.8, 15.2, '2024-01-01'),
    (3, 1, 'Weekday Lunch', 1, 12, 38.2, 10.5, '2024-01-01'),
    (4, 1, 'Weekday Evening', 1, 17, 55.3, 14.8, '2024-01-01'),
    -- Parking lot patterns
    (5, 2, 'Weekday Morning', 1, 8, 28.5, 8.2, '2024-01-01'),
    (6, 2, 'Weekday Evening', 1, 17, 32.1, 9.5, '2024-01-01'),
    -- Server room patterns
    (7, 4, 'Business Hours', 1, 10, 5.2, 2.8, '2024-01-01'),
    (8, 4, 'Night Hours', 1, 2, 0.5, 0.8, '2024-01-01'),
    -- Warehouse patterns
    (9, 6, 'Shift Start', 1, 6, 35.2, 8.5, '2024-01-01'),
    (10, 6, 'Operations', 1, 10, 85.5, 22.3, '2024-01-01');

Step 10: Insert Motion Alerts

Add generated alerts.

INSERT INTO motion_alerts (id, event_id, alert_type, alert_priority, alert_message, alert_time, notification_sent, notification_time, resolved, resolved_by, resolved_at, resolution_notes) VALUES
    (1, 4, 'after_hours_motion', 'high', 'Motion detected in Server Room during non-business hours', '2024-01-15 02:45:20', TRUE, '2024-01-15 02:45:22', TRUE, 'Security Team', '2024-01-15 02:50:00', 'Authorized maintenance visit verified'),
    (2, 9, 'after_hours_motion', 'medium', 'Motion detected in Emergency Exit during night hours', '2024-01-15 03:22:07', TRUE, '2024-01-15 03:22:10', FALSE, NULL, NULL, NULL),
    (3, 4, 'restricted_area', 'high', 'Motion in restricted zone - Server Room', '2024-01-15 02:45:20', TRUE, '2024-01-15 02:45:22', TRUE, 'Security Team', '2024-01-15 02:50:00', 'Same as after-hours alert');

Step 11: Get Recent Motion Events

List recent detections.

SELECT
    mc.camera_name,
    mc.location,
    me.event_time,
    me.duration_ms,
    me.motion_score,
    me.classification,
    me.confidence_score,
    me.is_acknowledged
FROM motion_events me
INNER JOIN motion_cameras mc ON me.camera_id = mc.id
WHERE me.event_time >= CURRENT_DATE
ORDER BY me.event_time DESC
LIMIT 20;

Step 12: Motion Events by Zone

Analyze zone activity.

SELECT
    mc.camera_name,
    dz.zone_name,
    dz.zone_type,
    COUNT(me.id) as event_count,
    AVG(me.motion_score) as avg_score,
    AVG(me.duration_ms) as avg_duration_ms
FROM detection_zones dz
INNER JOIN motion_cameras mc ON dz.camera_id = mc.id
LEFT JOIN motion_events me ON dz.id = me.zone_id
    AND me.event_time >= CURRENT_DATE - 7
GROUP BY mc.id, mc.camera_name, dz.id, dz.zone_name, dz.zone_type
ORDER BY event_count DESC;

Step 13: Anomaly Detection

Find unusual activity.

SELECT
    mc.camera_name,
    mc.location,
    mp.hour_of_day,
    mp.avg_events as baseline,
    COUNT(me.id) as actual_events,
    COUNT(me.id) - mp.avg_events as deviation
FROM motion_patterns mp
INNER JOIN motion_cameras mc ON mp.camera_id = mc.id
LEFT JOIN motion_events me ON mc.id = me.camera_id
    AND EXTRACT(HOUR FROM me.event_time) = mp.hour_of_day
    AND me.event_time >= CURRENT_DATE
WHERE mp.day_of_week = EXTRACT(DOW FROM CURRENT_DATE)
GROUP BY mc.id, mc.camera_name, mc.location, mp.hour_of_day, mp.avg_events, mp.std_deviation
HAVING ABS(COUNT(me.id) - mp.avg_events) > mp.std_deviation * 2
ORDER BY ABS(COUNT(me.id) - mp.avg_events) DESC;

Step 14: Unacknowledged Events

Find events needing review.

SELECT
    mc.camera_name,
    mc.zone,
    me.event_time,
    me.classification,
    me.confidence_score,
    me.motion_score,
    COALESCE(ma.alert_priority, 'none') as alert_priority
FROM motion_events me
INNER JOIN motion_cameras mc ON me.camera_id = mc.id
LEFT JOIN motion_alerts ma ON me.id = ma.event_id
WHERE me.is_acknowledged = FALSE
ORDER BY ma.alert_priority DESC NULLS LAST, me.event_time DESC;

Step 15: Hourly Activity Summary

View activity patterns.

SELECT
    EXTRACT(HOUR FROM me.event_time) as hour,
    COUNT(*) as total_events,
    COUNT(DISTINCT me.camera_id) as cameras_active,
    AVG(me.motion_score) as avg_motion_score,
    COUNT(CASE WHEN me.classification = 'person' THEN 1 END) as person_events,
    COUNT(CASE WHEN me.classification = 'vehicle' THEN 1 END) as vehicle_events
FROM motion_events me
WHERE me.event_time >= CURRENT_DATE
GROUP BY EXTRACT(HOUR FROM me.event_time)
ORDER BY hour;

Cleanup (Optional)

DROP TABLE IF EXISTS motion_alerts;
DROP TABLE IF EXISTS motion_patterns;
DROP TABLE IF EXISTS motion_events;
DROP TABLE IF EXISTS detection_zones;
DROP TABLE IF EXISTS motion_cameras;

Expected Outcomes

• Motion events captured
• Zones defined
• Patterns baselined
• Alerts generated
• Anomalies detected

Classification Types

Key Concepts Learned

• Motion event logging
• Zone-based detection
• Pattern baseline creation
• Anomaly identification
• Alert management