Ce .zip contient des fichiers XML avec diverses informations concernant les rues et les communes de Belgique. C'est la référence officielle. Ces données sont aussi accessibles via un webservice (REST).
En ne prenant que la Wallonie, on a un peu plus que 61.000 rues et 263 communes. Ce qui va permettre de jouer un peu avec SQL.
J'ignore quelle est la meilleure manière d'injecter ces données XML dans une base de données mySQL. Alors, je le fais à ma manière... J'utilise un fichier de transformation XSL pour extraire les données qui m'intéressent et les mettre sous une forme facilement injectable dans une DB mySQL. Je crée une nouvelle base de données, best_street, Je crée deux tables (et comme je n'y connais rien, je leur donne de mauvais noms... : street_table et municipality_table.
$ mysql -u root -p
CREATE DATABASE best_street;
GRANT select, show view, lock tables, reload, replication client on *.* to 'bibi'@'localhost';
USE best_street;
CREATE TABLE street_table(
street_id INT,
street_name VARCHAR(100),
municipality_id INT,
PRIMARY KEY (street_id));
CREATE TABLE municipality_table(
municipality_id INT,
municipality_name VARCHAR(100),
PRIMARY KEY (municipality_id));
Je récupère deux fichiers dans le .zip : WalloniaStreetname20191018.xml et WalloniaMunicipality20191018.xml. Ils contiennent des trucs du genre (attention, les fichiers des rues sont très grands, Firefox s'engorge) :
et
Que l'on peut traiter (sous Linux) avec
$ xsltproc x.xls y.xml > z.sqlLes fichiers .xsl étant, pour les rues,
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:tns="http://fsb.belgium.be/mappingservices/FullDownload/v1_00"
xmlns:com="http://vocab.belgif.be/ns/inspire/"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:template match="/">
INSERT INTO street_table VALUES
<xsl:for-each select="tns:StreetResponseBySource/tns:Streetname">
(<xsl:value-of select="com:streetnameCode/com:objectIdentifier"/>,"<xsl:value-of select="com:streetname/com:spelling"/>",<xsl:value-of select="com:isAssignedBy/com:Municipality/com:objectIdentifier"/>),</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
Et pour les communes,
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:tns="http://fsb.belgium.be/mappingservices/FullDownload/v1_00"
xmlns:com="http://vocab.belgif.be/ns/inspire/"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:template match="/">
INSERT INTO municipality_table VALUES
<xsl:for-each select="tns:MunResponseBySource/tns:Municipality">
(<xsl:value-of select="com:municipalityCode/com:objectIdentifier"/>,"<xsl:value-of select="com:municipalityName/com:spelling"/>"),</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
Le résultat n'est pas parfait et quelques adaptations cosmétiques sont nécessaires pour en faire de bons fichiers SQL. Il y a aussi des 'streetname' qui contiennent des '"' et le .xsl est trop primitif pour en tenir compte. Et, en fait, ce sont des données incorrectes. Cela donne quelque chose comme :
INSERT INTO street_table VALUES (7700015,"Chemin des Prés",25005), (7700016,"Chemin des Ramiers",25005), (7700017,"Chemin des Soeurs",25005), (7700018,"Chemin du Grand Champ",25005), (7700019,"Chemin du Petit Brou",25005), ... (7741188,"Rue Jean-Louis Dumont",64074), (7741189,"Rue Joseph Nicolas",64074), (7741190,"Rue Joseph Noville",64074), (7741254,"Rue des Bâtis",64075), (7736185,"Tribomont",63035), (7736549,"Route de Manhay",63045);
et
INSERT INTO municipality_table VALUES (52074,"Aiseau-Presles"), (61003,"Amay"), (63001,"Amel"), (92003,"Andenne"), (56001,"Anderlues"), (91005,"Anhée"), ... (25110,"Waterloo"), (25112,"Wavre"), (63084,"Welkenraedt"), (84075,"Wellin"), (91141,"Yvoir");
Il faut aussi noter qu'ils n'ont pas été fichus de donner des street_id distincts couvrant les trois régions. Il y a des duplicates du côté des 36000 et 37000 (!).
Il suffit maintenant d'injecter ces données dans notre DB :
SOURCE insert_streets.sql; SOURCE insert_municipality.sql;
Notre DB est prête, on peut commencer à jouer avec...
mysql> SELECT * FROM street_table LIMIT 5; +-----------+------------------------+-----------------+ | street_id | street_name | municipality_id | +-----------+------------------------+-----------------+ | 7700001 | Aux Tiennes | 25005 | | 7700002 | Avenue des Bouleaux | 25005 | | 7700003 | Avenue des Cerisiers | 25005 | | 7700004 | Avenue des Combattants | 25005 | | 7700005 | Avenue des Pruniers | 25005 | +-----------+------------------------+-----------------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM municipality_table ORDER BY municipality_name DESC LIMIT 5; +-----------------+-------------------+ | municipality_id | municipality_name | +-----------------+-------------------+ | 91141 | Yvoir | | 84075 | Wellin | | 63084 | Welkenraedt | | 25112 | Wavre | | 25110 | Waterloo | +-----------------+-------------------+ 5 rows in set (0.00 sec) mysql>Par exemple, je peux avoir la liste de commune ayant le moins ou le plus de rues :
mysql> SELECT count(*), m.municipality_name from street_table s LEFT JOIN municipality_table m USING (municipality_id) GROUP BY municipality_id ORDER BY count(*) LIMIT 10; +----------+-------------------+ | count(*) | municipality_name | +----------+-------------------+ | 54 | Martelange | | 58 | Hélécine | | 61 | Stoumont | | 62 | Gouvy | | 63 | Lincent | | 64 | Flobecq | | 65 | Verlaine | | 65 | Rumes | | 66 | Donceel | | 66 | Oreye | +----------+-------------------+ 10 rows in set (0.38 sec) mysql> SELECT count(*), m.municipality_name from street_table s LEFT JOIN municipality_table m USING (municipality_id) GROUP BY municipality_id ORDER BY count(*) DESC LIMIT 10; +----------+----------------------------+ | count(*) | municipality_name | +----------+----------------------------+ | 2117 | Charleroi | | 1998 | Liège | | 1730 | Namur | | 1280 | Mons | | 1086 | Tournai | | 923 | La Louvière | | 812 | Ottignies-Louvain-la-Neuve | | 738 | Wavre | | 677 | Mouscron | | 649 | Ath | +----------+----------------------------+ 10 rows in set (0.10 sec) mysql>
Cette dernière requête étant :
SELECT count(*), m.municipality_name from street_table s
LEFT JOIN municipality_table m USING (municipality_id)
GROUP BY municipality_id
ORDER BY count(*)
DESC LIMIT 10;
