The idea was to create a map of France and have a search for places and postal codes and visualize the results on the map. To get the data I queried the german Wikipedia.

The Wikipedia contains a list of all muncipalities in France in German. The english wikipedia is also quite complete but the lists of muncipalities within one department is split in a table of several columns. That does not make it so easy to extract the content. I didn't query the french Wikipedia because I am not so familiar with the language. Still I used the french Wikipedia to confirm some data.

First, we fetch the list of the departments with the links to each department:

wget -O kategorie_gemeinden_fr.html  https://de.wikipedia.org/wiki/Kategorie:Liste_(Gemeinden_in_Frankreich)

From that page we need to extract the link list, actually the link to each department:

cat kategorie_gemeinden_fr.html |\
  sed -e 's|<a|\n<a|' -e 's|</a>|</a>\n|' | \
  grep 'href="/wiki/Liste_der_Gemein' | \
  perl -pe 's/.*href="(\/wiki\/Liste_der_Gemein[^"]*)".*/\1/g' > liste_der_gemeinden.txt

Having the list of links to each department wiki page, we now fetch the list of muncipalities for each deplartment. In the table we do not fetch all columns, mainly not the population because that depends on the time fetched and seems more likable to change over and over again.

cat liste_der_gemeinden.txt | while read l
  do k=$(echo $l | perl -pe 's/.*?Gemeinden_(im|in(_der)?|auf)_(.*?)/\3/')
  python wiki2table.py https://de.wikipedia.org$l --table 1 --cols 1,2,3,5,8 --tag a > $k.json
done

The wiki2table.py script is taken from 4000er summits article.

Now we have a list of json files (they are encoded, the file for the department Aisne looks like D%C3%A9partement_Aisne.json) which contains a list of objects, one entry for each table row. Many muncipalities have two entries for the arrondissement, one actual and on from the time before the reformation as of 2017. Without any arguments the extract script would fetch the plain text data and remove all html tags and the content inside. In the command above we said to keep the anchor tag. Therefore, we have the most recent entry for the arrondissement. The older is stripped because it's inside a <small> tag, which is wipped out with all the containing content, i.e. the link to the arrondissement as well.

Now we use the custom tailored script department_details.py which was done just for this use case. The script fetches the relevant information from each entry of a muncipality. At least we need the link to the munciality page itself to fetch the geo coordinates. The result should be an entry with the muncipality name, the link to the wikipdia page, the area, post code, INSEE code, and department.

#!/usr/bin/env python

import re, sys, json;
from urllib.parse import unquote, quote
from subprocess import Popen, PIPE


def encodeWikiLink(link: str) -> str:
    link = link.replace(' ', '_')
    parts = link.split('/')
    parts[-1] = quote(parts[-1])
    return '/'.join(parts)

def fetchCoordinates(link: str) -> list:
    link = encodeWikiLink(link)
    if link.startswith('//'):
        link = 'https:' + link
    try:
        process = Popen(["python", "wiki2geojson.py", f"{link}"], stdout=PIPE)
        (output, err) = process.communicate()
        exit_code = process.wait()
        if exit_code == 0:
            geojson = json.loads(output)
            return [
                geojson['features'][0]['geometry']['coordinates'][1],
                geojson['features'][0]['geometry']['coordinates'][0]
            ]
    except Exception as e:
        print(f"Error fetching coordinates for {link}: {e}")
        pass
    return [0, 0]

try:
    with open(sys.argv[1], 'r') as fp:
        try:
            data = json.load(fp)
        except ValueError as e:
            print("Could not decode json: " + str(e))
            sys.exit(1)
except IOError as e:
    print("Could not decode json: " + str(e))
    sys.exit(1)

for entry in data:
    department = unquote(sys.argv[1].replace('.json', '').replace('_', ' '))
    x = re.search('.*href="(.*?)".*>(.*?)</a>', entry['Gemeinde'])
    muncipality = x.group(2)
    muncipalityLink = x.group(1)
    arrondissment = ''
    arrondissmentLink = ''
    postcode = []
    for key in entry:
        if key == 'PLZ' or "Post" in key:
            postcode = entry[key].split(' ')
        elif 'Arrondissement' in key:
            x = re.search('.*href="(.*?)".*>(.*?)</a>', entry[key])
            arrondissment = x.group(2)
            arrondissmentLink = x.group(1)
    insee = entry['Code INSEE']
    area = entry['Fl\u00e4che in km\u00b2'].replace('.', '').replace(',', '.')
    [lat, lon] = fetchCoordinates(muncipalityLink)

    newEntry = [
        muncipality,
        muncipalityLink,
        arrondissment,
        arrondissmentLink,
        department,
        postcode,
        insee,
        area,
        lat,
        lon
    ]
    print(newEntry)

