# Complete Optimization Problem and Solution: station_weather

## 1. Problem Context and Goals

### Context  
The optimization problem focuses on improving train scheduling to minimize total travel time while accounting for weather-related delays at stations along the route. The decision variable is the total travel time for each train, which is a continuous value. The objective is to minimize the sum of these travel times across all trains. Weather conditions, specifically precipitation and wind speed, linearly affect train delays. Precipitation increases delays by a factor of 0.1, and wind speed increases delays by a factor of 0.05, as determined by business configuration parameters. These factors are applied to the precipitation and wind speed values recorded at each station. The model ensures that trains operate within safe and feasible travel time limits, avoiding scenarios that would require nonlinear relationships such as variable products or divisions. The data used includes base travel times, maximum allowed travel times, and current weather conditions at each station, all of which are mapped to specific trains and stations.

### Goals  
The primary goal of this optimization is to minimize the total travel time for all trains. This is achieved by adjusting train schedules to account for weather-related delays while ensuring that travel times remain within operational limits. Success is measured by the reduction in the sum of total travel times across all trains, using realistic data for base travel times, maximum travel times, and weather conditions. The optimization ensures that all constraints are met without introducing nonlinear relationships, maintaining a linear formulation throughout.

## 2. Constraints  

The optimization problem includes two key constraints:  
1. **Minimum Travel Time Constraint**: The total travel time for each train must be at least equal to the base travel time plus the delays caused by precipitation and wind speed. Precipitation delays are calculated by multiplying the precipitation at each station by the precipitation factor (0.1), and wind speed delays are calculated by multiplying the wind speed at each station by the wind factor (0.05).  
2. **Maximum Travel Time Constraint**: The total travel time for each train must not exceed the maximum allowed travel time, ensuring that trains operate within safe and feasible limits.  

These constraints are designed to ensure that the optimization model remains linear and avoids scenarios that would require nonlinear relationships.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for base and max travel times, and updating configuration logic for precipitation and wind factors. Business configuration logic now includes scalar parameters and formulas for optimization.

CREATE TABLE train_travel_times (
  train_id INTEGER,
  base_travel_time FLOAT,
  max_travel_time FLOAT,
  total_travel_time FLOAT,
  delay FLOAT
);

CREATE TABLE weekly_weather (
  station_id INTEGER,
  precipitation FLOAT,
  wind_speed_mph FLOAT
);
```

### Data Dictionary  
- **train_travel_times**:  
  - **train_id**: Unique identifier for each train, used to map travel times to specific trains.  
  - **base_travel_time**: The base travel time for each train without delays, used in travel time constraints.  
  - **max_travel_time**: The maximum allowed travel time for each train, used in travel time constraints.  
  - **total_travel_time**: The total travel time for each train including delays, used in the objective function.  
  - **delay**: The delay at each station for each train, used in delay constraints.  

- **weekly_weather**:  
  - **station_id**: Unique identifier for each station, used to map weather conditions to specific stations.  
  - **precipitation**: The precipitation at each station, used in delay calculation for precipitation.  
  - **wind_speed_mph**: The wind speed at each station, used in delay calculation for wind speed.  


### Retrieved Values

**Query 1: This data is essential for setting the constraints in the optimization problem, ensuring that travel times remain within operational limits.**

```sql
SELECT train_id, base_travel_time, max_travel_time FROM train_travel_times;
```

**Results (CSV format):**
```csv
train_id,base_travel_time,max_travel_time
1,120.0,200.0
2,150.0,250.0
3,180.0,300.0
```

**Query 2: This data is crucial for calculating weather-related delays, which are factored into the total travel time constraints.**

```sql
SELECT station_id, precipitation, wind_speed_mph FROM weekly_weather;
```

**Results (CSV format):**
```csv
station_id,precipitation,wind_speed_mph
1,0.5,10.0
2,1.0,15.0
3,1.5,20.0
```

**Query 3: The result is used to adjust the base travel times in the optimization model.**

```sql
SELECT station_id, (precipitation * 0.1) + (wind_speed_mph * 0.05) AS total_delay FROM weekly_weather;
```

**Results (CSV format):**
```csv
station_id,total_delay
1,0.55
2,0.85
3,1.15
```

**Query 4: This data is used in the objective function to minimize the sum of total travel times across all trains.**

```sql
SELECT train_id, total_travel_time, delay FROM train_travel_times;
```

**Results (CSV format):**
```csv
train_id,total_travel_time,delay
1,130.0,5.0
2,160.0,10.0
3,190.0,15.0
```

**Query 5: This query provides a summary of the total delays across all stations, which can be useful for understanding the overall impact of weather on train schedules.**

```sql
SELECT SUM((precipitation * 0.1) + (wind_speed_mph * 0.05)) AS total_aggregated_delay FROM weekly_weather;
```

**Results (CSV format):**
```csv
total_aggregated_delay
2.55
```

**Query 6: This query combines base travel times, maximum travel times, and delays to provide a comprehensive view of the constraints and objective function components.**

```sql
SELECT t.train_id, t.base_travel_time, t.max_travel_time, t.delay FROM train_travel_times t;
```

**Results (CSV format):**
```csv
train_id,base_travel_time,max_travel_time,delay
1,120.0,200.0,5.0
2,150.0,250.0,10.0
3,180.0,300.0,15.0
```

**Query 7: This query identifies trains with significant delays, which may be prioritized in the optimization process to minimize overall travel time.**

```sql
SELECT train_id, delay FROM train_travel_times WHERE delay > 10;  -- Adjust the threshold as needed;
```

**Results (CSV format):**
```csv
train_id,delay
3,15.0
```

