Tri de données et sous-données

Version imprimable

22/11/2021, 10h54
julosjuju

Tri de données et sous-données

Bonjour,

Je débute en sql, veuillez m'excuser si ma question est trop triviale.
J'utilise SqLiteStudio v3.3.3 .
Je dispose d'un fichier csv comportant environ 400000 lignes.
Chaque ligne correspond à un résultat d'analyse physico-chimique.
Chaque résultat est associé à une commune et à un quartier de cette commune.
il y a (environ) 36000 communes.
Une commune peut avoir de 1 à 30 quartiers.
Un quartier peut avoir de 1 à 20 résultats d'analyse.
En résumé, mon fichier csv comporte donc les colonnes suivantes :
- latitude
- longitude
- commune
- quartier
- date_analyse
- resultat_analyse

Mon objectif final est d'afficher, dans une appli SIG (par exemple umap ou qgis), un point par commune, et d'associer à chaque point de commune un tableau comportant l'ensemble des résultats pour la commune, classés par quartier.
Existe-t-il une manière simple d'aboutir à ce résultat en utilisant le sql ?
J'avais dans l'idée de créer un nouveau fichier csv à partir du fichier source, dans lequel j'aurais 36000 lignes (une par commune) et des colonnes quartier_01_resultat_01 à quartier_x_resultat_y.
Je parviens à faire une requête individuelle pour sélectionner les résultats quartier_x_resultat_y de l'ensemble des communes, mais existe-t-il un moyen, en langage sql, de faire l'ensemble des requêtes pour tous les couples quartier_x_resultat_y et de les ranger dans les bonnes colonnes ?

Merci d'avance.
22/11/2021, 18h13
escartefigue

Bonsoir,

Il ne faut surtout pas modéliser une table de façon horizontale en répétant les quartiers, ce type de modélisation est ce qu'il y a de pire, pour la maintenance, pour la mise au point des requêtes et pour les performances.

Pour savoir comment modéliser les tables, il faut connaître les règles de gestion.

Par exemple, un même point (latitude et longitude) peut il appartenir à plusieurs villes ? Je pense à un point situé pile à la frontière de 2 ou 3 villes, est il dans une ville seulement ou dans toutes ?

Même question concernant les quartiers : un quartier c'est une seule ville ou pas forcément ?

Autre question : le quartier est-il obligatoire, ou bien (plus probablement) certains points sont associés directement à une (des) ville(s) sans passer par la notion de quartier.

Selon les réponses aux questions ci-dessus, le nombre de tables et les attributs qu'elles contiennent changent du tout au tout.
23/11/2021, 09h45
julosjuju

Bonjour,