The column (and therefore the key in the json) of the post code used different labels at some of the department pages, therefore we needed to have some search over the possible keys and extract the correct information. The same counts for the Arrondissement because some departments (like the Metropole of Lyon) do not have Arrondissements.

for i in `ls *.json`; do ./department_details.py $i >> db.txt ; done

The script may run for several hours because there is one request done for each muncipality, fetching the geocoordinates with the wiki2geojson.py script (see Fetch geo data from Wikipedia).

Because I interrupped the script a couple of times I had 3 dbX.json files.

For paris I created a paris.db manually. The Territory of Belfort had to be fetched also a bit different because the table with the muncipalities differs from the others. It includes the old German name of the muncipality, hence the column numbers are different. I also remember to have fetched Martinique and Mayotte separately.

Some coordinates are not fetched. A quick check revealed that the page in question actually existed in Wikipedia and even has coordinates. A separate call to the wiki2geojson.py script also fetched the coordinates. So it seems to be a runtime issue and we try to fetch all coordinates again where they are missing. First create a list of the entries with the missing coordinates:

for i in `ls db*.txt`; do cat $i | grep "0, 0]" >> missing-ccords.txt; done

I also had to create a script that takes each line of the "db" file, and replace the zero coordinates with the actual fetched ones.

#! /usr/bin/env python3

import sys, json
from urllib.parse import quote
from subprocess import Popen, PIPE


def getWikiUrlFromLine(line):
    entry = line.split(',')
    wikiurl = entry[1].strip()[1:-1] # Remove the surrounding quotes
    parts = wikiurl.split('/')
    parts[-1] = quote(parts[-1])
    return '/'.join(parts)

def fetchCoordinates(wikiurl):
    try:
        process = Popen(["python", "wiki2geojson.py", f"https:{wikiurl}"], stdout=PIPE)
        (output, err) = process.communicate()
        exit_code = process.wait()
        if exit_code == 0:
            geojson = json.loads(output)
            return [
                geojson['features'][0]['geometry']['coordinates'][1],
                geojson['features'][0]['geometry']['coordinates'][0]
            ]
    except Exception as e:
        print(f"Error fetching coordinates for {wikiurl}: {e}")
    return [0, 0]

# Main program:
try:
    with open(sys.argv[1], 'r', encoding='utf-8') as fp:
        while True:
            line = fp.readline()
            if not line:
                break
            line = line.strip()
            wikiurl = getWikiUrlFromLine(line)
            lon, lat = fetchCoordinates(wikiurl)
            print(line.replace(', 0, 0]', f', {lat}, {lon}]'))
except IOError as e:
    print("Could not decode json: " + str(e))
    sys.exit(1)

Unfortunately, not all coordinates where fetched in the second run either. I repeated the step as follows a couple of more times:

./fetch_coords_from_dbfile.py missing-X.txt > missing-X-withcord.txt
cat missing-X-withcord.txt | grep "0, 0]" > missing-Y.txt

with X and Y being natural numbers, increasing by one and using it in the subsequent call. At the end I got stuck with three lines that couldn't fetched, where I had to correct the wiki urls:

['Donnay', '//de.wikipedia.org/wiki/Donnay_(Gemeinde)', 'Caen', '//de.wikipedia.org/wiki/Arrondissement_Caen', 'Département Calvados', ['14220'], '14226', '11.19', -0.11277777777778, 48.915555555556]
['Lasse', '//de.wikipedia.org/wiki/Lasse_(Pyrénées-Atlantiques)', 'Bayonne', '//de.wikipedia.org/wiki/Arrondissement_Bayonne', 'Département Pyrénées-Atlantiques', ['64220'], '64322', '14.79', -1.25888888889, 43.1581]
['Nay', '//de.wikipedia.org/wiki/Nay_(Pyrénées-Atlantiques)', 'Pau', '//de.wikipedia.org/wiki/Arrondissement_Pau', 'Département Pyrénées-Atlantiques', ['64800'], '64417', '5.27', -0.26111111111111, 43.181111111111]

In this example, the urls are corrected and the coordinates are fetched.

All these missing-X-withcord.txt files need to be merged into one and only the lines with the coordinates must be contained. The 0, 0 and the lines with the error message need to be purged.

