GRIB File Processing Setup

Automating the extraction of GRIB2 file data into PostgreSQL using Python.

1. Overview

This process automates extraction of GRIB2 file data into a PostgreSQL table. The Python script processes GRIB2 files from /public_html/grib, extracts data, inserts into database, and deletes processed files.

2. Required Libraries

• pygrib - Reading GRIB files: pip install pygrib
• psycopg2 - PostgreSQL interaction: pip install psycopg2
• ecCodes - Install: sudo apt-get install eccodes

3. PostgreSQL Table Setup

CREATE TABLE imports.unpackednetcdf (
    id SERIAL PRIMARY KEY,
    filename TEXT,
    latitude REAL,
    longitude REAL,
    value REAL
);

4. Python Script

import pygrib
import psycopg2
import os
import glob

conn = psycopg2.connect(
    dbname="your_db",
    user="your_user",
    password="your_password",
    host="localhost",
    port="5432"
)
cursor = conn.cursor()

grib_folder = "/public_html/grib"

for grib_file in glob.glob(os.path.join(grib_folder, '*.grib2')):
    print(f"Processing file: {grib_file}")
    try:
        grbs = pygrib.open(grib_file)
        for message_number, grb in enumerate(grbs):
            print(f"Processing GRIB message {message_number}")
            lats, lons = grb.latlons()
            values = grb.values

            insert_data = [
                (os.path.basename(grib_file), lats[i][j], lons[i][j], values[i][j])
                for i in range(len(lats))
                for j in range(len(lons[i]))
            ]
            cursor.executemany(
                "INSERT INTO imports.unpackednetcdf (filename, latitude, longitude, value) VALUES (%s, %s, %s, %s)",
                insert_data
            )
        conn.commit()
        os.remove(grib_file)
    except Exception as e:
        print(f"Failed to process {grib_file}: {e}")
        conn.rollback()

cursor.close()
conn.close()

5. Automating with Cron

Set up a cron job to run the script periodically:

crontab -e

# Run every hour
0 * * * * /usr/bin/python3 /path/to/your/script.py >> /path/to/logfile.log 2>&1

6. Log Management

Output is redirected to a log file. Monitor for errors regularly.

7. Conclusion

Files are automatically picked up, processed, and deleted after successful database insertion.