# weather_history

**Repository Path**: onwebbe/weather_history

## Basic Information

- **Project Name**: weather_history
- **Description**: AI Assisted project
- **Primary Language**: Python
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2025-10-18
- **Last Updated**: 2025-10-24

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# Weather Station MQTT Subscriber

A Python application that subscribes to weather station data via MQTT and stores aggregated data in a PostgreSQL database. The application calculates average values for weather metrics over configurable time intervals (default: 10 minutes) and handles rain data specially.

## Features

- **MQTT Subscription**: Subscribes to weather station data via MQTT
- **Data Aggregation**: Calculates average values for temperature, humidity, pressure, light, and wind over 10-minute intervals
- **Rain Detection**: Special handling for rain data - stores "rain" or "no rain" based on any rain detected in the interval
- **PostgreSQL Storage**: Stores aggregated data in a PostgreSQL database with proper schema
- **Multi-location Support**: Supports multiple weather stations with device location tracking
- **Robust Error Handling**: Includes retry logic, graceful shutdown, and comprehensive logging
- **Configurable**: Environment-based configuration for easy deployment

## Sample Data Format

The application expects MQTT messages in the following JSON format:
```json
{
  "temperature": 25.32,
  "humidity": 100,
  "presure": 1025.91,
  "light": 0,
  "wind": 0,
  "rain": 0
}
```

**Note**: The typo "presure" (instead of "pressure") is maintained for compatibility with the original data source.

## Requirements

- Python 3.7+
- PostgreSQL 12+
- MQTT Broker (e.g., Mosquitto)

## Installation

### 1. Clone or Download the Application Files

Ensure you have the following files:
- `weather_subscriber_app.py` (main application)
- `config.py` (configuration management)
- `database_setup.sql` (database schema)
- `requirements.txt` (Python dependencies)
- `README-WEATHER.md` (this file)

### 2. Install Python Dependencies

```bash
pip install -r requirements.txt
```

### 3. Set Up PostgreSQL Database

#### Create Database and User
```sql
-- Connect to PostgreSQL as superuser
CREATE DATABASE weather_db;
CREATE USER weather_user WITH PASSWORD 'weather_password';
GRANT ALL PRIVILEGES ON DATABASE weather_db TO weather_user;
```

#### Run Database Setup Script
```bash
psql -h localhost -U weather_user -d weather_db -f database_setup.sql
```

### 4. Configure Environment Variables

Create a `.env` file in the application directory:

```bash
# MQTT Configuration
MQTT_BROKER=localhost
MQTT_PORT=1883
MQTT_TOPIC=homedevice/sensor/WatererAndWeather-333153da.weatherData/sensor/status
MQTT_USERNAME=
MQTT_PASSWORD=
MQTT_KEEPALIVE=60

# Database Configuration
DB_HOST=localhost
DB_PORT=5432
DB_NAME=weather_db
DB_USER=weather_user
DB_PASSWORD=weather_password

# Application Configuration
DEVICE_LOCATION=home_station_001
AGGREGATION_INTERVAL_MINUTES=10
LOG_LEVEL=INFO
MEMORY_RETENTION_HOURS=2

# Environment (development, production, default)
ENVIRONMENT=default
```

## Usage

### Running the Application

#### Basic Usage
```bash
python weather_subscriber_app.py
```

#### With Custom Environment
```bash
ENVIRONMENT=production python weather_subscriber_app.py
```

#### As a Background Service
```bash
nohup python weather_subscriber_app.py > weather_subscriber.log 2>&1 &
```

### Stopping the Application

The application handles graceful shutdown with `Ctrl+C` or system signals:
```bash
# Send SIGTERM for graceful shutdown
kill -TERM <process_id>
```

## Database Schema

### Main Data Table
```sql
weatherhistory.weather_data
├── id (SERIAL PRIMARY KEY)
├── timestamp (TIMESTAMP WITH TIME ZONE)
├── device_location (VARCHAR(100))
├── avg_temperature (DECIMAL(5,2))
├── avg_humidity (DECIMAL(5,2))
├── avg_pressure (DECIMAL(7,2))
├── avg_light (DECIMAL(8,2))
├── avg_wind (DECIMAL(6,2))
├── rain_status (VARCHAR(10)) -- 'rain' or 'no rain'
├── reading_count (INTEGER)
└── created_at (TIMESTAMP WITH TIME ZONE)
```

### Device Locations Table
```sql
weatherhistory.device_locations
├── id (SERIAL PRIMARY KEY)
├── location_code (VARCHAR(100) UNIQUE)
├── location_name (VARCHAR(200))
├── latitude (DECIMAL(10,8))
├── longitude (DECIMAL(11,8))
├── altitude (DECIMAL(8,2))
├── description (TEXT)
├── active (BOOLEAN)
├── created_at (TIMESTAMP WITH TIME ZONE)
└── updated_at (TIMESTAMP WITH TIME ZONE)
```