for i in 1 2 3 4 5 6 7; do
cat missing-${i}-withcord.txt | grep -v "0, 0]" | grep -v "Error fetching coordinates" >> merged-with-coordinates.txt
done

We need to check again which files we have and if there are no left overs from some subsequent runs. We will figure this out by writing all collected data into a database. The database schema for the tables and columns is in the file db_schema.sql.

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

SET NAMES utf8mb4;

DROP TABLE IF EXISTS `arrondissement`;
CREATE TABLE `arrondissement` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `wikiurl` varchar(255) NOT NULL,
  `department` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `department` (`department`),
  CONSTRAINT `arrondissement_ibfk_1` FOREIGN KEY (`department`) REFERENCES `department` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `id` int(11) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


DROP TABLE IF EXISTS `place`;
CREATE TABLE `place` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `wikiurl` varchar(255) NOT NULL,
  `lat` float NOT NULL,
  `lon` float NOT NULL,
  `insee` char(5) NOT NULL,
  `area` float NOT NULL,
  `department` int(11) unsigned NOT NULL,
  `arrondissement` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `insee` (`insee`),
  KEY `department` (`department`),
  KEY `arrondissement` (`arrondissement`),
  CONSTRAINT `place_ibfk_1` FOREIGN KEY (`department`) REFERENCES `department` (`id`),
  CONSTRAINT `place_ibfk_2` FOREIGN KEY (`arrondissement`) REFERENCES `arrondissement` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


DROP TABLE IF EXISTS `zipcode`;
CREATE TABLE `zipcode` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `zip` char(5) NOT NULL,
  `place` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `place_zip` (`place`, `zip`),
  KEY `place` (`place`),
  CONSTRAINT `zipcode_ibfk_1` FOREIGN KEY (`place`) REFERENCES `place` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Because the department and INSEE code are unique I used them for indexing and primary key. The department.id field also has no autoincrement. Note that for Corsica the department numbers are 2A and 2B, in the database I put them with 201 and 202. The oversees theritories use a three digit code: 971 and 972 which must also be handled different. All the other deplartment codes can be derived from the first two digits of the INSEE code.

Now the data from the various db files need to be pumped into the rational database, e.g. a MySQL or MariaDB. Any other DB would work as well but then the schema needs to be adjusted and also the script needs a different driver for accessing the other DB engine. I used a MariaDB. To insert the data from the files, I created the script write_dbfile_to_db.py that gets the db file as an argument.

#! /usr/bin/env python3

import mariadb
import sys
from urllib.parse import quote
from subprocess import Popen, PIPE


# Connect to MariaDB Platform
try:
    conn = mariadb.connect(
        user="root",
        password="root",
        host="0.0.0.0",
        port=3306,
        database="fr_geodata"
    )
except mariadb.Error as e:
    print(f"Error connecting to MariaDB Platform: {e}")
    sys.exit(1)

# Get Cursor
cursor = conn.cursor()

departmentSaved = {} # for caching.

def getEntryFromLine(line):
    labels = [
        'muncipality',
        'muncipalityLink',
        'arrondissment',
        'arrondissmentLink',
        'department',
        'postcode',
        'insee',
        'area',
        'lat',
        'lon'
    ]
    newEntry = {}
    entry = line.strip()[1:-1].split(', ')
    k = 0
    zipcodeList = []
    for i in range(len(entry)):
        cur = entry[i].strip()
        if cur[0] == "'" and cur[-1] == "'":
            cur = cur[1:-1] # Remove the surrounding quotes
        if i == 5: # zip code list
            if cur[-1] == ']':
                cur = cur[1:-1].replace("'", '')
                if '–' in cur:
                    newEntry['postcode'] = list(map(str, range(int(cur[0:cur.index('–')]), int(cur[cur.index('–') + 1:]) + 1)))
                else:
                    newEntry['postcode'] = cur.split(',')
            else:
                zipcodeList = []
                zipcodeList.append(cur.replace('[', '').replace("'", '').replace(',', ''))
            continue
        elif len(zipcodeList) > 0:
            zipcodeList.append(cur.replace(']', '').replace("'", '').replace(',', ''))
            if cur[-1] == ']':
                newEntry['postcode'] = zipcodeList
                k = len(zipcodeList) - 1
                zipcodeList = []
            continue
        if (cur[0:2] == '//'): # url
            parts = cur.split('/')
            parts[-1] = quote(parts[-1])
            cur = 'https:' + '/'.join(parts)
        newEntry[labels[i - k]] = cur
    return newEntry

