# MEDINA, une interface WEB de consultation de bases de données

Monique Harel, Cecile Le Roy

► To cite this version:

Monique Harel, Cecile Le Roy. MEDINA, une interface WEB de consultation de bases de données. Cahier des Techniques de l'INRA, 2010, N° Spécial: Données en sciences sociales, pp.73-83. hal-02657036

HAL Id: hal-02657036

https://hal.inrae.fr/hal-02657036v1

Submitted on 30 May 2020

HAL is a multi-disciplinary open access
archive for the deposit and dissemination of sci-
entific research documents, whether they are pub-
lished or not. The documents may come from
teaching and research institutions in France or
abroad, or from public or private research centers.

L'archive ouverte pluridisciplinaire HAL, est
destinée au dépôt et à la diffusion de documents
scientifiques de niveau recherche, publiés ou non,
émanant des établissements d'enseignement et de
recherche français ou étrangers, des laboratoires
publics ou privés.

<figure><img src="image_1.png" /></figure>
# MEDINA, une interface WEB de consultation de bases de données

Monique Harel<fnref n="1" />, Cécile le Roy<fnref n="1" />

**Résumé** : MEDINA (Marchés extérieurs des industries agroalimentaires) regroupe des bases de données implantées sous le SGBDR PostgreSQL localisé au centre Inra de Nantes et utilisées par les chercheurs du laboratoire LERECO. L'idée de créer un outil pour accéder facilement aux données et pour rendre l'utilisateur plus autonome est née de l'accroissement du volume des données et des demandes diverses et répétées d'extraction des bases.

**Mots-clefs** : commerce international, bases de données, interface Web, PHP, PostgreSQL

## Introduction

L'outil informatique MEDINA (Marchés extérieurs des industries agroalimentaires) est indispensable à la bonne conduite de nombreux travaux de recherche sur le commerce international. Cette gestion de données en amont de l'analyse statistique constitue une étape importante et incontournable dans le processus de recherche. Ce système regroupe 2000 produits de l'agriculture et de l'agroalimentaire, 245 pays et 15 années qu'il s'agit de mettre en relation avec la réglementation tarifaire et les informations sur les entreprises françaises. Il permet de fournir de la matière ordonnée directement utilisable par les chercheurs qui l'intègrent ensuite dans des modèles statistiques pour faire des simulations.

## 1. Contexte

### 1.1 Objectif MEDINA

Conçu pour répondre aux besoins des chercheurs en économie internationale, ce projet regroupe des informations officielles, bases de données, fichiers administratifs, enquête.
Ces données sont dispersées et hétérogènes.

L'objectif est de mettre en rapport des informations sur les échanges des produits, la tarification et les données individuelles des entreprises, permettre un suivi dans le temps et garantir la sécurité et la confidentialité des informations.

### 1.2 Sources et description des données

