Northern-Thailand-Ping-River-Monitor/docs/GEOLOCATION_GUIDE.md

# Geolocation Support for Grafana Geomap

This guide explains the geolocation functionality added to the Thailand Water Monitor for use with Grafana's geomap visualization.

## ✅ **Implemented Features**

### **Database Schema Updates**
All database adapters now support geolocation fields:
- **latitude**: Decimal latitude coordinates (DECIMAL(10,8) for SQL, REAL for SQLite)
- **longitude**: Decimal longitude coordinates (DECIMAL(11,8) for SQL, REAL for SQLite)  
- **geohash**: Geohash string for efficient spatial indexing (VARCHAR(20)/TEXT)

### **Station Data Enhancement**
Station mapping now includes geolocation fields:
```python
'8': {
    'code': 'P.1', 
    'thai_name': 'สะพานนวรัฐ', 
    'english_name': 'Nawarat Bridge',
    'latitude': 15.6944,    # Decimal degrees
    'longitude': 100.2028,  # Decimal degrees
    'geohash': 'w5q6uuhvfcfp25'  # Geohash for P.1
}
```

## 🗄️ **Database Schema**

### **Updated Stations Table**
```sql
CREATE TABLE stations (
    id INTEGER PRIMARY KEY,
    station_code TEXT UNIQUE NOT NULL,
    thai_name TEXT NOT NULL,
    english_name TEXT NOT NULL,
    latitude REAL,           -- NEW: Latitude coordinate
    longitude REAL,          -- NEW: Longitude coordinate
    geohash TEXT,           -- NEW: Geohash for spatial indexing
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

### **Database Support**
- ✅ **SQLite**: REAL columns for coordinates, TEXT for geohash
- ✅ **PostgreSQL**: DECIMAL(10,8) and DECIMAL(11,8) for coordinates, VARCHAR(20) for geohash
- ✅ **MySQL**: DECIMAL(10,8) and DECIMAL(11,8) for coordinates, VARCHAR(20) for geohash
- ✅ **VictoriaMetrics**: Geolocation data included in metric labels

## 📊 **Current Station Data**

### **P.1 - Nawarat Bridge (Sample)**
- **Station Code**: P.1
- **Thai Name**: สะพานนวรัฐ
- **English Name**: Nawarat Bridge
- **Latitude**: 15.6944
- **Longitude**: 100.2028
- **Geohash**: w5q6uuhvfcfp25

### **Remaining Stations**
The following stations are ready for geolocation data when coordinates become available:
- P.20 - บ้านเชียงดาว (Ban Chiang Dao)
- P.75 - บ้านช่อแล (Ban Chai Lat)
- P.92 - บ้านเมืองกึ๊ด (Ban Muang Aut)
- P.4A - บ้านแม่แตง (Ban Mae Taeng)
- P.67 - บ้านแม่แต (Ban Tae)
- P.21 - บ้านริมใต้ (Ban Rim Tai)
- P.103 - สะพานวงแหวนรอบ 3 (Ring Bridge 3)
- P.82 - บ้านสบวิน (Ban Sob win)
- P.84 - บ้านพันตน (Ban Panton)
- P.81 - บ้านโป่ง (Ban Pong)
- P.5 - สะพานท่านาง (Tha Nang Bridge)
- P.77 - บ้านสบแม่สะป๊วด (Baan Sop Mae Sapuord)
- P.87 - บ้านป่าซาง (Ban Pa Sang)
- P.76 - บ้านแม่อีไฮ (Banb Mae I Hai)
- P.85 - บ้านหล่ายแก้ว (Baan Lai Kaew)

## 🗺️ **Grafana Geomap Integration**

### **Data Source Configuration**
The geolocation data is automatically included in all database queries and can be used directly in Grafana:

#### **SQLite/PostgreSQL/MySQL Query Example**
```sql
SELECT 
    m.timestamp,
    s.station_code,
    s.english_name,
    s.thai_name,
    s.latitude,
    s.longitude,
    s.geohash,
    m.water_level,
    m.discharge,
    m.discharge_percent
FROM water_measurements m
JOIN stations s ON m.station_id = s.id
WHERE s.latitude IS NOT NULL 
  AND s.longitude IS NOT NULL
