Activité : Base de données SAE

Base de données SAE

Prérequis : Base de données

Présentation

Le Système d’Aide à l’Exploitation (SAE) assiste les opérateurs du PCC (exploitants) dans ses principales missions d’exploitation du réseau de bus. Les fonctionnalités principales du SAE sont l’élaboration du référentiel et le transfert des données du référentiel.

Le référentiel

Le référentiel est un ensemble de données descriptives de l’exploitation du réseau (référentiel SAE). Ces données concernent :

  • la description de la topologie du réseau : lignes, arrêts, chaînages des tronçons, etc.
  • la description des horaires : calendriers, services matériels etc.
  • la description des ressources : conducteurs, véhicules etc.

Ces données sont mémorisées dans la base de données MySQL du PCC, et mises à disposition des différents sous-systèmes en fonction de leurs besoins.

Remarque : Le référentiel respecte le format standardisé GTFS (General Transit Feed Specification). Ce format permet de communiquer des horaires de transports en commun et les informations géographiques associées (topographie d’un réseau : emplacement des arrêts, tracé des lignes). GTFS a été développé par Google et TriMet. Lien : https://developers.google.com/transit/gtfs/

L’offre de service sur une ligne est composée de courses reliant un terminus de départ à un terminus d’arrivée et desservant des arrêts selon un horaire. Une course est une mission effectuée par un véhicule : entre deux terminus extrêmes (course pleine), entre un terminus extrême et un arrêt (course partielle), sans desserte des arrêts entre l’arrêt de départ et l’arrêt d’arrivée (course dite HLP : Haut Le Pied) et enfin l’emprunt des itinéraires spéciaux (course spéciale). Donc, sur une ligne donnée, on trouve plusieurs types de courses possibles selon les arrêts desservis, l’itinéraire emprunté ou les consignes d’exploitation.

Les données GTFS exportées par les services de transport sont codées dans les principaux fichiers suivants :

  • agency.txt (table organisme) regroupe les informations sur le service de transport (compagnies de transport, nom du réseau)
  • calendar.txt (table calendrier) et calendar_dates.txt contiennent le calendrier de circulation
  • routes.txt (table ligne) présente le nom et la direction des lignes (au sens d’une origine-destination)
  • stops.txt (table lieu) liste de tous les points d’arrêt (lieu) et proposent d’éventuelles informations supplémentaires
  • trips.txt (table itineraire) fournit les itinéraires, sous la forme d’une table de liaison entre les services (agency), les routes et les régimes de circulation (calendar.txt et calendar_dates.txt)
  • stops_times.txt (table arret) présente les horaires des courses aux points d’arrêt (lieu)
  • traces.txt (table trace) contient les coordonnées pour assurer un tracé sur une carte (ces données peuvent servir aussi pour un simulateur de déplacement de bus)

Les tables ci-dessous ont été ajoutées aux données GTFS :

  • course qui correspond à la réalisation d’un itinéraire par un conducteur à bord d’un véhicule pour un service donné
  • service qui associe un conducteur à un véhicule pour réaliser une ou plusieurs courses lors d’une journée
  • vehicule qui décrit la liste des véhicules de la flotte
  • conducteur qui liste l’ensemble des conducteurs en leur associant un code

Le SAE assure tous les soirs la fonction de transfert des données du référentiel vers le SAI et les SIV-BERRY de chaque bus en n’y intégrant que les données descriptives de la topologie et des horaires de la journée, préparées et transférées par Wifi (automatiquement ou manuellement). Il est ajouté une notion de “service” associant un conducteur à un véhicule et l’ensemble des courses à effectuer pendant ce service. Le format des base de données SAI et les SIV-BERRY est SQLite.

Objectifs

Être capable d’exécuter des requêtes SQL sur une base de données MySQL.

Séquence 0 : installation de MySQL

Vérifier si les paquetages mysql sont installés sur votre machine :

$ dpkg -l | grep -i mysql

