Discussion :

[adpoli]

Bonjour,

J'aimerais savoir si quelqu'un connaîtrais un tuto ou aurait une idée sur la manière d'aborder une problématique multi-pays (Je développe en PHP /mySQL /Symfony / Doctrine)

Voici un la problématique dans la théorie:
J'ai un objet X qui est localiser dans une commune Y en France.J'aimerais pouvoir créer le même type d'objet, mais cette fois localiser dans une ville Z, par exemple aux états-unis.
Ces deux pays et d'autres n'ayant pas la même manière de gérer le découpage territoriale, j'aimerais trouver une solution pour rattacher un objet à une entité de localisation qui soit indépendante du pays tout en gardant l'information objet <->pays.

Et dans la pratique:
Ma personne X habite en France à paris. J'ai une table user qui contient l'id de cette personne et un commune_id correspondant à l'identifiant de la commune dans une table commune. Le lien est simple.
Il faudrait qu'une autre personne Y de la même table user puisse être rattaché à une entité city_id qui serait en relation avec une table city.
Le must serait bien évidemment que les entités city/commune (ou autre suivant le pays) soit en réalité une seule entité localisation qui renverrait par un certain moyen vers la bonne table (commune, city, ...).

Merci à ceux qui auront le courage de comprendre et de m'aider sur cette problématique

[fsmrel]

Bonsoir adpoli,

Je ne sais pas si ce qui suit vous concerne exactement. En tout cas, je définis les tables suivantes (avec SQL Server) :

• TYPE_ENTITE_GEO : type d’entité géographique (pays, code postal / commune, etc.)
• ENTITE_GEO : entité géographique (France, USA, 75005 Paris, 95448 Healdsburg, etc.)
• NOMENCLATURE : rattachement des entités géographiques entre elles. Par exemple, 75005 Paris est rattaché à France, 95448 Healdsburg est rattaché à California qui est à son tour rattaché à USA.
• PERSONNE : attributs d’une personne et son entité géographique de rattachement. Par exemple, M. Jean de la lune est rattaché à l’entité géographique 75005 Paris et M. John of the moon est rattaché à l’entité géographique 95448 Healdsburg.

Code SQL :

Types d’entités géographiques :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE TYPE_ENTITE_GEO
(
       TypeEntId    int          NOT NULL
     , TypeEntNom   varchar(45)  NOT NULL
  , CONSTRAINT TYPE_ENT_PK PRIMARY KEY (TypeEntId) 
) ;
INSERT INTO TYPE_ENTITE_GEO VALUES (1, 'pays') ;
INSERT INTO TYPE_ENTITE_GEO VALUES (2, 'province') ;
INSERT INTO TYPE_ENTITE_GEO VALUES (3, 'département') ;
INSERT INTO TYPE_ENTITE_GEO VALUES (4, 'commune') ;
INSERT INTO TYPE_ENTITE_GEO VALUES (5, 'state') ;
INSERT INTO TYPE_ENTITE_GEO VALUES (6, 'code postal / commune') ;
INSERT INTO TYPE_ENTITE_GEO VALUES (7, 'zip code / city') ;

Entités géographiques :

Code SQL :

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

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
35
36
37
38
CREATE TABLE ENTITE_GEO
(
       EntiteId     int          NOT NULL
     , EntiteNom    varchar(45)  NOT NULL
     , TypeEntId    int          NOT NULL          
  , CONSTRAINT ENTITE_GEO_PK PRIMARY KEY (EntiteId) 
  , CONSTRAINT ENT_TYPE_ENT_FK FOREIGN KEY (TypeEntId) 
          REFERENCES TYPE_ENTITE_GEO (TypeEntId)  
) ;
INSERT INTO ENTITE_GEO VALUES (1, 'France', 1) ;
INSERT INTO ENTITE_GEO VALUES (2, 'USA', 1) ;
INSERT INTO ENTITE_GEO VALUES (3, 'Île-de-France', 2) ;
INSERT INTO ENTITE_GEO VALUES (4, 'Bretagne', 2) ;
INSERT INTO ENTITE_GEO VALUES (5, 'Essonne', 3) ;
INSERT INTO ENTITE_GEO VALUES (6, 'Hauts-de-Seine', 3) ;
INSERT INTO ENTITE_GEO VALUES (7, 'Seine-Saint-Denis', 3) ;
INSERT INTO ENTITE_GEO VALUES (8, 'Finistère', 3) ;
INSERT INTO ENTITE_GEO VALUES (9, 'Ille-et-Vilaine', 3) ;
INSERT INTO ENTITE_GEO VALUES (10, '91000 Évry', 6) ;
INSERT INTO ENTITE_GEO VALUES (12, '91120 Palaiseau', 6) ;
INSERT INTO ENTITE_GEO VALUES (14, '91430 Igny', 6) ;
INSERT INTO ENTITE_GEO VALUES (16, '92290 Châtenay-Malabry', 6) ;
INSERT INTO ENTITE_GEO VALUES (18, '75005 Paris', 6) ;
INSERT INTO ENTITE_GEO VALUES (19, '75015 Paris', 6) ;
INSERT INTO ENTITE_GEO VALUES (20, '97150 Saint-Martin', 6) ;
INSERT INTO ENTITE_GEO VALUES (21, '32300 Saint-Martin', 6) ;
INSERT INTO ENTITE_GEO VALUES (30, 'Arizona', 5) ;
INSERT INTO ENTITE_GEO VALUES (31, 'California', 5) ;
INSERT INTO ENTITE_GEO VALUES (32, 'Texas', 5) ;
INSERT INTO ENTITE_GEO VALUES (33, '75460 Paris', 7) ;
INSERT INTO ENTITE_GEO VALUES (34, '75461 Paris', 7) ;
INSERT INTO ENTITE_GEO VALUES (35, '95448 Healdsburg', 7) ;
INSERT INTO ENTITE_GEO VALUES (44, '90001 Los Angeles', 7) ;
INSERT INTO ENTITE_GEO VALUES (45, '90002 Los Angeles', 7) ;
INSERT INTO ENTITE_GEO VALUES (49, '85701 Tucson', 7) ;
INSERT INTO ENTITE_GEO VALUES (50, '85702 Tucson', 7) ;
INSERT INTO ENTITE_GEO VALUES (51, '85703 Tucson', 7) ;
INSERT INTO ENTITE_GEO VALUES (55, '86505 Navajo Station', 7) ;

