1_🚚_Completez.py

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

@st.cache_data
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

@st.cache_data
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=[])