361 lines
14 KiB
SQL
361 lines
14 KiB
SQL
-- ================================================================
|
||
-- 电子围栏功能数据库表结构更新脚本
|
||
-- 用于支持无人车电子围栏准入检测功能
|
||
-- 版本：1.0
|
||
-- 创建时间：2025-07-12
|
||
-- ================================================================
|
||
|
||
-- 1. 更新 airport_areas 表结构
|
||
-- 先删除旧表（如果存在）
|
||
DROP TABLE IF EXISTS "public"."airport_areas" CASCADE;
|
||
|
||
-- 创建新的机场区域表，匹配Java实体类AirportArea
|
||
CREATE TABLE "public"."airport_areas" (
|
||
"id" BIGSERIAL NOT NULL,
|
||
"area_id" varchar(50) NOT NULL UNIQUE,
|
||
"name" varchar(100) NOT NULL,
|
||
"type" varchar(30) NOT NULL,
|
||
"boundary" geometry(POLYGON, 4326) NOT NULL,
|
||
"description" varchar(500),
|
||
"active_time" timestamptz,
|
||
"expiry_time" timestamptz,
|
||
"enabled" boolean DEFAULT true,
|
||
"priority" int4 DEFAULT 1,
|
||
"area_sqm" float8,
|
||
"functional_category" varchar(50),
|
||
"parent_area_id" varchar(50),
|
||
"created_at" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
|
||
"updated_at" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
|
||
"version" bigint DEFAULT 0,
|
||
CONSTRAINT "airport_areas_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
-- 表注释
|
||
COMMENT ON TABLE "public"."airport_areas" IS '机场区域信息表 - 支持电子围栏功能';
|
||
COMMENT ON COLUMN "public"."airport_areas"."id" IS '主键ID';
|
||
COMMENT ON COLUMN "public"."airport_areas"."area_id" IS '区域标识符';
|
||
COMMENT ON COLUMN "public"."airport_areas"."name" IS '区域名称';
|
||
COMMENT ON COLUMN "public"."airport_areas"."type" IS '区域类型（RUNWAY、TAXIWAY、APRON等）';
|
||
COMMENT ON COLUMN "public"."airport_areas"."boundary" IS '区域几何边界（PostGIS多边形）';
|
||
COMMENT ON COLUMN "public"."airport_areas"."description" IS '区域描述';
|
||
COMMENT ON COLUMN "public"."airport_areas"."active_time" IS '生效时间（用于临时区域）';
|
||
COMMENT ON COLUMN "public"."airport_areas"."expiry_time" IS '失效时间（用于临时区域）';
|
||
COMMENT ON COLUMN "public"."airport_areas"."enabled" IS '是否启用';
|
||
COMMENT ON COLUMN "public"."airport_areas"."priority" IS '优先级（数值越高优先级越高）';
|
||
COMMENT ON COLUMN "public"."airport_areas"."area_sqm" IS '区域面积（平方米）';
|
||
COMMENT ON COLUMN "public"."airport_areas"."functional_category" IS '功能分类';
|
||
COMMENT ON COLUMN "public"."airport_areas"."parent_area_id" IS '父区域ID';
|
||
COMMENT ON COLUMN "public"."airport_areas"."version" IS '数据版本号（用于乐观锁）';
|
||
|
||
-- 创建索引
|
||
CREATE INDEX "idx_airport_areas_area_id" ON "public"."airport_areas" ("area_id");
|
||
CREATE INDEX "idx_airport_areas_type" ON "public"."airport_areas" ("type");
|
||
CREATE INDEX "idx_airport_areas_enabled" ON "public"."airport_areas" ("enabled");
|
||
CREATE INDEX "idx_airport_areas_functional_category" ON "public"."airport_areas" ("functional_category");
|
||
CREATE INDEX "idx_airport_areas_parent_area_id" ON "public"."airport_areas" ("parent_area_id");
|
||
CREATE INDEX "idx_airport_areas_geom" ON "public"."airport_areas" USING GIST ("boundary");
|
||
|
||
-- 2. 创建空间规则表
|
||
DROP TABLE IF EXISTS "public"."spatial_rules" CASCADE;
|
||
DROP TABLE IF EXISTS "public"."spatial_rule_vehicle_types" CASCADE;
|
||
|
||
CREATE TABLE "public"."spatial_rules" (
|
||
"rule_id" varchar(50) NOT NULL,
|
||
"rule_name" varchar(200) NOT NULL,
|
||
"description" text,
|
||
"rule_category" varchar(50) NOT NULL,
|
||
"status" varchar(20) NOT NULL,
|
||
"priority" int4 NOT NULL,
|
||
"spatial_object_type" varchar(30) NOT NULL,
|
||
"spatial_object_id" varchar(50),
|
||
"rule_parameters" jsonb,
|
||
"alert_level" varchar(20) NOT NULL,
|
||
"alert_message" varchar(500),
|
||
"effective_start_time" timestamp(6),
|
||
"effective_end_time" timestamp(6),
|
||
"daily_start_time" time,
|
||
"daily_end_time" time,
|
||
"weekday_pattern" int4,
|
||
"time_patterns" jsonb,
|
||
"created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"updated_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"created_by" varchar(100),
|
||
"updated_by" varchar(100),
|
||
"version" bigint DEFAULT 0,
|
||
"custom_geometry" geometry,
|
||
CONSTRAINT "spatial_rules_pkey" PRIMARY KEY ("rule_id")
|
||
);
|
||
|
||
-- 创建车辆类型关联表
|
||
CREATE TABLE "public"."spatial_rule_vehicle_types" (
|
||
"rule_id" varchar(50) NOT NULL,
|
||
"vehicle_type" varchar(30) NOT NULL,
|
||
CONSTRAINT "spatial_rule_vehicle_types_pkey" PRIMARY KEY ("rule_id", "vehicle_type"),
|
||
CONSTRAINT "fk_spatial_rule_vehicle_types_rule" FOREIGN KEY ("rule_id") REFERENCES "public"."spatial_rules" ("rule_id") ON DELETE CASCADE
|
||
);
|
||
|
||
-- 表注释
|
||
COMMENT ON TABLE "public"."spatial_rules" IS '空间安全规则表';
|
||
COMMENT ON TABLE "public"."spatial_rule_vehicle_types" IS '空间规则允许的车辆类型表';
|
||
|
||
-- 创建索引
|
||
CREATE INDEX "idx_spatial_rules_status" ON "public"."spatial_rules" ("status");
|
||
CREATE INDEX "idx_spatial_rules_category" ON "public"."spatial_rules" ("rule_category");
|
||
CREATE INDEX "idx_spatial_rules_object_type" ON "public"."spatial_rules" ("spatial_object_type");
|
||
CREATE INDEX "idx_spatial_rules_priority" ON "public"."spatial_rules" ("priority");
|
||
CREATE INDEX "idx_spatial_rules_object_id" ON "public"."spatial_rules" ("spatial_object_id");
|
||
|
||
-- 3. 插入测试电子围栏区域
|
||
-- 基于route.md中两辆无人车的运行路线创建测试区域
|
||
|
||
-- 无人车A测试区域 (基于路线: 经度120.083084,纬度36.369696 到 经度120.084637,纬度36.365617)
|
||
INSERT INTO "public"."airport_areas" (
|
||
"area_id", "name", "type", "boundary", "description", "enabled", "priority",
|
||
"functional_category"
|
||
) VALUES (
|
||
'UV_TEST_AREA_A',
|
||
'无人车A测试区域',
|
||
'UNMANNED_VEHICLE_ZONE',
|
||
ST_GeomFromText('POLYGON((120.082584 36.370196, 120.085137 36.370196, 120.085137 36.365117, 120.082584 36.365117, 120.082584 36.370196))', 4326),
|
||
'无人车A运行测试区域，包含起点和终点的矩形区域',
|
||
true,
|
||
5,
|
||
'UNMANNED_VEHICLE_OPERATIONS'
|
||
);
|
||
|
||
-- 无人车B测试区域 (基于路线: 经度120.086965,纬度36.368599 到 经度120.086263,纬度36.370484)
|
||
INSERT INTO "public"."airport_areas" (
|
||
"area_id", "name", "type", "boundary", "description", "enabled", "priority",
|
||
"functional_category"
|
||
) VALUES (
|
||
'UV_TEST_AREA_B',
|
||
'无人车B测试区域',
|
||
'UNMANNED_VEHICLE_ZONE',
|
||
ST_GeomFromText('POLYGON((120.085765 36.371484, 120.087465 36.371484, 120.087465 36.368099, 120.085765 36.368099, 120.085765 36.371484))', 4326),
|
||
'无人车B运行测试区域，包含起点和终点的矩形区域',
|
||
true,
|
||
5,
|
||
'UNMANNED_VEHICLE_OPERATIONS'
|
||
);
|
||
|
||
-- 共同测试区域 (两辆无人车路径可能相交的区域)
|
||
INSERT INTO "public"."airport_areas" (
|
||
"area_id", "name", "type", "boundary", "description", "enabled", "priority",
|
||
"functional_category"
|
||
) VALUES (
|
||
'UV_INTERSECTION_ZONE',
|
||
'无人车交汇测试区域',
|
||
'INTERSECTION_ZONE',
|
||
ST_GeomFromText('POLYGON((120.083000 36.371000, 120.087000 36.371000, 120.087000 36.365000, 120.083000 36.365000, 120.083000 36.371000))', 4326),
|
||
'无人车路径可能相交的大型测试区域，用于测试电子围栏功能',
|
||
true,
|
||
10,
|
||
'UNMANNED_VEHICLE_OPERATIONS'
|
||
);
|
||
|
||
-- 4. 插入测试规则
|
||
-- 无人车A测试区域的准入控制规则
|
||
INSERT INTO "public"."spatial_rules" (
|
||
"rule_id", "rule_name", "description", "rule_category", "status", "priority",
|
||
"spatial_object_type", "spatial_object_id", "alert_level", "alert_message",
|
||
"rule_parameters", "created_by"
|
||
) VALUES (
|
||
'GEOFENCE_UV_AREA_A_ACCESS',
|
||
'无人车A区域准入控制',
|
||
'限制无人车A测试区域的访问权限',
|
||
'ACCESS_CONTROL',
|
||
'ACTIVE',
|
||
1,
|
||
'AREA',
|
||
'UV_TEST_AREA_A',
|
||
'WARNING',
|
||
'进入无人车A测试区域需要授权',
|
||
'{"accessControl":{"requiresPermission":true,"allowedOperationModes":["AUTONOMOUS","MANUAL"]},"physicalLimits":{"maxSpeed":20.0},"safetyRequirements":{"minBatteryLevel":30}}',
|
||
'system'
|
||
);
|
||
|
||
-- 无人车B测试区域的准入控制规则
|
||
INSERT INTO "public"."spatial_rules" (
|
||
"rule_id", "rule_name", "description", "rule_category", "status", "priority",
|
||
"spatial_object_type", "spatial_object_id", "alert_level", "alert_message",
|
||
"rule_parameters", "created_by"
|
||
) VALUES (
|
||
'GEOFENCE_UV_AREA_B_ACCESS',
|
||
'无人车B区域准入控制',
|
||
'限制无人车B测试区域的访问权限',
|
||
'ACCESS_CONTROL',
|
||
'ACTIVE',
|
||
1,
|
||
'AREA',
|
||
'UV_TEST_AREA_B',
|
||
'WARNING',
|
||
'进入无人车B测试区域需要授权',
|
||
'{"accessControl":{"requiresPermission":true,"allowedOperationModes":["AUTONOMOUS","MANUAL"]},"physicalLimits":{"maxSpeed":20.0},"safetyRequirements":{"minBatteryLevel":30}}',
|
||
'system'
|
||
);
|
||
|
||
-- 交汇区域的严格控制规则
|
||
INSERT INTO "public"."spatial_rules" (
|
||
"rule_id", "rule_name", "description", "rule_category", "status", "priority",
|
||
"spatial_object_type", "spatial_object_id", "alert_level", "alert_message",
|
||
"rule_parameters", "created_by"
|
||
) VALUES (
|
||
'GEOFENCE_INTERSECTION_CRITICAL',
|
||
'交汇区域严格控制',
|
||
'交汇区域需要严格的安全控制',
|
||
'ACCESS_CONTROL',
|
||
'ACTIVE',
|
||
10,
|
||
'AREA',
|
||
'UV_INTERSECTION_ZONE',
|
||
'CRITICAL',
|
||
'进入交汇区域需要最高级别授权',
|
||
'{"accessControl":{"requiresPermission":true,"allowedOperationModes":["MANUAL"]},"physicalLimits":{"maxSpeed":15.0},"safetyRequirements":{"minBatteryLevel":50,"requiresOperatorPresence":true}}',
|
||
'system'
|
||
);
|
||
|
||
-- 5. 插入规则允许的车辆类型
|
||
INSERT INTO "public"."spatial_rule_vehicle_types" ("rule_id", "vehicle_type") VALUES
|
||
('GEOFENCE_UV_AREA_A_ACCESS', 'UNMANNED_VEHICLE'),
|
||
('GEOFENCE_UV_AREA_B_ACCESS', 'UNMANNED_VEHICLE'),
|
||
('GEOFENCE_INTERSECTION_CRITICAL', 'UNMANNED_VEHICLE');
|
||
|
||
-- 6. 添加APRON（停机坪）区域和限速规则（兼容原有限速检测系统）
|
||
-- APRON区域
|
||
INSERT INTO "public"."airport_areas" (
|
||
"area_id", "name", "type", "boundary", "description", "enabled", "priority",
|
||
"functional_category"
|
||
) VALUES (
|
||
'APRON_01',
|
||
'停机坪区域01',
|
||
'APRON',
|
||
ST_GeomFromText('POLYGON((120.080000 36.372000, 120.088000 36.372000, 120.088000 36.366000, 120.080000 36.366000, 120.080000 36.372000))', 4326),
|
||
'主要停机坪区域，覆盖无人车和航空器活动区域，包含route.md中所有测试路线',
|
||
true,
|
||
8,
|
||
'AIRCRAFT_OPERATIONS'
|
||
);
|
||
|
||
-- 停机坪限速规则
|
||
INSERT INTO "public"."spatial_rules" (
|
||
"rule_id", "rule_name", "description", "rule_category", "status", "priority",
|
||
"spatial_object_type", "spatial_object_id", "alert_level", "alert_message",
|
||
"rule_parameters", "created_by"
|
||
) VALUES (
|
||
'SPEED_LIMIT_APRON',
|
||
'停机坪限速规则',
|
||
'停机坪区域内所有车辆的限速控制规则',
|
||
'SPEED_LIMIT',
|
||
'ACTIVE',
|
||
1,
|
||
'AREA',
|
||
'APRON_01',
|
||
'WARNING',
|
||
'停机坪内超速行驶',
|
||
'{"maxSpeed":15.0,"unit":"kmh","warningThreshold":0.8,"strictMode":true}',
|
||
'system'
|
||
);
|
||
|
||
-- 停机坪限速规则的车辆类型（所有类型车辆都受限速规则约束）
|
||
INSERT INTO "public"."spatial_rule_vehicle_types" ("rule_id", "vehicle_type") VALUES
|
||
('SPEED_LIMIT_APRON', 'UNMANNED_VEHICLE'),
|
||
('SPEED_LIMIT_APRON', 'AIRCRAFT'),
|
||
('SPEED_LIMIT_APRON', 'SPECIAL_VEHICLE'),
|
||
('SPEED_LIMIT_APRON', 'AIRPORT_VEHICLE'),
|
||
('SPEED_LIMIT_APRON', 'NORMAL_VEHICLE');
|
||
|
||
-- 7. 添加滑行道区域和限速规则
|
||
INSERT INTO "public"."airport_areas" (
|
||
"area_id", "name", "type", "boundary", "description", "enabled", "priority",
|
||
"functional_category"
|
||
) VALUES (
|
||
'TAXIWAY_ALPHA',
|
||
'滑行道Alpha',
|
||
'TAXIWAY',
|
||
ST_GeomFromText('POLYGON((120.082000 36.370000, 120.086000 36.370000, 120.086000 36.368000, 120.082000 36.368000, 120.082000 36.370000))', 4326),
|
||
'主要滑行道，连接停机坪和跑道',
|
||
true,
|
||
7,
|
||
'AIRCRAFT_OPERATIONS'
|
||
);
|
||
|
||
-- 滑行道限速规则
|
||
INSERT INTO "public"."spatial_rules" (
|
||
"rule_id", "rule_name", "description", "rule_category", "status", "priority",
|
||
"spatial_object_type", "spatial_object_id", "alert_level", "alert_message",
|
||
"rule_parameters", "created_by"
|
||
) VALUES (
|
||
'SPEED_LIMIT_TAXIWAY',
|
||
'滑行道限速规则',
|
||
'滑行道区域内的限速控制',
|
||
'SPEED_LIMIT',
|
||
'ACTIVE',
|
||
2,
|
||
'AREA',
|
||
'TAXIWAY_ALPHA',
|
||
'WARNING',
|
||
'滑行道内超速行驶',
|
||
'{"maxSpeed":25.0,"unit":"kmh","warningThreshold":0.9}',
|
||
'system'
|
||
);
|
||
|
||
-- 滑行道限速规则的车辆类型
|
||
INSERT INTO "public"."spatial_rule_vehicle_types" ("rule_id", "vehicle_type") VALUES
|
||
('SPEED_LIMIT_TAXIWAY', 'UNMANNED_VEHICLE'),
|
||
('SPEED_LIMIT_TAXIWAY', 'AIRCRAFT'),
|
||
('SPEED_LIMIT_TAXIWAY', 'SPECIAL_VEHICLE'),
|
||
('SPEED_LIMIT_TAXIWAY', 'AIRPORT_VEHICLE');
|
||
|
||
-- 8. 创建限速检测视图
|
||
CREATE OR REPLACE VIEW speed_limit_areas AS
|
||
SELECT
|
||
aa.area_id,
|
||
aa.name as area_name,
|
||
aa.type as area_type,
|
||
aa.boundary,
|
||
sr.rule_id,
|
||
sr.rule_name,
|
||
(sr.rule_parameters->>'maxSpeed')::float as max_speed_kmh,
|
||
(sr.rule_parameters->>'warningThreshold')::float as warning_threshold,
|
||
sr.alert_level,
|
||
array_agg(DISTINCT vt.vehicle_type) as applicable_vehicle_types
|
||
FROM airport_areas aa
|
||
JOIN spatial_rules sr ON aa.area_id = sr.spatial_object_id
|
||
JOIN spatial_rule_vehicle_types vt ON sr.rule_id = vt.rule_id
|
||
WHERE sr.rule_category = 'SPEED_LIMIT' AND sr.status = 'ACTIVE' AND aa.enabled = true
|
||
GROUP BY aa.area_id, aa.name, aa.type, aa.boundary, sr.rule_id, sr.rule_name,
|
||
sr.rule_parameters, sr.alert_level;
|
||
|
||
-- 9. 更新序列值（如果需要）
|
||
SELECT setval('airport_areas_id_seq', (SELECT COALESCE(MAX(id), 1) FROM airport_areas));
|
||
|
||
-- 10. 验证数据插入
|
||
SELECT
|
||
area_id,
|
||
name,
|
||
type,
|
||
enabled,
|
||
priority,
|
||
ST_AsText(boundary) as boundary_wkt,
|
||
created_at
|
||
FROM airport_areas
|
||
WHERE area_id IN ('UV_TEST_AREA_A', 'UV_TEST_AREA_B', 'UV_INTERSECTION_ZONE', 'APRON_01', 'TAXIWAY_ALPHA')
|
||
ORDER BY priority DESC;
|
||
|
||
SELECT
|
||
rule_id,
|
||
rule_name,
|
||
rule_category,
|
||
status,
|
||
spatial_object_id,
|
||
alert_level,
|
||
created_at
|
||
FROM spatial_rules
|
||
WHERE rule_id LIKE 'GEOFENCE_%' OR rule_id LIKE 'SPEED_LIMIT_%'
|
||
ORDER BY rule_category, rule_id;
|
||
|
||
-- 11. 验证限速检测视图
|
||
SELECT * FROM speed_limit_areas ORDER BY max_speed_kmh;
|
||
|
||
-- 完成
|
||
SELECT 'Database schema updated successfully for geofence functionality!' as status; |