161 lines
12 KiB
SQL
161 lines
12 KiB
SQL
create database if not exists suav_supervision DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
|
||
use suav_supervision;
|
||
CREATE TABLE `airspace_config` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
|
||
`area_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '空域编号',
|
||
`area_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '空域名称',
|
||
`area_range` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '空域范围（geojson）',
|
||
`area_type` int NOT NULL COMMENT '空域类型：10 .适飞区 20 管制区',
|
||
`area_sub_category` int NOT NULL COMMENT '管制区分类，针对管制区，21：管制区1， 22：管制区2,... 11:适飞区',
|
||
`lower_height` decimal(9,3) DEFAULT NULL COMMENT '空域底部距离地面高度',
|
||
`upper_height` decimal(9,3) DEFAULT NULL COMMENT '空域顶部距离地面高度',
|
||
`temporary` tinyint NOT NULL COMMENT '是否为临时空域: 0.非临时空域 1.临时空域',
|
||
`create_user_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建人ID',
|
||
`shape_type` varchar(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '空域形状。“Point”、“MultiPoint”、“LineString”、“MultiLineString”、“Polygon”、“MultiPolygon”、“GeometryCollection”',
|
||
`start_time` datetime DEFAULT NULL COMMENT '生效开始时间',
|
||
`end_time` datetime DEFAULT NULL COMMENT '生效截至时间',
|
||
`min_latitude84` decimal(10,7) DEFAULT NULL COMMENT '空域纬度最小值',
|
||
`max_latitude84` decimal(10,7) DEFAULT NULL COMMENT '空域纬度最大值',
|
||
`min_longitude84` decimal(10,7) DEFAULT NULL COMMENT '空域经度最小值',
|
||
`max_longitude84` decimal(10,7) DEFAULT NULL COMMENT '空域经度最大值',
|
||
`status` int DEFAULT NULL COMMENT '发布状态。 0. 待审批 1.待发布 2.已发布',
|
||
`publish_time` datetime DEFAULT NULL COMMENT '发布时间',
|
||
`out_area_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '导入时空域唯一标识',
|
||
`ref_area_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '关联空域配置记录（修改前的记录）',
|
||
`update_at` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`create_at` datetime DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uniq_area_code` (`area_code`),
|
||
UNIQUE KEY `uniq_area_name` (`area_name`),
|
||
KEY `idx_start_time` (`start_time`),
|
||
KEY `idx_end_time` (`end_time`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='空域配置表';
|
||
|
||
|
||
CREATE TABLE `airspace_config_his` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
|
||
`area_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '空域编号',
|
||
`area_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '空域名称',
|
||
`area_range` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '空域范围（geojson）',
|
||
`area_type` int DEFAULT NULL COMMENT '空域类型：10 .适飞区 20 管制区',
|
||
`area_sub_category` int DEFAULT NULL COMMENT '管制区分类，针对管制区，21：管制区1， 22：管制区2,... 11:适飞区',
|
||
`lower_height` decimal(9,3) DEFAULT NULL COMMENT '空域底部距离地面高度',
|
||
`upper_height` decimal(9,3) DEFAULT NULL COMMENT '空域顶部距离地面高度',
|
||
`temporary` tinyint DEFAULT NULL COMMENT '是否为临时空域: 0.非临时空域 1.临时空域',
|
||
`create_user_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建人ID',
|
||
`shape_type` varchar(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '空域形状。“Point”、“MultiPoint”、“LineString”、“MultiLineString”、“Polygon”、“MultiPolygon”、“GeometryCollection”',
|
||
`start_time` datetime DEFAULT NULL COMMENT '生效开始时间',
|
||
`end_time` datetime DEFAULT NULL COMMENT '生效截至时间',
|
||
`min_latitude84` decimal(10,7) DEFAULT NULL COMMENT '空域纬度最小值',
|
||
`max_latitude84` decimal(10,7) DEFAULT NULL COMMENT '空域纬度最大值',
|
||
`min_longitude84` decimal(10,7) DEFAULT NULL COMMENT '空域经度最小值',
|
||
`max_longitude84` decimal(10,7) DEFAULT NULL COMMENT '空域经度最大值',
|
||
`real_start_time` datetime DEFAULT NULL COMMENT '实际生效开始时间',
|
||
`real_end_time` datetime DEFAULT NULL COMMENT '实际生效结束时间',
|
||
`status` int DEFAULT NULL COMMENT '发布状态。 0. 待审批 1.待发布 2.已发布',
|
||
`publish_time` datetime DEFAULT NULL COMMENT '发布时间',
|
||
`out_area_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '导入时空域唯一标识',
|
||
`sync_status` int DEFAULT NULL COMMENT '同步状态。 0.未同步 1.已同步',
|
||
`sync_time` datetime DEFAULT NULL COMMENT '同步时间',
|
||
`validate` tinyint NOT NULL COMMENT '当前记录是否有效。 0, 无效 1.有效',
|
||
`ref_area_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '关联空域配置记录（修改前的记录）',
|
||
`ref_id` bigint DEFAULT NULL COMMENT '关联的空域记录id',
|
||
`operate_type` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '操作类型. add:新建； update：修改',
|
||
`delete_status` tinyint(1) DEFAULT '0' COMMENT '删除状态标识,0 ,未删除 1.已删除',
|
||
`delete_time` datetime DEFAULT NULL COMMENT '删除时间',
|
||
`delete_type` tinyint DEFAULT NULL COMMENT '删除该空域的原因: 0.删除 1.取消发布',
|
||
`update_at` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`create_at` datetime DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uniq_area_code` (`area_code`),
|
||
KEY `idx_start_time` (`start_time`),
|
||
KEY `idx_area_name` (`area_name`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='空域配置历史表';
|
||
|
||
|
||
create database if not exists suav_supervision DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
|
||
use suav_supervision;
|
||
CREATE TABLE `air_line_info` (
|
||
`id` bigint NOT NULL COMMENT '自增ID',
|
||
`line_name` varchar(255) NOT NULL COMMENT '航线名称',
|
||
`source_type` int unsigned DEFAULT NULL COMMENT '航线来源',
|
||
`import_time` datetime DEFAULT NULL COMMENT '为KML导入时记录导入的时间',
|
||
`import_user_id` bigint DEFAULT NULL COMMENT '为KML导入时记录导入userId',
|
||
`import_user_name` varchar(255) DEFAULT NULL COMMENT '为KML导入时记录导入者姓名',
|
||
`shape_type` int DEFAULT NULL COMMENT '航线类型',
|
||
`remarks` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '备注',
|
||
`org_id` int DEFAULT NULL COMMENT '公司ID',
|
||
`create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '创建人',
|
||
`create_id` bigint DEFAULT NULL COMMENT '创建人ID',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`modify_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '更新人',
|
||
`modify_id` bigint DEFAULT NULL COMMENT '更新人ID',
|
||
`modify_time` datetime DEFAULT NULL COMMENT '更新时间',
|
||
`length` decimal(10,2) DEFAULT NULL COMMENT '路径长度',
|
||
`height` decimal(6,2) DEFAULT NULL COMMENT '飞行高度',
|
||
`planned_fly_duration` decimal(10,2) DEFAULT NULL COMMENT '预计飞行时间',
|
||
`point_num` int DEFAULT NULL COMMENT '航点数',
|
||
`origin_coordinate_type` int DEFAULT NULL COMMENT '原始坐标类型',
|
||
`step` int NOT NULL COMMENT '航线创建步数',
|
||
`deleted` int unsigned DEFAULT '0' COMMENT '0保留,1假删除',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_create_id` (`create_id`),
|
||
KEY `idx_line_name` (`line_name`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='航线规划基本信息表';
|
||
|
||
|
||
CREATE TABLE `air_point_info` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增ID',
|
||
`sort_number` int DEFAULT NULL COMMENT '航点飞行顺序',
|
||
`air_point_type` int DEFAULT NULL COMMENT '航点类型:1起飞点,2飞行点,3.降落点(终点)',
|
||
`height` decimal(6,2) DEFAULT NULL COMMENT '高度',
|
||
`altitude` decimal(6,2) DEFAULT NULL COMMENT '海拔高度',
|
||
`speed` decimal(4,2) DEFAULT NULL COMMENT '速度',
|
||
`lat` decimal(20,7) DEFAULT NULL COMMENT 'GCJ-02纬度',
|
||
`lng` decimal(20,7) DEFAULT NULL COMMENT 'GCJ-02经度',
|
||
`wlat` decimal(20,7) DEFAULT NULL COMMENT 'WGS84纬度',
|
||
`wlng` decimal(20,7) DEFAULT NULL COMMENT 'WGS84经度',
|
||
`air_line_id` bigint DEFAULT NULL COMMENT '关联的航线id',
|
||
`create_by` varchar(20) DEFAULT NULL COMMENT '创建人名称',
|
||
`create_id` bigint DEFAULT NULL COMMENT '创建人id',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`modify_by` varchar(20) DEFAULT NULL COMMENT '修改人名称',
|
||
`modify_id` bigint DEFAULT NULL COMMENT '修改人id',
|
||
`modify_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`deleted` int(1) unsigned zerofill DEFAULT '0' COMMENT '0保留,1假删除',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_air_line_id` (`air_line_id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='航点信息表';
|
||
|
||
|
||
create database if not exists suav_supervision DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
|
||
use suav_supervision;
|
||
CREATE TABLE `airspace_export_log` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
|
||
`name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '名称',
|
||
`file_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '类型',
|
||
`geo_status` int DEFAULT NULL COMMENT '包含网格码 0 包含 1 不包含',
|
||
`file_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '素材仓库中文件id',
|
||
`status` int DEFAULT NULL COMMENT '发布状态 0. 发布中 1.成功 2.失败',
|
||
`create_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '发布者',
|
||
`create_at` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`update_at` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`export_time` datetime DEFAULT NULL COMMENT '导出时间',
|
||
`deleted` int(1) unsigned zerofill DEFAULT NULL COMMENT '删除标志 0 未删除 1 删除',
|
||
`export_size` int DEFAULT NULL COMMENT '数据大小',
|
||
`area_codes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '导出的空域code集',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_name` (`name`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='空域数据导出';
|
||
create database if not exists suav_supervision DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
|
||
use suav_supervision;
|
||
alter table `airspace_export_log` add column `draw_range` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '绘制空域范围';
|
||
create database if not exists suav_supervision DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
|
||
use suav_supervision;
|
||
ALTER TABLE `suav_supervision`.`airspace_export_log` ADD COLUMN `draw_area_info` longtext COLLATE utf8mb4_bin COMMENT '绘制空域信息' AFTER `draw_range`;
|
||
create database if not exists suav_supervision DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
|
||
use suav_supervision;
|
||
ALTER TABLE `suav_supervision`.`airspace_export_log` MODIFY COLUMN `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名称' ;
|
||
create database if not exists suav_supervision DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
|
||
use suav_supervision;
|
||
alter TABLE `airspace_export_log` MODIFY COLUMN `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名称' ; |