Sinon pour installer le SGBDR MySQL, il faut faire :

$ sudo apt install mysql-server

Vérifier si le serveur mysql est démarré sur votre machine :

$ sudo service mysql status

Pour démarrer le serveur MySQL :

$ sudo service mysql start

Pour redémarrer le serveur MySQL :

$ sudo service mysql restart

Pour arrêter le serveur MySQL :

$ sudo service mysql stop

Démarrer la console mysql :

$ sudo mysql -uroot -ppassword -hlocalhost
mysql>

Lien : https://doc.ubuntu-fr.org/mysql

Questions :

  1. Quel est le nom du fichier de configuration du serveur MySQL ? Où se situe-t-il ?
  2. Quel est le port d’écoute par défaut du serveur MySQL ?
  3. Quelle option interdit l’accès à distance au serveur MySQL ?
  4. Quelle option permet l’accès à distance au serveur MySQL ? Donner un exemple pour un intranet.
  5. Dans quelle base de données et quelle table sont stockées les comptes utilisateur de MySQL ?

Séquence 1 : installation de la base de données sae

Pour installer la base de données sae (le référentiel, ici celui de Tisséo Toulouse), il faut :

  • créer la base de données sae (CREATE DATABASE sae)
  • créer les tables (CREATE TABLE organisme ... etc …)
  • insérer des données dans les tables (INSERT INTO organisme ... etc …)

Pour automatiser ces tâches, on utilisera le script sae.sh fourni.

$ cp $HOME/sae.sh /tmp
$ cp $HOME/sae.zip /tmp

$ cd /tmp

$ chmod +x sae.sh

$ sudo ./sae.sh

Le script sae.sh :

#!/bin/bash
HOSTNAME=localhost
USERNAME=root
PASSWORD=password
DBNAME=sae
PATH=/tmp
ARCHIVE=sae.zip
SQL=sae.sql
TABLES="ligne calendrier itineraire arret lieu trace"

echo "Installation base de donnees $DBNAME"
if [ -f $PATH/$ARCHIVE ]
then
    echo "Extraction tables"
    /usr/bin/unzip -u $PATH/$ARCHIVE -d "${PATH}" > /dev/null 2>&1
else
    echo "Fichier ${ARCHIVE} manquant !"
fi

if [ -f $PATH/$SQL ]
then
    echo "Creation tables"
    /usr/bin/mysql -u$USERNAME -p$PASSWORD -h$HOSTNAME < $PATH/$SQL
    /bin/rm -f $PATH/$SQL
else
    echo "Fichier ${SQL} manquant !"    
fi

for table in $TABLES
do
    if [ -f $PATH/$table.txt ]
    then
        echo "Insertion table $table"
        /bin/chown mysql $PATH/$table.txt
        /usr/bin/mysql -u$USERNAME -p$PASSWORD -h$HOSTNAME $DBNAME -e"LOAD DATA INFILE '$PATH/$table.txt' REPLACE INTO TABLE $table FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';"
        /bin/rm -f $PATH/$table.txt
    else
        echo "Fichier $table.txt manquant !"  
    fi
done

Script : sae.sh

Base de données : sae.zip

Questions :

  1. Donner les commandes MySQL qui vérifient que les tables de la base de données sae ont bien été créées ?

Séquence 2 : exécution de requêtes

Démarrer la console mysql :

$ sudo mysql -uroot -ppassword -hlocalhost sae
mysql>

Liste l’ensemble des lignes :

select idLigne, nomLong, description from ligne;
+-------------------+---------+----------------------------------------------+
| idLigne           | nomLong | description                                  |
+-------------------+---------+----------------------------------------------+
| 11821949021891615 | 1       | Ligne Grand Rond / Compans - Caffarelli      |
| ...               | ...     | ...                                          |
| 11821953316814855 | 87      | Ligne Lycée Polyvalent / Cugnaux Henry Galdi |
+-------------------+---------+----------------------------------------------+

