Analizando los datos abiertos de Cercanias

Una vez parseado los datos abiertos de Cercanias (renfe), apuntes en Crear un parser de datos de Cercanias (Renfe) en python, se deben analizar los datos para everiguar que datos contiene, que releación tienen entre ellos y poder determinar su proposito.

Rutas o bien Lineas disponibles

En la tabla routes es donde podemos encontrar todas las lineas disponibles, haciendo un select basico para obtener todas las lineas.

SELECT routes.route_id, routes.route_short_name FROM routes

Si queremos obtener las que corresponden en C1

SELECT routes.route_id, routes.route_short_name FROM routes WHERE routes.route_short_name = "C1"

Se puede observar que se deberán filtrar por región, ya que lo ideal es obtener las C1 de una región especifica y no todas, en falta de que haya un campo identificador de region, se puede determinar como empieza el identificador route_id.

Para simplificar se crea un campo region_id que apuntará hacia una tabla regions

ALTER TABLE `routes`
	ADD COLUMN `region_id` VARCHAR(10) NOT NULL DEFAULT '0' AFTER `route_text_color`,
	ADD INDEX `region_id` (`region_id`);

SQL de creación de la tabla regions

CREATE TABLE `regions` (
	`region_id` VARCHAR(10) NOT NULL COLLATE 'utf8mb4_general_ci',
	`shortName` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`longName` VARCHAR(40) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	PRIMARY KEY (`region_id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;

Rellenar con los indentificadores de las rutas

INSERT INTO regions (regions.region_id) SELECT DISTINCT SUBSTRING(routes.route_id, 1, 2) AS region_id FROM routes;

A mano se deberá rellenar los datos faltantes, nombre corto y nombre largo, eso lo podemos extraer de la página oficial de cercanias.

10, Madrid, Madrid
20, Asturias, Asturias
30, Sevilla, Sevilla
31, Cádiz, Cadiz
32, Málaga, Málaga
40, Valencia, Valencia
41, Murcia/Alicante, Murcia y Alicante
50, Catalunya, Rodalies Catalunya
60, Bilbao, Bilbao
61, San Sebastián, San Sebastián
62, Santader, Santander
70, Zaragoza, Zaragoza

Autorellenar el valor de region_id en la tabala routes

UPDATE routes SET routes.region_id = SUBSTRING(routes.route_id, 1, 2);

Ahora para consultar las lineas disponibles de una region, por ejemplo las de Asturias que corresponden con las 20T

SELECT DISTINCT routes.route_id, routes.route_short_name FROM routes WHERE  routes.region_id = "20T";

Nos puede devolver registros huerfanos, cuando comprobamos el route_id dentro la tabla trips

Limpiar datos

Algunos registros de la tabla routes están duplicados pero sin asignación de trenes en la ruta trips, para detectar cuales están en activo.

SELECT DISTINCT routes.route_id, routes.route_short_name FROM routes
	INNER JOIN trips
		ON trips.route_id = routes.route_id
WHERE EXISTS (SELECT routes.route_id FROM routes);

Crear una columna active de tipo Bit en la tabla routes, ejecutar el siguiente SQL script, el que estén en 0, serán las rutas que no tienen ninguna asignación, con lo cual se puede descartar en las consulas futuras.

UPDATE routes SET routes.active = 1 WHERE routes.route_id IN (
SELECT DISTINCT routes.route_id FROM routes
	INNER JOIN trips
		ON trips.route_id = routes.route_id
WHERE EXISTS (SELECT routes.route_id FROM routes));

Ahora para obtener las lineas de una region debemos especificar solo las activas

SELECT routes.route_id, routes.route_short_name FROM routes WHERE routes.region_id = "20" AND routes.active = 1;

Calendario

La tabla calendario, cada entrada representa un dia y por lo que se puede apreciar, dependiendo del dia que se ha descargado los datos, solo se incluyen los datos a partir de ese dia hacia adelante.

Para obtener la fecha minima de los registros

SELECT MIN(calendar.start_date) AS start_date FROM calendar;

Para obtener la fecha máxima de los registros

SELECT MAX(calendar.start_date) AS end_date FROM calendar;

Obtener los servicios disponibles en la fecha actual

SELECT *  FROM calendar WHERE start_date = CURDATE();

Obtener los servicios en una fecha especifica, permite especificar rango de fecha inicial y fecha final

SELECT calendar.service_id FROM calendar WHERE start_date BETWEEN '2022-10-31' AND '2022-10-31';

El campo service_id es el indentificador que conjuntamente con route_id sobe la tabla trips, se puede obtener los trenes asociados a una ruta en una fecha determinada.

Paradas

En la tabla paradas, no hay complicación, están los datos de las estaciones, con su posición GPS etc.. se puede acabar de complementar datos como dirección, código postal, con el stop_id se puede recuperar una estación individual.

Por ejemplo obtener los datos de la estación Madrid Aeropuerto T4

SELECT * FROM stops WHERE stop_id = "98305";

Rutas

Obtener todas las paradas de una linea/ruta, ejemplo de C1 de Madrid

SELECT DISTINCT stop_times.stop_id , stops.stop_name
FROM stop_times
LEFT JOIN stops 
	ON stop_times.stop_id = stops.stop_id
WHERE
#	stop_times.trip_id = "1029X27201C2" 
stop_times.trip_id IN ( SELECT trips.trip_id  FROM trips WHERE trips.route_id = "10T0001C1" )
ORDER BY stop_times.stop_sequence;

Obtener el total de paradas de una ruta

SELECT COUNT(DISTINCT stops.stop_id ) AS stops_count
FROM stop_times
LEFT JOIN stops 
	ON stop_times.stop_id = stops.stop_id
WHERE stop_times.trip_id IN 
	( SELECT trips.trip_id  FROM trips 
WHERE trips.route_id = "10T0001C1" );

Obtener el viaje (trip) con más paradas, para obtener luego las paradas, en este caso la linea C2 con 19 paradas

SELECT stop_times.trip_id, COUNT(stop_times.trip_id) AS stops_count
FROM stop_times
LEFT JOIN stops 
	ON stop_times.stop_id = stops.stop_id
WHERE
	stop_times.trip_id IN ( SELECT trips.trip_id FROM trips WHERE trips.route_id = "10T0005C2" )

GROUP BY stop_times.trip_id
ORDER BY stops_count DESC LIMIT 1;

//devuelve 1032S27203C2

Ahora obtener las paradas de ese viaje con las paradas ordenadas por sequencia de parada

SELECT stop_times.trip_id, stop_times.stop_id , stops.stop_name, stop_times.departure_time, stop_times.stop_sequence
FROM stop_times
LEFT JOIN stops 
	ON stop_times.stop_id = stops.stop_id
WHERE
	stop_times.trip_id = "1032S27203C2"
ORDER BY stop_times.stop_sequence;

Horarios de los trenes

En la tabla stop_times es donde se encuentra los horarios de todos los trenes, es el eje central de los datos, por jemplo si queremos obtener los horarios de los trenes de la linea C1 de Príncipe Pío a Aeropuerto T4 , región madrid

Primero obtener el route_id con el filtro de region y nombre de linea

SELECT routes.route_id, routes.route_short_name , routes.route_long_name FROM routes WHERE routes.region_id = "10" AND routes.route_short_name = "C1" AND routes.active = 1;

devolverá dos entradas, una corresponde dirección al aeropuerto y la otra ruta a la inversa del aeropuerto hacia Príncipe Pío, nos interesa la primera route_id=10T0001C1

Obtener los servicios de trenes disponibles de la fecha actual, para comprobar si hay trenes disponibles para la linea C1 hacia el aeropuerto

SELECT calendar.service_id FROM calendar WHERE start_date = CURDATE();

Para obtener los viajes hacia el aeropuerto, se combina

SELECT stop_times.trip_id, stop_times.stop_id , stops.stop_name, stop_times.departure_time
FROM stop_times
LEFT JOIN stops 
	ON stop_times.stop_id = stops.stop_id
WHERE
stop_times.trip_id IN ( SELECT trips.trip_id  FROM trips WHERE trips.route_id = "10T0001C1" AND trips.service_id IN (SELECT calendar.service_id FROM calendar WHERE start_date BETWEEN '2022-11-02' AND '2022-11-02') )
ORDER BY trip_id, stop_times.stop_sequence;

Para obtener el viaje de un solo tren hacia un destino, en este caso con la consulta anterior obtenemos el trip_id que es 1030J27797C1

SELECT * FROM stop_times LEFT JOIN stops ON stop_times.stop_id = stops.stop_id WHERE stop_times.trip_id = "1030J27797C1";

Nos devolverá todas las estaciones del primer tren que sale de Madrid Atocha hacia el Aeropuerto, el primer registo es en las 05:15 y la llegada en Aeropuerto a las 05:44 en el último registro.

En construción….

Anuncio publicitario