def getDeptarmentFromInsee(insee: str) -> int:
    prefix = insee[0:2]
    if prefix == '97':
        return int(insee[0:3])
    if prefix == '2A':
        return 201
    if prefix == '2B':
        return 202
    return int(insee[0:2])

def saveToDB(entry):
    # Must have coordinates to save
    if entry['lat'] == '0' and entry['lon'] == '0':
        print(f"Skipping entry {entry} due to missing coordinates.")
        return
    # Save department
    departmentId = getDeptarmentFromInsee(entry['insee'])
    if departmentId not in departmentSaved:
        departmentSaved[departmentId] = saveDepartment(departmentId, entry['department'])

    # Save arrondissement
    arrondissementId = saveArrondissement(entry['arrondissment'], entry['arrondissmentLink'], departmentId)

    # Save place
    try:
        cursor.execute(
            "INSERT INTO place (name, wikiurl, lat, lon, insee, area, department, arrondissement) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
            (entry['muncipality'], entry['muncipalityLink'], entry['lat'], entry['lon'], entry['insee'], entry['area'], departmentId, arrondissementId)
        )
        conn.commit()
        placeId = cursor.lastrowid
    except mariadb.Error as e:
        print(f"Error saving place {entry}: {e}")
        return

    # Save zipcodes
    for zipcode in entry['postcode']:
        try:
            if zipcode.strip() == '':
                continue
            cursor.execute(
                "INSERT INTO zipcode (zip, place) VALUES (?, ?)",
                (zipcode, placeId)
            )
            conn.commit()
        except mariadb.Error as e:
            print(f"Error saving zipcode {zipcode}: {e}")

def saveArrondissement(name, wikiurl, departmentId):
    cursor.execute("SELECT id FROM arrondissement WHERE name = ? AND department = ?", (name, departmentId))
    result = cursor.fetchone()
    if result:
        return result[0]
    else:
        # Insert new arrondissement
        try:
            cursor.execute(
                "INSERT INTO arrondissement (name, wikiurl, department) VALUES (?, ?, ?)",
                (name, wikiurl, departmentId)
            )
            conn.commit()
            return cursor.lastrowid
        except mariadb.Error as e:
            print(f"Error saving arrondissement {name}: {e}")
            return None

def saveDepartment(id, name):
    try:
        cursor.execute("INSERT INTO department (id, name) VALUES (?, ?)", (id, name,))
        conn.commit()
        return cursor.lastrowid
    except mariadb.Error as e:
        print(f"Error saving department {name}: {e}")
        return None

# Main program:
try:
    with open(sys.argv[1], 'r', encoding='utf-8') as fp:
        while True:
            line = fp.readline()
            if not line:
                break
            entry = getEntryFromLine(line)
            if entry is None:
                continue
            saveToDB(entry)
except Exception as e:
    print("Error: " + str(e))
    sys.exit(1)

The "db" files are not in a real json format anymore because a print(entry) for a python dictionary does not use double quotes. That cost me more time to read the lines and extract the content into the properties again. Mainly the postcode column was a bit tricky to handle.

At the end, the database should be filled. The HTML with the leaflet was actually mainly created with the help of AI. I just did a prompt to describe the user interface. I haven't worked with Leaflet before but did not wonder that the AI came up with this library. It seems to be the most popular nowadays for displaying maps.

After having the application running I wanded to do a cross check at some examples whether the data seems to be plausible. There were a lot errors about an existing key, which happens, when an entry is added twice, because I didn't clean the db files for double entries. Because of the index set in the database and the foreign keys, there should not be no double entries.

When doing the cross check, I discovered some points show up in Ethiopia. This was because I accidently mixed latitude and longitude. Thas had to be corrected in the source data, before truncating the tables and write the data again.

Some points showed up in the very north, because of a latitude > 180. This was a bug in the wiki2geodata.py script. The pattern matching of the coordinates had to be done in a different order. Muncipalities close to Paris had an integer for the longitude and a decimal for the latitude. Matching natural numbers first only used the digits after the decimal point.

The easiest way to check the data was to enter the first two digits of the postal code. The dots showing up in the map should roughly form the department. At least there should be no points show up anywhere else. Like this, I corrected a handful of places where apparently the German wikipedia had an error on their pages (twice a wrong zip code, a misplaced village and also a wrong link to a muncipality - Saint-Nazaire exists at the estuary of the Loire but there is another place of the same name in the Pyrenees).

Becuase the search is limited to 200 results, I only checked the these first 200 entries of each department, because the others are not displayed. I would have to temporarily change the script to display all places in order to verify that they seem to be correct.

The final application France Geodata works now as I wanted it to have.