128 lines
7.2 KiB
SQL
128 lines
7.2 KiB
SQL
-- 地理围栏系统数据库表
|
||
-- 创建时间: 2025-01-15
|
||
|
||
-- 1. 地理围栏表
|
||
CREATE TABLE `geofence` (
|
||
`geofence_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '围栏ID',
|
||
`geofence_name` varchar(100) NOT NULL COMMENT '围栏名称',
|
||
`description` varchar(500) DEFAULT NULL COMMENT '围栏描述',
|
||
`geofence_type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '围栏类型：1-圆形，2-多边形，3-矩形',
|
||
`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '围栏状态：1-启用，0-禁用',
|
||
`center_latitude` decimal(10,6) DEFAULT NULL COMMENT '中心点纬度（圆形围栏）',
|
||
`center_longitude` decimal(10,6) DEFAULT NULL COMMENT '中心点经度（圆形围栏）',
|
||
`radius` decimal(10,2) DEFAULT NULL COMMENT '半径（米，圆形围栏）',
|
||
`boundary_points` text COMMENT '围栏边界点坐标（JSON格式，多边形/矩形围栏）',
|
||
`alarm_type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '报警类型：1-进入报警，2-离开报警，3-进入和离开报警',
|
||
`alarm_level` tinyint(4) NOT NULL DEFAULT '2' COMMENT '报警级别：1-低，2-中，3-高',
|
||
`enable_push` tinyint(4) NOT NULL DEFAULT '1' COMMENT '是否推送报警：1-是，0-否',
|
||
`push_method` tinyint(4) NOT NULL DEFAULT '4' COMMENT '推送方式：1-短信，2-邮件，4-应用内通知（可组合）',
|
||
`contact_list` text COMMENT '联系人列表（JSON格式）',
|
||
`effective_time` text COMMENT '生效时间段（JSON格式，如工作时间等）',
|
||
`phone_numbers` text COMMENT '关联的终端手机号列表（JSON格式）',
|
||
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`geofence_id`),
|
||
UNIQUE KEY `uk_geofence_name` (`geofence_name`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_geofence_type` (`geofence_type`),
|
||
KEY `idx_alarm_level` (`alarm_level`),
|
||
KEY `idx_create_time` (`create_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='地理围栏表';
|
||
|
||
-- 2. 地理围栏报警记录表
|
||
CREATE TABLE `geofence_alarm` (
|
||
`alarm_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '报警ID',
|
||
`geofence_id` bigint(20) NOT NULL COMMENT '围栏ID',
|
||
`geofence_name` varchar(100) NOT NULL COMMENT '围栏名称',
|
||
`phone_number` varchar(20) NOT NULL COMMENT '终端手机号',
|
||
`plate_number` varchar(20) DEFAULT NULL COMMENT '车牌号',
|
||
`alarm_type` tinyint(4) NOT NULL COMMENT '报警类型：1-进入，2-离开',
|
||
`alarm_level` tinyint(4) NOT NULL COMMENT '报警级别：1-低，2-中，3-高',
|
||
`alarm_time` datetime NOT NULL COMMENT '报警时间',
|
||
`latitude` decimal(10,6) NOT NULL COMMENT '报警位置纬度',
|
||
`longitude` decimal(10,6) NOT NULL COMMENT '报警位置经度',
|
||
`address` varchar(200) DEFAULT NULL COMMENT '报警位置地址',
|
||
`speed` decimal(6,2) DEFAULT NULL COMMENT '速度（km/h）',
|
||
`direction` int(11) DEFAULT NULL COMMENT '方向',
|
||
`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '报警状态：1-待处理，2-已处理，3-已忽略',
|
||
`process_by` varchar(64) DEFAULT NULL COMMENT '处理人',
|
||
`process_time` datetime DEFAULT NULL COMMENT '处理时间',
|
||
`process_remark` varchar(500) DEFAULT NULL COMMENT '处理备注',
|
||
`is_pushed` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否已推送：1-是，0-否',
|
||
`push_time` datetime DEFAULT NULL COMMENT '推送时间',
|
||
`push_result` varchar(500) DEFAULT NULL COMMENT '推送结果',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`alarm_id`),
|
||
KEY `idx_geofence_id` (`geofence_id`),
|
||
KEY `idx_phone_number` (`phone_number`),
|
||
KEY `idx_alarm_time` (`alarm_time`),
|
||
KEY `idx_alarm_level` (`alarm_level`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_is_pushed` (`is_pushed`),
|
||
KEY `idx_create_time` (`create_time`),
|
||
CONSTRAINT `fk_geofence_alarm_geofence` FOREIGN KEY (`geofence_id`) REFERENCES `geofence` (`geofence_id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='地理围栏报警记录表';
|
||
|
||
-- 3. 终端围栏状态表（用于记录终端当前在哪些围栏内，避免重复报警）
|
||
CREATE TABLE `terminal_geofence_status` (
|
||
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`phone_number` varchar(20) NOT NULL COMMENT '终端手机号',
|
||
`geofence_id` bigint(20) NOT NULL COMMENT '围栏ID',
|
||
`geofence_name` varchar(100) NOT NULL COMMENT '围栏名称',
|
||
`enter_time` datetime NOT NULL COMMENT '进入时间',
|
||
`last_update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_phone_geofence` (`phone_number`, `geofence_id`),
|
||
KEY `idx_phone_number` (`phone_number`),
|
||
KEY `idx_geofence_id` (`geofence_id`),
|
||
KEY `idx_enter_time` (`enter_time`),
|
||
CONSTRAINT `fk_terminal_geofence_status_geofence` FOREIGN KEY (`geofence_id`) REFERENCES `geofence` (`geofence_id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='终端围栏状态表';
|
||
|
||
-- 插入示例数据
|
||
|
||
-- 示例地理围栏（北京天安门广场圆形围栏）
|
||
INSERT INTO `geofence` (
|
||
`geofence_name`, `description`, `geofence_type`, `status`,
|
||
`center_latitude`, `center_longitude`, `radius`,
|
||
`alarm_type`, `alarm_level`, `enable_push`, `push_method`,
|
||
`phone_numbers`, `create_by`, `remark`
|
||
) VALUES (
|
||
'天安门广场监控区', '北京天安门广场500米范围监控', 1, 1,
|
||
39.903847, 116.397451, 500.00,
|
||
3, 2, 1, 4,
|
||
'["020322495257"]', 'system', '示例圆形围栏'
|
||
);
|
||
|
||
-- 示例地理围栏（办公区域矩形围栏）
|
||
INSERT INTO `geofence` (
|
||
`geofence_name`, `description`, `geofence_type`, `status`,
|
||
`boundary_points`, `alarm_type`, `alarm_level`, `enable_push`, `push_method`,
|
||
`phone_numbers`, `create_by`, `remark`
|
||
) VALUES (
|
||
'公司办公区域', '公司办公楼及周边区域', 3, 1,
|
||
'[{"lat":39.908000,"lng":116.395000},{"lat":39.905000,"lng":116.400000}]',
|
||
2, 1, 1, 4,
|
||
'["020322495257"]', 'system', '示例矩形围栏'
|
||
);
|
||
|
||
-- 示例地理围栏（停车场多边形围栏）
|
||
INSERT INTO `geofence` (
|
||
`geofence_name`, `description`, `geofence_type`, `status`,
|
||
`boundary_points`, `alarm_type`, `alarm_level`, `enable_push`, `push_method`,
|
||
`phone_numbers`, `create_by`, `remark`
|
||
) VALUES (
|
||
'停车场区域', '公司停车场多边形区域', 2, 1,
|
||
'[{"lat":39.910000,"lng":116.396000},{"lat":39.910000,"lng":116.398000},{"lat":39.909000,"lng":116.398000},{"lat":39.909000,"lng":116.396000}]',
|
||
1, 1, 1, 4,
|
||
'["020322495257"]', 'system', '示例多边形围栏'
|
||
);
|
||
|
||
-- 创建索引以提高查询性能
|
||
CREATE INDEX idx_geofence_alarm_phone_time ON geofence_alarm(phone_number, alarm_time);
|
||
CREATE INDEX idx_geofence_alarm_geofence_time ON geofence_alarm(geofence_id, alarm_time);
|
||
CREATE INDEX idx_terminal_geofence_phone_update ON terminal_geofence_status(phone_number, last_update_time); |