Données géographiques suisses MAT[CH].ch

Les données pour la Suisse ont été trouvées sur le site officiel  MAT[CH].ch.

Numéros postaux (NPA)

 MAT[CH]zip fournit la liste exhaustive des numéros postaux de Suisse avec de nombreuses autres informations. La liste PLZ Plus est la plus complète. Voici donc les étapes pour son importation dans une base de données MySQL.

Commençons par créer la structure de la base de données à l’aide du fichier  plzplus_structure.sql (6 KB).

Il faut ensuite importer le fichier de données  plz_p1_YYYYMMDD.txt que l’on peut télécharger sur le site. Ma démarche s’est faite avec la version du 1er janvier 2008. Voilà pourquoi j’utilise le suffixe 20080101 en lieu et place de YYYYMMDD :

LOAD DATA LOCAL INFILE '/tmp/plz_p1_20080101.txt'
   INTO TABLE PLZ_P1
   FIELDS TERMINATED BY '\t'
   LINES TERMINATED BY '\n'
   (onrp, plz_type_code, postcode, postcode_suffix,
   location_description_18, location_description_27,
   canton, language_code, language_code_alternative,
   available_in_sortierfile, postcode_end, bfsnr,
   validity) ;

Faisons de même avec le fichier de données  plz_p2_YYYYMMDD.txt :

LOAD DATA LOCAL INFILE '/tmp/plz_p2_20080101.txt'
   INTO TABLE PLZ_P2
   FIELDS TERMINATED BY '\t'
   LINES TERMINATED BY '\n'
   (onrp, laufnummer, description_type, language_code,
   description_18, description_27) ;

Et enfin avec le fichier de données  plz_c_YYYYMMDD.txt (la dernière version disponible est celle du 1er décembre 2007) :

LOAD DATA LOCAL INFILE '/tmp/plz_c_20071201.txt'
   INTO TABLE PLZ_COMMUNITIES
   FIELDS TERMINATED BY '\t'
   LINES TERMINATED BY '\n'
   (bfsnr, name, canton, agglomeration) ;

Comme les listes contiennent des informations invalides pour leur utilisation immédiate dans une base de données relationnelle, nous allons corriger ces problèmes avant de mettre en place les contraintes sur les tables :

UPDATE PLZ_P1 SET language_code_alternative = NULL
   WHERE language_code_alternative = 0 ;
 
UPDATE PLZ_P1 SET postcode_end = onrp
   WHERE postcode_end = 0 ;
 
UPDATE PLZ_COMMUNITIES SET agglomeration = NULL
   WHERE agglomeration = 0 ;

Le 1er janvier 2008, je suis tombé sur des erreurs dans la base de données qui m’ont empêché de créer les contraintes décrites plus loin. Les requêtes suivantes permettent de les corriger.

Dans un cas, le numéro d’agglomération ne correspond pas aux valeurs prévues (deux équipes indépendantes ont probablement collaboré lors de la collecte des données) :

UPDATE PLZ_P1 SET bfsnr = 3702 WHERE onrp = 4256 ;
UPDATE PLZ_P1 SET bfsnr = 3706 WHERE onrp = 4257 ;

Dans l’autre cas, la valeur de postcode_end dans la table PLZ_P1 pointe sur des enregistrements inexistants :

SELECT * FROM PLZ_P1 WHERE postcode_end NOT IN (
   SELECT onrp FROM PLZ_P1
) ;
 

Ce sont 11 localités dans le canton de Zurich. Étant donné que je n’ai pas la possibilité de trouver la valeur à corriger, je propose de « boucler l’enregistrement sur lui-même » :

UPDATE PLZ_P1 SET postcode_end = onrp
   WHERE onrp IN (
      4489, 4490, 4491, 4492, 5159, 6283,
      6284, 6670, 7419, 7486, 8218
   ) ;
 

Il ne reste plus qu’à créer les contraintes :

