From f329f504040f3608478c687a99aa36a3cf5720b7 Mon Sep 17 00:00:00 2001
From: xyc <jc_xiong@hotmail.com>
Date: 星期六, 03 十月 2026 23:45:04 +0800
Subject: [PATCH] feat(observation): 新增观测站分日调查数据导入与明细查看

---
 docs/sql_observation_station.sql |   39 +++++++++++++++++++++++++++++++++++++++
 1 files changed, 39 insertions(+), 0 deletions(-)

diff --git a/docs/sql_observation_station.sql b/docs/sql_observation_station.sql
new file mode 100644
index 0000000..01c638d
--- /dev/null
+++ b/docs/sql_observation_station.sql
@@ -0,0 +1,39 @@
+-- Observation station daily survey data (raw retention for future analysis).
+-- Run against the traffic_audit database.
+CREATE TABLE IF NOT EXISTS observation_station_flow (
+  id BIGINT NOT NULL AUTO_INCREMENT,
+  obs_date DATE NOT NULL COMMENT 'Observation date',
+  station_code VARCHAR(64) NOT NULL COMMENT 'Station code',
+  station_name VARCHAR(128) DEFAULT NULL COMMENT 'Station name',
+  mileage DECIMAL(30,15) DEFAULT NULL COMMENT 'Observed mileage (km)',
+  city_name VARCHAR(64) DEFAULT NULL COMMENT 'City name',
+  county_name VARCHAR(64) DEFAULT NULL COMMENT 'County name',
+  direction VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'Travel direction',
+  small_medium_passenger DECIMAL(30,15) DEFAULT NULL COMMENT 'Small/medium passenger cars',
+  large_passenger DECIMAL(30,15) DEFAULT NULL COMMENT 'Large passenger cars',
+  small_truck DECIMAL(30,15) DEFAULT NULL COMMENT 'Small trucks',
+  medium_truck DECIMAL(30,15) DEFAULT NULL COMMENT 'Medium trucks',
+  large_truck DECIMAL(30,15) DEFAULT NULL COMMENT 'Large trucks',
+  extra_large_truck DECIMAL(30,15) DEFAULT NULL COMMENT 'Extra large trucks',
+  container_truck DECIMAL(30,15) DEFAULT NULL COMMENT 'Container trucks',
+  motorcycle DECIMAL(30,15) DEFAULT NULL COMMENT 'Motorcycles',
+  tractor DECIMAL(30,15) DEFAULT NULL COMMENT 'Tractors',
+  passenger_total DECIMAL(30,15) DEFAULT NULL COMMENT 'Total passenger vehicles',
+  truck_total DECIMAL(30,15) DEFAULT NULL COMMENT 'Total trucks',
+  car_total DECIMAL(30,15) DEFAULT NULL COMMENT 'Total automobiles',
+  motor_total DECIMAL(30,15) DEFAULT NULL COMMENT 'Total motor vehicles',
+  passenger_equivalent DECIMAL(30,15) DEFAULT NULL COMMENT 'Passenger car equivalent',
+  truck_equivalent DECIMAL(30,15) DEFAULT NULL COMMENT 'Truck equivalent',
+  car_equivalent DECIMAL(30,15) DEFAULT NULL COMMENT 'Automobile equivalent',
+  motor_equivalent DECIMAL(30,15) DEFAULT NULL COMMENT 'Motor vehicle equivalent',
+  vehicle_speed DECIMAL(30,15) DEFAULT NULL COMMENT 'Motor vehicle speed',
+  congestion_degree DECIMAL(30,15) DEFAULT NULL COMMENT 'Congestion degree',
+  source_file VARCHAR(255) DEFAULT NULL COMMENT 'Source file name',
+  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
+  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  PRIMARY KEY (id),
+  UNIQUE KEY uk_observation_station (obs_date, station_code, direction),
+  KEY idx_observation_station_date (obs_date),
+  KEY idx_observation_station_city (city_name),
+  KEY idx_observation_station_code (station_code)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
Gitblit v1.9.1