-- 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;
|