ALTER TABLE PLZ_P1
   ADD FOREIGN KEY ( plz_type_code )
      REFERENCES PLZ_TYPES ( code ) ,
   ADD FOREIGN KEY ( canton )
      REFERENCES PLZ_CANTONS ( canton ) ,
   ADD FOREIGN KEY ( language_code )
      REFERENCES PLZ_LANGUAGES ( code ) ,
   ADD FOREIGN KEY ( language_code_alternative )
      REFERENCES PLZ_LANGUAGES ( code ) ,
   ADD FOREIGN KEY ( postcode_end )
      REFERENCES PLZ_P1 ( onrp ) ,
   ADD FOREIGN KEY ( bfsnr )
      REFERENCES PLZ_COMMUNITIES ( bfsnr ) ;
 
ALTER TABLE PLZ_P2
   ADD FOREIGN KEY ( language_code )
      REFERENCES PLZ_LANGUAGES ( code ) ;
 
ALTER TABLE PLZ_COMMUNITIES
   ADD FOREIGN KEY ( canton )
      REFERENCES PLZ_CANTONS ( canton ) ;

Fichiers à télécharger

Noms des rues

 MAT[CH]street fournit gratuitement une liste partielle des rues de Suisse. Voici les étapes pour son importation dans une base de données MySQL.

Commençons par préparer le fichier de données  styDDMMYYYY.txt que l’on peut télécharger sur le site. Ce fichier contient des enregistrements à taille fixe de 100 octets. Nous allons donc le transformer en séparant les champs par des tabulateurs, afin de permettre un chargement aisé dans la base de données. Ma démarche s’est faite avec la version du 17 décembre 2007. Voilà pourquoi j’utilise le suffixe 17122007 en lieu et place de DDMMYYYY : 

$ gawk 'BEGIN{
    FIELDWIDTHS = "6 4 1 25 6 1 4 2 4 2 25 10 2 2 1"
  }
  {
    print $1 "\t" $2 "\t" $3 "\t" $4 "\t" $5 "\t" $6 "\t" $7 "\t" $8 "\t"
          $9 "\t" $10 "\t" $11 "\t" $12 "\t" $13 "\t" $14
  }’ sty17122007.txt | sed -r ’s/ +\t/\t/g’ > sty17122007.tab

Vous remarquerez que j’en ai profité (avec sed) pour supprimer les espaces inutiles à la fin des enregistrements. On ne fait jamais trop bien…

Maintenant que c’est fait, nous pouvons créer la structure de la base de données à l’aide du fichier  sty_structure.sql (1 KB).

Remarque : J’ai réutilisé la table des langues des numéros postaux. Si vous importez les données dans la même base de données, pensez à supprimer la création de la table PLZ_LANGUAGES ainsi que l’insertion des données initiales.

Importons les données :

LOAD DATA LOCAL INFILE '/tmp/sty17122007.tab'
   INTO TABLE STY_STREETS
   FIELDS TERMINATED BY '\t'
   LINES TERMINATED BY '\n'
   (no, postcode_base, language_code, name_upcase,
   postcode_addressing, splitting_code, house_nr,
   house_nr2, house_nr3, house_nr4, name, name_root,
   street_type, prefix_code) ;

Comme les listes contiennent des informations invalides pour leur utilisation immédiate dans une base de données relationnelle, nous allons corriger ces problèmes avant de mettre en place les contraintes sur les tables :

UPDATE STY_STREETS SET language_code = NULL WHERE language_code = 0 ;

Pour éviter de créer des tables supplémentaires, voici la signification du code de séparation (splitting_code dans la base de données) :

  1. Numéros de maisons pairs et impairs
  2. Numéros de maisons pairs
  3. Numéros de maisons impairs

Pour les autres codes, référez-vous à la E description détaillée des données.

Il ne reste plus qu’à créer l’unique contrainte :

ALTER TABLE STY_STREETS
   ADD FOREIGN KEY ( language_code )
      REFERENCES PLZ_LANGUAGES ( code ) ;

Fichiers à télécharger