Discussion :

[vintofs]

Bonjour,

J'ai depuis un bail une base géographique française (territoriale, administrative et démographique) qui fonctionne correctement mais que j'ai à l'époque modélisé à la va-vite avec mes siper compétences DBA de programmeur moyen : médiocres.

J'ai décidé de remettre tout ça au goût du jour et je me pose quelques questions en matière de "bonnes pratiques" :

(J'utilise MySQL 5 avec InnoDB, les colonnes sont incomplètes)

Les relations "de base" sont les suivantes :



Le premier problème se pose pour l'enregistrement des chefs lieux (régions, départements, ar. et cantons), idéalement j'aurais crée des clés étrangères dans l'autre sens toutes allant vers la table "Ville", mais le croisement de FK ne me paraît pas être la bonne solution.

J'ai donc pensé créer des tables de jointure (ChefLieu_Departement etc) comportant l'id de la sub-division et celui de la ville concernée.
Je peux aussi enregistrer simplement le code INSEE (unique, not null) de la ville en question et effectuer une recherche avec JOIN.

Quelle serait selon vous la solution idéale ? En prenant également en considération les performances.

De même je serais souvent amené à faire des recoupements démographiques à l'échelle d'un canton/ar./departement/région à partir des infos d'une ville (population, densité etc).
Est-il préférable d'incorporer ces données directement dans la table "Ville" ou de créer une table à part ?

Et pour finir, est-il intéressant d'effectuer les calculs et les recoupements directement à partir du SGBD en renvoyant des vues pré-remplies ou comme actuellement, au niveau de l'application ?

Merci d'avance, et désolé pour le pavé fourre-tout.

PS: Les perfs ne sont pas un point critique, j'ai surtout envie d'apprendre à faire ça correctement, tant qu'à faire.

[Richard_35]

Bonjour Vintofs,

Peut-être que ce document pourra t'aider.

L'IGN me semble une bonne référence.

Le MCD est à déduire, mais je te laisse dégrossir le sujet. Re-postes, si besoin.

[vintofs]

Merci Richard_35,

En effet il s'agit d'un document très intéressant à côté duquel j'étais passé (j'utilise pourtant pas mal de données venant de chez eux).

Il répond en partie à la question de l'organisation des données, en revanche je m'interroge toujours sur la pertinence de certaines clés étrangères et sur celle du calcul directement à partir du SGBD en matière d'optimisation.

Quelques précisions en plus seraient les bienvenues si c'est possible.

[Richard_35]

OK, mais il faut que tu dégrossisses le système.

je m'interroge toujours sur la pertinence de certaines clés étrangères

==> lesquelles et dans quel contexte (dans quelle partie du MCD que tu auras pris soins de créer) ?

et sur celle du calcul directement à partir du SGBD en matière d'optimisation

==> idem : il faut partir de ta modélisation déduite du document (si le document te convient) et s'interroger sur les parties sur lesquelles tu t'interroges.

[CinePhil]

J'ai lu en diagonale cet intéressant document qui m'a rafraîchi la mémoire sur mon précédent boulot.

J'ai cru comprendre que le modèle de l'INSEE pour le découpage géographique français est le suivant :

ville -1,1----composer----1,n- canton -1,1----composer----1,n- arrondissement -1,1----composer----1,n- departement -1,1----composer----1,n- region

Dans mon précédent boulot à l'INRA, nous n'utilisions pas le niveau arrondissement, pour autant que je m'en souvienne.

Nous avions donc le modèle suivant :
ville -1,1----composer----1,n- canton -1,1----composer----1,n- departement -1,1----composer----1,n- region

Mais il n'était pas implanté comme ça dans la BDD pour des raisons historiques et parce que nous avions aussi d'autres découpages géographiques maison.

Le premier problème se pose pour l'enregistrement des chefs lieux (régions, départements, ar. et cantons), idéalement j'aurais crée des clés étrangères dans l'autre sens toutes allant vers la table "Ville", mais le croisement de FK ne me paraît pas être la bonne solution.

J'ai donc pensé créer des tables de jointure (ChefLieu_Departement etc) comportant l'id de la sub-division et celui de la ville concernée.
Je peux aussi enregistrer simplement le code INSEE (unique, not null) de la ville en question et effectuer une recherche avec JOIN.

Quelle serait selon vous la solution idéale ? En prenant également en considération les performances.