Les données volumineuses proviennent de sources officielles françaises, européennes et mondiales ; l'acquisition des données se fait à partir de fichiers fournis par les différents organismes. Certaines sont gratuites et facilement accessibles, d'autres sont financées par le département SAE2 (sciences sociales, agriculture et alimentation, environnement et espace) ou les unités et sont soumises à justification de projets, d'autres encore comportant des données individuelles confidentielles sont soumises à l'approbation du CNIS (conseil national de

<footnote n="1">¹ UR1134 LERECO - Laboratoire d'études et de recherches économiques - INRA - F- 44000 Nantes,
📞 02 40 67 51 16- 📧 Monique.Harel@nantes.inra.fr ; Cecile.leroy@nantes.inra.fr</footnote>
l'information statistique). Pour ces dernières, seuls les utilisateurs du projet sont autorisés à
les exploiter. Principales sources :

- Douanes : INSEE – Institut national de la statistique et des études économiques
Les échanges des entreprises françaises de 1995 à 2007 par pays, produits (NC8 et CPF6).
L'utilisation de ces données est soumise à une convention entre la direction des Douanes et
l'unité (LERECO, Nantes).

- EAE : Enquêtes annuelles d'entreprises (IAA) – Agreste-Ministère de l'agriculture et de la pêche. De 1995 à 2005 accès restreint car rattachées à un projet de recherche (Nantes).

- Comext : Eurostat – Office statistique des communautés européennes
Les échanges mensuels européens par pays et par produit (NC8) de 1992 à 2007

- Taric : Commission européenne-Direction générale de la Fiscalité et Union douanière
La réglementation douanière européenne (1995 à 2008)

- Comtrade (Commodity trade statistics database) : Nations Unies (UN)
Les échanges mondiaux par pays et par produit (HS6) de 1992 à 2007 (financement
département SAE2)

## 2. L'interface de consultation

L'objectif est de faciliter l'accès aux données et de rendre l'utilisateur plus autonome c'est-à-dire que la connaissance de la structure et de l'organisation des bases et la maîtrise du langage SQL (langage structuré de requêtes) d'interrogation des bases ne sont pas nécessaires.
Cette application regroupe une partie des informations de MEDINA, plus précisément les données du commerce international (Comext et Comtrade) et les nomenclatures correspondantes.

L'interface de consultation permet d'accéder aux données via un navigateur. Les principales fonctionnalités sont les suivantes :

- consultation des données et calcul d'agrégats

- extraction des données pour les traitements statistiques

- visualisation et extraction des nomenclatures pays (correspondance Comext, Comtrade)

- affichage de l'arborescence des produits de la nomenclature combinée (NC8)

### 2.1. Développement

L'application client/serveur a été développée en PHP et JavaScript permettant de créer des pages WEB dynamiques ; elle est couplée à une base de données PostgreSQL qui contient toutes les informations. La sélection de l'utilisateur (requête) est envoyée via le serveur Web au serveur des bases de données qui renvoie le résultat qui s'affiche à travers le navigateur
Le schéma 1, ci-dessous, représente l'architecture de l'application :
Les données sont hébergées sur le serveur de calcul sur lequel sont implantées les bases PostgreSQL ; les pages HTML et les programmes PHP sont installés sur un serveur d'applications WEB
<figure><img src="image_2.png" /><figcaption>**Schéma 1 : architecture de l'application**</figcaption></figure>

<figure><img src="image_3.png" /><figcaption>**Schéma 2 : principe de fonctionnement du projet**</figcaption></figure>

## 2.2 Description de l'interface

La page d'accueil donne un accès direct aux nomenclatures des pays et des produits, ainsi qu'aux données du commerce Intra et Extra européens.
<figure><img src="image_4.png" /></figure>

### 2.2.1 Les nomenclatures

La gestion des nomenclatures pays et produits est la clé de voûte du projet MEDINA ; en effet il est indispensable de s'assurer de la concordance entre les sources et de leur compatibilité dans le temps. Le croisement des différentes sources est réalisé grâce aux tables de correspondance

#### - Nomenclatures pays

##### Sélection des nomenclatures par nom

Pour obtenir le code correspondant à ces pays, l'utilisateur sélectionne les noms de ces pays dans une liste déroulante, la touche `ctrl` maintenue enfoncée permet de choisir plusieurs noms

<figure><img src="image_5.png" /></figure>
L'option « afficher » permet d'obtenir le code de ces pays dans les différentes nomenclatures

<figure><img src="image_6.png" /></figure>

L'utilisateur peut aussi interroger à partir des codes numériques et ainsi obtenir le libellé du pays et la correspondance avec les autres nomenclatures ; il doit choisir dans le menu nomenclature pays, l'option « par code eurostat »

<figure><img src="image_7.png" /></figure>
<figure><img src="image_8.png" /></figure>

## Nomenclatures produits

Les statistiques du commerce international utilisent plusieurs niveaux de codification des marchandises : le système harmonisé (SH) codifié sur 6 positions numériques, la nomenclature combinée (NC) à 8 chiffres correspondant à la SH plus 2 chiffres et la classification française des produits sur 6 positions numériques (CPF6).

La recherche des équivalences dans les nomenclatures citées peut se faire à partir d'un code NC8 ou CPF6. La nomenclature NC8 est formée par l'agrégat chapitre (1 à 24 pour les produits agricoles et agroalimentaires), groupe, rubrique, détail à renseigner ou non pour la demande. On renseigne aussi les rubriques résultats souhaitées (libellé, date de validité...).

<figure><img src="image_9.png" /></figure>
Dans cet exemple, on demandait les nomenclatures NC8 et CPF6, le libellé de tous les produits du chapitre 10 et du groupe 01

<figure><img src="image_10.png" /></figure>

Pour une demande à partir de la nomenclature CPF6, plus agrégée, on choisit les codes parmi la liste des produits existants et on obtient le même type de résultat.

### 2.2.2 Les données

Avec le lien **données Comext**, **données détaillées**, on accède à une nouvelle page pour la sélection des informations à partir de différentes listes (Déclarants, Partenaires, Produits, Flux, Période) :

- déclarants : pays de l'Union Européenne importateur ou exportateur

- partenaires : pays destinataires

- produits : produits de l'agriculture et de l'agroalimentaire

- flux : importation ou exportation

- période : année(s) concernée(s)

#### Choix des pays déclarants

Pour sélectionner un ou plusieurs pays, en les mettant en surbrillance, ces derniers sont stockés dans un tableau de session utilisé par PHP
<figure><img src="image_11.png" /></figure>

Lorsque les choix sont effectués, ils sont affichés sur la page principale dans les listes prévues à cet effet

<figure><img src="image_12.png" /></figure>

De cette page on peut générer une requête SQL en fonction des choix donnés par l'utilisateur.

Le résultat de la requête s'affiche dans un tableau. Il est possible de télécharger ces informations dans un fichier texte (type .txt)
<figure><img src="image_13.png" /></figure>

## 3. Sécurité de l'application

La sécurité du projet MEDINA se situe à 2 niveaux (serveur et base de données). Au niveau serveur, la sécurité est gérée par un fichier de configuration PostgreSQL. Au niveau bases de données, certaines étant financées par le département SAE2, il est indispensable de restreindre l'accès aux personnes appartenant à ce département via un système d'authentification par login/mot de passe qui doit être reconnu au niveau des bases de données. Pour le moment, il existe un écran d'identification avec saisie d'un identifiant et d'un mot de passe fournis par l'administrateur des bases.

<figure><img src="image_14.png" /></figure>
## 4. Limites de l'application

A ce stade, l'application ne donne pas accès à l'ensemble du projet MEDINA, néanmoins elle permet la consultation et l'extraction d'informations sur le commerce extérieur et sur les nomenclatures pays et produits.

Pour rendre accessible l'ensemble des données (comme les « douanes »), il reste à mettre en place un processus d'identification pour les membres du projet associé à ces données.

## Conclusion

L'application Web MEDINA est un outil qui peut facilement évoluer en fonction de la demande.

De nouvelles fonctionnalités telles que le calcul d'agrégats (c'est à dire, calculer pour un produit ou groupe de produits donné, un flux, un ou plusieurs partenaires, une ou plusieurs années, la quantité et la valeur échangée par l'ensemble des pays de la zone UE) et les sélections multiples sont maintenant proposées. On pourrait ajouter, des informations concernant la réglementation douanière et l'évolution des nomenclatures produits qui évoluent en permanence.

Par ailleurs, le CATI<fnref n="2" /> IATISS<fnref n="3" /> outil d'animation informatique a pour objectif de mettre en commun les applications développées dans le département. Ainsi, les données gérées dans les unités seront mises à disposition à l'ensemble des chercheurs.

C'est ainsi qu'une version test a été portée sur le serveur d'application dédié au CATI localisé à Toulouse.

## Bibliographie

- Rigaux P. (2002) Pratique de MySQL et PHP

- Geschwinde E., Schönig H. (2005) Pratique de MySQL et PHP

- Messager J., Rousseau R. pour Learning Tree International (2007) Développement Web avec PHP (cours)

- INSEE : guide d'utilisation des nomenclatures d'activités et de produits

- Documentation PHP, http://www.php.net/manual/fr/

- Documentation PostgreSQL, http://docs.postgresql.fr/8.3/

<footnote n="2">² Centre automatisé de traitement de l'information</footnote>

<footnote n="3">³ Informatique Appliquée au Traitement de l'Information en Sciences Sociales</footnote>
Encadré 1 : extrait d'une page PHP

<h2>Nomenclatures des pays classés par nom</h2>
<?
/*initialisation de la connexion avec la base de données*/
require两次('dblogin_intercom.php');
/*définition de la requête*/
$req="SELECT * from pays_9207_appli where";
$i=1;
foreach($_SESSION['panier_pays'] as $codegeo)
{
    $req.=" codegeo=".$codegeo;
    if($i!=count($_SESSION['panier_pays']))/*count donne le nombre d'éléments d'un tableau*/
    {
        $req.=" or";/*si l'élément lu n'est pas le dernier du tableau on ajoute l'élément suivant*/
    }
    $i++;
}
$req.=" order by libel_fr;";
/*exécution de la requête et récupération du résultat*/
$resultat=pg_query($req);
/*génération du nom de fichier temporaire : le fichier doit avoir un nom unique au cas ou 2 utilisateurs sont présents en même temps sur la page(le fichier du 1er utilisateur serait écrasé par celui du 2e dans le cas contraire).*/
srand();/*initialisation du générateur de nombres aléatoires*/
$sn=rand();/*génération d'un nombre aléatoire*/
$d date('His');/*récupération de l'heure (HeuresMinutesSecsecondes)/*/
$nom_fichier="tmp/nomenc_pays-".$d.$n.".txt";
/*ouverture du fichier texte en écriture*/
$fichier=fopen($nom_fichier,"w");/*w est le mode "écriture" d'ouverture du fichier*/
/*affichage des informations dans un tableau*/
echo "
<table class='result' border='1'>
  <thead>
    <tr>
      <th>Libellé Français</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Libellé Anglais</td>
    </tr>
    <tr>
      <td>Code Eurostat</td>
    </tr>
    <tr>
      <td>Code ISO2</td>
    </tr>
    <tr>
      <td>Code ISO3</td>
    </tr>
    <tr>
      <td>Code Comtrade</td>
    </tr>
    <tr>
      <td>Code FAO</td>
    </tr>
    <tr>
      <td>Date début Eurostat</td>
    </tr>
    <tr>
      <td>Date fin Eurostat</td>
    </tr>
    <tr>
      <td>Date début Comtrade</td>
    </tr>
    <tr>
      <td>Date fin Comtrade</td>
    </tr>
  </tbody>
</table>";