Spaces:
Sleeping
Sleeping
import pandas as pd | |
import psycopg2 | |
import numpy as np | |
import streamlit as st | |
#import hydralit_components as hc | |
from streamlit_folium import st_folium | |
import folium | |
from folium import plugins | |
import json | |
import geopandas as gp | |
from geojson import Feature, Point, FeatureCollection | |
import matplotlib | |
import altair as alt | |
import os | |
import h3 | |
def coneccion_db(): | |
conn = psycopg2.connect(host = "blau-pre-prod.cvcvpaamxkwo.us-east-2.rds.amazonaws.com", | |
port = 5432, | |
dbname = "gps_oruss", | |
user = "data100ce_rw", | |
password = "Hdg2K2LmI16X1GsMFizrwR3PZ" ) | |
return conn | |
def get_points(fecha1:str, fecha2:str, id_camion:int): | |
q3 = f""" | |
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 '{fecha1}' AND '{fecha2}' | |
AND (saved_at AT time ZONE 'utc' AT TIME ZONE 'cst') :: time BETWEEN '05:00:00' AND '22:00:00' | |
AND ((received_json -> 'position') ->> 'device_id') :: NUMERIC = '{id_camion}' | |
ORDER BY ((received_json -> 'position') ->> 'device_id') :: NUMERIC, saved_at | |
""" | |
conn = coneccion_db() | |
df = pd.read_sql_query(q3, conn) | |
conn.close() | |
df["saved_at"] = pd.to_datetime(df["saved_at"]) | |
df["fecha"] = df["saved_at"].dt.date | |
return df | |
def distance_time(): | |
q1 = """ | |
SELECT device_id | |
, fecha | |
, hora_final | |
, hora_inicio | |
, ROUND((st_length(truck_path :: geography) /1000 ):: NUMERIC, 2) AS total_km | |
, semana | |
, round((EXTRACT(epoch FROM (hora_final - hora_inicio)) / 3600) :: NUMERIC, 2) AS total_horas | |
FROM ( | |
SELECT CASE WHEN device_id = 14 THEN 'Camion 14' | |
WHEN device_id = 15 THEN 'Camion 15' | |
WHEN device_id = 13 THEN 'Camion 13' ELSE NULL END AS device_id | |
, fecha | |
, st_makeline(points ORDER BY saved_at) AS truck_path | |
, max(saved_at) AS hora_final | |
, min(saved_at) AS hora_inicio | |
, EXTRACT(week FROM fecha) AS semana | |
FROM ( | |
SELECT device_id | |
,st_difference(st_setsrid(st_makepoint(lng, lat), 4326), 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 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 | |
)x | |
WHERE device_id != 13 | |
)y | |
WHERE NOT st_isempty(points) | |
GROUP BY fecha, device_id | |
)a | |
""" | |
conn = coneccion_db() | |
df = pd.read_sql_query(q1, conn) | |
conn.close() | |
return df | |
def mapa(): | |
m = folium.Map(location=[20.068181841083025, -102.70672195476091], zoom_start=15) | |
return m | |
st.set_page_config(page_title="GPS Blau", layout="wide", page_icon="🛰️") | |
with st.sidebar: | |
st.markdown('### Filtros de paradas') | |
with st.form(key="completez"): | |
inicio_fecha = st.date_input(label="Fecha de inicio") | |
final_fecha = st.date_input(label="Fecha final") | |
id_camion = st.selectbox(label="Seleccione un vehiculo:", options=[13, 14, 15]) | |
hexagono_size = st.slider(label="Tamaño de hexagonos", min_value=0, max_value=15, step=1, value=11) | |
estadistica = st.form_submit_button("Enviar") | |
mapa2 = mapa() | |
df_puntos = get_points(inicio_fecha, final_fecha, id_camion) | |
if not df_puntos.empty: | |
df_puntos.loc[:, ("id_hexagono")] = df_puntos.apply(lambda x: h3.geo_to_h3(x.lat, x.lng, hexagono_size), 1) | |
df_agg = df_puntos.groupby(["id_hexagono", "fecha", "device_id"], as_index=False).agg(num_registro=('id_hexagono', 'count'), max_hora=("saved_at", "max"), min_hora=("saved_at", "min")).reset_index() | |
df_agg["diff_tiempo"] = (df_agg["max_hora"] - df_agg["min_hora"]).astype('timedelta64[s]').dt.total_seconds() / 60 | |
df_agg["diff_tiempo"] = df_agg["diff_tiempo"].round(2) | |
df_agg = df_agg.query("diff_tiempo < 60 & diff_tiempo > 5") | |
#df_agg = df_agg[df_agg["num_registros"] > 2] | |
valor_min = df_agg["diff_tiempo"].min() | |
valor_max = df_agg["diff_tiempo"].max() | |
valor_prom = df_agg["diff_tiempo"].mean() | |
list_features = [] | |
df_hex = df_agg.groupby("id_hexagono", as_index=False).agg(diff_tiempo=("diff_tiempo", "mean")) | |
for i, row in df_hex.iterrows(): | |
geometry_row = {"type": "Polygon", "coordinates": [h3.h3_to_geo_boundary(h=row["id_hexagono"], geo_json=True)]} | |
feature = Feature(geometry=geometry_row, id=row["id_hexagono"], properties={"diff_tiempo": int(row["diff_tiempo"])}) | |
list_features.append(feature) | |
feat_collection = FeatureCollection(list_features) | |
geojson_result = json.dumps(feat_collection) | |
colores_hexagonos = matplotlib.cm.get_cmap("Reds") | |
hexagonos = folium.GeoJson(geojson_result, | |
style_function=lambda feature:{ | |
"fillColor": matplotlib.colors.to_hex(colores_hexagonos((feature["properties"]["diff_tiempo"] - valor_min) / (valor_max - valor_min))), | |
"color": "black", | |
"weight": 1, | |
"fillOpacity": 0.7 | |
}, | |
name="hexagonos", | |
highlight_function=lambda x:{"weight": 3}) | |
hexagonos.add_to(mapa2) | |
hexagonos.add_child( | |
folium.features.GeoJsonTooltip(['diff_tiempo'], labels=False) | |
) | |
st.markdown("# Estudio de tiempos muertos") | |
### GRAFICA | |
df_km_tiempo = distance_time() | |
df_km_semana = df_km_tiempo.groupby(["device_id", "semana"], as_index=False).agg(total_km=("total_km", "sum"), total_hrs=("total_horas", "sum")) | |
df_km_semana.rename(columns={"device_id": "Vehiculo", "semana": "Semana", "total_km": "Total km", "total_hrs": "Total hr"}, inplace=True) | |
def create_chart_altair(df:pd.DataFrame, column:str): | |
highlight = alt.selection_point(on='mouseover', fields=["Vehiculo"], nearest=True) | |
base = alt.Chart(df).mark_line().encode( | |
x=alt.X("Semana", scale=alt.Scale(domain=[32, 35])), | |
y=column, | |
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 | |
return chart | |
km_chart = create_chart_altair(df_km_semana, "Total km") | |
km_chart = km_chart.properties(title="Km recorridos por semana") | |
hr_chart = create_chart_altair(df_km_semana, "Total hr") | |
hr_chart = hr_chart.properties(title="Horas por semana") | |
col1 , col2 = st.columns(2) | |
with col1: | |
col1.altair_chart(km_chart) | |
with col2: | |
col2.altair_chart(hr_chart) | |
paradas = st_folium(mapa2, width=900) | |
if not df_puntos.empty: | |
df_paradas = df_agg.copy() | |
if paradas["last_active_drawing"]: | |
id = paradas["last_active_drawing"]["id"] | |
df_paradas = df_paradas.query("id_hexagono == @id") | |
df_paradas = df_paradas[["device_id", "fecha", "min_hora", "max_hora", "diff_tiempo"]] | |
df_paradas.rename(columns={"device_id": "Vehiculo", "fecha": "Fecha", "max_hora": "Hora de entrada", "min_hora": "Hora de salida", "diff_tiempo": "duracion"}, inplace=True) | |
#st.dataframe(df_paradas, hide_index=True) | |
col11, col22 = st.columns(2) | |
with col11: | |
if not df_puntos.empty: | |
df = df_agg.copy() | |
df = df[["device_id", "fecha", "min_hora", "max_hora", "diff_tiempo"]] | |
df.rename(columns={"device_id": "Vehiculo", "fecha": "Fecha", "max_hora": "Hora de entrada", "min_hora": "Hora de salida", "diff_tiempo": "duracion"}, inplace=True) | |
col11.write("Duración de paradas") | |
col11.dataframe(df, hide_index=True) | |
with col22: | |
if not df_puntos.empty: | |
col22.write("Parada seleccionada") | |
col22.dataframe(df_paradas, hide_index=True) | |