DICTIONNAIRE DES DONNEES

Historique du document 

TABLE DES MATIÈRES

• Introduction
  • Contexte
  • Organisation du document
• Conventions et règles de nommage
• Modèle physique des données
  • La table “schema_informations”
  • La table “landmarks”
  • La table “roadlinks”
  • La table “operators”
  • La table “operator_logos”
  • La table “count_points”
  • La table “count_point_roadlinks”
  • La table “measure_sources”
  • La table “count_point_measure_sources”
  • La table “raw_measures”
  • La table “fixed_measures”
  • Les vues “*_aggregated_measures”
  • La vue “daily_quality”
  • La table “extra_measures”
  • La table “measure_natures”
  • La table “measure_classes”
  • La table “count_point_models”
  • La table “spatial_aggregates”
  • La table “default_flows”
  • La table “users”
  • La table “user_operators”
  • La table “unauthentified_users_details”
  • La table “api_tracking_measures”
  • La table “operator_reports”
  • Le schéma “avatar_geoserver”
    • La table “punctual_count_points”
    • La table “linear_count_points”
  • Le schéma sitraficroute_core
  • Le schéma sitraficroute_deposite
• Compression des données
• Rétention des données

Introduction

Contexte

Le présent document constitue le modèle physique des données et le dictionnaire des données du projet AVATAR (Analyse & Visualisation Automatique de données de TrAfic Routier).

Il a pour objectif de décrire précisément le modèle de données relationnel de la base de données mise en œuvre en vue de répondre au besoin décrit dans le modèle conceptuel des données.

Organisation du document

Les principaux paragraphes de ce document sont les suivants :

• Conventions et règles de nommage ;

• Modèle physique des données .

Conventions et règles de nommage

Les règles de nommages retenues pour le modèle de données d’AVATAR sont :

• L’utilisation de la langue anglaise pour les noms des tables et champs (à l’exception des champs directement issus des référentiels BD TOPO et BD PR, cf. La table “landmarks” et La table “roadlinks”),

• L’utilisation du pluriel pour le nom des tables,

• L’utilisation de lettres minuscules pour le nom des tables et champs (à l’exception des champs directement issus des référentiels BD TOPO et BD PR, cf. La table “landmarks” et La table “roadlinks”, dans lesquelles les noms des champs sont en majuscules),

• L’utilisation du séparateur “_” entre les mots dans le nom des tables et champs (tout en considérant que les mots “landmark” et “roadlink” sont unitaires, d’où les noms de tables “landmarks” et “roadlinks” plutôt que “land_marks” et “road_links”),

• L’utilisation du nom de champ “id” pour les clés primaires de chaque table,

• L’utilisation de la règle de nommage “<nom_de_la_table_référencée_au_singulier>_id” pour le nom des clés étrangères (par exemple, le champ permettant de définir le gestionnaire associé à un point de comptage est par convention le champ “operator_id” de la table “count_points”).

La présence d’une clé primaire (champ “id”) est systématique pour chaque table, à l’exception :

• Des tables d’association (La table count_point_roadlinks, La table “count_point_measure_sources”), dont la clé primaire est constituée de la paire des deux clés étrangères définissant la relation,

