License Plate Recognition

Objective

Create a system for storing and querying license plate recognition (LPR) data, enabling vehicle tracking, parking management, and security monitoring.

Step 1: Create LPR Cameras Table

Define LPR camera locations.

CREATE TABLE lpr_cameras (
    id INTEGER PRIMARY KEY,
    camera_id VARCHAR(50) NOT NULL UNIQUE,
    camera_name VARCHAR(200),
    location VARCHAR(200),
    direction VARCHAR(50),
    lane_type VARCHAR(50),
    is_entry BOOLEAN DEFAULT TRUE,
    paired_camera_id INTEGER,
    status VARCHAR(50) DEFAULT 'active',
    installed_date DATE,
    FOREIGN KEY (paired_camera_id) REFERENCES lpr_cameras(id)
);

Step 2: Create Registered Vehicles Table

Store known vehicles.

CREATE TABLE registered_vehicles (
    id INTEGER PRIMARY KEY,
    plate_number VARCHAR(20) NOT NULL,
    plate_state VARCHAR(10),
    vehicle_make VARCHAR(50),
    vehicle_model VARCHAR(50),
    vehicle_color VARCHAR(30),
    vehicle_year INTEGER,
    vehicle_type VARCHAR(50),
    owner_type VARCHAR(50),
    owner_id VARCHAR(50),
    owner_name VARCHAR(200),
    department VARCHAR(100),
    parking_permit VARCHAR(50),
    permit_valid_from DATE,
    permit_valid_to DATE,
    status VARCHAR(50) DEFAULT 'active',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 3: Create LPR Reads Table

Store plate recognition events.

CREATE TABLE lpr_reads (
    id INTEGER PRIMARY KEY,
    camera_id INTEGER NOT NULL,
    read_time TIMESTAMP NOT NULL,
    plate_number VARCHAR(20),
    plate_state VARCHAR(10),
    confidence_score DECIMAL(5, 2),
    plate_image IMAGE(JPEG),
    vehicle_image IMAGE(JPEG),
    vehicle_color_detected VARCHAR(30),
    vehicle_type_detected VARCHAR(50),
    vehicle_make_detected VARCHAR(50),
    direction VARCHAR(20),
    speed_mph INTEGER,
    lane_number INTEGER,
    is_matched BOOLEAN DEFAULT FALSE,
    matched_vehicle_id INTEGER,
    processing_time_ms INTEGER,
    FOREIGN KEY (camera_id) REFERENCES lpr_cameras(id),
    FOREIGN KEY (matched_vehicle_id) REFERENCES registered_vehicles(id)
);

Step 4: Create Watchlist Table

Store plates of interest.

CREATE TABLE lpr_watchlist (
    id INTEGER PRIMARY KEY,
    plate_number VARCHAR(20) NOT NULL,
    plate_state VARCHAR(10),
    alert_type VARCHAR(50),
    alert_priority VARCHAR(20),
    description TEXT,
    added_by VARCHAR(100),
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    notification_email VARCHAR(200),
    notification_sms VARCHAR(20)
);

Step 5: Create LPR Alerts Table

Log watchlist hits.

CREATE TABLE lpr_alerts (
    id INTEGER PRIMARY KEY,
    read_id INTEGER NOT NULL,
    watchlist_id INTEGER NOT NULL,
    alert_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    alert_type VARCHAR(50),
    alert_priority VARCHAR(20),
    notification_sent BOOLEAN DEFAULT FALSE,
    notification_time TIMESTAMP,
    acknowledged BOOLEAN DEFAULT FALSE,
    acknowledged_by VARCHAR(100),
    acknowledged_at TIMESTAMP,
    action_taken TEXT,
    FOREIGN KEY (read_id) REFERENCES lpr_reads(id),
    FOREIGN KEY (watchlist_id) REFERENCES lpr_watchlist(id)
);

Step 6: Insert LPR Cameras

Add camera locations.

INSERT INTO lpr_cameras (id, camera_id, camera_name, location, direction, lane_type, is_entry, paired_camera_id, installed_date) VALUES
    (1, 'LPR-GATE-A-IN', 'Gate A Entry', 'Main Gate A', 'inbound', 'vehicle', TRUE, NULL, '2023-06-01'),
    (2, 'LPR-GATE-A-OUT', 'Gate A Exit', 'Main Gate A', 'outbound', 'vehicle', FALSE, 1, '2023-06-01'),
    (3, 'LPR-GATE-B-IN', 'Gate B Entry', 'Side Gate B', 'inbound', 'vehicle', TRUE, NULL, '2023-06-01'),
    (4, 'LPR-GATE-B-OUT', 'Gate B Exit', 'Side Gate B', 'outbound', 'vehicle', FALSE, 3, '2023-06-01'),
    (5, 'LPR-PARK-P1-IN', 'Parking P1 Entry', 'Parking Garage P1', 'inbound', 'garage', TRUE, NULL, '2023-07-15'),
    (6, 'LPR-PARK-P1-OUT', 'Parking P1 Exit', 'Parking Garage P1', 'outbound', 'garage', FALSE, 5, '2023-07-15'),
    (7, 'LPR-LOAD-IN', 'Loading Dock Entry', 'Loading Area', 'inbound', 'delivery', TRUE, NULL, '2023-08-01'),
    (8, 'LPR-LOAD-OUT', 'Loading Dock Exit', 'Loading Area', 'outbound', 'delivery', FALSE, 7, '2023-08-01');

Step 7: Insert Registered Vehicles

Add known vehicles.

INSERT INTO registered_vehicles (id, plate_number, plate_state, vehicle_make, vehicle_model, vehicle_color, vehicle_year, vehicle_type, owner_type, owner_id, owner_name, department, parking_permit, permit_valid_from, permit_valid_to, status) VALUES
    (1, 'ABC1234', 'CA', 'Toyota', 'Camry', 'Silver', 2022, 'sedan', 'employee', 'EMP-001', 'John Smith', 'IT', 'P-10001', '2024-01-01', '2024-12-31', 'active'),
    (2, 'XYZ5678', 'CA', 'Honda', 'Accord', 'Black', 2021, 'sedan', 'employee', 'EMP-002', 'Sarah Johnson', 'Engineering', 'P-10002', '2024-01-01', '2024-12-31', 'active'),
    (3, 'DEF9012', 'CA', 'Ford', 'F-150', 'White', 2023, 'truck', 'employee', 'EMP-003', 'Michael Williams', 'Facilities', 'P-10003', '2024-01-01', '2024-12-31', 'active'),
    (4, 'GHI3456', 'CA', 'Tesla', 'Model 3', 'Red', 2023, 'sedan', 'employee', 'EMP-006', 'Lisa Martinez', 'Executive', 'P-EXEC-001', '2024-01-01', '2024-12-31', 'active'),
    (5, 'JKL7890', 'CA', 'BMW', 'X5', 'Blue', 2022, 'suv', 'visitor', NULL, 'Regular Visitor', NULL, 'V-TEMP', '2024-01-01', '2024-01-31', 'active'),
    (6, 'MNO2345', 'NV', 'Chevrolet', 'Express', 'White', 2020, 'van', 'vendor', 'VND-001', 'Delivery Services Inc', NULL, 'D-001', '2024-01-01', '2024-12-31', 'active');

Step 8: Insert LPR Reads

Add plate recognition events.

INSERT INTO lpr_reads (id, camera_id, read_time, plate_number, plate_state, confidence_score, vehicle_color_detected, vehicle_type_detected, vehicle_make_detected, direction, speed_mph, lane_number, is_matched, matched_vehicle_id, processing_time_ms) VALUES
    (1, 1, '2024-01-15 08:15:32', 'ABC1234', 'CA', 98.5, 'Silver', 'sedan', 'Toyota', 'inbound', 15, 1, TRUE, 1, 45),
    (2, 1, '2024-01-15 08:22:18', 'XYZ5678', 'CA', 97.2, 'Black', 'sedan', 'Honda', 'inbound', 12, 1, TRUE, 2, 52),
    (3, 1, '2024-01-15 08:35:45', 'GHI3456', 'CA', 99.1, 'Red', 'sedan', 'Tesla', 'inbound', 10, 1, TRUE, 4, 38),
    (4, 3, '2024-01-15 08:45:00', 'DEF9012', 'CA', 96.8, 'White', 'truck', 'Ford', 'inbound', 8, 1, TRUE, 3, 48),
    (5, 7, '2024-01-15 09:00:15', 'MNO2345', 'NV', 94.5, 'White', 'van', 'Chevrolet', 'inbound', 5, 1, TRUE, 6, 55),
    (6, 1, '2024-01-15 09:30:22', 'UNK9999', 'CA', 85.2, 'Gray', 'sedan', 'Unknown', 'inbound', 18, 1, FALSE, NULL, 62),
    (7, 2, '2024-01-15 17:15:10', 'ABC1234', 'CA', 97.8, 'Silver', 'sedan', 'Toyota', 'outbound', 12, 1, TRUE, 1, 42),
    (8, 2, '2024-01-15 17:45:30', 'XYZ5678', 'CA', 98.2, 'Black', 'sedan', 'Honda', 'outbound', 10, 1, TRUE, 2, 40),
    (9, 4, '2024-01-15 18:00:00', 'DEF9012', 'CA', 96.5, 'White', 'truck', 'Ford', 'outbound', 8, 1, TRUE, 3, 50),
    (10, 8, '2024-01-15 15:30:45', 'MNO2345', 'NV', 95.2, 'White', 'van', 'Chevrolet', 'outbound', 5, 1, TRUE, 6, 48);

Step 9: Insert Watchlist Entries

Add plates of interest.

INSERT INTO lpr_watchlist (id, plate_number, plate_state, alert_type, alert_priority, description, added_by, expires_at, notification_email) VALUES
    (1, 'STOLEN01', 'CA', 'stolen_vehicle', 'critical', 'Reported stolen 2024-01-10', 'Security Admin', '2024-03-15 00:00:00', 'security@company.com'),
    (2, 'BAN1234', 'CA', 'banned', 'high', 'Former employee - terminated for cause', 'HR Admin', NULL, 'security@company.com'),
    (3, 'VIP0001', 'CA', 'vip', 'low', 'CEO vehicle - notify for arrival', 'Executive Assistant', NULL, 'exec.assistant@company.com'),
    (4, 'UNK9999', 'CA', 'suspicious', 'medium', 'Unknown vehicle - multiple unauthorized attempts', 'Security Team', '2024-02-15 00:00:00', 'security@company.com');

Step 10: Insert LPR Alerts

Log watchlist matches.

INSERT INTO lpr_alerts (id, read_id, watchlist_id, alert_time, alert_type, alert_priority, notification_sent, notification_time, acknowledged, acknowledged_by, acknowledged_at, action_taken) VALUES
    (1, 6, 4, '2024-01-15 09:30:25', 'suspicious', 'medium', TRUE, '2024-01-15 09:30:27', TRUE, 'Security Officer', '2024-01-15 09:35:00', 'Vehicle observed - no threat identified, added to monitor list');

Step 11: Get Vehicle Entry/Exit Log

Track specific vehicle.

SELECT
    lc.camera_name,
    lc.location,
    lr.read_time,
    lr.direction,
    lr.confidence_score,
    lr.speed_mph
FROM lpr_reads lr
INNER JOIN lpr_cameras lc ON lr.camera_id = lc.id
WHERE lr.plate_number = 'ABC1234'
ORDER BY lr.read_time DESC;

Step 12: Current Vehicles On-Site

List vehicles still present.

SELECT
    rv.plate_number,
    rv.vehicle_make || ' ' || rv.vehicle_model as vehicle,
    rv.vehicle_color,
    rv.owner_name,
    rv.department,
    entry.read_time as entry_time,
    lc.location as entry_point
FROM registered_vehicles rv
INNER JOIN lpr_reads entry ON rv.id = entry.matched_vehicle_id
INNER JOIN lpr_cameras lc ON entry.camera_id = lc.id
WHERE lc.is_entry = TRUE
  AND entry.read_time >= CURRENT_DATE
  AND NOT EXISTS (
      SELECT 1 FROM lpr_reads exit_read
      INNER JOIN lpr_cameras exit_cam ON exit_read.camera_id = exit_cam.id
      WHERE exit_read.matched_vehicle_id = rv.id
        AND exit_cam.is_entry = FALSE
        AND exit_read.read_time > entry.read_time
  )
ORDER BY entry.read_time;

Step 13: Unregistered Vehicle Report

Find unknown plates.

SELECT
    lr.plate_number,
    lr.plate_state,
    lr.read_time,
    lc.camera_name,
    lr.vehicle_color_detected,
    lr.vehicle_type_detected,
    lr.confidence_score
FROM lpr_reads lr
INNER JOIN lpr_cameras lc ON lr.camera_id = lc.id
WHERE lr.is_matched = FALSE
  AND lr.read_time >= CURRENT_DATE - 7
ORDER BY lr.read_time DESC;

Step 14: Daily Traffic Summary

Analyze vehicle flow.

SELECT
    lc.location,
    COUNT(CASE WHEN lc.is_entry THEN 1 END) as entries,
    COUNT(CASE WHEN NOT lc.is_entry THEN 1 END) as exits,
    COUNT(CASE WHEN lr.is_matched THEN 1 END) as registered,
    COUNT(CASE WHEN NOT lr.is_matched THEN 1 END) as unregistered,
    AVG(lr.confidence_score) as avg_confidence
FROM lpr_reads lr
INNER JOIN lpr_cameras lc ON lr.camera_id = lc.id
WHERE lr.read_time >= CURRENT_DATE
GROUP BY lc.location
ORDER BY entries DESC;

Step 15: Watchlist Alert Summary

Review alert activity.

SELECT
    lw.plate_number,
    lw.alert_type,
    lw.alert_priority,
    lw.description,
    COUNT(la.id) as alert_count,
    MAX(la.alert_time) as last_alert,
    COUNT(CASE WHEN la.acknowledged THEN 1 END) as acknowledged_count
FROM lpr_watchlist lw
LEFT JOIN lpr_alerts la ON lw.id = la.watchlist_id
WHERE lw.is_active = TRUE
GROUP BY lw.id, lw.plate_number, lw.alert_type, lw.alert_priority, lw.description
ORDER BY lw.alert_priority, alert_count DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS lpr_alerts;
DROP TABLE IF EXISTS lpr_watchlist;
DROP TABLE IF EXISTS lpr_reads;
DROP TABLE IF EXISTS registered_vehicles;
DROP TABLE IF EXISTS lpr_cameras;

Expected Outcomes

• Plate reads captured
• Vehicles registered
• Watchlist maintained
• Alerts generated
• Traffic analyzed

Alert Types

Key Concepts Learned

• LPR data modeling
• Vehicle registration
• Watchlist management
• Entry/exit tracking
• Traffic analysis