File size: 2,771 Bytes
574b4c9 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | import streamlit as st
import pandas as pd
from sqlalchemy import create_engine, text
from sshtunnel import SSHTunnelForwarder
import os
import tempfile
# --- Config ---
st.set_page_config(page_title="GPS Map Viewer", layout="wide")
st.title("🛰️ GPS Track Viewer")
# --- Load secrets ---
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"]
# --- SSH Key File ---
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)
# --- SSH Tunnel ---
@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()
# --- SQLAlchemy Engine ---
MYSQL_URL = f"mysql+pymysql://{MYSQL_USER}:{MYSQL_PASSWORD}@{LOCAL_BIND_HOST}:{MYSQL_PORT}/{MYSQL_DB}"
engine = create_engine(MYSQL_URL)
# --- Load GPS Data ---
@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()
# --- Display Map ---
if not gps_data.empty:
# Convert to JS array
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.")
|