84 lines
3.8 KiB
SQL
84 lines
3.8 KiB
SQL
-- 创建无人车违规事件表
|
||
-- 专门针对机场无人车冲突检测场景设计的简化表结构
|
||
-- 作者: AI Assistant
|
||
-- 创建日期: 2025-01-17
|
||
-- 版本: 1.0
|
||
|
||
-- 如果表已存在则删除
|
||
DROP TABLE IF EXISTS uv_violation_events CASCADE;
|
||
|
||
-- 创建无人车违规事件表
|
||
CREATE TABLE uv_violation_events (
|
||
-- 主键ID
|
||
id BIGSERIAL PRIMARY KEY,
|
||
|
||
-- 车辆ID（关联sys_vehicle_info.vehicle_id）
|
||
vehicle_id BIGINT NOT NULL,
|
||
|
||
-- 无人车车牌号（保留用于显示）
|
||
vehicle_license VARCHAR(50),
|
||
|
||
-- 违规规则名称
|
||
rule_name VARCHAR(100) NOT NULL,
|
||
|
||
-- 违规类型（SPEED-超速, ACCESS-未授权进入, HEIGHT-超高, WEIGHT-超重）
|
||
violation_type VARCHAR(50) NOT NULL CHECK (violation_type IN ('SPEED', 'ACCESS', 'HEIGHT', 'WEIGHT', 'OTHER')),
|
||
|
||
-- 告警级别
|
||
alert_level VARCHAR(50) NOT NULL CHECK (alert_level IN ('INFO', 'WARNING', 'CRITICAL', 'EMERGENCY')),
|
||
|
||
-- 违规描述
|
||
description VARCHAR(150),
|
||
|
||
-- 违规位置（PostGIS几何点）
|
||
location GEOMETRY(Point, 4326),
|
||
|
||
-- 实际值（如实际速度：19.8）
|
||
actual_value DOUBLE PRECISION,
|
||
|
||
-- 限制值（如限速：5.0）
|
||
limit_value DOUBLE PRECISION,
|
||
|
||
-- 违规发生时间
|
||
violation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
||
-- 记录创建时间
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 创建索引以提高查询性能
|
||
CREATE INDEX idx_uv_violations_vehicle_id ON uv_violation_events(vehicle_id);
|
||
CREATE INDEX idx_uv_violations_vehicle_license ON uv_violation_events(vehicle_license);
|
||
CREATE INDEX idx_uv_violations_rule ON uv_violation_events(rule_name);
|
||
CREATE INDEX idx_uv_violations_time ON uv_violation_events(violation_time);
|
||
CREATE INDEX idx_uv_violations_type ON uv_violation_events(violation_type);
|
||
CREATE INDEX idx_uv_violations_alert_level ON uv_violation_events(alert_level);
|
||
|
||
-- 创建复合索引用于常见查询组合
|
||
CREATE INDEX idx_uv_violations_vehicle_id_time ON uv_violation_events(vehicle_id, violation_time DESC);
|
||
CREATE INDEX idx_uv_violations_vehicle_license_time ON uv_violation_events(vehicle_license, violation_time DESC);
|
||
CREATE INDEX idx_uv_violations_type_time ON uv_violation_events(violation_type, violation_time DESC);
|
||
|
||
-- 如果启用了PostGIS，创建空间索引
|
||
CREATE INDEX idx_uv_violations_location ON uv_violation_events USING GIST(location);
|
||
|
||
-- 添加表注释
|
||
COMMENT ON TABLE uv_violation_events IS '无人车违规事件表 - 专门记录机场无人车的各种违规行为';
|
||
COMMENT ON COLUMN uv_violation_events.id IS '主键ID，自增';
|
||
COMMENT ON COLUMN uv_violation_events.vehicle_id IS '车辆ID，关联sys_vehicle_info.vehicle_id，用于内部处理和查询优化';
|
||
COMMENT ON COLUMN uv_violation_events.vehicle_license IS '无人车车牌号，用于显示和业务标识，可为空';
|
||
COMMENT ON COLUMN uv_violation_events.rule_name IS '违规的规则名称';
|
||
COMMENT ON COLUMN uv_violation_events.violation_type IS '违规类型：SPEED超速/ACCESS未授权进入/HEIGHT超高/WEIGHT超重';
|
||
COMMENT ON COLUMN uv_violation_events.alert_level IS '告警级别：INFO信息/WARNING警告/CRITICAL严重/EMERGENCY紧急';
|
||
COMMENT ON COLUMN uv_violation_events.description IS '违规描述，如"超速: 19.8km/h"';
|
||
COMMENT ON COLUMN uv_violation_events.location IS '违规发生的地理位置（PostGIS Point）';
|
||
COMMENT ON COLUMN uv_violation_events.actual_value IS '实际测量值，如实际速度19.8';
|
||
COMMENT ON COLUMN uv_violation_events.limit_value IS '规则限制值，如限速5.0';
|
||
COMMENT ON COLUMN uv_violation_events.violation_time IS '违规发生的时间';
|
||
COMMENT ON COLUMN uv_violation_events.created_at IS '记录创建时间';
|
||
|
||
-- 显示表结构
|
||
\d uv_violation_events;
|
||
|
||
-- 查询表中的数据
|
||
-- SELECT * FROM uv_violation_events ORDER BY violation_time DESC LIMIT 10; |