• Des tables liées aux mesures (La table “raw_measures”, La table “fixed_measures”, Les vues “*_aggregated_measures”, pour lesquelles TimescaleDB impose la définition d’un clé primaire composée incluant l’horodate associée à la mesure.

Les géométries sont stockées non projetées (système EPSG:4326).

Par ailleurs, chaque table possède les champs “created_on” et “updated_on”, automatiquement renseignés par la base de données à l’aide de triggers, et correspondant à l’instant de création et de dernière modification de la ligne correspondante (à l’exception des tables “raw_measures”, “fixed_measures”, “extra_measures” et des vues “*_aggregated_measures” pour des raisons de performance et de frugalité en espace disque nécessaire pour ces tables constituées de très nombreuses lignes).

Modèle physique des données

Le schéma suivant représente le Modèle Physique des Données d’AVATAR :

Modèle Physique des données d’AVATAR

L’ensemble des tables représentées ci-dessus font partie d’un schéma appelé “avatar” dans la base de données. Il existe également un schéma dédié au serveur WFS d’AVATAR (avatar_geoserver) décrit dans un paragraphe dédié (Le schéma “avatar_geoserver”).

Enfin, il existe également des schémas sitraficroute_* mis en place dans le cadre du futur SI trafic national (brique des dépôts intégrée à AVATAR, notamment), qu’on peut décrire ainsi :

• le schéma “sitraficroute_core”, dédié aux entités génériques potentiellement utile à l’ensemble des briques du SI trafic. On y retrouve pour le moment les tables liées à la gestion des utilisateurs et leurs organismes.
• le schéma “sitraficroute_deposite”, dédié à la brique de dépôt du SI Trafic. Celui-ci référence les utilisateurs et organisme définis dans le schéma “sitraficroute_core”.

Le diagramme suivant illustre cette organisation :

classDiagram
namespace sitraficroute_core {
    class organizations
    class users
}
namespace sitraficroute_deposite {
    class deposite_users
    class deposites
}
users <-- deposite_users
organizations <-- deposites

Le logiciel utilisé pour réaliser le schéma “avatar” est MysSQL Workbench. Les codes graphiques utilisés sont les suivants :

• Une clé jaune () devant le nom d’un champ indique qu’il s’agit de la clé primaire de la table,

• Une clé rouge () devant le nom d’un champ indique qu’il s’agit d’une clé étrangère,

• Un losange plein () devant le nom d’un champ indique qu’il est “NOT NULL” (ne pouvant pas prendre la valeur NULL),

• Un losange vide () devant le nom d’un champ indique qu’il n’est pas “NOT NULL” (il peut prendre la valeur NULL)

Les cardinalités des relations sont représentées par :

• Plusieurs lignes () du côté d’une cardinalité “0..n”,

• Une seule ligne () du côté d’une cardinalité “0..1”.

Par ailleurs, la table de correspondance entre les types indiqués sur le diagramme (types MySQL) et les types PostgreSQL, lorsque différents, est la suivante :

Les types PostgreSQL utilisés en pratique pour chaque champ sont indiqués dans le détail des tables donné aux paragraphes suivants.

La règle de lecture de la colonne “Spécificités” des tableaux présents dans les sous paragraphes suivants est la suivante :

• La présence d’un “P” indique que la colonne fait partie de la clé primaire de la table,

• La présence d’un “F” indique que la colonne est une clé étrangère (et référence donc la clé primaire d’une autre table),

• La présence d’un “U” indique la présence d’une contrainte d’unicité sur la colonne correspondante. En cas de contrainte d’unicité sur plusieurs colonnes, il est indiqué “U1” pour l’ensemble des colonnes concernées par la première contrainte d’unicité composée de la table “U2” pour la seconde, etc…

• La présence d’un “N” indique que le champ est défini comme étant non nullable (NOT NULL),

• La présence d’un “I” indique la présence d’un index sur le champ correspondant. En cas d’index composé, il est indiqué “I1” pour l’ensemble des colonnes concernées par le premier index composé de la table, “I2” pour le second, etc…

La table “schema_informations”

La table “schema_informations” est une table permettant de stocker des métadonnées générales sur le modèle de données d’AVATAR, sous forme d’un ensemble de couples “clé + valeur”.

A ce jour, les clés suivantes sont définies :

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “landmarks”

La table “landmarks” est la table permettant de stocker les points de repère de la version courante de la BD PR de l’IGN (version identifiée par la valeur associée à la clé “BD_PR_version” de la table “schema_information”).

Les champs dont les valeurs sont directement copiées de champs existants de la BD PR en conservent le nom et sont ainsi facilement identifiables (en langue française et en majuscules).

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “roadlinks”

La table “roadlinks” est la table permettant de stocker les tronçons de route de la version courante de la BD TOPO de l’IGN (version identifiée par la valeur associée à la clé “BD_TOPO_version” de la table “schema_information”).

Les tronçons de route de la BD TOPO dont l’attribut “Sens” vaut “Sans objet” (sentiers, escaliers, pistes cyclables) ne sont pas représentés dans cette table.

Les champs dont les valeurs sont directement copiées de champs existants de la BD TOPO en conservent le nom et sont ainsi facilement identifiables (en langue française et en majuscules), à l’exception du champ “ID” renommé en “BDTOPO_ID” pour éviter toute ambiguïté avec la clé primaire de la table.

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “operators”

La table “operators” est la table permettant de stocker les gestionnaires des stations de comptage modélisées dans AVATAR.

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “operator_logos”

La table “operator_logos” est la table permettant de stocker les logos des gestionnaires des stations de comptage modélisées dans AVATAR.

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “count_points”

La table “count_points” est la table permettant de stocker les points de comptage modélisés dans AVATAR.

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “count_point_roadlinks”

La table “count_point_roadlinks” est une table d’association entre les tables “count_points” et “roadlinks”, permettant de définir l’emprise d’un point de comptage sur le réseau routier. Un point de comptage peut être associé à plusieurs tronçons de route.

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “measure_sources”

La table “measure_sources” est la table permettant de stocker des métadonnées sur les sources de données associées à un ou plusieurs points de comptage. Il s’agit d’identifier le job Talend responsable de l’acquisition des données brutes le cas échéant, la résolution temporelle des données, et les informations sur les éventuels pré-traitements apportés aux données brutes (réagrégation de données minute en données 6 minutes par exemple).

Une source de mesure peut être associée directement à un point de comptage s’il s’agit de la source de mesure active pour l’alimentation des données temps réel pour ce point de comptage (via la clé étrangère “realtime_measure_source_id” de la table “count_points”), ou indirectement pour conserver l’historique de l’ensemble des sources de mesures ayant servi à alimenter les données brutes d’un point de comptage au long du cycle de vie d’AVATAR via la table “count_point_measure_sources”.

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “count_point_measure_sources”

La table “count_point_measure_sources” est une table d’association entre les points de comptage et l’ensemble des sources de mesures associées aux données de mesure brutes acquises pour les points de comptage (y compris la source de mesure référencée par la colonne “realtime_measure_source_id” de la table “count_points”).

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “raw_measures”

La table “raw_measures” est la table permettant de stocker mesures brutes effectuées par les différents canaux.

Il s’agit d’une hypertable TimescaleDB.

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “fixed_measures”

La table “fixed_measures” est la table permettant de stocker les mesures corrigées et/ou complétées à partir des données brutes présentes dans la table “raw_measurements”.

Les colonnes définies dans cette table sont globalement identiques à celles de la table “raw_measurements”, auxquelles sont ajoutées des colonnes dédiées aux indices de confiance des grandeurs correspondantes.

Il s’agit d’une hypertable TimescaleDB.

Le détail des colonnes de cette table est donné dans le tableau suivant :

Les vues “*_aggregated_measures”

Les vues “hourly_aggregated_measures”, “daily_aggregated_measures”, “weekly_aggregated_measures”, “monthly_aggregated_measures” et “yearly_aggregated_measures” permettent la consultation des données de mesures corrigées / complétées (table “fixed_measures”) agrégées respectivement par heure, jour, semaine, mois et année.

A l’exception de la vue ” hourly_aggregated_measures”, pour laquelle la fonctionnalité pénalise les performances plus qu’elle ne les améliore, ces vues sont définies à l’aide de la fonctionnalité d’agrégats continus de TimescaleDB (https://docs.timescale.com/timescaledb/latest/how-to-guides/continuous-aggregates/) afin de permettre le calcul rapide des indicateurs agrégés quotidiens (cf. La table “spatial_aggregates”) et d’accélérer les requêtes de données agrégées temporellement depuis l’API et le tableau de bord.

Les colonnes de ces vues sont identiques à celles définies dans la table “fixed_measures”, où la colonne d’indicateurs de qualité a été remplacée par une colonne par variable de trafic présentant un tableau avec le nombre de mesures ayant obtenu chaque indicateur de qualité (tableau d’entiers à 9 éléments pour les 9 indicateurs de A à I pris dans cet ordre). Les agrégats sur des périodes supérieures à l’heure ne disposent que des données de débits journaliers (valeur de débit horaire moyennée sur la période multipliée par 24) (pas de vitesse ni taux d’occupation) et des comptes par indicateur de qualité.

La colonne “measure_datetime” est renommée en “aggregate_datetime” dans ces vues (exceptions pour les agrégations hebdomadaires : “aggregate_week” et mensuelles : “month_aggregate_datetime”).

Le code SQL définissant la vue “hourly_aggregated_measures” est le suivant :

CREATE VIEW hourly_aggregated_measures AS
SELECT count_point_id,
time_bucket(INTERVAL '1 hour', measure_datetime) AS aggregate_datetime,
round(AVG(flow))::integer as flow,
AVG(occupancy) as occupancy,
SUM(speed*flow)/SUM(NULLIF(flow, 0)) as speed,
round(AVG(flow_trucks))::integer as flow_trucks,
sum_confidence_c(flow_confidence) as flow_confidence_counts,
sum_confidence_c(speed_confidence) as speed_confidence_counts,
sum_confidence_c(occupancy_confidence) as occupancy_confidence_counts,
sum_confidence_c(flow_trucks_confidence) as flow_trucks_confidence_counts
FROM avatar.fixed_measures
GROUP BY count_point_id, aggregate_datetime;

Les débits TV et PL horaires sont moyennés ainsi que les taux d’occupation. Les vitesses sont moyennées en pondérant par le débit associé. Pour des raisons de performance, les comptes par indicateur de qualité sont stockés sous forme de tableau et calculés par une fonction d’agrégation sur-mesure développée en C. Cette fonction est compilée dans le conteneur et importée dans postgresql via le code suivant :

CREATE OR REPLACE FUNCTION sum_confidence_c(confidence_array IN integer [9], confidence IN "char") RETURNS integer [9]
AS '$libdir/sum_confidence', 'sum_confidence_c'
LANGUAGE C STRICT;

CREATE OR REPLACE AGGREGATE sum_confidence_c ("char")
(
    sfunc = sum_confidence_c,
    stype = integer[9],
    initcond = '{0,0,0,0,0,0,0,0,0}'
);

Les agrégations journalières et supérieures exploitent la fonctionnalité d’agrégats continus hiérarchiques de Timescaledb (les agrégations annuelles sont calculées à partir des agrégations mensuelles, elles-mêmes calculées à partir des agrégations journalières comme les agrégations hebdomadaires), et sont mises à jour périodiquement. A titre d’exemple, le code de définition des agrégations journalières est le suivant. Il intègre le calcul de la première et dernière date des données agrégées, le nombre de valeurs, le nombre de valeurs non modifiées par le module IA (afin de donner des indicateurs de confiance aux agrégations annuelles), et les comptes détaillés par indicateur de qualité.

CREATE MATERIALIZED VIEW daily_aggregated_measures WITH (timescaledb.continuous) AS
SELECT count_point_id,
time_bucket(INTERVAL '1 day', measure_datetime, timezone => 'Europe/Paris') AS aggregate_datetime,
round(AVG(flow)*24)::integer as flow,
round(AVG(flow_trucks)*24)::integer as flow_trucks,
min(CASE WHEN flow IS NOT NULL THEN measure_datetime ELSE NULL END) as min_datetime,
max(CASE WHEN flow IS NOT NULL THEN measure_datetime ELSE NULL END) as max_datetime,
min(CASE WHEN flow_trucks IS NOT NULL THEN measure_datetime ELSE NULL END) as min_trucks_datetime,
max(CASE WHEN flow_trucks IS NOT NULL THEN measure_datetime ELSE NULL END) as max_trucks_datetime,
count(CASE WHEN flow_confidence IN ('A','B','C','D') THEN 1 END) as confident_values_nb,
count(flow) as all_values_nb,
sum_confidence_c(flow_confidence) as flow_confidence_counts,
sum_confidence_c(flow_trucks_confidence) as flow_trucks_confidence_counts
FROM avatar.fixed_measures
GROUP BY count_point_id, aggregate_datetime
WITH NO DATA; -- WITH NO DATA required since we're in a transaction. The following update policy will take care of refreshing it

ALTER MATERIALIZED VIEW daily_aggregated_measures set
(timescaledb.materialized_only = false);

SELECT add_continuous_aggregate_policy('daily_aggregated_measures',
start_offset => INTERVAL '10 year', -- had to add something there to
avoid an error with start time must be after the CA origin
end_offset => INTERVAL '1 day',
schedule_interval => INTERVAL '1 day');

Les agrégations hebdomadaires reposent sur les agrégations journalières. Pour le calcul des comptes par indicateur de qualité, elles utilisent également une fonction C sur-mesure sommant les tableaux d’entiers élément à élément. Cette fonction est importée dans PostgreSQL via le code suivant :

CREATE OR REPLACE FUNCTION array_sum_c(confidence_array IN integer
[9], confidence_array_ IN integer [9]) RETURNS integer [9]
AS '$libdir/sum_confidence', 'array_sum_c'
LANGUAGE C STRICT;
CREATE OR REPLACE AGGREGATE array_sum_c (int [9])
(
    sfunc = array_sum_c,
    stype = integer[9],
    initcond = '{0,0,0,0,0,0,0,0,0}'
);

Les agrégations mensuelles ne fournissent une valeur que si le mois est complet (après reconstitution par le module IA). De plus, elles fournissent un débit pour les jours ouvrés uniquement en plus du débit moyen global.

Les agrégations annuelles ne fournissent une valeur que si au moins 10 valeurs mensuelles sont présentes (donc 10 mois complets), afin de se conformer aux règles Mélodie/Arpèges. Comme pour les agrégations mensuelles, sont calculés un TMJA et un TMJO, ainsi que les nombres de valeurs agrégées au total et nombres de valeurs agrégées non modifiées par le module IA, permettant de fournir un indice de confiance aux TMJA produits.

Les paramètres des politiques de mise à jour des agrégats continus sont les suivants :

La vue “daily_quality”

Un agrégat continu timescaleDB est également défini pour la gestion des indicateurs de qualité des données des points de comptages :

CREATE MATERIALIZED VIEW daily_quality WITH (timescaledb.continuous) AS
SELECT count_point_id,
time_bucket(INTERVAL '1 day', measure_datetime, 'Europe/Paris') AS aggregate_datetime,
flow_confidence,
count(count_point_id) as flow_confidence_count
FROM avatar.fixed_measures
GROUP BY count_point_id, aggregate_datetime, flow_confidence;

SELECT add_continuous_aggregate_policy('daily_quality',
start_offset => '10 year', -- had to add something there to avoid an error with start time must be after the CA origin
end_offset => INTERVAL '1 day',
schedule_interval => INTERVAL '1 day');

Il permet de disposer de la proportion de chaque valeur d’indice de confiance des valeurs de débits par jour et par point de comptage, à utilité de l’espace dédié aux gestionnaires dans le dashboard AVATAR.

La table “extra_measures”

La table “extra_measures” est une table permettant de stocker des mesures supplémentaires par rapport à celles stockées dans la table raw_measures. Ces mesures supplémentaires ne sont pas prises en compte par le module de correction des données d’AVATAR, ni par les vues agrégées (cf. Les vues “*_aggregated_measures”).

Cette table permet de stocker tout type de mesure puisque chaque ligne référence une nature et une classe de mesure, définies dans les tables suivantes (cf. La table “measure_natures” et La table “measure_classes”).

Il s’agit d’une hypertable TimescaleDB.

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “measure_natures”

La table “measure_natures” est une table permettant de stocker les natures des mesures stockées dans la table “extra_measures”, cf. La table “extra_measures”.

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “measure_classes”

La table “measure_classes” est une table permettant de stocker la définition des classes pour les mesures classifiées présentes dans la table “extra_measures”.

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “count_point_models”

La table “count_point_models” est une table permettant de stocker les modèles entraînés par le module IA d’avatar. On sauvegarde ainsi un modèle par point de comptage et par grandeur de mesure.

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “spatial_aggregates”

La table “spatial_aggregates” est une table permettant de stocker des indicateurs précalculés par zone géographique ou par gestionnaire, à usage de la page d’accueil du dashboard AVATAR (TMJs et tendances journalières).

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “default_flows”

La table “default_flows” est une table permettant de stocker les débits moyens des points de comptage pour un jour de la semaine et une heure donnés. Ces valeurs permettent de compléter les données manquantes dans le cadre du calcul des tendances journalières représentées en page d’accueil du tableau de bord AVATAR.

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “users”

La table “users” est une table permettant de définir les comptes utilisateurs AVATAR. Bien qu’AVATAR utilise le SSO Orion du CEREMA, il reste de sa responsabilité de définir les permissions applicatives des utilisateurs de la plateforme.

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “user_operators”

La table “user_operators” est la table permettant de définir les permissions des utilisateurs par association à un gestionnaire et à des droits sur le référentiel correspondant ou sur l’administration des utilisateurs associés au gestionnaire.

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “unauthentified_users_details”

La table “unauthentified_users_details” est la table permettant de sauvegarder les réponses des utilisateurs non authentifiés au formulaire apparaissant dans le tableau de bord avant le téléchargement des données.

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “api_tracking_measures”

La table “api_tracking_measures” permet d’enregistrer les appels faits aux ressource API suivantes : RawMeasureDownload, FixedMeasureDownload, AggregatedMeasureDownload, ExtraMeasureDownload, RawMeasureList, FixedMeasureList, AggregatedMeasureList, ExtraMeasureList. Notons que sur cette table, un pg_cron planifié tous les 9 du mois permet de supprimer d’anciennes lignes dans le cas où la taille des lignes de la table dépasse 10 GB.

Le détail des colonnes de cette table est donné dans le tableau suivant :

La table “operator_reports”

La table “operator_reports” permet de stocker les rapports gestionnaires.

Le détail des colonnes de cette table est donné dans le tableau suivant :

Le schéma “avatar_geoserver”

La table “punctual_count_points”

Cette table correspond à la couche WFS homonyme, permettant de représenter les points de comptage par leur géométrie ponctuelle. Ses attributs sont très proches de ceux de la table avatar.count_points :

La table “linear_count_points”

Cette table correspond à la couche WFS homonyme, permettant de représenter les points de comptage par leur géométrie linéaire. Ses attributs sont très proches de ceux de la table avatar.count_points :

Le schéma “sitraficroute_core”

Le schéma suivant représente le modèle physique des données du shéma “sitraficroute_core” :

erDiagram
  users {
    serial id
    varchar(512) email
    integer organization_id
    boolean is_representative
    varchar(255) name
    varchar(255) position
    varchar(255) service
    timestamp created_on
    timestamp updated_on
  }

  organizations {
    serial id
    varchar(128) name
    varchar(10) acronym
    varchar(255) ign_name
    integer declared_by
    timestamp created_on
    timestamp updated_on
  }

  organizations ||--|{ users : "contient"
  users ||--|{ organizations : "a déclaré"

Le code utilisé pour créer ce schéma en base est disponible ici.

Table organizations

La table “organizations” est la table permettant de stocker les organismes impliqués dans le SI trafic route, par exemple les gestionnaires routiers.

Le détail des colonnes de cette table est donné dans le tableau suivant :

Remarque : il n’existe pour le moment pas de champ explicite indiquant le type de l’organisme (par exemple, s’agit-il d’un gestionnaire routier ou non ?). Ce type est déduit implicitement des relations existantes entre un organisme et les dépôts réalisés pour lui : on suppose qu’un organisme pour lequel a été réalisé un dépôt de données de trafic est un gestionnaire routier.

Table users

La table “users” est la table permettant de stocker les utilisateurs déclarés dans le SI trafic route (pour le moment, les utilisateurs ayant paramétré leur compte dans la brique de dépôt).

Le détail des colonnes de cette table est donné dans le tableau suivant :

Le schéma “sitraficroute_deposite”

Le schéma suivant représente le modèle physique des données du shéma “sitraficroute_deposite” :

erDiagram
  deposites {
    serial id
    integer organization_id
    varchar(50) name
    varchar(1024) comment
    timestamptz date
    varchar(10) type
    varchar(10) format
    varchar(1024) file_path
    bigint file_size
    timestamp created_on
    timestamp updated_on
  }

  deposite_users {
    integer user_id
    integer deposite_id
    varchar(10) user_deposite_rel_type
    timestamp created_on
    timestamp updated_on
  }

  deposites ||--|{ deposite_users : "est accessible par"

Le code utilisé pour créer ce schéma en base est disponible ici.

Table deposites

La table “deposites” est la table permettant de stocker les dépôts réalisés avec la brique de dépôt du SI trafic route.

Le détail des colonnes de cette table est donné dans le tableau suivant :

Table deposite_users

La table “deposite_users” est la table permettant de définir les relations entre les dépôts et les utilisateurs (quel est l’utilisateur déposant, et quels sont les utilisateurs avec lesquels le dépôt a été partagé le cas échéant).

Le détail des colonnes de cette table est donné dans le tableau suivant :

Compression des données

La compression des données proposée par timescaleDB (https://docs.timescale.com/api/latest/compression/) est mise en place pour les tables de données :

• raw_measures
• fixed_measures
• extra_measures

Une politique de compression des données datant de plus d’un mois a été mise en place pour l’ensemble de ces trois tables.

Le code correspondant est le suivant :

ALTER TABLE avatar.extra_measures SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'count_point_id, measure_class_id, measure_nature_id'
);

SELECT add_compression_policy('avatar.extra_measures', INTERVAL '1 month');

ALTER TABLE avatar.raw_measures SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'count_point_id'
);

SELECT add_compression_policy('avatar.raw_measures', INTERVAL '1 month');

ALTER TABLE avatar.fixed_measures SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'count_point_id'
);

SELECT add_compression_policy('avatar.fixed_measures', INTERVAL '1 month');

Rétention des données

A l’avenir, il faudra sans doute définir des politiques de nettoyage automatique des données trop anciennes (https://docs.timescale.com/api/latest/data-retention/add_retention_policy/). Aucune n’est définie pour le moment.