|
|
-- ============================================
|
|
|
-- 无人机指挥中心数据库初始化脚本
|
|
|
-- 包含用户认证、威胁区域管理、路径规划等核心功能
|
|
|
-- ============================================
|
|
|
|
|
|
-- 创建数据库
|
|
|
CREATE DATABASE IF NOT EXISTS command_center CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
USE command_center;
|
|
|
|
|
|
-- 设置字符集
|
|
|
SET NAMES utf8mb4;
|
|
|
SET CHARACTER SET utf8mb4;
|
|
|
SET character_set_connection=utf8mb4;
|
|
|
|
|
|
-- ============================================
|
|
|
-- 1. 用户认证系统
|
|
|
-- ============================================
|
|
|
|
|
|
-- 创建用户表
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
|
password VARCHAR(255) NOT NULL,
|
|
|
role ENUM('admin', 'operator') NOT NULL DEFAULT 'operator',
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
|
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
|
|
-- ============================================
|
|
|
-- 2. 威胁区域管理系统
|
|
|
-- ============================================
|
|
|
|
|
|
-- 删除现有威胁区表（如果存在）以确保结构正确
|
|
|
DROP TABLE IF EXISTS threat_zones;
|
|
|
|
|
|
-- 创建威胁区表
|
|
|
CREATE TABLE threat_zones (
|
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
|
type ENUM('radar', 'missile', 'aircraft', 'ground', 'weather') NOT NULL,
|
|
|
level ENUM('low', 'medium', 'high', 'critical') NOT NULL,
|
|
|
description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
|
|
|
geometry_type ENUM('circle', 'polygon', 'rectangle') NOT NULL,
|
|
|
geometry_data JSON NOT NULL COMMENT '存储几何形状数据',
|
|
|
time_start TIMESTAMP NULL COMMENT '威胁区开始时间',
|
|
|
time_end TIMESTAMP NULL COMMENT '威胁区结束时间',
|
|
|
status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
|
|
|
created_by INT DEFAULT NULL,
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
INDEX idx_type (type),
|
|
|
INDEX idx_level (level),
|
|
|
INDEX idx_status (status),
|
|
|
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
|
|
|
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
|
|
-- ============================================
|
|
|
-- 3. 路径规划系统
|
|
|
-- ============================================
|
|
|
|
|
|
-- 删除现有路径规划表（如果存在）以确保结构正确
|
|
|
DROP TABLE IF EXISTS path_plans;
|
|
|
|
|
|
-- 创建路径规划表（增强版本，支持完整的路径保存和历史查看）
|
|
|
CREATE TABLE path_plans (
|
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
|
name VARCHAR(255) DEFAULT NULL COMMENT '路径规划名称',
|
|
|
description TEXT DEFAULT NULL COMMENT '路径描述信息',
|
|
|
start_latitude DECIMAL(10, 7) NOT NULL COMMENT '起点纬度',
|
|
|
start_longitude DECIMAL(10, 7) NOT NULL COMMENT '起点经度',
|
|
|
end_latitude DECIMAL(10, 7) NOT NULL COMMENT '终点纬度',
|
|
|
end_longitude DECIMAL(10, 7) NOT NULL COMMENT '终点经度',
|
|
|
path_points JSON NOT NULL COMMENT '路径点数据，包含lng, lat, altitude',
|
|
|
smoothed_path_points JSON DEFAULT NULL COMMENT '平滑后的路径点数据',
|
|
|
original_path_points JSON DEFAULT NULL COMMENT '原始A*算法路径点（未平滑）',
|
|
|
algorithm VARCHAR(20) NOT NULL DEFAULT 'astar' COMMENT '使用的规划算法',
|
|
|
distance DECIMAL(10, 2) DEFAULT NULL COMMENT '路径总距离（米）',
|
|
|
estimated_time INT DEFAULT NULL COMMENT '预计时间（分钟）',
|
|
|
flight_altitude INT DEFAULT 100 COMMENT '飞行高度（米）',
|
|
|
flight_speed DECIMAL(5, 2) DEFAULT 10.00 COMMENT '飞行速度（米/秒）',
|
|
|
waypoint_count INT DEFAULT 0 COMMENT '路径点数量',
|
|
|
threat_zones_avoided JSON DEFAULT NULL COMMENT '避开的威胁区ID列表',
|
|
|
threat_zones_passed JSON DEFAULT NULL COMMENT '穿过的威胁区ID列表',
|
|
|
target_order JSON DEFAULT NULL COMMENT '目标点访问顺序',
|
|
|
planning_options JSON DEFAULT NULL COMMENT '规划时的选项参数',
|
|
|
execution_result JSON DEFAULT NULL COMMENT '执行结果信息',
|
|
|
status ENUM('planned', 'executing', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'planned',
|
|
|
visibility ENUM('public', 'private') NOT NULL DEFAULT 'public' COMMENT '路径可见性',
|
|
|
color VARCHAR(7) DEFAULT '#FF5722' COMMENT '路径显示颜色',
|
|
|
created_by INT DEFAULT NULL COMMENT '创建者ID',
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
executed_at TIMESTAMP NULL COMMENT '开始执行时间',
|
|
|
completed_at TIMESTAMP NULL COMMENT '完成时间',
|
|
|
INDEX idx_status (status),
|
|
|
INDEX idx_algorithm (algorithm),
|
|
|
INDEX idx_created_at (created_at),
|
|
|
INDEX idx_created_by (created_by),
|
|
|
INDEX idx_visibility (visibility),
|
|
|
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='路径规划表（增强版）';
|
|
|
|
|
|
-- ============================================
|
|
|
-- 3.1 多对多路径规划系统
|
|
|
-- ============================================
|
|
|
|
|
|
-- 删除现有多对多路径规划表（如果存在）以确保结构正确
|
|
|
DROP TABLE IF EXISTS multi_to_multi_plans;
|
|
|
|
|
|
-- 创建多对多路径规划表
|
|
|
CREATE TABLE multi_to_multi_plans (
|
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
|
name VARCHAR(255) DEFAULT NULL COMMENT '多对多规划名称',
|
|
|
description TEXT DEFAULT NULL COMMENT '规划描述信息',
|
|
|
planning_mode ENUM('task_assignment', 'collaborative') NOT NULL DEFAULT 'task_assignment' COMMENT '规划模式：任务分配或协同规划',
|
|
|
|
|
|
-- 起点和目标点信息
|
|
|
start_points JSON NOT NULL COMMENT '起点数据数组，格式：[{id, lat, lng, altitude, name}]',
|
|
|
target_points JSON NOT NULL COMMENT '目标点数据数组，格式：[{id, lat, lng, altitude, name, priority}]',
|
|
|
start_points_count INT NOT NULL DEFAULT 0 COMMENT '起点数量',
|
|
|
target_points_count INT NOT NULL DEFAULT 0 COMMENT '目标点数量',
|
|
|
|
|
|
-- 分配和路径信息
|
|
|
assignment_type ENUM('one_to_one', 'many_to_few', 'few_to_many') NOT NULL COMMENT '分配类型',
|
|
|
assignment_strategy ENUM('greedy', 'hungarian', 'optimal') NOT NULL DEFAULT 'greedy' COMMENT '分配策略',
|
|
|
assignments JSON NOT NULL COMMENT '分配结果，格式：[{startPointId, targetPointIds, paths}]',
|
|
|
|
|
|
-- 算法参数
|
|
|
algorithm VARCHAR(20) NOT NULL DEFAULT 'astar' COMMENT '路径规划算法',
|
|
|
flight_altitude INT DEFAULT 100 COMMENT '飞行高度（米）',
|
|
|
flight_speed DECIMAL(5, 2) DEFAULT 10.00 COMMENT '飞行速度（米/秒）',
|
|
|
max_flight_time INT DEFAULT 30 COMMENT '最大飞行时间（分钟）',
|
|
|
safe_distance DECIMAL(8, 2) DEFAULT 50.00 COMMENT '安全距离（米）',
|
|
|
|
|
|
-- 统计信息
|
|
|
total_distance DECIMAL(12, 2) DEFAULT NULL COMMENT '总距离（米）',
|
|
|
total_time INT DEFAULT NULL COMMENT '总时间（分钟）',
|
|
|
max_time INT DEFAULT NULL COMMENT '最大单机时间（分钟）',
|
|
|
min_time INT DEFAULT NULL COMMENT '最小单机时间（分钟）',
|
|
|
avg_time DECIMAL(8, 2) DEFAULT NULL COMMENT '平均单机时间（分钟）',
|
|
|
threat_level_stats JSON DEFAULT NULL COMMENT '威胁等级统计，格式：{low: count, medium: count, high: count}',
|
|
|
|
|
|
-- 威胁区域处理
|
|
|
threat_zones_avoided JSON DEFAULT NULL COMMENT '避开的威胁区ID列表',
|
|
|
threat_zones_passed JSON DEFAULT NULL COMMENT '穿过的威胁区ID列表',
|
|
|
|
|
|
-- 规划选项和结果
|
|
|
planning_options JSON DEFAULT NULL COMMENT '规划选项参数',
|
|
|
execution_result JSON DEFAULT NULL COMMENT '执行结果信息',
|
|
|
optimization_metrics JSON DEFAULT NULL COMMENT '优化指标结果',
|
|
|
|
|
|
-- 状态管理
|
|
|
status ENUM('planned', 'executing', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'planned',
|
|
|
visibility ENUM('public', 'private') NOT NULL DEFAULT 'public' COMMENT '方案可见性',
|
|
|
|
|
|
-- 元数据
|
|
|
created_by INT DEFAULT NULL COMMENT '创建者ID',
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
executed_at TIMESTAMP NULL COMMENT '开始执行时间',
|
|
|
completed_at TIMESTAMP NULL COMMENT '完成时间',
|
|
|
|
|
|
-- 索引
|
|
|
INDEX idx_planning_mode (planning_mode),
|
|
|
INDEX idx_assignment_type (assignment_type),
|
|
|
INDEX idx_algorithm (algorithm),
|
|
|
INDEX idx_status (status),
|
|
|
INDEX idx_created_at (created_at),
|
|
|
INDEX idx_created_by (created_by),
|
|
|
INDEX idx_visibility (visibility),
|
|
|
INDEX idx_start_points_count (start_points_count),
|
|
|
INDEX idx_target_points_count (target_points_count),
|
|
|
|
|
|
-- 外键约束
|
|
|
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='多对多路径规划表';
|
|
|
|
|
|
-- ============================================
|
|
|
-- 4. 操作日志系统
|
|
|
-- ============================================
|
|
|
|
|
|
-- 操作日志表
|
|
|
CREATE TABLE IF NOT EXISTS operation_logs (
|
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
|
operation_type ENUM('create_threat_zone', 'delete_threat_zone', 'update_threat_zone', 'plan_path', 'plan_multi_to_multi', 'execute_path', 'save_scheme', 'delete_scheme', 'load_scheme') NOT NULL COMMENT '操作类型',
|
|
|
operation_name VARCHAR(100) NOT NULL COMMENT '操作名称',
|
|
|
operation_description TEXT COMMENT '操作描述',
|
|
|
user_id INT DEFAULT NULL COMMENT '操作用户ID',
|
|
|
user_name VARCHAR(50) DEFAULT NULL COMMENT '操作用户名',
|
|
|
ip_address VARCHAR(45) DEFAULT NULL COMMENT 'IP地址',
|
|
|
user_agent TEXT DEFAULT NULL COMMENT '用户代理',
|
|
|
request_data JSON DEFAULT NULL COMMENT '请求数据',
|
|
|
response_data JSON DEFAULT NULL COMMENT '响应数据',
|
|
|
operation_result ENUM('success', 'failed', 'partial') NOT NULL DEFAULT 'success' COMMENT '操作结果',
|
|
|
error_message TEXT DEFAULT NULL COMMENT '错误信息',
|
|
|
execution_time_ms INT DEFAULT NULL COMMENT '执行时间(毫秒)',
|
|
|
affected_resources JSON DEFAULT NULL COMMENT '影响的资源',
|
|
|
operation_context JSON DEFAULT NULL COMMENT '操作上下文',
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
|
|
|
|
INDEX idx_operation_type (operation_type),
|
|
|
INDEX idx_user_id (user_id),
|
|
|
INDEX idx_created_at (created_at),
|
|
|
INDEX idx_operation_result (operation_result)
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作日志表';
|
|
|
|
|
|
-- ============================================
|
|
|
-- 5. 插入初始数据
|
|
|
-- ============================================
|
|
|
|
|
|
-- 插入默认管理员用户
|
|
|
INSERT INTO users (username, password, role)
|
|
|
VALUES ('admin', '$2a$10$X7UrH5QxX5QxX5QxX5QxX.5QxX5QxX5QxX5QxX5QxX5QxX5QxX5Qx', 'admin')
|
|
|
ON DUPLICATE KEY UPDATE username = username;
|
|
|
|
|
|
-- 插入示例路径规划数据（增强版本）
|
|
|
INSERT INTO path_plans (
|
|
|
name, description, start_latitude, start_longitude, end_latitude, end_longitude,
|
|
|
path_points, smoothed_path_points, original_path_points, algorithm, distance,
|
|
|
estimated_time, flight_altitude, flight_speed, waypoint_count, threat_zones_avoided,
|
|
|
planning_options, status, visibility, color, created_by
|
|
|
) VALUES
|
|
|
(
|
|
|
'长沙市区巡逻路径',
|
|
|
'A*算法规划路径，距离1.20km，避开2个威胁区',
|
|
|
28.194090, 112.982279, 28.200000, 112.990000,
|
|
|
JSON_ARRAY(
|
|
|
JSON_OBJECT('lng', 112.982279, 'lat', 28.194090, 'altitude', 100),
|
|
|
JSON_OBJECT('lng', 112.985000, 'lat', 28.197000, 'altitude', 100),
|
|
|
JSON_OBJECT('lng', 112.990000, 'lat', 28.200000, 'altitude', 100)
|
|
|
),
|
|
|
JSON_ARRAY(
|
|
|
JSON_OBJECT('lng', 112.982279, 'lat', 28.194090, 'altitude', 100),
|
|
|
JSON_OBJECT('lng', 112.985000, 'lat', 28.197000, 'altitude', 100),
|
|
|
JSON_OBJECT('lng', 112.990000, 'lat', 28.200000, 'altitude', 100)
|
|
|
),
|
|
|
JSON_ARRAY(
|
|
|
JSON_OBJECT('lng', 112.982279, 'lat', 28.194090, 'altitude', 100),
|
|
|
JSON_OBJECT('lng', 112.984000, 'lat', 28.195500, 'altitude', 100),
|
|
|
JSON_OBJECT('lng', 112.985000, 'lat', 28.197000, 'altitude', 100),
|
|
|
JSON_OBJECT('lng', 112.987000, 'lat', 28.198500, 'altitude', 100),
|
|
|
JSON_OBJECT('lng', 112.990000, 'lat', 28.200000, 'altitude', 100)
|
|
|
),
|
|
|
'astar', 1200.50, 8, 100, 10.0, 3,
|
|
|
JSON_ARRAY(1, 2),
|
|
|
JSON_OBJECT('gridSize', 0.001, 'maxIterations', 8000, 'smoothed', true),
|
|
|
'completed', 'public', '#FF5722', 1
|
|
|
),
|
|
|
(
|
|
|
'湘江沿岸监控路径',
|
|
|
'A*算法规划路径，距离1.80km，避开2个威胁区',
|
|
|
28.196000, 112.985000, 28.205000, 112.995000,
|
|
|
JSON_ARRAY(
|
|
|
JSON_OBJECT('lng', 112.985000, 'lat', 28.196000, 'altitude', 120),
|
|
|
JSON_OBJECT('lng', 112.988000, 'lat', 28.199000, 'altitude', 120),
|
|
|
JSON_OBJECT('lng', 112.992000, 'lat', 28.202000, 'altitude', 120),
|
|
|
JSON_OBJECT('lng', 112.995000, 'lat', 28.205000, 'altitude', 120)
|
|
|
),
|
|
|
JSON_ARRAY(
|
|
|
JSON_OBJECT('lng', 112.985000, 'lat', 28.196000, 'altitude', 120),
|
|
|
JSON_OBJECT('lng', 112.988000, 'lat', 28.199000, 'altitude', 120),
|
|
|
JSON_OBJECT('lng', 112.992000, 'lat', 28.202000, 'altitude', 120),
|
|
|
JSON_OBJECT('lng', 112.995000, 'lat', 28.205000, 'altitude', 120)
|
|
|
),
|
|
|
JSON_ARRAY(
|
|
|
JSON_OBJECT('lng', 112.985000, 'lat', 28.196000, 'altitude', 120),
|
|
|
JSON_OBJECT('lng', 112.986500, 'lat', 28.197500, 'altitude', 120),
|
|
|
JSON_OBJECT('lng', 112.988000, 'lat', 28.199000, 'altitude', 120),
|
|
|
JSON_OBJECT('lng', 112.990000, 'lat', 28.200500, 'altitude', 120),
|
|
|
JSON_OBJECT('lng', 112.992000, 'lat', 28.202000, 'altitude', 120),
|
|
|
JSON_OBJECT('lng', 112.993500, 'lat', 28.203500, 'altitude', 120),
|
|
|
JSON_OBJECT('lng', 112.995000, 'lat', 28.205000, 'altitude', 120)
|
|
|
),
|
|
|
'astar', 1800.75, 12, 120, 10.0, 4,
|
|
|
JSON_ARRAY(1, 3),
|
|
|
JSON_OBJECT('gridSize', 0.001, 'maxIterations', 8000, 'smoothed', true),
|
|
|
'executing', 'public', '#2196F3', 1
|
|
|
),
|
|
|
(
|
|
|
'橘子洲头勘察路径',
|
|
|
'直线规划路径，距离0.80km，无威胁区域',
|
|
|
28.180000, 112.970000, 28.185000, 112.975000,
|
|
|
JSON_ARRAY(
|
|
|
JSON_OBJECT('lng', 112.970000, 'lat', 28.180000, 'altitude', 50),
|
|
|
JSON_OBJECT('lng', 112.972000, 'lat', 28.182000, 'altitude', 60),
|
|
|
JSON_OBJECT('lng', 112.975000, 'lat', 28.185000, 'altitude', 50)
|
|
|
),
|
|
|
JSON_ARRAY(
|
|
|
JSON_OBJECT('lng', 112.970000, 'lat', 28.180000, 'altitude', 50),
|
|
|
JSON_OBJECT('lng', 112.972000, 'lat', 28.182000, 'altitude', 60),
|
|
|
JSON_OBJECT('lng', 112.975000, 'lat', 28.185000, 'altitude', 50)
|
|
|
),
|
|
|
JSON_ARRAY(
|
|
|
JSON_OBJECT('lng', 112.970000, 'lat', 28.180000, 'altitude', 50),
|
|
|
JSON_OBJECT('lng', 112.972000, 'lat', 28.182000, 'altitude', 60),
|
|
|
JSON_OBJECT('lng', 112.975000, 'lat', 28.185000, 'altitude', 50)
|
|
|
),
|
|
|
'straight', 800.25, 5, 50, 8.0, 3,
|
|
|
JSON_ARRAY(),
|
|
|
JSON_OBJECT('gridSize', 0.001, 'maxIterations', 0, 'smoothed', false),
|
|
|
'planned', 'public', '#4CAF50', 1
|
|
|
),
|
|
|
(
|
|
|
'高速避障路径测试',
|
|
|
'A*算法规划路径，距离2.15km，避开4个威胁区',
|
|
|
28.175000, 112.965000, 28.210000, 113.000000,
|
|
|
JSON_ARRAY(
|
|
|
JSON_OBJECT('lng', 112.965000, 'lat', 28.175000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 112.970000, 'lat', 28.178000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 112.975000, 'lat', 28.185000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 112.985000, 'lat', 28.195000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 112.995000, 'lat', 28.205000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 113.000000, 'lat', 28.210000, 'altitude', 150)
|
|
|
),
|
|
|
JSON_ARRAY(
|
|
|
JSON_OBJECT('lng', 112.965000, 'lat', 28.175000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 112.970000, 'lat', 28.178000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 112.975000, 'lat', 28.185000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 112.985000, 'lat', 28.195000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 112.995000, 'lat', 28.205000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 113.000000, 'lat', 28.210000, 'altitude', 150)
|
|
|
),
|
|
|
JSON_ARRAY(
|
|
|
JSON_OBJECT('lng', 112.965000, 'lat', 28.175000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 112.967000, 'lat', 28.176000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 112.970000, 'lat', 28.178000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 112.972000, 'lat', 28.180000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 112.975000, 'lat', 28.185000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 112.980000, 'lat', 28.190000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 112.985000, 'lat', 28.195000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 112.990000, 'lat', 28.200000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 112.995000, 'lat', 28.205000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 112.998000, 'lat', 28.208000, 'altitude', 150),
|
|
|
JSON_OBJECT('lng', 113.000000, 'lat', 28.210000, 'altitude', 150)
|
|
|
),
|
|
|
'astar', 2150.80, 15, 150, 12.0, 6,
|
|
|
JSON_ARRAY(1, 2, 3, 4),
|
|
|
JSON_OBJECT('gridSize', 0.0005, 'maxIterations', 10000, 'smoothed', true),
|
|
|
'completed', 'public', '#FF9800', 1
|
|
|
);
|
|
|
|
|
|
-- ============================================
|
|
|
-- 6. 数据验证和显示
|
|
|
-- ============================================
|
|
|
|
|
|
-- 验证数据插入
|
|
|
SELECT '=== 数据库初始化完成 ===' as status;
|
|
|
SELECT 'Users count:' as info, COUNT(*) as count FROM users;
|
|
|
SELECT 'Threat zones count:' as info, COUNT(*) as count FROM threat_zones;
|
|
|
SELECT 'Path plans count:' as info, COUNT(*) as count FROM path_plans;
|
|
|
SELECT 'Multi-to-multi plans count:' as info, COUNT(*) as count FROM multi_to_multi_plans;
|
|
|
SELECT 'Operation logs count:' as info, COUNT(*) as count FROM operation_logs;
|
|
|
|
|
|
-- 显示威胁区域统计
|
|
|
SELECT
|
|
|
level as '威胁级别',
|
|
|
COUNT(*) as '数量',
|
|
|
GROUP_CONCAT(type SEPARATOR ', ') as '类型'
|
|
|
FROM threat_zones
|
|
|
WHERE status = 'active'
|
|
|
GROUP BY level
|
|
|
ORDER BY FIELD(level, 'low', 'medium', 'high', 'critical');
|
|
|
|
|
|
-- 显示路径规划统计
|
|
|
SELECT
|
|
|
algorithm as '算法',
|
|
|
status as '状态',
|
|
|
COUNT(*) as '数量',
|
|
|
ROUND(AVG(distance), 2) as '平均距离(米)'
|
|
|
FROM path_plans
|
|
|
GROUP BY algorithm, status
|
|
|
ORDER BY algorithm, status;
|
|
|
|
|
|
-- ============================================
|
|
|
-- 7. 无人机信息表
|
|
|
-- ============================================
|
|
|
|
|
|
DROP TABLE IF EXISTS drones;
|
|
|
CREATE TABLE drones (
|
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
|
name VARCHAR(100) NOT NULL COMMENT '无人机名称',
|
|
|
type VARCHAR(100) NOT NULL COMMENT '无人机类型/型号',
|
|
|
serial_number VARCHAR(100) NOT NULL UNIQUE COMMENT '无人机序列号',
|
|
|
app_key VARCHAR(255) NOT NULL COMMENT 'DJI App Key',
|
|
|
app_secret VARCHAR(255) NOT NULL COMMENT 'DJI App Secret',
|
|
|
description TEXT DEFAULT NULL COMMENT '备注/描述',
|
|
|
latitude DECIMAL(10,7) DEFAULT NULL COMMENT '当前纬度',
|
|
|
longitude DECIMAL(10,7) DEFAULT NULL COMMENT '当前经度',
|
|
|
battery INT DEFAULT NULL COMMENT '当前电量百分比',
|
|
|
status ENUM('connected','disconnected','flying','paused') NOT NULL DEFAULT 'disconnected' COMMENT '无人机状态',
|
|
|
last_heartbeat_at TIMESTAMP NULL COMMENT '最后一次收到消息时间',
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
|
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='无人机信息表';
|
|
|
|
|
|
-- ============================================
|
|
|
-- 8. 无人机历史轨迹表
|
|
|
-- ============================================
|
|
|
CREATE TABLE IF NOT EXISTS drone_tracks (
|
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
|
drone_id INT NOT NULL,
|
|
|
latitude DECIMAL(10,7) NOT NULL,
|
|
|
longitude DECIMAL(10,7) NOT NULL,
|
|
|
recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
FOREIGN KEY (drone_id) REFERENCES drones(id) ON DELETE CASCADE
|
|
|
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='无人机历史轨迹点';
|
|
|
|
|
|
-- ============================================
|
|
|
-- 初始化完成
|
|
|
-- ============================================
|
|
|
|
|
|
-- ============================================
|
|
|
-- 初始化完成
|
|
|
-- ============================================ |