Pour ce qui est des chefs-lieux, ou bien tu considères comme l'INSEE qu'une commune ne peut être que d'un type de chef-lieu le plus grand possible, ou bien tu veux enregistrer le fait qu'une commune peut être préfecture de région, de département et chef-lieu de canton. Les cardinalités seront différentes et cela aura des conséquences sur les tables.

De même je serais souvent amené à faire des recoupements démographiques à l'échelle d'un canton/ar./departement/région à partir des infos d'une ville (population, densité etc).
Est-il préférable d'incorporer ces données directement dans la table "Ville" ou de créer une table à part ?

Tu enregistres seulement la population de la commune dans la table des communes et tu fais des calculs d'agrégation.

Et pour finir, est-il intéressant d'effectuer les calculs et les recoupements directement à partir du SGBD en renvoyant des vues pré-remplies ou comme actuellement, au niveau de l'application ?

Un SGBD est plus performant qu'une application développée pour effectuer des calculs en masse sur une grande quantité de données car il travaille sur une logique ensembliste.

Plutôt que d'importer dans l'application les 36000 communes de France et de faire des calculs de regroupements en parcourant le tableau de résultats, il vaut mieux laisser le SGBD faire les calcul et récupérer son résultat pour n'avoir plus qu'à l'afficher.

[Richard_35]

Bonjour Philippe et Vintofs,

ville -1,1----composer----1,n- canton -1,1----composer----1,n- arrondissement -1,1----composer----1,n- departement -1,1----composer----1,n- region

==> le document de l'IGN semble complexifier l'affaire au niveau des cantons (page 15) : il y a une entité "pseudo-canton" qui est une division d'une commune.

• Cas 1 : 1 Commune, composée de 2 cantons électoraux ;
• Cas 2 : x communes, 2 cantons électoraux divisant une des communes ;
• Cas 3 : x communes formant 1 canton électoral.

==> une des difficultés est qu'il y a une relation n,n entre "canton" et "commune" et entre "canton" et "pseudo-canton" (avec exclusivité). La solution trouvée avec le test du code canton 80 me semble proche de la bidouille... mais bon, souvent, suivant le contexte, nous sommes obligés de transiger...

Un MCD du style :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
canton -1,n---[composer]---1,n- commune_pseudo-canton -1,1---[lier]---(1,1)- pseudo-canton
                                        ^
                                        |--------------1,1---[lier]---(1,1)- commune

me semble plus juste (avec exclusion).

[CinePhil]

Bonjour Richard.
J'ai lu assez vite mais ce n'est pas ce que j'ai compris au sujet des cantons.

Le document explique que les cantons de Geofla ne sont pas les cantons électoraux mais des "pseudo-cantons" qui comprennent de 1 à plusieurs communes.

Un pseudo-canton, quant à lui, est un regroupement d'une ou plusieurs communes entières.

Le terme utilisé dans les descriptifs de contenu et de livraison reste néanmoins "canton", dans un souci de simplification dans les définitions, et d'harmonisation entre les divers documents.

Les trois cas montrent bien que les cantons au sens Geofla sont des regroupements de communes.

Cas 1 :
Une commune composée de deux cantons électoraux devient un canton Geofla.

Cas 2 :
x communes, 2 cantons électoraux divisant une commune deviennent 3 cantons Geofla (1 pour la commune coupée, 1 pour la commune isolée, 1 pour les deux communes regroupées dans le canton électoral comprenant la commune coupée).

Cas 3 :
x communes formant 1 canton électoral deviennent un canton Geofla.

C'est bien cette imbrication que notre logiciel "Carto-Dynamique" utilisait à l'INRA, ce qui était pratique pour les zooms de la France vers la région puis département puis canton ou pour faire une carte de France à l'échelle cantonnale qui était basé sur des regroupements de polygones communaux.

Maintenant, si vintofs veut tenir compte des cantons électoraux, ça va devenir plus compliqué. C'est un autre découpage géographique indépendant du découpage hiérarchique de l'INSEE, tout comme il pourrait avoir un découpage selon les pays ruraux, les bassins versants ou les régions commerciales du groupe Trucmuche.

[vintofs]

Merci messieurs,

J'y vois un peu plus clair, entre vos réponses et les cours Merise du site je pense m'en sortir !

Cordialement