Base Arrêts TC : architecture et fonctionnement DtecTV - Direction Technique Territoires et Ville (CEREMA) Base arrêt TC Architecture et fonctionnement date : Mai 2014 auteur : CEREMA responsable de l'étude : Laurent Chevereau DTecTV participants : Silvio Rousic, Patrick Gendre, Cerema DTerMed sous-traitants : Cyril Bloudeau, Mobigis résumé : L’objectif de ce document est d’illustrer le principe, le fonctionnement et l’architecture de la base Arrêt TC et son état d'avancement début 2014, correspondant à la publication des données 2013. Le document conclut par les perspectives d'amélioration en 2014. zone géographique : France Métropolitaine diffusion publique : oui nombre de pages : n n° d'affaire : 0099999 maître d'ouvrage : DTecTV référence : devis n° xx/xxx du jj/mm/aa Base Arrêts TC : architecture et fonctionnement – Mai 2014 2/17 SOMMAIRE 1 INTRODUCTION............................................................................................................................4 1.1 Objet du document.......................................................................................................................4 1.2 Contexte.......................................................................................................................................4 2 ARCHITECTURE............................................................................................................................4 3 ÉTAT DES LIEUX DES SCRIPTS ACTUELS................................................................................5 4 DESCRIPTION DES TRAITEMENTS EFFECTUÉS......................................................................6 4.1 Tables et couches créées............................................................................................................6 4.2 Indicateurs calculés pour chaque élément..................................................................................6 5 PROCESSUS DE TRAITEMENT DES DONNÉES........................................................................7 5.1 Pré-requis.....................................................................................................................................8 5.2 Procédure.....................................................................................................................................8 6 DIFFUSION DES DONNÉES PRODUITES.................................................................................10 7 RETOUR QUALITÉ......................................................................................................................10 8 CONCLUSION..............................................................................................................................10 9 ANNEXE........................................................................................................................................11 9.1 Références.................................................................................................................................11 9.2 librairies et logiciels....................................................................................................................11 9.3 Utilisation des scripts Python Automate*.py sous Windows......................................................11 9.3.1 Prérequis :..............................................................................................................................................................................................................11 9.3.2 Pocédure :..............................................................................................................................................................................................................11 9.3.2.1 Cas des données formatées (GTFS ou Neptune)..................................................................................................................................11 9.3.2.2 Cas des données OSM..........................................................................................................................................................................12 9.3.2.3 Cas des données SIG............................................................................................................................................................................13 9.3.3 Résultats.................................................................................................................................................................................................................15 9.4 Liste des réseaux publiés pour 2013.........................................................................................15 1 Introduction 1.1 Objet du document L’objectif de ce document est d’illustrer le principe, le fonctionnement et l’architecture de la base Arrêt TC et son état d'avancement début 2014, correspondant à la publication des données 2013. Le document conclut par les perspectives d'amélioration en 2014. 1.2 Contexte Le CEREMA, service technique du ministère du développement durable, souhaite constituer progressivement une Base de Données (BD) nationale sur les Transports Collectifs (TC), en vue de répondre aux besoins d’études des services du ministère (administration centrale, directions régionales et départementales, centres d'études techniques). Les données sont en principe détenues par les autorités organisatrices de transport (AOT) auprès desquelles elles seront récupérées. Ces données sont gérées dans des Bases de Données régionales ou départementales dans le cadre de Systèmes d'information multimodale impliquant de nombreux partenaires. Le projet a vocation à se dérouler sur plusieurs mois au fur et à mesure de leur réception. Lors d'un stage effectué en 2013 au CETE Méditerranée, une 1ère version de base de données a été produite sur des données disponibles (données open data de la ville de Rennes). Deux couches SIG ont ainsi été produites (arrêts et tronçons de lignes), complétées par des indicateurs (issus de requêtes SQL). La DTecTV1 (L. Chevereau - DD/PST) a chargé le PCI Géomatique de la DTerMed 2 de procéder à l'élaboration de la Base nationale des arrêts de TC (base ATC), en proposant une codification des arrêts cohérente avec le modèle d'arrêts partagés de l'AFIMB. 2 Architecture La base de données proprement dite s'insère dans une chaîne plus large partant des données disponibles à la source (données fournies par les AOT formatées - Neptune / GTFS - ou issues de leur SIG à divers formats, données open data TC ou OSM), et allant jusqu'à la publication de couches SIG d'arrêts au format SHP par le Cerema DtecTV (ex. Certu) sur la plate-forme Cartélie. 1 2 Direction Technique Territoires et Villes (ex-Certu) du CEREMA Direction Territoriale Méditerrannée ( ex-Cete Méditerranée) du CEREMA Base Arrêts TC : architecture et fonctionnement – Mai 2014 4/17 SIM Export AOT Export Export Cerema Cerema Formats divers Territoires et ville Territoires et ville Open data GTFS, Neptune Diffusion .shp Cerema Méditerranée SOeS statistiques Dreal NPdC Dreal Cerema Études locales La base de données choisie pour la solution est PostGreSQL (9.2) / PostGIS (2.0). Compte tenu de l'existant, la base de données s'appuie sur le schéma de données de l'application Chouette : les données d'offre TC disponibles au format GTFS sont importées dans Chouette, tout comme les données au format Neptune, avant d'être traitées. Les données de points d'arrêt venant d'autres sources SIG ou OSM sont importées dans des schémas séparés et adaptées au schéma du modèle partagé de l'AFIMB. La base arrêts (base chouette2) est donc connectée directement à l’outil Chouette (version 2.1) permettant l’import des données de format Neptune et GTFS. Cette base contient actuellement 4 schémas : • f : fonctions utilisées par les calculs et les traitements • adm : données administratives permettant de savoir quels sont les départements concernés par les données • pop : données de population par département • status : métadonnées décrivant la base arrêts : • table « historique » : table de logs mise à jour après chaque exécution d’une fonction de traitement ou d’export • table « metadonnees » : table des metadonnées de réseau TC A chaque nouvel import de donnée, un nouveau schéma portant le nom du réseau s’ajoute dans la base chouette2. 3 État des lieux des scripts actuels Actuellement, 4 scripts sont utilisés pour le traitement des données : • 1-schema_f.sql : création des fonctions en base dans un nouveau schéma f • 2-create_status.sql : création, dans le schéma status, des tables « historique » (contenant l'historique des scripts lancés sur les données) et « metadonnees » (contenant les métadonnées : dates de validités, format, divers indicateurs...) Base Arrêts TC : architecture et fonctionnement – Mai 2014 5/17 • 3-import_status.sql : mise à jour de la table metadonnees après chaque import (requête actuellement mise à jour et exécutée manuellement) • 4-requetes_chouette.sql : commandes SQL qui appellent les différentes fonctions et qui permettent de faire manuellement le traitement sur les données • Automate.py : fichier python permettant de réaliser automatiquement les traitements et les exports (appelé par 4-Automate.sh ou par 4-Automate.bat). Des scripts similaires ont été produits pour traiter les données Osm (Automate_osm.py) ou les fichiers SIG (Automate_XY.py). 4 Description des traitements effectués 4.1 Tables et couches créées A l’issue de l’exécution des scripts et des fonctions, 3 nouvelles tables sont créées pour le réseau concerné : • • • baseatc_indic_line_stoparea : indicateurs TC des stopareas baseatc_indic_service_links : indicateurs des service-links baseatc_indic_stopareas : indicateurs Population des stopareas Également, 6 couches géographiques sont créées dans la base, puis exportées au format .SHP et au format .TAB : • • • baseatc_quays : couche des stopareas de type quay baseatc_quays_pathlinks : couche des tronçons inter-arrêts de quays baseatc_quays_service_links : couche des service-links de quays • • • baseatc_stop_places : couche des stopplaces baseatc_stop_places_pathlinks : couche des tronçons inter-arrêts de stopplaces baseatc_stopplaces_service_links : couche des service-links de stopplace Enfin, un fichier de métadonnées concernant le réseau en question est exporté au format .CSV. Ce fichier décrit notamment le réseau (nom du réseau, nom de l'AOT, format de données, mode de transport, type de lieu d'arrêt, etc.) , et fournit plusieurs comptages d'éléments (nombre de zones d'embarquement (ze), de lieux d'arrêt monomodaux (lam), nombre de lignes) et indicateurs de qualité (nombre de coordonnées nulles, ratio nb_ze/nb_lam, etc.). A l'issue des traitements, 12 couches et un fichier CSV sont donc exportés dans un répertoire dont le nom est spécifié par l'utilisateur. 4.2 Indicateurs calculés pour chaque élément Les indicateurs pour chacune de ces 6 couches sont décris dans les tableaux suivants : Base Arrêts TC : architecture et fonctionnement – Mai 2014 6/17 Baseatc_quays Nbhab_300m Nombre d’habitants situés dans un rayon de 300 m à vol d’oiseau du quay Min_time Heure minimale du premier passage au quay Max_time Heure maximale du dernier passage au quay Amplitude Amplitude maximale entre le premier passage et le dernier passage au quay sur une journée Nbpas_intr Nombre de passages maximum en moyenne journée ( 09h30 – 16h29 ) Nbpas_pnte Nombre de passages maximum en heure de pointe ( 07h30 - 9h29 / 16h30 -19h59 ) Nbpas_crse Nombre de passages maximum en heure creuse ( 00h00 - 7h29 / 20h00 - 23h59 ) Nbpas_tot Nombre total de passages sur la journée Baseatc_quays_pathlinks Vitesse_moy_directe Rapport Duree / Distance_directe en km/h Baseatc_quays_service_links Distance_directe Longueur vol d'oiseau du service-link de quays Duree Temps de parcours du service-link de quays Vitesse_moy_directe Rapport Duree / Distance_directe en km/h Baseatc_stop_places Nbhab_300m Nombre d’habitants situés dans un rayon de 300 m à vol d’oiseau du stopplace Baseatc_stopplaces_pathlinks Vitesse_moy_directe Rapport Duree / Distance_directe en km/h Baseatc_stopplaces_service_links Distance_directe Longueur directe du service-link de stoplaces Duree Temps de parcours du service-link de stoplaces Vitesse_moy_directe Rapport Duree / Distance_directe en km/h 5 Processus de traitement des données Le processus de constitution de la base de données ATC intègre les disparités dues aux Base Arrêts TC : architecture et fonctionnement – Mai 2014 7/17 différents formats des données en entrée : • données formatées (Neptune/GTFS), fournies par les AOT ou diffusées en open data → contiennent généralement des informations horaires • des données SIG (dans des formats divers tels que CSV, SHP, KML...), fournies par les AOT ou diffusées en open data → données limitées aux seuls points d'arrêt • données OSM : le Cerema a souhaité inclure les données d'arrêts issus d'OpenStreetMap, afin d'élargir le territoire couvert par la base et de permettre aux gestionnaires de données, et le cas échéant, un processus d'amélioration mutuelle entre les données OSM et celles des AOT. 5.1 Pré-requis Pour permettre l'exécution des traitements dans la base de données, les deux scripts suivants doivent tout d'abord être exécutés : 1. schema_f.sql : création dans la base de données d'un schéma « f » contenant l'ensemble des fonctions utiles 2. create_status.sql : création dans la base de données d'un schéma « status » contenant 2 tables : • historique : ensemble des actions effectuées sur un jeu de données, de l'import, en passant par les différents scripts, jusqu'à l'export shp et tab. • metadonnees : ensemble des métadonnées d'un jeu de données importé dans la base ATC id nom_reseau nom_aot mode type_desserte type_lieu_arret date_creation date_derniere_miseajour num_version_indicateurs date_debut_validite date_fin_validite producteur projection source_indicateurs date_calcul_indicateurs nb_lignes_tc nb_ze nb_lam ratio_ze_lam nb_ze_xy_0 nb_lam_xy_0 nb_lam_coord_ze Identifiant incrémenté Nom du réseau Nm de l'AOT Bus, Metro, Tram... Service régulier, scolaire, TAD... ZE, LAM ou ZE_et_LAM Date de l'import Date de MàJ du lot de données N° de version des indicateurs Date de début de validité du lot de données Date de fin de validité du lot de données Producteur des données 2154 Cerema Date de calcul des indicateurs Nb total de lignes TC (issus de calculs) Nb de Zones d'Embarquement (ZE) Nb de Lieux d'Arrêt Monomodaux (LAM) Rapport ZE/LAM Nb de ZE ayant des coordonnées vides Nb de LAM ayant des coordonnées vides Nb de doublons géométriques LAM/ZE Base Arrêts TC : architecture et fonctionnement – Mai 2014 8/17 5.2 Procédure Pour chaque nouvel import de données dans la base Arrêts TC, la procédure est la suivante : 1. Importer de nouvelles données dans la base : • pour les données GTFS et Neptune → via chouette • pour les données shapes et osm → export au format shape via QGIS (fichier nommé arrets.shp) puis commande ogr2ogr dans FWTools exemple de commande ogr2ogr pour le réseau de St-quentin : ogr2ogr -f "PostgreSQL" PG:"host=172.23.215.10 user=chouette password=chouette dbname=chouette2" -lco GEOMETRY_NAME=the_geom D:\Donnees\Etudes\TC\1_Donnees_SIG\St_Quentin\arrets.shp -nln xy_St_Quentin.arrets -overwrite -a_srs EPSG:2154 Il est important que la table produite dans le nouveau schéma soit nommée « arrets », car les scripts s'exécuteront avec ce nom. Dans l'exemple ci-dessus, on crée bien une table « arrets » dans le schéma « xy_St_Quentin » 2. Editer la requête 3-import_status.sql en fonction du nom du réseau à traiter, puis exécuter celle-ci (une nouvelle ligne est alors ajoutée dans la table metadonnees du schéma status) 3. Ensuite, deux solutions sont possibles pour poursuivre la procédure : • soit un traitement automatique (permet de lancer l'ensemble des scripts et d'exporter les tables aux formats shp et tab) : → éditer et exécuter le fichier .sh ou .bat (selon le système d’exploitation : linux ou windows) adapté aux données à traiter. Les données n'ayant pas toutes le Base Arrêts TC : architecture et fonctionnement – Mai 2014 9/17 même contenu, les scripts à exécuter diffèrent. (cf. annexe 7.3) • soit un traitement manuel (lancement des scripts un à un). Cette méthode est préconisée en cas de plantage pour un schéma donné, afin de déterminer à quel stade la procédure plante : → exécuter le script adapté aux données : 4-requetes_chouette.sql pour les données formatées (GTFS ou Neptune) 5-requetes_xy.sql pour les fichiers SIG 6-requetes_osm.sql pour les données OSM → effectuer un export des tables via la commande ogr2ogr (commandes décrites dans le fichier commandes_exports.txt) 6 Diffusion des données produites Les données obtenues après utilisation des scripts pythons sont transmises à la DTecTV sous forme zippée via un serveur FTP. Pour un réseau donné, 2 dossiers Zip sont fournis : • « NomAOT_NomRéseau.zip » contenant : - les tables quays et stop_places (au format shp) - un fichier de métadonnées - un fichier readme.txt (indiquant le contenu des données) • NomAOT_NomRéseau_annexes.zip contenant : - les tables quays et stop_places (au format tab) - les tables quays_pathlinks, quays_service_links, stopplaces_pathlinks et stopplaces_service_links (aux formats shp et tab) 7 Retour qualité Une « fiche qualité des données TC » est produite (aujourd'hui manuellement, mais automatiquement à terme) à chaque intégration de données, et sera transmise aux AOT/producteurs de données correspondants. Cette fiche dressera le bilan des différentes traitements effectués sur les données, indiquera les problèmes rencontrés lors de certaines étapes (import des données, exports..), les corrections apportées aux données (encodage sans BOM, UTF8, suppression de virgules en trop, inversion de champs..) et eventuellement certaines pistes d'amélioration. Base Arrêts TC : architecture et fonctionnement – Mai 2014 10/17 8 Conclusion Les scripts réalisés jusqu'à maintenant permettent de générer des indicateurs sur les arrêts et sur les lignes TC issues de jeux de données divers (GTFS, Neptune, OSM, etc.). Une fois les calculs statistiques réalisés, les données géographiques sont également exportées avec un fichier de métadonnées afin de permettre le partage des données et des informations. Les perspectives d'évolution de cette base de données sont nombreuses. Les plus importantes sont les suivantes : • • • • Intégration et organisation des données GTFS sans passer par l'outil Chouette Calcul d'indicateurs supplémentaires Automatisation de la production des fiches qualité Etc. Base Arrêts TC : architecture et fonctionnement – Mai 2014 11/17 9 ANNEXE 9.1 Références Constitution d’une base de données nationale des arrêts de transport collectif, Laurent Chevereau, Patrick Gendre, ATEC ITS-France, janvier 2014 9.2 librairies et logiciels Postgresql, Postgis, QGIS, Python... 9.3 Utilisation des scripts Python Automate*.py sous Windows Ces scripts python permettent d’exécuter des fonctions SQL dans Postgres/Postgis qui calculent des indicateurs dans un schéma d’une base donnée. Les scripts effectuent également les exports aux formats shp et tab, et génèrent automatiquement le fichier de métadonnées. 9.3.1 Prérequis : • Python doit être installé sur le poste avec le module psycopg2, le package python utilisé dans Automate.py pour se connecter à PostgresQL • Qgis doit être installé sur le poste • La base de données utilisée doit comporter le schéma f contenant l’ensemble des fonctions SQL nécessaires aux analyses. • Le schéma analysé doit être présent dans la base de données. • Dans les variables d’environnement, la variable système Path doit contenir les deux chemins suivants, à adapter suivant le dossier d’installation de Qgis : § C:\Program Files (x86)\Quantum GIS Lisboa\bin § C:\Program Files (x86)\Quantum GIS Lisboa\share\gdal 9.3.2 Pocédure : La procédure est différente selon le type de données à traiter. 9.3.2.1 Cas des données formatées (GTFS ou Neptune) • Placer les fichiers Automate.py et le fichier 4-Automate.bat dans le même répertoire de votre choix. • Effectuer un clic droit sur le fichier 4-Automate.bat pour l’ouvrir dans un Base Arrêts TC : architecture et fonctionnement – Mai 2014 12/17 éditeur de texte afin de le modifier. • Le fichier 4-Automate.bat a la structure suivante : cmd /k python Automate.py --database "chouette2" --user "chouette" --password "chouette" --port "5432" --host "172.23.215.10" --schema "ratp" --folder "C:\Travail\Temp\Certu_export" • Editer le fichier et modifier les paramètres correspondant à la base de données et au schéma que vous voulez étudier : --database : <nom de la base de données> --user :< utilisateur > --password :< mot de passe > --port : <port > --host :< adresse IP du serveur > --schema :< nom du schéma à analyser> --folder : <chemin du dossier d’export à créer> • Enregistrer les modifications du fichier 4-Automate.bat et double cliquer sur le fichier pour exécuter la commande. 9.3.2.2 Cas des données OSM • Placer les fichiers Automate_osm.py et le fichier 6-Automate_osm.bat dans le même répertoire de votre choix. • Effectuer un clic droit sur le fichier 6-Automate_osm.bat pour l’ouvrir dans un éditeur de texte afin de le modifier. • Le fichier 6-Automate_osm.bat a la structure suivante : cmd /k python Automate_osm.py --database "chouette2" --user "chouette" --password "chouette" --port "5432" --host "172.23.215.10" --schema "osm" --folder "C:\Travail\Temp\Certu_export\osm" • Editer le fichier et modifier les paramètres correspondant à la base de données et au schéma que vous voulez étudier : --database : <nom de la base de données> --user :< utilisateur > --password :< mot de passe > --port : <port > --host :< adresse IP du serveur > --schema :< nom du schéma à analyser> --folder : <chemin du dossier d’export à créer> Base Arrêts TC : architecture et fonctionnement – Mai 2014 13/17 • Enregistrer les modifications du fichier 6-Automate_osm.bat et double cliquer sur le fichier pour exécuter la commande. 9.3.2.3 Cas des données SIG Les données SIG ayant des contenus tous différents (nombre et noms des champs), les scripts executés dans la base de données doivent être adaptés à chaque jeu de données, afin de conserver les quelques éléments fournis et les affecter aux champs du modèle de données partagé. • 1ère étape : connaître les champs contenus dans la table « arrets » que vous souhaitez traiter (dans pgAdmin clic droit sur la table arrets / Scripts / script SELECT) • Copiez les noms de champs placés après le terme SELECT • Ouvrir la fonction xy_quays(text, text, text, text) qui se trouve dans le schéma f pour modification (clic droit sur la fonction / Scripts / script CREATE) • se placer à la ligne 73, et remplacer les champs placés après le terme SELECT par les champs que vous avez copié précédemment. • Modifiez les lignes 82 à 96 en fonction des champs présents dans la table « arrets » que vous traitez. Le but de cette étape est de répartir les champs présents dans le résultat final. Toutes les lignes ne sont pas à modifier, cela dépend de la quantité d'informations présents dans la table « arrets ». • Champ obligatoire : – ident : identifiant UNIQUE de l'arret → noté 'prefixe||'_'||abrev||'_'||id 1. → si la table « arrêt » contient un identifiant particulier, remplacer id par « rec.nom_du_champ »(rec.objectid par exemple) 2. → si pas de champ, mettre la valeur par défaut (rec.ogc_fid) Base Arrêts TC : architecture et fonctionnement – Mai 2014 14/17 • Champs facultatifs : – name : nom de l'arret → rec.nom_du_champ si présence d'un nom (sinon valeur par défaut = '-') – postalreg : code postal – privatecode : codification particulière – descr : champ descriptif → peut résulter de la concaténation de plusieurs champs ( rec.nom_du_champ_1 ||_|| rec.nom_du_champ_2 ) Exemple de paramétrage de la fonction xy_quays : • Une fois les modifications faites, exécutez la fonction (bouton fermez la fenêtre. ) et • Placer les fichiers Automate_xy.py et le fichier 5-Automate_xy.bat dans le même répertoire de votre choix. • Effectuer un clic droit sur le fichier 5-Automate_xy.bat pour l’ouvrir dans un éditeur de texte afin de le modifier. • Le fichier 5-Automate_xy.bat a la structure suivante : cmd /k python Automate_XY.py --database "chouette2" --user "chouette" --password "chouette" --port "5432" --host "172.23.215.10" --schema "perpignan" --folder "C:\Travail\Temp\Certu_export\perpignan" • Editer le fichier et modifier les paramètres correspondant à la base de données et au schéma que vous voulez étudier : --database : <nom de la base de données> Base Arrêts TC : architecture et fonctionnement – Mai 2014 15/17 --user :< utilisateur > --password :< mot de passe > --port : <port > --host :< adresse IP du serveur > --schema :< nom du schéma à analyser> --folder : <chemin du dossier d’export à créer> • Enregistrer les modifications du fichier 5-Automate_XY.bat et double cliquer sur le fichier pour exécuter la commande. 9.3.3 Résultats A l'issue de ces scripts on obtient, dans le dossier d'export spécifié : • pour les fichiers GTFS ou Neptune : → les tables quays ( shp et tab ) → les tables stop_places ( shp et tab ) → les tables quays_pathlinks ( shp et tab ) → les tables quays_service_links ( shp et tab ) → les tables stopplaces_pathlinks ( shp et tab ) → les tables stopplaces_service_links ( shp et tab ) → un fichier de métadonnées ( csv ) • pour les fichiers OSM et SIG → les tables quays ( shp et tab ) → un fichier de métadonnées ( csv ) 9.4 Liste des réseaux publiés pour 2013 Voici la liste des réseaux qui seront publiés dans la base ATC version 2013 (v1) : Données avec informations horaires : - open data → lepilote (16 réseaux diffusés, au format Neptune) → Ville de Rennes (réseau Star, au format GTFS) → CG33 (réseaux TransGironde, au format GTFS) → CG44 (réseau Lila, au format GTFS) → CG44 (réseau TAN, au format GTFS) → Toulouse (réseau TISSEO, au format GTFS) Base Arrêts TC : architecture et fonctionnement – Mai 2014 16/17 → Bordeaux (réseaux Kéolis tram, au format GTFS) → RATP (données au format GTFS) → SNCF (réseau TransIlien au format GTFS) → CTS (réseau Strasbourg, au format Neptune ) - source AOT → Nord-Pas-de-Calais (données GTFS) → Symitam (dépt 06, contient 6 réseaux au format Neptune) → Brest (données Neptune) Données sans informations horaires : - données OpenStreetMap (osm) - données SIG → St-Quentin (format kml) → Pont-Audemer (format xls) → Le Mans (format tab) → Perpignan (format shp) - open data / Autres → CG71 (format csv / xml) → CG41 (format shp) → Montpellier (shp) Base Arrêts TC : architecture et fonctionnement – Mai 2014 17/17