Merci pour votre réponse.
Voici quelques précisions :
- un point (latitude et longitude) correspond à une commune (il s'agit des coordonnées de l'hôtel de ville).
- une commune possède forcément au moins un quartier. Une commune est découpée en 1 à 30 quartiers.
- un point de mesure est forcément associé à un couple ville/quartier.
- un quartier peut être commun à plusieurs communes (la notion de "quartier" correspond en fait à un réseau de distribution d'eau, qui peut desservir plusieurs communes), mais pour l'usage que je veux faire de cette base on peut considérer que ce n'est pas le cas. Je m'explique : si un résultat d'analyse concerne un quartier qui dessert 3 communes, dans la base il y a 3 lignes (une ligne pour chaque association résultat/quartier/commune). Ce que je souhaite c'est que lorsqu'un utilisateur clique sur le repère d'une commune donnée, une fenêtre s'ouvre avec tous les résultats d'analyse concernant la commune, classés quartier par quartier.

Je précise que la base est vouée à grossir, en fonction des nouveaux résultats d'analyses physico-chimiques qui seront effectués dans le futur.
Je n'ai pas besoin de faire une mise à jour dynamique, le but serait de faire une mise à jour une fois par trimestre en fonction des nouveaux résultats.
Ainsi :
- l'association point (latitude longitude) / commune ne va pas changer dans le temps,
- la quantité de quartiers par commune ne va pas changer dans le temps,
- la quantité de mesures par quartier (et donc par commune) va grossir dans le temps.
23/11/2021, 10h45
escartefigue

Ceci me semble contradictoire :

Citation:

Envoyé par julosjuju

Voici quelques précisions :
- un point (latitude et longitude) correspond à une commune (il s'agit des coordonnées de l'hôtel de ville).
- une commune possède forcément au moins un quartier. Une commune est découpée en 1 à 30 quartiers.
- un point de mesure est forcément associé à un couple ville/quartier.
- un quartier peut être commun à plusieurs communes (la notion de "quartier" correspond en fait à un réseau de distribution d'eau, qui peut desservir plusieurs communes),

Si un point de mesure représente les coordonnées de l'hôtel de ville, il ne peut correspondre qu'à un et un seul quartier
23/11/2021, 11h19
julosjuju

Les coordonnées géographiques ne sont pas celles du point de mesure, mais celles de l'hôtel de ville.
Je ne connais pas les coordonnées géographiques des points de mesure proprement dits, et je n'en ai pas besoin.
Mon objectif est d'avoir une carte de france avec 36000 épingles, une épingle par commune. Et quand on clique sur l'épingle cela ouvre une fenêtre avec tous les résultats d'analyse concernant la commune.
Voici un exemple :
- nous avons communes voisines, COMMUNE1 et COMMUNE2;
- COMMUNE1 a deux quartiers (correspondant à deux réseaux de distribution), intitulés QUARTIER1 et QUARTIER2;
- COMMUNE2 n'a qu'un quartier, QUARTIER1 (ce quartier est commun avec COMMUNE 1);
- QUARTIER1 a 3 résultats de mesure, nommés QUARTIER1_RESULTAT1, QUARTIER1_RESULTAT2 et QUARTIER1_RESULTAT3;
- QUARTIER2 a 2 résultats de mesure, nommés QUARTIER2_RESULTAT1 et QUARTIER2_RESULTAT2;
- COMMUNE1 est associée à une épingle localisée aux coordonnées COMMUNE1_LAT et COMMUNE1_LON;
- COMMUNE2 est associée à une épingle localisée aux coordonnées COMMUNE2_LAT et COMMUNE2_LON;

Je souhaite que lorsque l'utilisateur clique sur l'épingle de COMMUNE1, une fenêtre s'ouvre avec les informations suivantes :
Nom de la commune : COMMUNE1
QUARTIER1
QUARTIER1_RESULTAT1
QUARTIER1_RESULTAT2
QUARTIER1_RESULTAT3
QUARTIER2
QUARTIER2_RESULTAT1
QUARTIER2_RESULTAT2

Et que lorsqu'il clique sur l'épingle de COMMUNE2, la fenêtre qui s'ouvre indique :
Nom de la commune : COMMUNE2
QUARTIER1
QUARTIER1_RESULTAT1
QUARTIER1_RESULTAT2
QUARTIER1_RESULTAT3

J'espère que c'est clair...

OK, du coup le modèle de données correspondant est le suivant (créé avec l'excellent Looping, qui plus est gratuit :ccool:) :

Pièce jointe 609142

Ce qui donne le DDL suivant (ici décliné pour SQL server) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
CREATE TABLE VI_ville(
   VI_ident INT IDENTITY,
   VI_insee CHAR(5) NOT NULL,
   VI_nom VARCHAR(50) NOT NULL,
   VI_latitude DECIMAL(9,6) NOT NULL,
   VI_longitude DECIMAL(9,6) NOT NULL,
   PRIMARY KEY(VI_ident),
   UNIQUE(VI_insee)
);
 
CREATE TABLE QU_quartier(
   QU_ident INT IDENTITY,
   QU_code CHAR(6) NOT NULL,
   QU_nom VARCHAR(50) NOT NULL,
   PRIMARY KEY(QU_ident),
   UNIQUE(QU_code)
);
 
CREATE TABLE MS_mesure(
   QU_ident INT,
   MS_ident INT IDENTITY,
   MS_timest DATETIME2 NOT NULL,
   MS_valeur DECIMAL(13,4) NOT NULL,
   PRIMARY KEY(QU_ident, MS_ident),
   FOREIGN KEY(QU_ident) REFERENCES QU_quartier(QU_ident)
);
 
CREATE TABLE VQ_situer(
   VI_ident INT,
   QU_ident INT,
   PRIMARY KEY(VI_ident, QU_ident),
   FOREIGN KEY(VI_ident) REFERENCES VI_ville(VI_ident),
   FOREIGN KEY(QU_ident) REFERENCES QU_quartier(QU_ident)
);

24/11/2021, 10h47
julosjuju

Merci beaucoup !
Il ne me reste plus qu'à peupler les tables et à faire la requête qui me permettra de créer des fiches de synthèse de l'ensemble des résultats (classés par quartier) pour chaque commune.
Je n'ose pas demander comment faire (ça semble du b.a.ba), mais je reviens vers vous si je n'y arrive pas ;-)
24/11/2021, 10h56
julosjuju

J'ai une question : sachant que chaque commune est identifiée par son code INSEE et qu'il ne peut pas y avoir 2 codes INSEE identiques, le code INSEE ne pourrait-il pas être utilisé en tant que clé primaire pour la table VI_VILLE ? Y a-t-il une utilité particulière à créer une colonne distincte de la colonne INSEE pour cette table ?
24/11/2021, 13h44
escartefigue

Bonne question, mais, le code INSEE est une nomenclature externe. À ce titre il peut changer à tout moment et sans préavis.
Or, l'une des qualités premières d'une PK est d'être stable : en effet, elle se propage dans d'autres tables à cause des contraintes d'intégrité.
Donc, si vous choisissez d'utiliser le code INSEE comme PK, vous aurez des MàJ en cascade à chaque modif du référentiel INSEE, c'est à dire à peu près à chaque élection municipale.
Une clef technique attribuée par le SGBD (identity, auto_increment, sequence... selon le SGBD) est à ce titre un choix idéal.