## Data Queries

### Get Latest Weather Data
```sql
SELECT * FROM weatherhistory.latest_weather_data;
```

### Get Weather Summary for Time Range
```sql
SELECT * FROM weatherhistory.get_weather_summary(
    'home_station_001',
    CURRENT_TIMESTAMP - INTERVAL '1 day',
    CURRENT_TIMESTAMP
);
```

### Get Hourly Averages
```sql
SELECT 
    DATE_TRUNC('hour', timestamp) as hour,
    device_location,
    AVG(avg_temperature) as hourly_avg_temp,
    AVG(avg_humidity) as hourly_avg_humidity,
    COUNT(CASE WHEN rain_status = 'rain' THEN 1 END) as rain_periods
FROM weatherhistory.weather_data
WHERE timestamp >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
GROUP BY DATE_TRUNC('hour', timestamp), device_location
ORDER BY hour DESC, device_location;
```

## Configuration Options

### MQTT Settings
- `MQTT_BROKER`: MQTT broker hostname/IP
- `MQTT_PORT`: MQTT broker port (default: 1883)
- `MQTT_TOPIC`: Topic to subscribe to
- `MQTT_USERNAME`: MQTT username (optional)
- `MQTT_PASSWORD`: MQTT password (optional)

### Database Settings
- `DB_HOST`: PostgreSQL host
- `DB_PORT`: PostgreSQL port (default: 5432)
- `DB_NAME`: Database name
- `DB_USER`: Database username
- `DB_PASSWORD`: Database password

### Application Settings
- `DEVICE_LOCATION`: Identifier for the weather station location
- `AGGREGATION_INTERVAL_MINUTES`: Data aggregation interval (default: 10)
- `LOG_LEVEL`: Logging level (DEBUG, INFO, WARNING, ERROR)
- `MEMORY_RETENTION_HOURS`: How long to keep readings in memory (default: 2)

## Logging

The application creates detailed logs in:
- Console output (stdout)
- `weather_subscriber.log` file

Log levels available: DEBUG, INFO, WARNING, ERROR

## Deployment

### Systemd Service (Linux)

Create `/etc/systemd/system/weather-subscriber.service`:

```ini
[Unit]
Description=Weather Station MQTT Subscriber
After=network.target postgresql.service

[Service]
Type=simple
User=weather
WorkingDirectory=/opt/weather-subscriber
Environment=ENVIRONMENT=production
ExecStart=/usr/bin/python3 weather_subscriber_app.py
Restart=always
RestartSec=10

[Install]
WantedBy=multi-user.target
```

Enable and start:
```bash
sudo systemctl enable weather-subscriber
sudo systemctl start weather-subscriber
sudo systemctl status weather-subscriber
```

### Docker Deployment

Create `Dockerfile`:
```dockerfile
FROM python:3.9-slim

WORKDIR /app
COPY requirements.txt .
RUN pip install -r requirements.txt

COPY . .
CMD ["python", "weather_subscriber_app.py"]
```

Create `docker-compose.yml`:
```yaml
version: '3.8'
services:
  weather-subscriber:
    build: .
    environment:
      - MQTT_BROKER=mqtt-broker
      - DB_HOST=postgres
      - ENVIRONMENT=production
    depends_on:
      - postgres
      - mqtt-broker
    restart: unless-stopped

  postgres:
    image: postgres:13
    environment:
      POSTGRES_DB: weather_db
      POSTGRES_USER: weather_user
      POSTGRES_PASSWORD: weather_password
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./database_setup.sql:/docker-entrypoint-initdb.d/setup.sql

  mqtt-broker:
    image: eclipse-mosquitto:2
    ports:
      - "1883:1883"

volumes:
  postgres_data:
```

## Monitoring

### Health Checks
- Monitor log files for errors
- Check database connectivity
- Verify MQTT connection status
- Monitor data insertion rates

### Key Metrics to Monitor
- Message processing rate
- Database insertion success rate
- Memory usage of aggregated readings
- MQTT connection stability

## Troubleshooting

### Common Issues

1. **MQTT Connection Failed**
   - Check broker address and port
   - Verify network connectivity
   - Check authentication credentials

2. **Database Connection Failed**
   - Verify PostgreSQL is running
   - Check database credentials
   - Ensure database and schema exist

3. **No Data Being Stored**
   - Check MQTT topic subscription
   - Verify message format matches expected JSON
   - Check aggregation interval timing

4. **Memory Usage Growing**
   - Adjust `MEMORY_RETENTION_HOURS` setting
   - Monitor old reading cleanup process

### Debug Mode
Run with debug logging:
```bash
LOG_LEVEL=DEBUG python weather_subscriber_app.py
```

## License

This project is provided as-is for weather station data collection and storage.