Nomenclature des entités géographiques :

Code SQL :

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

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
35
36
37
CREATE TABLE NOMENCLATURE
(
       EntiteId     int          NOT NULL
     , ParentId     int          NOT NULL
  , CONSTRAINT NOMENCLATURE_PK PRIMARY KEY (EntiteId) 
  , CONSTRAINT ENTITE_GEO_FK1 FOREIGN KEY (EntiteId)  
          REFERENCES ENTITE_GEO (EntiteId)  
  , CONSTRAINT ENTITE_GEO_FK2 FOREIGN KEY (ParentId)  
          REFERENCES ENTITE_GEO (EntiteId)  
) ;  
INSERT INTO NOMENCLATURE VALUES (3, 1) ;
INSERT INTO NOMENCLATURE VALUES (4, 1) ;
INSERT INTO NOMENCLATURE VALUES (5, 3) ;
INSERT INTO NOMENCLATURE VALUES (6, 3) ;
INSERT INTO NOMENCLATURE VALUES (7, 3) ;
INSERT INTO NOMENCLATURE VALUES (8, 4) ;
INSERT INTO NOMENCLATURE VALUES (9, 4) ;
INSERT INTO NOMENCLATURE VALUES (10, 1) ;
INSERT INTO NOMENCLATURE VALUES (12, 1) ;
INSERT INTO NOMENCLATURE VALUES (14, 1) ;
INSERT INTO NOMENCLATURE VALUES (16, 1) ;
INSERT INTO NOMENCLATURE VALUES (18, 1) ;
INSERT INTO NOMENCLATURE VALUES (19, 1) ;
INSERT INTO NOMENCLATURE VALUES (20, 1) ;
INSERT INTO NOMENCLATURE VALUES (21, 1) ;
INSERT INTO NOMENCLATURE VALUES (30, 2) ;
INSERT INTO NOMENCLATURE VALUES (31, 2) ;
INSERT INTO NOMENCLATURE VALUES (32, 2) ;
INSERT INTO NOMENCLATURE VALUES (33, 32) ;
INSERT INTO NOMENCLATURE VALUES (34, 32) ;
INSERT INTO NOMENCLATURE VALUES (35, 31) ;
INSERT INTO NOMENCLATURE VALUES (44, 31) ;
INSERT INTO NOMENCLATURE VALUES (45, 31) ;
INSERT INTO NOMENCLATURE VALUES (49, 30) ;
INSERT INTO NOMENCLATURE VALUES (50, 30) ;
INSERT INTO NOMENCLATURE VALUES (51, 30) ;
INSERT INTO NOMENCLATURE VALUES (55, 30) ;

Les personnes.

L’attribut EntiteId référence l’entité géographique dans laquelle cette personne réside.

Jean de la lune réside ici : 75005 Paris,
et John of the moon réside là : 95448 Healdsburg.

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE PERSONNE
(  
        PersonneId     int          NOT NULL
      , PersonneNom    Varchar(45)  NOT NULL
      , EntiteId       int          NOT NULL
  , CONSTRAINT PERSONNE_PK PRIMARY KEY (PersonneId) 
  , CONSTRAINT PERSONNE_FK FOREIGN KEY (EntiteId)  
          REFERENCES ENTITE_GEO (EntiteId)  
) ;
INSERT INTO PERSONNE VALUES (1, 'Jean de la lune', 18) ;
INSERT INTO PERSONNE VALUES (2, 'John of the moon', 35) ;

