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');