Spaces:
Sleeping
Sleeping
import pandas as pd | |
import psycopg2 | |
import numpy as np | |
import streamlit as st | |
from streamlit_folium import st_folium | |
import folium | |
from folium import plugins | |
import json | |
import geopandas as gp | |
import altair as alt | |
from datetime import date, timedelta | |
from geojson import Feature, Point, FeatureCollection | |
import matplotlib | |
import os | |
import h3 | |
st.set_page_config(page_title="GPS Blau", layout="wide", page_icon="🛰️") | |
def coneccion_db(): | |
conn = psycopg2.connect(**st.secrets["postgres"]) | |
return conn | |
def db_gps_data(fecha:str): | |
q = f""" | |
SELECT device_id | |
, array_agg( | |
json_build_object( | |
'type', 'Feature', | |
'geometry', st_asgeojson(lineas)::jsonb, | |
'properties', json_build_object('times', ARRAY[time_first, time_second]) | |
) | |
) AS info_gps | |
FROM ( | |
SELECT device_id | |
, time_first | |
, time_second | |
, st_difference(lineas, st_geomfromtext('POLYGON ((-102.7103698 20.0650677, -102.7107507 20.0638433, -102.7105093 20.0626087, -102.7074301 20.062639, -102.707237 20.0650173, -102.7103698 20.0650677))', 4326)) AS lineas | |
FROM ( | |
SELECT device_id | |
, st_makeline(points, LEAD(points) OVER (ORDER BY device_id, saved_at)) AS lineas | |
, saved_at AS time_first | |
,LEAD(saved_at) OVER (ORDER BY device_id, saved_at) AS time_second | |
FROM ( | |
SELECT device_id | |
,st_setsrid(st_makepoint(lng, lat), 4326) AS points | |
, saved_at | |
FROM ( | |
SELECT ((received_json -> 'position') ->> 'device_id') :: NUMERIC AS device_id | |
,((received_json -> 'position') ->> 'lat') :: NUMERIC AS lat | |
,((received_json -> 'position') ->> 'lng') :: NUMERIC AS lng | |
,(saved_at AT time ZONE 'utc' AT TIME ZONE 'cst') AS saved_at | |
FROM json_dump jd | |
WHERE (saved_at AT time ZONE 'utc' AT TIME ZONE 'cst') :: date = '{fecha}' | |
)a | |
ORDER BY device_id, saved_at DESC | |
)b | |
GROUP BY device_id, points, saved_at | |
)c | |
)d | |
WHERE NOT st_isempty(lineas) | |
GROUP BY device_id | |
""" | |
conn = coneccion_db() | |
df = pd.read_sql_query(q, conn) | |
conn.close() | |
return df | |
def perc_recorrido(fecha:str, fecha2:str): | |
q1 = f""" | |
SELECT json_build_object( | |
'type', 'Feature', | |
'geometry', st_asgeojson(ruta_cubierta)::jsonb, | |
'properties', json_build_object('style', json_build_object('color', 'green')) | |
) AS route_cover, | |
json_build_object( | |
'type', 'Feature', | |
'geometry', st_asgeojson(ruta_no_cubierta)::jsonb, | |
'properties', json_build_object('style', json_build_object('color', 'red')) | |
) AS route_not_cover, | |
st_asgeojson(ruta) :: json AS route_plan, | |
st_asgeojson(driver_position) :: json AS driver_position, | |
st_asgeojson(poligono) :: json AS poligono, | |
ROUND((st_length(ruta_cubierta :: geography) / 1000):: NUMERIC, 2) AS km_cover, | |
ROUND((st_length(ruta_no_cubierta :: geography) / 1000) :: NUMERIC, 2) AS km_not_cover, | |
ROUND((st_length(ruta :: geography) /1000 ):: NUMERIC, 2) AS km_theory, | |
nombre_poligono, | |
device_id, | |
CASE WHEN device_id::int = 14 THEN 'Camion 14' | |
WHEN device_id::int = 15 THEN 'Camion 15' | |
ELSE NULL END AS vehiculo, | |
fecha | |
FROM ( | |
SELECT device_id, | |
nombre_poligono, | |
st_intersection(truck_path, poligono) AS ruta_cubierta, | |
st_difference(streets, st_buffer(st_intersection( truck_path, poligono), 0.00011)) AS ruta_no_cubierta, | |
streets AS ruta, | |
poligono, | |
fecha, | |
st_endpoint(truck_path) AS driver_position | |
FROM ( | |
SELECT st_intersection(st_collect(geometry), poligono) AS streets | |
,poligono | |
,nombre AS nombre_poligono | |
, id_device | |
FROM osmnx_sahuayo_info | |
CROSS JOIN geocerca | |
GROUP BY poligono, nombre, id_device)a | |
LEFT JOIN ( | |
SELECT device_id | |
, fecha | |
, st_makeline(points ORDER BY saved_at) AS truck_path | |
FROM ( | |
SELECT device_id | |
,st_setsrid(st_makepoint(lng, lat), 4326) AS points | |
, saved_at | |
, saved_at :: date AS fecha | |
FROM ( | |
SELECT ((received_json -> 'position') ->> 'device_id') :: NUMERIC AS device_id | |
,((received_json -> 'position') ->> 'lat') :: NUMERIC AS lat | |
,((received_json -> 'position') ->> 'lng') :: NUMERIC AS lng | |
,(saved_at AT time ZONE 'utc' AT TIME ZONE 'cst') AS saved_at | |
FROM json_dump jd | |
WHERE (saved_at AT time ZONE 'utc' AT TIME ZONE 'cst') :: date BETWEEN '{fecha2}' AND '{fecha}' | |
)x | |
)y | |
GROUP BY device_id, fecha | |
)b | |
ON b.device_id = a.id_device | |
)c | |
""" | |
q2 = f""" | |
SELECT st_asgeojson(st_Difference(truck_path , st_union(poligono))) :: json AS truck_path | |
,id_device | |
FROM geocerca g | |
LEFT JOIN ( | |
SELECT device_id | |
, st_makeline(points ORDER BY saved_at) AS truck_path | |
FROM ( | |
SELECT device_id | |
,st_setsrid(st_makepoint(lng, lat), 4326) AS points | |
, saved_at | |
FROM ( | |
SELECT ((received_json -> 'position') ->> 'device_id') :: NUMERIC AS device_id | |
,((received_json -> 'position') ->> 'lat') :: NUMERIC AS lat | |
,((received_json -> 'position') ->> 'lng') :: NUMERIC AS lng | |
,(saved_at AT time ZONE 'utc' AT TIME ZONE 'cst') AS saved_at | |
FROM json_dump jd | |
WHERE (saved_at AT time ZONE 'utc' AT TIME ZONE 'cst') :: date = '{fecha}' | |
)x | |
)y | |
GROUP BY device_id | |
)a | |
ON g.id_device = a.device_id | |
GROUP BY truck_path, id_device | |
""" | |
conn = coneccion_db() | |
df = pd.read_sql_query(q1, conn) | |
df_path = pd.read_sql(q2,conn) | |
conn.close() | |
return df, df_path | |
def mapa(): | |
m = folium.Map(location=[20.068181841083025, -102.70672195476091], zoom_start=15) | |
return m | |
m = mapa() | |
st.markdown("# Recorrido realizado") | |
with st.sidebar: | |
st.markdown("### Filtros de animacion") | |
with st.form(key="form"): | |
fecha = st.date_input(label="Fecha") | |
vehiculo = st.selectbox(label="Seleccione un vehiculo:", options=[13, 14, 15]) | |
#poligonos = st.multiselect("Selecciones las geocercas:", options=["Ruta 3 - San Miguel", "Ruta 3 - San Isidro", "RUTA 10 - Forestal", "Ruta 10", "Ruta 6"], default=None) | |
col1_side, col2_side = st.columns(2) | |
animacion = col1_side.form_submit_button("Animación") | |
perc_completez = col2_side.form_submit_button("Completez") | |
fecha2 = fecha - timedelta(days=7) | |
fecha2_str = fecha2.strftime("%Y-%m-%d") | |
fecha_str = fecha.strftime("%Y-%m-%d") | |
df_gps_data = db_gps_data(fecha_str) | |
df_perc_recorrido, df_camino = perc_recorrido(fecha_str, fecha2_str) | |
df_gps_vehiculo = df_gps_data.query("device_id == @vehiculo")["info_gps"] | |
if perc_completez and not df_perc_recorrido.empty: | |
style_no_cubierto = {"color": "red"} | |
style_cubierto = {"color": "green"} | |
group_name = {14: "Camion 14", 15: "Camion 15"} | |
for id in group_name: | |
feature_group = folium.FeatureGroup(group_name[id]) | |
df_group = df_perc_recorrido.query("device_id == @id and fecha == @fecha").reset_index() | |
df_camino_group = df_camino.query("id_device == @id").reset_index() | |
for i in range(len(df_group)): | |
ruta_no_cubierta = folium.GeoJson(df_group["route_not_cover"].iloc[i], style_function=lambda x:style_no_cubierto) | |
ruta_no_cubierta.add_to(feature_group) | |
ruta_cubierta = folium.GeoJson(df_group["route_cover"].iloc[i], style_function=lambda x:style_cubierto) | |
ruta_cubierta.add_to(feature_group) | |
poligono = folium.GeoJson(df_group["poligono"].iloc[i]) | |
poligono.add_to(feature_group) | |
fuera_poligono = folium.GeoJson(df_camino_group["truck_path"].iloc[0]) | |
fuera_poligono.add_to(feature_group) | |
feature_group.add_to(m) | |
folium.LayerControl(collapsed=False).add_to(m) | |
elif animacion and not df_gps_vehiculo.empty: | |
info_gps = df_gps_vehiculo.iloc[0] | |
ultimo_elemento = info_gps.pop() | |
plugins.TimestampedGeoJson({ | |
"type": "FeatureCollection", | |
"features": info_gps, | |
}, | |
period="PT1M", | |
add_last_point=False, | |
auto_play=True | |
).add_to(m) | |
elif animacion or perc_completez: | |
with st.sidebar: | |
st.error("No se cuenta con registros") | |
if not df_perc_recorrido.empty: | |
col1, col2 = st.columns([0.6, 0.4]) | |
df_graph = df_perc_recorrido.groupby(["vehiculo", "fecha"], as_index=False).agg(km_cubiertos=("km_cover", "sum"), km_no_cubiertos=("km_not_cover", "sum"), km=("km_theory", "sum")) | |
df_graph["Porcentaje de completez"] = df_graph["km_cubiertos"] * 100 / df_graph["km"] | |
df_graph["Porcentaje de completez"] = df_graph["Porcentaje de completez"].round(2) | |
df_graph["Porcentaje de completez"] = np.where(df_graph["Porcentaje de completez"] > 100, 100, df_graph["Porcentaje de completez"]) | |
highlight = alt.selection_point(on='mouseover', fields=["vehiculo"], nearest=True) | |
base = alt.Chart(df_graph).mark_line().encode( | |
x=alt.X("fecha:T", axis=alt.Axis(format="%Y-%m-%d")), | |
y="Porcentaje de completez", | |
color="vehiculo" | |
) | |
puntos = base.mark_circle().encode( | |
opacity=alt.value(0) | |
).add_params( | |
highlight | |
).properties( | |
width=600 | |
) | |
lineas = base.mark_line().encode( | |
size=alt.condition(~highlight, alt.value(1), alt.value(3)) | |
) | |
chart = lineas + puntos | |
with col1: | |
col1.altair_chart(chart, use_container_width=True) | |
#vehiculo = st.selectbox(label="Seleccione un vehiculo:", options=[14, 15]) | |
with col2: | |
df_perc_recorrido["Porcentaje de completez"] = df_perc_recorrido["km_cover"] / df_perc_recorrido["km_theory"] | |
df_perc_recorrido["Porcentaje de completez"] = df_perc_recorrido["Porcentaje de completez"].map('{:.2%}'.format) | |
df_perc_recorrido.rename(columns={"fecha": "Fecha", "vehiculo": "Vehiculo", "nombre_poligono": "Geocerca"}, inplace=True) | |
df_perc_recorrido.sort_values(["Fecha", "Vehiculo", "Geocerca"], inplace=True, ascending=True, axis=0) | |
col2.dataframe(df_perc_recorrido[["Fecha", "Vehiculo", "Geocerca", "Porcentaje de completez"]], hide_index=True) | |
st_data = st_folium(m, width=800, returned_objects=[]) | |