| import streamlit as st |
| import pandas as pd |
| from sqlalchemy import create_engine, text |
| from sshtunnel import SSHTunnelForwarder |
| import os |
| import tempfile |
|
|
| |
| st.set_page_config(page_title="GPS Map Viewer", layout="wide") |
| st.title("🛰️ GPS Track Viewer") |
|
|
| |
| SSH_KEY = st.secrets["SSH_KEY"] |
| SSH_USER = st.secrets["SSH_USER"] |
| SSH_HOST = st.secrets["DB_HOST"] |
| REMOTE_BIND_HOST = st.secrets["REMOTE_BIND_HOST"] |
| LOCAL_BIND_HOST = st.secrets["LOCAL_BIND_HOST"] |
| MYSQL_USER = st.secrets["MYSQL_USER"] |
| MYSQL_PASSWORD = st.secrets["MYSQL_PASSWORD"] |
| MYSQL_DB = st.secrets["DB_NAME"] |
| MYSQL_PORT = int(st.secrets["MYSQL_PORT"]) |
| GOOGLE_MAPS_API_KEY = st.secrets["GOOGLE_MAPS_API_KEY"] |
|
|
| |
| with tempfile.NamedTemporaryFile(delete=False, suffix=".pem") as temp_file: |
| temp_file.write(SSH_KEY.encode()) |
| temp_file_path = temp_file.name |
| os.chmod(temp_file_path, 0o600) |
|
|
| |
| @st.cache_resource |
| def start_ssh_tunnel(): |
| tunnel = SSHTunnelForwarder( |
| (SSH_HOST, 22), |
| ssh_username=SSH_USER, |
| ssh_pkey=temp_file_path, |
| remote_bind_address=(REMOTE_BIND_HOST, MYSQL_PORT), |
| local_bind_address=(LOCAL_BIND_HOST, MYSQL_PORT) |
| ) |
| tunnel.start() |
| return tunnel |
|
|
| server = start_ssh_tunnel() |
|
|
| |
| MYSQL_URL = f"mysql+pymysql://{MYSQL_USER}:{MYSQL_PASSWORD}@{LOCAL_BIND_HOST}:{MYSQL_PORT}/{MYSQL_DB}" |
| engine = create_engine(MYSQL_URL) |
|
|
| |
| @st.cache_data(ttl=300) |
| def load_gps_data(): |
| query = text("SELECT latitude, longitude FROM gps_points ORDER BY timestamp") |
| df = pd.read_sql(query, engine) |
| return df |
|
|
| gps_data = load_gps_data() |
|
|
| |
| if not gps_data.empty: |
| |
| points_js = gps_data.to_dict(orient="records") |
| st.components.v1.html(f""" |
| <div id="map" style="height: 90vh; width: 100%;"></div> |
| <script src="https://maps.googleapis.com/maps/api/js?key={GOOGLE_MAPS_API_KEY}"></script> |
| <script> |
| const points = {points_js}; |
| function initMap() {{ |
| const map = new google.maps.Map(document.getElementById("map"), {{ |
| zoom: 14, |
| center: {{ lat: points[0].latitude, lng: points[0].longitude }}, |
| mapTypeId: 'roadmap' |
| }}); |
| const path = points.map(p => new google.maps.LatLng(p.latitude, p.longitude)); |
| new google.maps.Polyline({{ |
| path, |
| geodesic: true, |
| strokeColor: "#FF0000", |
| strokeOpacity: 1.0, |
| strokeWeight: 2, |
| map: map |
| }}); |
| }} |
| window.onload = initMap; |
| </script> |
| """, height=700) |
| else: |
| st.info("No GPS data available.") |
|
|