ORDER BY m.timestamp DESC
```

#### **VictoriaMetrics Query Example**
```promql
water_level{latitude!="",longitude!=""}
```

### **Geomap Panel Configuration**

#### **1. Create Geomap Panel**
1. Add new panel in Grafana
2. Select "Geomap" visualization
3. Configure data source (SQLite/PostgreSQL/MySQL/VictoriaMetrics)

#### **2. Configure Location Fields**
- **Latitude Field**: `latitude`
- **Longitude Field**: `longitude`
- **Alternative**: Use `geohash` field for geohash-based positioning

#### **3. Configure Display Options**
- **Station Labels**: Use `station_code` or `english_name`
- **Tooltip Information**: Include `thai_name`, `water_level`, `discharge`
- **Color Mapping**: Map to `water_level` or `discharge_percent`

#### **4. Sample Geomap Configuration**
```json
{
  "type": "geomap",
  "title": "Thailand Water Stations",
  "targets": [
    {
      "rawSql": "SELECT latitude, longitude, station_code, english_name, water_level, discharge_percent FROM stations s JOIN water_measurements m ON s.id = m.station_id WHERE s.latitude IS NOT NULL AND m.timestamp = (SELECT MAX(timestamp) FROM water_measurements WHERE station_id = s.id)",
      "format": "table"
    }
  ],
  "fieldConfig": {
    "defaults": {
      "custom": {
        "hideFrom": {
          "legend": false,
          "tooltip": false,
          "vis": false
        }
      },
      "mappings": [],
      "color": {
        "mode": "continuous-GrYlRd",
        "field": "water_level"
      }
    }
  },
  "options": {
    "view": {
      "id": "coords",
      "lat": 15.6944,
      "lon": 100.2028,
      "zoom": 8
    },
    "controls": {
      "mouseWheelZoom": true,
      "showZoom": true,
      "showAttribution": true
    },
    "layers": [
      {
        "type": "markers",
        "config": {
          "size": {
            "field": "discharge_percent",
            "min": 5,
            "max": 20
          },
          "color": {
            "field": "water_level"
          },
          "showLegend": true
        }
      }
    ]
  }
}
```

## 🔧 **Adding New Station Coordinates**

### **Method 1: Update Station Mapping**
Edit `water_scraper_v3.py` and add coordinates to the station mapping:
```python
'1': {
    'code': 'P.20', 
    'thai_name': 'บ้านเชียงดาว', 
    'english_name': 'Ban Chiang Dao',
    'latitude': 19.3056,    # Add actual coordinates
    'longitude': 98.9264,   # Add actual coordinates
    'geohash': 'w4r6...'    # Add actual geohash
}
```

### **Method 2: Direct Database Update**
```sql
UPDATE stations 
SET latitude = 19.3056, longitude = 98.9264, geohash = 'w4r6uuhvfcfp25'
WHERE station_code = 'P.20';
```

### **Method 3: Bulk Update Script**
```python
import sqlite3

coordinates = {
    'P.20': {'lat': 19.3056, 'lon': 98.9264, 'geohash': 'w4r6uuhvfcfp25'},
    'P.75': {'lat': 18.7756, 'lon': 99.1234, 'geohash': 'w4r5uuhvfcfp25'},
    # Add more stations...
}

conn = sqlite3.connect('water_monitoring.db')
cursor = conn.cursor()

for station_code, coords in coordinates.items():
    cursor.execute("""
        UPDATE stations 
        SET latitude = ?, longitude = ?, geohash = ?
        WHERE station_code = ?
    """, (coords['lat'], coords['lon'], coords['geohash'], station_code))

conn.commit()
conn.close()
```

## 🌐 **Geohash Information**

### **What is Geohash?**
Geohash is a geocoding system that represents geographic coordinates as a short alphanumeric string. It provides:
- **Spatial Indexing**: Efficient spatial queries
- **Proximity**: Similar geohashes indicate nearby locations
- **Hierarchical**: Longer geohashes provide more precision

### **Geohash Precision Levels**
- **5 characters**: ~2.4km precision
- **6 characters**: ~610m precision  
- **7 characters**: ~76m precision
- **8 characters**: ~19m precision
- **9+ characters**: <5m precision

### **Example: P.1 Geohash**
- **Geohash**: `w5q6uuhvfcfp25`
- **Length**: 14 characters
- **Precision**: Sub-meter accuracy
- **Location**: Nawarat Bridge, Thailand

## 📈 **Grafana Visualization Examples**

### **1. Station Location Map**
- **Type**: Geomap with markers
- **Data**: Current station locations
- **Color**: Water level or discharge percentage
- **Size**: Discharge volume

### **2. Regional Water Levels**
- **Type**: Geomap with heatmap
- **Data**: Water level data across regions
- **Visualization**: Color-coded intensity map
- **Filters**: Time range, station groups

### **3. Alert Zones**
- **Type**: Geomap with threshold markers
- **Data**: Stations exceeding alert thresholds
- **Visualization**: Red markers for high water levels
- **Alerts**: Automated notifications for critical levels

## 🔄 **Updating a Running System**

### **Automated Migration Script**
Use the provided migration script to safely add geolocation columns to your existing database:

```bash
# Stop the water monitoring service first
sudo systemctl stop water-monitor

# Run the migration script
python migrate_geolocation.py

# Restart the service
sudo systemctl start water-monitor
```

### **Migration Script Features**
- ✅ **Auto-detects database type** from environment variables
- ✅ **Checks existing columns** to avoid conflicts
- ✅ **Supports all database types** (SQLite, PostgreSQL, MySQL)
- ✅ **Adds sample data** for P.1 station
- ✅ **Safe operation** - won't break existing data

### **Step-by-Step Migration Process**

#### **1. Stop the Application**
```bash
# If running as systemd service
sudo systemctl stop water-monitor

# If running in screen/tmux
# Use Ctrl+C to stop the process

# If running as Docker container
docker stop water-monitor
```

#### **2. Backup Your Database**
```bash
# SQLite backup
cp water_monitoring.db water_monitoring.db.backup

