# ==========================================
# 札幌圏1kmメッシュ 気象データ SQLite自動登録システム
# ==========================================
import sys, os, glob, sqlite3, time, re, traceback
from datetime import datetime, timedelta
import numpy as np
from PyQt6.QtWidgets import (QApplication, QWidget, QVBoxLayout, QHBoxLayout,
QPushButton, QLabel, QListWidget, QFileDialog, QProgressBar, QMessageBox)
from PyQt6.QtCore import QThread, pyqtSignal, QTimer, QSettings
DB_FILE = “sapporo_1km_mesh.db”
# 1kmメッシュ（約0.01度刻み）のグリッドを生成 (東経141.00～141.40, 北緯42.50～43.20)
GRID_LONS = np.arange(141.00, 141.401, 0.01)
GRID_LATS = np.arange(42.50, 43.201, 0.01)
LONS_2D, LATS_2D = np.meshgrid(GRID_LONS, GRID_LATS)
FLAT_LONS = LONS_2D.flatten()
FLAT_LATS = LATS_2D.flatten()
def init_db():
conn = sqlite3.connect(DB_FILE)
c = conn.cursor()
c.execute(”’
CREATE TABLE IF NOT EXISTS weather_data (
id INTEGER PRIMARY KEY AUTOINCREMENT,
model TEXT,
init_time TEXT,
valid_time TEXT,
forecast_time INTEGER,
lat REAL,
lon REAL,
precip REAL,
temp REAL,
UNIQUE(model, init_time, valid_time, lat, lon)
)
”’)
conn.commit()
conn.close()
class DatabaseWorker(QThread):
log_signal = pyqtSignal(str)
progress_signal = pyqtSignal(int, int)
finished_signal = pyqtSignal()
def __init__(self, npz_dir):
super().__init__()
self.npz_dir = npz_dir
self.is_running = True
def get_var_key(self, data_obj, exact_keys, fuzzy_keys=None):
for k in data_obj.files:
if k.lower() in [ek.lower() for ek in exact_keys]: return k
if fuzzy_keys:
for k in data_obj.files:
kl = k.lower()
if not any(ex in kl for ex in [‘lon’, ‘lat’, ‘time’, ‘valid’]):
if any(fk.lower() in kl for fk in fuzzy_keys): return k
return None
def run(self):
self.log_signal.emit(“🤖 データベース自動登録監視を開始しました。”)
init_db()
processed_files = set()
while self.is_running:
try:
npz_files = glob.glob(os.path.join(self.npz_dir, “*.npz”))
new_files = [f for f in npz_files if f not in processed_files]
if new_files:
self.log_signal.emit(f”📄 新規NPZファイルを {len(new_files)}件 検出。処理開始…”)
conn = sqlite3.connect(DB_FILE)
cursor = conn.cursor()
for i, fpath in enumerate(new_files):
if not self.is_running: break
fname = os.path.basename(fpath)
# 例: MSM_20260524120000_FT00.npz
m = re.match(r'(MSM|GSM_JP|MSM_GUID|GSM_GUID)_(\d{14})_FT(\d+)\.npz’, fname)
if not m:
processed_files.add(fpath)
continue
model, it_str, ft_str = m.groups()
ft = int(ft_str)
init_dt = datetime.strptime(it_str, “%Y%m%d%H%M%S”)
valid_dt = init_dt + timedelta(hours=ft)
try:
data = np.load(fpath)
lon_key = self.get_var_key(data, [‘lon_surf’, ‘lon’, ‘longitude’])
lat_key = self.get_var_key(data, [‘lat_surf’, ‘lat’, ‘latitude’])
if not lon_key or not lat_key:
data.close(); processed_files.add(fpath); continue
data_lon = data[lon_key]
data_lat = data[lat_key]
precip_key = self.get_var_key(data, [‘precip’, ‘precip_raw’, ‘apcp’, ‘tp’], [‘precip’, ‘apcp’])
temp_key = self.get_var_key(data, [‘tmp’, ‘temp’, ‘t’, ‘t2m’, ‘tmp2m’], [‘tmp’, ‘temp’])
p_val = np.squeeze(data[precip_key]) if precip_key else None
t_val = np.squeeze(data[temp_key]) if temp_key else None
# 形状の補正
if data_lon.ndim == 1:
if p_val is not None and p_val.shape == (len(data_lon), len(data_lat)): p_val = p_val.T
if t_val is not None and t_val.shape == (len(data_lon), len(data_lat)): t_val = t_val.T
db_records = []
for lat_pt, lon_pt in zip(FLAT_LATS, FLAT_LONS):
if data_lon.ndim == 1:
i_lon = (np.abs(data_lon – lon_pt)).argmin()
i_lat = (np.abs(data_lat – lat_pt)).argmin()
else:
dist = (data_lon – lon_pt)**2 + (data_lat – lat_pt)**2
i_lat, i_lon = np.unravel_index(dist.argmin(), dist.shape)
precip = float(p_val[i_lat, i_lon]) if p_val is not None else None
temp = float(t_val[i_lat, i_lon]) if t_val is not None else None
if temp is not None and temp > 100: temp -= 273.15 # Kelvin to Celsius
if precip is not None: precip = max(0.0, float(np.nan_to_num(precip)))
db_records.append((model, init_dt.isoformat(), valid_dt.isoformat(), ft, lat_pt, lon_pt, precip, temp))
if db_records:
cursor.executemany(”’
INSERT OR REPLACE INTO weather_data
(model, init_time, valid_time, forecast_time, lat, lon, precip, temp)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
”’, db_records)
conn.commit()
data.close()
processed_files.add(fpath)
self.progress_signal.emit(i + 1, len(new_files))
except Exception as e:
self.log_signal.emit(f”⚠️ ファイル処理エラー ({fname}): {e}”)
processed_files.add(fpath)
conn.close()
if self.is_running:
self.log_signal.emit(f”✅ 新規データ {len(new_files)}件 のDB登録が完了しました。”)
except Exception as e:
self.log_signal.emit(f”⚠️ 監視ループエラー: {e}”)
for _ in range(10): # 10秒待機
if not self.is_running: break
time.sleep(1)
def stop(self):
self.is_running = False
class DBApp(QWidget):
def __init__(self):
super().__init__()
self.settings = QSettings(‘WeatherApp’, ‘DatabaseLoader’)
self.npz_dir = self.settings.value(‘npz_dir’, os.path.join(os.getcwd(), “gpv_cache_npz”))
os.makedirs(self.npz_dir, exist_ok=True)
self.worker = None
self.init_ui()
def init_ui(self):
self.setWindowTitle(“札幌圏 1kmメッシュ 気象データベース 自動構築システム”)
self.resize(600, 450)
self.setStyleSheet(“””QWidget { background-color: #2D2D30; color: #CCCCCC; font-family: ‘MS Gothic’; font-size: 10pt; } QPushButton { background-color: #007ACC; border: none; padding: 8px; border-radius: 4px; color: white; font-weight: bold; } QPushButton:hover { background-color: #1C97EA; } QListWidget { background-color: #1E1E1E; border: 1px solid #3F3F46; padding: 5px; color: #4EC9B0; } QProgressBar { text-align: center; color: white; background-color: #333; border-radius: 4px; border: 1px solid #555; } QProgressBar::chunk { background-color: #007ACC; border-radius: 4px; }”””)
layout = QVBoxLayout(self)
title = QLabel(“📡 SQLite データベース自動連携機能”)
title.setStyleSheet(“font-size: 12pt; font-weight: bold; color: white;”)
layout.addWidget(title)
dir_layout = QHBoxLayout()
self.lbl_in = QLabel(f”監視対象 NPZフォルダ: {self.npz_dir}”)
btn_in = QPushButton(“フォルダ変更”)
btn_in.clicked.connect(self.change_dir)
dir_layout.addWidget(self.lbl_in)
dir_layout.addWidget(btn_in)
layout.addLayout(dir_layout)
self.progress_bar = QProgressBar()
layout.addWidget(self.progress_bar)
self.log_list = QListWidget()
layout.addWidget(self.log_list)
ctrl_layout = QHBoxLayout()
self.btn_start = QPushButton(“▶ DB自動登録を開始”)
self.btn_start.setStyleSheet(“background-color: #27AE60; height: 35px;”)
self.btn_start.clicked.connect(self.start_worker)
self.btn_stop = QPushButton(“■ 停止”)
self.btn_stop.setStyleSheet(“background-color: #C0392B; height: 35px;”)
self.btn_stop.setEnabled(False)
self.btn_stop.clicked.connect(self.stop_worker)
ctrl_layout.addWidget(self.btn_start)
ctrl_layout.addWidget(self.btn_stop)
layout.addLayout(ctrl_layout)
def log(self, msg):
self.log_list.addItem(f”[{datetime.now().strftime(‘%H:%M:%S’)}] {msg}”)
self.log_list.scrollToBottom()
def change_dir(self):
d = QFileDialog.getExistingDirectory(self, “監視フォルダ選択”, self.npz_dir)
if d:
self.npz_dir = d
self.settings.setValue(‘npz_dir’, self.npz_dir)
self.lbl_in.setText(f”監視対象 NPZフォルダ: {self.npz_dir}”)
def start_worker(self):
if self.worker and self.worker.isRunning(): return
self.btn_start.setEnabled(False)
self.btn_stop.setEnabled(True)
self.progress_bar.setValue(0)
self.log(“🚀 SQLite連携システムを起動しました。”)
self.worker = DatabaseWorker(self.npz_dir)
self.worker.log_signal.connect(self.log)
self.worker.progress_signal.connect(lambda c, t: self.progress_bar.setValue(int(c/t*100) if t>0 else 0))
self.worker.start()
def stop_worker(self):
if self.worker:
self.worker.stop()
self.worker.wait()
self.worker = None
self.btn_start.setEnabled(True)
self.btn_stop.setEnabled(False)
self.log(“⏹️ システムを停止しました。”)
def closeEvent(self, event):
self.stop_worker()
event.accept()
if __name__ == ‘__main__’:
app = QApplication(sys.argv)
window = DBApp()
window.show()
sys.exit(app.exec())
コメント