Nombre d’itinéraires pour la ligne “Grand Rond / Compans - Caffarelli” (11821949021891615) :

SELECT COUNT( DISTINCT idItineraire ) AS nbItineraires FROM itineraire WHERE idLigne='11821949021891615';
+---------------+
| nbItineraires |
+---------------+
|            82 |
+---------------+

Liste des horaires de départ des itinéraires pour la ligne “Grand Rond / Compans - Caffarelli” (11821949021891615) triés par heure de départ :

SELECT itineraire.idItineraire, itineraire.destination, itineraire.direction, heureDepart FROM itineraire INNER JOIN ligne ON ligne.idLigne=itineraire.idLigne INNER JOIN arret ON arret.idItineraire=itineraire.idItineraire WHERE itineraire.idLigne='11821949021891615' AND arret.numeroSequence='0' ORDER BY heureDepart ASC;
+------------------+-------------------------------+-----------+-------------+
| idItineraire     | destination                   | direction | heureDepart |
+------------------+-------------------------------+-----------+-------------+
| 4503603928073507 | Grand Rond TOULOUSE           | 0         | 06:00:00    |
| 4503603928073508 | Grand Rond TOULOUSE           | 0         | 06:28:00    |
| 4503603928073618 | Compans - Caffarelli TOULOUSE | 1         | 06:30:00    |
| 4503603928073619 | Compans - Caffarelli TOULOUSE | 1         | 06:50:00    |
| 4503603928073509 | Grand Rond TOULOUSE           | 0         | 06:55:00    |
| 4503603928073620 | Compans - Caffarelli TOULOUSE | 1         | 07:10:00    |
| ...              | ...                           | ...       | ...         |
| 4503603928073545 | Grand Rond TOULOUSE           | 0         | 20:25:00    |
| 4503603928073658 | Compans - Caffarelli TOULOUSE | 1         | 20:30:00    |
+------------------+-------------------------------+-----------+-------------+

Horaires et arrêts d’un itinéraire (4503603928073507) :

SELECT arret.numeroSequence, nomLieu, arret.heureDepart FROM itineraire INNER JOIN arret ON arret.idItineraire=itineraire.idItineraire INNER JOIN lieu ON lieu.idArret=arret.idArret WHERE itineraire.idItineraire='4503603928073507' ORDER BY heureDepart ASC;
+----------------+--------------------------+-------------+
| numeroSequence | nomLieu                  | heureDepart |
+----------------+--------------------------+-------------+
|              0 | Compans-Caffarelli       | 06:00:00    |
|              1 | Sciences Sociales        | 06:02:00    |
|              2 | Arsenal                  | 06:02:00    |
|              3 | Barcelone Leclerc        | 06:03:00    |
|              4 | Leclerc                  | 06:04:00    |
|              5 | Héraclès                 | 06:05:00    |
|              6 | Amidonniers              | 06:05:00    |
|              8 | St Cyprien - République  | 06:07:00    |
|              7 | Les Abattoirs            | 06:07:00    |
|              9 | Teinturiers              | 06:08:00    |
|             10 | Fer à Cheval             | 06:09:00    |
|             11 | Ile du Ramier            | 06:11:00    |
|             12 | Palais de Justice        | 06:11:00    |
|             13 | Jardin Royal             | 06:13:00    |
|             14 | Grand Rond               | 06:15:00    |
+----------------+--------------------------+-------------+

Donner les requêtes et résulats pour les cas suivants :

  1. Horaires et arrêts d’un itinéraire (4503603928073507) pour le samedi
  2. Horaires et arrêts de départ des itinéraires de la ligne “Grand Rond / Compans - Caffarelli” (11821949021891615) pour le lundi (triés par heure de départ)
  3. Horaires de départ des itinéraires de la ligne “Grand Rond / Compans - Caffarelli” (11821949021891615) pour la destination “Grand Rond TOULOUSE” (direction 0) triés par heure de départ

Retour au sommaire

© 2016 Thierry Vaira