# PostgreSQL backup
pg_dump water_monitoring > water_monitoring_backup.sql

# MySQL backup
mysqldump water_monitoring > water_monitoring_backup.sql
```

#### **3. Run Migration Script**
```bash
# Default (uses environment variables)
python migrate_geolocation.py

# Or specify database path for SQLite
SQLITE_DB_PATH=/path/to/water_monitoring.db python migrate_geolocation.py
```

#### **4. Verify Migration**
```bash
# Check SQLite schema
sqlite3 water_monitoring.db ".schema stations"

# Check PostgreSQL schema
psql -d water_monitoring -c "\d stations"

# Check MySQL schema
mysql -e "DESCRIBE water_monitoring.stations"
```

#### **5. Update Application Code**
Ensure you have the latest version of the application with geolocation support:
```bash
# Pull latest code
git pull origin main

# Install any new dependencies
pip install -r requirements.txt
```

#### **6. Restart Application**
```bash
# Systemd service
sudo systemctl start water-monitor

# Docker container
docker start water-monitor

# Manual execution
python water_scraper_v3.py
```

### **Migration Output Example**
```
2025-07-28 17:30:00,123 - INFO - Starting geolocation column migration...
2025-07-28 17:30:00,124 - INFO - Detected database type: SQLITE
2025-07-28 17:30:00,125 - INFO - Migrating SQLite database: water_monitoring.db
2025-07-28 17:30:00,126 - INFO - Current columns in stations table: ['id', 'station_code', 'thai_name', 'english_name', 'created_at', 'updated_at']
2025-07-28 17:30:00,127 - INFO - Added latitude column
2025-07-28 17:30:00,128 - INFO - Added longitude column
2025-07-28 17:30:00,129 - INFO - Added geohash column
2025-07-28 17:30:00,130 - INFO - Successfully added columns: latitude, longitude, geohash
2025-07-28 17:30:00,131 - INFO - Updated P.1 station with sample geolocation data
2025-07-28 17:30:00,132 - INFO - P.1 station geolocation: ('P.1', 15.6944, 100.2028, 'w5q6uuhvfcfp25')
2025-07-28 17:30:00,133 - INFO - ✅ Migration completed successfully!
2025-07-28 17:30:00,134 - INFO - You can now restart your water monitoring application
2025-07-28 17:30:00,135 - INFO - The system will automatically use the new geolocation columns
```

## 🔍 **Troubleshooting**

### **Migration Issues**

#### **Database Locked Error**
```bash
# Stop all processes using the database
sudo systemctl stop water-monitor
pkill -f water_scraper

# Wait a few seconds, then run migration
sleep 5
python migrate_geolocation.py
```

#### **Permission Denied**
```bash
# Check database file permissions
ls -la water_monitoring.db

# Fix permissions if needed
sudo chown $USER:$USER water_monitoring.db
chmod 664 water_monitoring.db
```

#### **Missing Dependencies**
```bash
# For PostgreSQL
pip install psycopg2-binary

# For MySQL
pip install pymysql

# For all databases
pip install -r requirements.txt
```

### **Verification Issues**

#### **Missing Coordinates**
If stations don't appear on the geomap:
1. Check if latitude/longitude are NULL in database
2. Verify geolocation data in station mapping
3. Ensure database schema includes geolocation columns
4. Run migration script if columns are missing

#### **Incorrect Positioning**
If stations appear in wrong locations:
1. Verify coordinate format (decimal degrees)
2. Check latitude/longitude order (lat first, lon second)
3. Validate geohash accuracy

### **Rollback Procedure**
If migration causes issues:

#### **SQLite Rollback**
```bash
# Stop application
sudo systemctl stop water-monitor

# Restore backup
cp water_monitoring.db.backup water_monitoring.db

# Restart with old version
sudo systemctl start water-monitor
```

#### **PostgreSQL Rollback**
```sql
-- Remove added columns
ALTER TABLE stations DROP COLUMN IF EXISTS latitude;
ALTER TABLE stations DROP COLUMN IF EXISTS longitude;
ALTER TABLE stations DROP COLUMN IF EXISTS geohash;
```

#### **MySQL Rollback**
```sql
-- Remove added columns
ALTER TABLE stations DROP COLUMN latitude;
ALTER TABLE stations DROP COLUMN longitude;
ALTER TABLE stations DROP COLUMN geohash;
```

## 🎯 **Next Steps**

### **Immediate Actions**
1. **Gather Coordinates**: Collect GPS coordinates for all 16 stations
2. **Update Database**: Add coordinates to remaining stations
3. **Create Dashboards**: Build Grafana geomap visualizations

### **Future Enhancements**
1. **Automatic Geocoding**: API integration for address-to-coordinate conversion
2. **Mobile GPS**: Mobile app for field coordinate collection
3. **Satellite Integration**: Satellite imagery overlay in Grafana
4. **Geofencing**: Alert zones based on geographic boundaries

The geolocation functionality is now fully implemented and ready for use with Grafana's geomap visualization. Station P.1 (Nawarat Bridge) serves as a working example with complete coordinate data.