import sys
import os
import sqlite3
import pandas as pd
import numpy as np
from datetime import datetime, timedelta, timezone
import re
import subprocess
import glob
import io
import logging
from PyQt6.QtWidgets import (QApplication, QMainWindow, QWidget,
QVBoxLayout, QHBoxLayout, QPushButton, QLabel,
QFileDialog, QComboBox, QDateEdit, QMessageBox,
QGroupBox, QSystemTrayIcon, QStyle, QRadioButton,
QButtonGroup, QFrame, QGraphicsView, QGraphicsScene, QMenu)
from PyQt6.QtCore import QDate, Qt, QTimer, QSettings
from PyQt6.QtGui import QAction, QPixmap, QImageReader
class ZoomableView(QGraphicsView):
def __init__(self, scene, parent=None):
super().__init__(scene, parent)
self.setDragMode(QGraphicsView.DragMode.ScrollHandDrag)
self.setTransformationAnchor(QGraphicsView.ViewportAnchor.AnchorUnderMouse)
self.setResizeAnchor(QGraphicsView.ViewportAnchor.AnchorUnderMouse)
def wheelEvent(self, event):
zoom_in_factor = 1.15
zoom_out_factor = 1.0 / zoom_in_factor
if event.angleDelta().y() > 0:
zoom_factor = zoom_in_factor
else:
zoom_factor = zoom_out_factor
self.scale(zoom_factor, zoom_factor)
class GPVExtractorApp(QMainWindow):
def __init__(self):
super().__init__()
self.setWindowTitle("GPV解析値 (GRIB2) 高速抽出エンジン (Ver 22.0 - 完全並行稼働・チャンク処理版)")
self.resize(1100, 750)
self.really_quit = False
# --- 設定の保存・読み込み用 ---
self.settings = QSettings("WeatherApp", "GPVExtractor")
self.setStyleSheet("""
QMainWindow { background-color: #F4F6F9; font-family: 'Segoe UI', 'Meiryo', sans-serif; }
QGroupBox { font-weight: bold; color: #E65100; border: 1px solid #D0D7DE; border-radius: 6px; margin-top: 15px; background-color: #FFFFFF; }
QGroupBox::title { subcontrol-origin: margin; left: 10px; padding: 0 5px; }
QPushButton { background-color: #FF8C00; color: white; border: none; border-radius: 5px; padding: 8px 16px; font-weight: bold; }
QPushButton:hover { background-color: #F57C00; }
QPushButton:pressed { background-color: #E65100; }
QPushButton:disabled { background-color: #A0A0A0; color: #E0E0E0; }
QPushButton#ActionBtn { background-color: #28A745; }
QPushButton#ActionBtn:hover { background-color: #218838; }
QPushButton#AutoBtn { background-color: #D73A49; }
QLineEdit, QComboBox, QDateEdit { border: 1px solid #D0D7DE; border-radius: 4px; padding: 5px; background-color: #FAFBFC; min-height: 25px; }
""")
# ★前回保存したDBパスを読み込む（未設定時はデフォルト）
default_db = os.path.abspath("weather_verification(削除禁止).db")
self.db_path = self.settings.value("last_db_path", default_db)
# 札幌圏 座標設定
self.lat_min = 42.0 + (40.0 / 60.0)
self.lat_max = 43.0 + (20.0 / 60.0)
self.lon_min = 141.0 + (0.0 / 60.0)
self.lon_max = 141.0 + (40.0 / 60.0)
self.wgrib2_path = "wgrib2.exe"
self.setup_tray_icon()
self.init_db()
self.init_ui()
self.t8_auto_timer = QTimer(self)
self.t8_auto_timer.timeout.connect(self.auto_fetch_grib2)
QTimer.singleShot(100, self.reload_reference_map)
QTimer.singleShot(500, self.ask_auto_start)
def ask_auto_start(self):
reply = QMessageBox.question(
self,
"自動起動の確認",
"システムが起動しました。\n直ちにGRIB2データの自動抽出（常駐監視）を開始しますか？",
QMessageBox.StandardButton.Yes | QMessageBox.StandardButton.No
)
if reply == QMessageBox.StandardButton.Yes:
if not self.t8_auto_timer.isActive():
self.toggle_t8_auto()
def setup_tray_icon(self):
self.tray_icon = QSystemTrayIcon(self)
self.tray_icon.setIcon(self.style().standardIcon(QStyle.StandardPixmap.SP_DriveNetIcon))
self.tray_icon.setToolTip("GPV抽出エンジン - 稼働中")
tray_menu = QMenu()
show_action = QAction("画面を表示する", self)
show_action.triggered.connect(self.showNormal)
quit_action = QAction("完全に終了する", self)
quit_action.triggered.connect(self.force_quit)
tray_menu.addAction(show_action)
tray_menu.addSeparator()
tray_menu.addAction(quit_action)
self.tray_icon.setContextMenu(tray_menu)
self.tray_icon.show()
self.tray_icon.activated.connect(self.on_tray_icon_activated)
def on_tray_icon_activated(self, reason):
if reason == QSystemTrayIcon.ActivationReason.DoubleClick:
self.showNormal()
self.activateWindow()
def closeEvent(self, event):
if not self.really_quit:
event.ignore()
self.hide()
self.tray_icon.showMessage("バックグラウンドで実行中", "GPV自動抽出処理は継続しています。", QSystemTrayIcon.MessageIcon.Information, 3000)
else:
event.accept()
def force_quit(self):
self.really_quit = True
QApplication.quit()
def init_db(self):
# ★原因1解決: タイムアウトを30秒に設定し、複数アプリの同時書き込み衝突を完全に防止
self.conn = sqlite3.connect(self.db_path, timeout=30.0)
self.conn.execute("PRAGMA journal_mode=WAL;")
cursor = self.conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS OBS_GP (ObsDay TEXT, ObsTime TEXT, MsCd TEXT, Rainfall1h REAL, Snowfall1h REAL, SnowDepth REAL, PRIMARY KEY (ObsDay, ObsTime, MsCd))''')
cursor.execute('''CREATE TABLE IF NOT EXISTS GPV_Points (PointID INTEGER PRIMARY KEY AUTOINCREMENT, Lon REAL, Lat REAL, UNIQUE(Lon, Lat))''')
try:
cursor.execute("ALTER TABLE GPV_Points ADD COLUMN PointIDText TEXT")
except sqlite3.OperationalError:
pass # 既に存在する場合は無視
self.conn.commit()
def init_ui(self):
main_widget = QWidget()
main_layout = QVBoxLayout(main_widget)
header = QHBoxLayout()
# ★動的にDBパスを書き換えられるように self.lbl_db_path とする
abs_db_path = os.path.abspath(self.db_path)
self.lbl_db_path = QLabel(f"🔗 統合共有DB: {abs_db_path} \n(※WALモード防衛線稼働中)")
self.lbl_db_path.setStyleSheet("font-weight: bold; color: #0366D6; font-size: 14px;")
header.addWidget(self.lbl_db_path)
header.addStretch()
# ★DB選択ボタンを追加
btn_select_db = QPushButton("⚙️ DBを選択 / 変更")
btn_select_db.setStyleSheet("padding: 5px 15px; font-weight: bold;")
btn_select_db.clicked.connect(self.select_db)
header.addWidget(btn_select_db)
btn_quit = QPushButton("❌ 完全に終了する")
btn_quit.setStyleSheet("background-color: #CB2431; color: white; padding: 5px 15px; font-weight: bold;")
btn_quit.clicked.connect(self.force_quit)
header.addWidget(btn_quit)
main_layout.addLayout(header)
content_layout = QHBoxLayout()
content_layout.setSpacing(15)
# 左パネル
left_panel = QWidget()
left_layout = QVBoxLayout(left_panel)
left_panel.setFixedWidth(460)
group_mode = QGroupBox("1. GRIB2 データ取得モード (※元データはUTC基準です)")
mode_layout = QVBoxLayout()
auto_layout = QHBoxLayout()
self.btn_t8_auto = QPushButton("▶ GRIB2 自動取得を開始 (10分毎)")
self.btn_t8_auto.setObjectName("AutoBtn")
self.btn_t8_auto.clicked.connect(self.toggle_t8_auto)
self.lbl_t8_auto = QLabel("停止中")
self.lbl_t8_auto.setStyleSheet("color: #D73A49; font-weight: bold;")
auto_layout.addWidget(self.btn_t8_auto)
auto_layout.addWidget(self.lbl_t8_auto)
auto_layout.addStretch()
mode_layout.addLayout(auto_layout)
self.grib_radio_latest = QRadioButton("🌟 最新データを取得 (自動取得用)")
self.grib_radio_latest.setChecked(True)
self.grib_radio_range = QRadioButton("📅 過去データを期間指定して一括抽出")
mode_group = QButtonGroup()
mode_group.addButton(self.grib_radio_latest)
mode_group.addButton(self.grib_radio_range)
date_layout = QHBoxLayout()
self.grib_date_start = QDateEdit(QDate.currentDate().addDays(-1))
self.grib_date_start.setCalendarPopup(True)
self.grib_date_start.setEnabled(False)
self.grib_date_end = QDateEdit(QDate.currentDate())
self.grib_date_end.setCalendarPopup(True)
self.grib_date_end.setEnabled(False)
date_layout.addWidget(QLabel("開始(JST):"))
date_layout.addWidget(self.grib_date_start)
date_layout.addWidget(QLabel("〜 終了:"))
date_layout.addWidget(self.grib_date_end)
self.grib_radio_range.toggled.connect(lambda checked: self.grib_date_start.setEnabled(checked))
self.grib_radio_range.toggled.connect(lambda checked: self.grib_date_end.setEnabled(checked))
mode_layout.addWidget(self.grib_radio_latest)
mode_layout.addWidget(self.grib_radio_range)
mode_layout.addLayout(date_layout)
group_mode.setLayout(mode_layout)
left_layout.addWidget(group_mode)
group_folders = QGroupBox("2. 解析値データ フォルダ設定 ＆ 超高速一括書込")
f_layout = QVBoxLayout()
self.grib_folders = {
"rain": self.settings.value("folder_rain", ""),
"snowfall": self.settings.value("folder_snowfall", ""),
"snowdepth": self.settings.value("folder_snowdepth", "")
}
self.grib_labels = {}
for key, icon, name in [("rain", "🌧️", "解析雨量"), ("snowfall", "❄️", "解析降雪量"), ("snowdepth", "⛄", "解析積雪深")]:
row = QVBoxLayout()
current_folder = self.grib_folders[key]
lbl_text = f"📁 {current_folder}" if current_folder else f"{icon} {name} フォルダ: 未設定"
lbl = QLabel(lbl_text)
self.grib_labels[key] = lbl
btn_layout = QHBoxLayout()
btn_set = QPushButton("フォルダ選択")
btn_set.clicked.connect(lambda _, k=key: self.select_grib_folder(k))
btn_ext = QPushButton(f"{name} 個別抽出")
btn_ext.clicked.connect(lambda _, k=key, n=name: self.run_grib_db_extraction(k, n))
btn_layout.addWidget(btn_set)
btn_layout.addWidget(btn_ext)
row.addWidget(lbl)
row.addLayout(btn_layout)
f_layout.addLayout(row)
line = QFrame()
line.setFrameShape(QFrame.Shape.HLine)
line.setFrameShadow(QFrame.Shadow.Sunken)
f_layout.addWidget(line)
btn_ext_all = QPushButton("🌟 3要素一括 同時高速抽出 (雨・雪・積雪)")
btn_ext_all.setStyleSheet("background-color: #28A745; color: white; font-size: 14px; padding: 10px;")
btn_ext_all.clicked.connect(self.run_grib_db_extraction_all)
f_layout.addWidget(btn_ext_all)
group_folders.setLayout(f_layout)
left_layout.addWidget(group_folders)
group_query = QGroupBox("3. 解析値 簡易データ確認 (※日本時間JST指定)")
query_layout = QVBoxLayout()
dt_layout = QHBoxLayout()
self.t8_q_date = QDateEdit(QDate.currentDate())
self.t8_q_date.setCalendarPopup(True)
self.t8_q_time = QComboBox()
self.t8_q_time.addItem("日合計 / 日最大 (24h)")
self.t8_q_time.addItems([f"{str(h).zfill(2)}:00" for h in range(24)])
dt_layout.addWidget(QLabel("日時(JST):"))
dt_layout.addWidget(self.t8_q_date)
dt_layout.addWidget(self.t8_q_time)
query_layout.addLayout(dt_layout)
pt_layout = QHBoxLayout()
self.t8_q_point = QComboBox()
self.update_t8_query_points()
pt_layout.addWidget(QLabel("地点:"))
pt_layout.addWidget(self.t8_q_point)
query_layout.addLayout(pt_layout)
el_layout = QHBoxLayout()
self.t8_q_element = QComboBox()
self.t8_q_element.addItems(["降水量 (Rainfall1h)", "降雪量 (Snowfall1h)", "積雪深 (SnowDepth)"])
el_layout.addWidget(QLabel("要素:"))
el_layout.addWidget(self.t8_q_element)
query_layout.addLayout(el_layout)
res_layout = QHBoxLayout()
btn_query = QPushButton("値を確認")
btn_query.setObjectName("ActionBtn")
btn_query.clicked.connect(self.run_t8_simple_query)
self.lbl_t8_q_result = QLabel("結果: --")
self.lbl_t8_q_result.setStyleSheet("font-size: 15px; font-weight: bold; color: #D73A49;")
res_layout.addWidget(btn_query)
res_layout.addWidget(self.lbl_t8_q_result)
res_layout.addStretch()
query_layout.addLayout(res_layout)
group_query.setLayout(query_layout)
left_layout.addWidget(group_query)
left_layout.addStretch()
content_layout.addWidget(left_panel)
# 右パネル
right_panel = QWidget()
right_layout = QVBoxLayout(right_panel)
group_map = QGroupBox("4. GPV座標 リファレンスマップ")
map_layout = QVBoxLayout()
self.scene = QGraphicsScene()
self.view = ZoomableView(self.scene)
self.view.setStyleSheet("background-color: #E1E4E8; border: 1px solid #D0D7DE;")
map_layout.addWidget(self.view)
btn_reload = QPushButton("🔄 地図画像を再読み込み")
btn_reload.clicked.connect(self.reload_reference_map)
map_layout.addWidget(btn_reload)
group_map.setLayout(map_layout)
right_layout.addWidget(group_map)
content_layout.addWidget(right_panel, stretch=1)
main_layout.addLayout(content_layout)
self.setCentralWidget(main_widget)
def select_db(self):
"""データベースファイルを動的に選択し、接続を切り替える"""
path, _ = QFileDialog.getOpenFileName(
self, "SQLiteデータベースを選択",
os.path.dirname(self.db_path) if self.db_path else "",
"SQLite DB (*.db);;All Files (*.*)"
)
if path:
self.db_path = os.path.abspath(path)
self.settings.setValue("last_db_path", self.db_path)
self.lbl_db_path.setText(f"🔗 統合共有DB: {self.db_path}\n(※WALモード防衛線稼働中)")
try:
if hasattr(self, 'conn'):
self.conn.close()
self.init_db()
if hasattr(self, 'update_t8_query_points'):
self.update_t8_query_points()
logging.info(f"データベースを切り替えました: {path}")
QMessageBox.information(self, "完了", f"データベースを切り替えました。\n{path}")
except Exception as e:
logging.error(f"データベースの切り替えに失敗: {e}", exc_info=True)
QMessageBox.critical(self, "エラー", f"データベースの読み込みに失敗しました:\n{e}")
def update_t8_query_points(self):
if not hasattr(self, 't8_q_point'): return
self.t8_q_point.clear()
try:
cursor = self.conn.cursor()
cursor.execute("SELECT DISTINCT MsCd FROM OBS_GP ORDER BY MsCd")
points = cursor.fetchall()
cursor.execute("SELECT PointID, Lon, Lat, PointIDText FROM GPV_Points")
gpv_map = {}
for r in cursor.fetchall():
pid_text = r[3] if r[3] else str(r[0]).zfill(5)
gpv_map[f"GPV_{pid_text}"] = (r[1], r[2])
display_items = []
for p in points:
code = str(p[0])
if code in gpv_map:
lon, lat = gpv_map[code]
display_items.append(f"{code} (Lon: {lon:.3f}, Lat: {lat:.3f})")
else:
display_items.append(code)
self.t8_q_point.addItems(display_items)
except Exception: pass
def toggle_t8_auto(self):
if self.t8_auto_timer.isActive():
self.t8_auto_timer.stop()
self.lbl_t8_auto.setText("停止中")
self.btn_t8_auto.setText("▶ GRIB2 自動取得を開始 (10分毎)")
else:
self.t8_auto_timer.start(600000)
self.lbl_t8_auto.setText("🔴 自動取得中")
self.btn_t8_auto.setText("■ GRIB2 自動取得を停止")
self.auto_fetch_grib2()
def auto_fetch_grib2(self):
self.grib_radio_latest.setChecked(True)
for key, name in [("rain", "解析雨量"), ("snowfall", "解析降雪量"), ("snowdepth", "解析積雪深")]:
if self.grib_folders.get(key):
self.run_grib_db_extraction(key, name, silent=True)
def reload_reference_map(self):
self.scene.clear()
pixmap = QPixmap("gpv_reference_map.png")
if not pixmap.isNull():
self.scene.addPixmap(pixmap)
self.view.fitInView(self.scene.sceneRect(), Qt.AspectRatioMode.KeepAspectRatio)
else:
self.scene.addText("画像 'gpv_reference_map.png' が見つかりません。")
def run_t8_simple_query(self):
date_str = self.t8_q_date.date().toString("yyyy-MM-dd")
time_str = self.t8_q_time.currentText()
ms_cd = self.t8_q_point.currentText().split(" (")[0]
el_str = self.t8_q_element.currentText()
if "Rain" in el_str: col, unit = "Rainfall1h", "mm"
elif "Snowfall" in el_str: col, unit = "Snowfall1h", "cm"
else: col, unit = "SnowDepth", "cm"
try:
cursor = self.conn.cursor()
if "日合計" in time_str:
jst_start = datetime.combine(self.t8_q_date.date().toPyDate(), datetime.min.time())
utc_start = jst_start - timedelta(hours=9)
utc_end = utc_start + timedelta(hours=23)
u_start_str = utc_start.strftime("%Y-%m-%d %H:%M")
u_end_str = utc_end.strftime("%Y-%m-%d %H:%M")
if col == "SnowDepth":
cursor.execute(f"SELECT MAX({col}) FROM OBS_GP WHERE ObsDay || ' ' || ObsTime BETWEEN ? AND ? AND MsCd=?", (u_start_str, u_end_str, ms_cd))
else:
cursor.execute(f"SELECT SUM({col}) FROM OBS_GP WHERE ObsDay || ' ' || ObsTime BETWEEN ? AND ? AND MsCd=?", (u_start_str, u_end_str, ms_cd))
else:
jst_dt = datetime.strptime(f"{date_str} {time_str}", "%Y-%m-%d %H:%M")
utc_dt = jst_dt - timedelta(hours=9)
cursor.execute(f"SELECT {col} FROM OBS_GP WHERE ObsDay=? AND ObsTime=? AND MsCd=?", (utc_dt.strftime("%Y-%m-%d"), utc_dt.strftime("%H:%M"), ms_cd))
row = cursor.fetchone()
if row and row[0] is not None:
self.lbl_t8_q_result.setText(f"結果: {row[0]:.1f} {unit}")
else:
self.lbl_t8_q_result.setText("結果: データなし")
except Exception:
self.lbl_t8_q_result.setText("結果: エラー")
def select_grib_folder(self, key):
folder = QFileDialog.getExistingDirectory(self, "フォルダを選択")
if folder:
self.grib_folders[key] = folder
self.grib_labels[key].setText(f"📁 {folder}")
self.settings.setValue(f"folder_{key}", folder)
def run_grib_db_extraction_all(self):
QApplication.setOverrideCursor(Qt.CursorShape.WaitCursor)
success_count = 0
try:
for key, name in [("rain", "解析雨量"), ("snowfall", "解析降雪量"), ("snowdepth", "解析積雪深")]:
if self.grib_folders.get(key):
inserted = self.run_grib_db_extraction(key, name, silent=True)
if inserted is not None: success_count += inserted
QMessageBox.information(self, "完了", f"3要素の一括抽出処理がすべて完了しました。\n実更新データ総数: {success_count} 件")
finally:
QApplication.restoreOverrideCursor()
def run_grib_db_extraction(self, data_type_key, data_name, silent=False):
folder_path = self.grib_folders.get(data_type_key)
if not folder_path or not os.path.exists(folder_path):
if not silent: QMessageBox.warning(self, "警告", f"{data_name} のフォルダが設定されていません。")
return None
if data_type_key == "rain":
pattern = "**/*Prr60lv*.bin"
col_name = "Rainfall1h"
elif data_type_key == "snowfall":
pattern = "**/*Psflv*.bin"
col_name = "Snowfall1h"
else:
pattern = "**/*Psdlv*.bin"
col_name = "SnowDepth"
sql_upsert = f"INSERT INTO OBS_GP (ObsDay, ObsTime, MsCd, {col_name}) VALUES (?, ?, ?, ?) ON CONFLICT(ObsDay, ObsTime, MsCd) DO UPDATE SET {col_name} = excluded.{col_name};"
bin_files = glob.glob(os.path.join(folder_path, pattern), recursive=True)
if not bin_files:
if not silent: QMessageBox.warning(self, "ファイルなし", "対象ファイルが見つかりません。")
return None
# ★原因2解決: どんな命名規則でも確実に日付(12桁)を抽出する最強の正規表現
def get_date_from_filename(f):
m = re.search(r'(20[2-9]\d{5,11})', os.path.basename(f))
if m:
ds = m.group(1)
if len(ds) >= 12: return ds[:12]
elif len(ds) == 10: return ds + "00"
elif len(ds) == 8: return ds + "0000"
return "000000000000"
bin_files.sort(key=get_date_from_filename)
target_files = bin_files[-1:] if self.grib_radio_latest.isChecked() else []
if not self.grib_radio_latest.isChecked():
sd_utc = (self.grib_date_start.date().toPyDate() - timedelta(days=1)).strftime("%Y%m%d")
ed_utc = self.grib_date_end.date().toPyDate().strftime("%Y%m%d")
for f in bin_files:
dt_str = get_date_from_filename(f)
if sd_utc <= dt_str[:8] <= ed_utc:
target_files.append(f)
if not target_files:
if not silent: QMessageBox.warning(self, "該当なし", "画面で指定された期間内のファイルがありません。\n(期間設定が古い可能性があります)")
return None
total_inserted = 0
cursor = self.conn.cursor()
try:
cursor.execute("UPDATE GPV_Points SET PointIDText = substr('00000' || PointID, -5, 5) WHERE PointIDText IS NULL")
self.conn.commit()
db_points = pd.read_sql_query("SELECT PointIDText, Lon, Lat FROM GPV_Points", self.conn)
db_points['Lon'] = db_points['Lon'].round(4)
db_points['Lat'] = db_points['Lat'].round(4)
total_pts = len(db_points)
for target_file in target_files:
dt_str = get_date_from_filename(target_file)
if dt_str == "000000000000": continue
dt_utc = datetime.strptime(dt_str, "%Y%m%d%H%M")
obs_day = dt_utc.strftime("%Y-%m-%d")
obs_time = dt_utc.strftime("%H:%M")
if not obs_time.endswith("00"): continue
# ★スキップ機能: 最新取得モード(Auto)でも実行し、同じ処理の無駄打ちを防止
if total_pts > 0:
cursor.execute(f"SELECT COUNT({col_name}) FROM OBS_GP WHERE ObsDay=? AND ObsTime=? AND {col_name} IS NOT NULL", (obs_day, obs_time))
exist_count = cursor.fetchone()[0]
if exist_count >= total_pts:
continue
# ★原因3解決: PandasのChunking(分割処理)を利用し、1kmメッシュデータを省メモリ・超高速で確実に処理する
proc = subprocess.Popen([self.wgrib2_path, target_file, "-no_header", "-csv", "-"], stdout=subprocess.PIPE, text=True)
f_df_list = []
# wgrib2の出力を10万行ずつ直接Pythonに流し込み、条件に合うものだけを拾い上げる
for chunk in pd.read_csv(proc.stdout, header=None, engine='c', chunksize=100000):
val_1 = pd.to_numeric(chunk.iloc[:, -3], errors='coerce')
val_2 = pd.to_numeric(chunk.iloc[:, -2], errors='coerce')
chunk["Value"] = pd.to_numeric(chunk.iloc[:, -1], errors='coerce')
if val_1.mean() > 100:
chunk["Lon"], chunk["Lat"] = val_1, val_2
else:
chunk["Lon"], chunk["Lat"] = val_2, val_1
chunk["Lon"] = chunk["Lon"].round(4)
chunk["Lat"] = chunk["Lat"].round(4)
chunk = chunk.dropna(subset=["Lon", "Lat", "Value"])
filtered = chunk[(chunk["Lat"] >= self.lat_min) & (chunk["Lat"] <= self.lat_max) &
(chunk["Lon"] >= self.lon_min) & (chunk["Lon"] <= self.lon_max)]
if not filtered.empty:
f_df_list.append(filtered)
proc.wait() # 読み込み終了を待機
if not f_df_list:
continue # 該当データが1つも無かった場合
f_df = pd.concat(f_df_list, ignore_index=True)
unique_coords = f_df[['Lon', 'Lat']].drop_duplicates()
merged_check = pd.merge(unique_coords, db_points, on=['Lon', 'Lat'], how='left')
missing_coords = merged_check[merged_check['PointIDText'].isna()]
if not missing_coords.empty:
new_coords = list(missing_coords[['Lon', 'Lat']].itertuples(index=False, name=None))
cursor.executemany("INSERT OR IGNORE INTO GPV_Points (Lon, Lat) VALUES (?, ?)", new_coords)
cursor.execute("UPDATE GPV_Points SET PointIDText = substr('00000' || PointID, -5, 5) WHERE PointIDText IS NULL")
db_points = pd.read_sql_query("SELECT PointIDText, Lon, Lat FROM GPV_Points", self.conn)
db_points['Lon'] = db_points['Lon'].round(4)
db_points['Lat'] = db_points['Lat'].round(4)
total_pts = len(db_points)
f_df = pd.merge(f_df, db_points, on=['Lon', 'Lat'], how='left')
f_df = f_df.dropna(subset=['PointIDText', 'Value'])
f_df['ObsDay'] = obs_day
f_df['ObsTime'] = obs_time
f_df['MsCd'] = 'GPV_' + f_df['PointIDText'].astype(str)
bulk_data = list(f_df[['ObsDay', 'ObsTime', 'MsCd', 'Value']].itertuples(index=False, name=None))
cursor.executemany(sql_upsert, bulk_data)
total_inserted += len(bulk_data)
self.conn.commit()
if hasattr(self, 'update_t8_query_points'):
self.update_t8_query_points()
if not silent:
QMessageBox.information(self, "書込完了", f"{data_name}の抽出・書込が完了しました。\n処理対象ファイル数: {len(target_files)}\n実更新データ数: {total_inserted} 件")
return total_inserted
except Exception as e:
self.conn.rollback()
if not silent: QMessageBox.critical(self, "エラー", f"抽出処理エラー:\n{e}")
return None
if __name__ == "__main__":
app = QApplication(sys.argv)
app.setQuitOnLastWindowClosed(False)
QImageReader.setAllocationLimit(0)
window = GPVExtractorApp()
window.show()
sys.exit(app.exec())
未分類