Pour connaître la localisation complète des personnes, on définit une vue récursive :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE VIEW LOCALITE (EntiteId, ParentId, Chemin)
AS
WITH V (EntiteId, ParentId, Chemin) AS 
 (
    SELECT EntiteId, 0,  CAST(EntiteNom AS VARCHAR(512))
    FROM   ENTITE_GEO AS x 
    WHERE  NOT EXISTS
           (SELECT ''
            FROM   NOMENCLATURE AS y
            WHERE  x.EntiteId = y.EntiteId)     
  UNION ALL
    SELECT   x.EntiteId, x.ParentId
           , CAST(z.EntiteNom + ' , ' + CAST(v.Chemin  AS VARCHAR(512) ) AS VARCHAR(512))
    FROM    NOMENCLATURE AS x JOIN V ON x.ParentId = v.EntiteId 
                            JOIN ENTITE_GEO AS z ON  x.EntiteId = z.EntiteId 
 )
SELECT EntiteId, ParentId, Chemin         
FROM   V AS x    
GO

Pour afficher la localisation des personnes :

Code SQL :

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

1
2
3
4
SELECT y.PersonneNom, x.Chemin AS Résidence
FROM   LOCALITE AS x
         JOIN PERSONNE AS y
           ON x.EntiteId = y.EntiteId ;

=>

Code :

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

1
2
3
4
PersonneNom         Résidence
---------------     --------------------------------
Jean de la lune     75005 Paris , France
John of the moon    95448 Healdsburg , California , USA

Maintenant, avec MySQL, peut-être CinePhil et SQLpro ont-ils des solutions...

[adpoli]

Bonjour,

Tout d'abord merci pour le changement de forum, je n'en avais pas vu de ce type c'est pourquoi j'avais poster dans une section plus axé code.

Merci fsmrel pour votre réponse, votre modèle semble très intéressant.

Le seul problème est le manque de liaison (edit:récursive) dans les table sTYPE_ENTITE_GEO / NOMENCLATURE
entre commune/departement/region ou state/city par exemple qui est ennuyeux dans mon cas.

Je pense cependant qu'une simple couche de liaison sous forme de hiérarchie (dont je ne me rappelle plus le nom) avec des bornes gauches/droites et un root_id devrait me permettre d'obtenir ce que je souhaite.

Ex:

TypeEntId int NOT NULL
TypeEntNom varchar(45) NOT NULL
ordre int NOT NULL
root_id int NOT NULL
lft int NOT NULL
rgt int NOT NULL
level int NOT NULL


Un autre "problème" que je vois est le manque de découpage sous forme de sous-table ou autre pour toutes les entités géographiques.
J'aimerais trouvé une solution avec des tables ou BDD différentes comprenant des ensembles plus restreint, le fait de stocker toutes ses entités dans une seule et même table comporte le risque de la faire grossir énormément et de rendre le tout difficilement maintenable.

Merci en tout cas pour votre réponse très clair et intéressante.

[CinePhil]

Je pense cependant qu'une simple couche de liaison sous forme de hiérarchie (dont je ne me rappelle plus le nom) avec des bornes gauches/droites et un root_id devrait me permettre d'obtenir ce que je souhaite.

C'est la modélisation d'arborescence par représentation intervallaire.

La structure qui est identique pour toutes les communes, c'est leur appartenance à un pays, même si ces termes diffèrent d'un pays à l'autre, entre autres pour des questions de langue :
Commune -1,1----Situer----1,n- Pays.

Ensuite, le découpage peut être différent selon le pays :
- départements et régions en France ;
- lander en Allemagne ;
- counties and states je crois aux USA...

Il y a même des découpages imbriqués puisqu'en France par exemple, un code postal peut couvrir plusieurs communes et une commune peut être couverte par plusieurs codes postaux :
Code_postal -1,n----Couvrir----1,n- Commune

La structure proposée par fsmrel permet peut-être de couvrir tous les cas possibles mais risque d'être difficile à interroger si tu utilises MySQL qui ne dispose pas des requêtes récursives.

L'arbre intervallaire est plus complexe à mettre en oeuvre et à alimenter (mais tous les codes nécessaires figurent sur le site de SQLPro, il "suffit" de les adapter pour ton SGBD) mais plus facile à interroger.

Si tu dois ensuite gérer des polygones représentant les entités géographiques, fais attention ! Il existe par exemple en France des enclaves départementales et trans-frontalières, ainsi que des îles. Ces polygones font donc partie d'une entité géographique mais n'y sont pas situés géographiquement !

[adpoli]

C'est tout à fait cela, j'ai déjà implémenté un système équivalent pour "archiver" des objets dans ce qui pourrait s'apparenter à des répertoires.
Bien que le code de manipulation soit légèrement plus complexe, cela permet d'améliorer grandement la vitesse de traitement des objets 'récursif'.

Concernant la géolocalisation de mes objets que je vais devoir mettre en place, elle n'ira certainement pas aussi loin que vous le décrivez, il s'agira simplement de "point" aux coordonnées uniques.

Merci pour votre aide.