Ob bei der Auswahl des bestmöglichen Standortes für die neue Restaurantfiliale, bei der Planung und Anbindung von Solar- und Windkraftanlagen oder bei der Anzeige personalisierter Web-Inhalte anhand des Aufenthaltsortes: Geografische Daten sind der Schlüssel zur Lösung unzähliger Optimierungsprobleme in den verschiedensten Branchen und bilden einen integralen Bestandteil in zahlreichen modernen digitalen Wertschöpfungsketten. Ihre Struktur und die Vorgehensweise bei der Informationsextraktion unterscheiden sich jedoch grundlegend von denen herkömmlicher Datenquellen, sodass ihre Verwertung oftmals eine besondere Herausforderung darstellt.
Dieser Beitrag soll als technischer Deep Dive einen praktischen Leitfaden zum Umgang mit diesem außergewöhnlichen Datentypus bieten.
Vorstellung des Technologie-Stacks
Relationale Datenbanken sind seit Jahrzehnten bewährt und weit verbreitet. Sie zeichnen sich durch ihre hohe technologische Reife und Zuverlässigkeit besonders aus. Durch Plug-Ins kann ihr Funktionsumfang flexibel erweitert werden, um so auch Aufgabenstellungen abzudecken, welche über das klassische relationale Datenmodell hinausgehen. Das betrifft nicht nur Daten in Form von Zahlen und Texte. So ist z.B. die vielfach eingesetzte Open-Source-Datenbank PostgreSQL mithilfe einer Erweiterung namens PostGIS in der Lage, geografische Daten zu speichern und zu verarbeiten. GIS steht dabei für “Geografisches Informationssystem”. Hierdurch eröffnen sich vielfältige neue Möglichkeiten, um die komplexen Strukturen von Geodaten in Analyse-Szenarien mit anderen Datentypen zu kombinieren und für KI-Anwendungen nutzbar zu machen. Wie genau die Verarbeitung sowie die Analyse geografischer Daten funktionieren und welche Besonderheiten dabei zu beachten sind, wird im Folgenden Schritt für Schritt detailliert beschrieben. Die hierbei verwendeten Konsolenbefehle und die Codebasis werden als Snippets frei zur Verfügung gestellt. Interessierte Leser und Leserinnen sind wärmstens eingeladen diese als direkten Einstieg zu nutzen, um selbst in die erkenntnisreiche Welt der Geo-Analytics einzutauchen.
Wir bei der DDG verwenden für die Datenverarbeitung:
- PostgreSQL: Das Datenbankmanagement-System
- PostGIS: Die geografische Erweiterung für PostgreSQL
- DBeaver Community: Ein Open-Source-Datenbank-Tool
- QGIS: Ein Open-Source-GIS-Tool, u.a. für Visualisierung
Als kleines praktisches Beispiel zur Einführung in die Thematik dient uns die Analyse der Wahlergebnisse der letzten Bundestagswahl, wofür wir auf eine öffentlich frei zugängliche Datengrundlage zurückgreifen.
Material
Unser Datensatz enthält folgende Dateien, welche von offiziellen Stellen kostenfrei zum Download bereitgestellt werden:
- Umrisse der PLZ-Gebiete (in Form von Shapefiles in “5stellig.shp.zip”)
- Umrisse der Wahlkreise (in Form von Shapefiles, Variante “WGS84”)
- Einwohneranzahl je PLZ (als CSV-Datei “plz_einwohner.csv”)
- Zweitstimmen der Bundestagswahl 2021 je Wahlkreis (als CSV-Datei “kerg2.csv”)
Aufgabe
Unsere Fragestellung soll lauten:
Gibt es einen Zusammenhang zwischen Wahlergebnis und Bevölkerungsdichte?
Insbesondere soll in diesem Beispiel überprüft werden, ob die weitverbreitete Annahme, dass Unionsparteien CDU/CSU eher in bevölkerungsarmen (ländlichen) Wahlkreisen und die Grünen eher in bevölkerungsreichen (städtischen/urbanen) Wahlkreisen gewählt werden, durch eine geo-demografische Analyse der offiziellen Daten statistisch bestätigt werden kann.
Problemstellung
Die Problematik besteht darin, dass keine Bevölkerungsdichten der Wahlkreise verfügbar sind. Also müssen wir diese Größe selbst berechnen. Es gilt:
Bevölkerungsdichte = Bevölkerung/Fläche
Leider liegen uns noch nicht einmal die Bevölkerungsanzahl und Fläche je Wahlkreis vor. Zwar existieren immerhin Bevölkerungszahlen der PLZ-Gebiete. Erschwerend kommt jedoch hinzu, dass es keine (eindeutige) Zuordnung von Wahlkreis und PLZ-Gebiet gibt.
Lösung
Flächen der Wahlkreise
Aus den Umrissen der Wahlkreise lassen sich direkt in der Datenbank mithilfe von PostGIS-spezifischen Befehlen die Flächen der Wahlkreise berechnen.
Bevölkerungen der Wahlkreise
Außerdem existieren Umrisse der PLZ-Gebiete, sodass man Wahlkreise und PLZ-Gebiete übereinander legen kann. Durch die Überschneidungen zwischen Wahlkreisen und PLZ-Gebieten lässt sich wiederum auf die Bevölkerung des Wahlkreises schließen (wenn auch nur approximativ).
Vorgehensweise
Um die beiden oben genannten Größen zu ermitteln, müssen wir also mehrere statistische und geografische Daten miteinander verknüpfen und bearbeiten. Danach ist alles vorhanden, um die Bevölkerungsdichte je Wahlkreis zu ermitteln und mit den Wahlergebnissen zu vergleichen.
Die notwendigen Schritte sind nachfolgend aufgeführt:
Statistische Informationen
Der Import der statistischen Informationen über PLZ-Gebiete (Einwohneranzahl) und Wahlkreise (Stimmen) kann über die Import-Funktion von DBeaver erfolgen. Das Ergebnis sind die statistischen Rohdaten in der Datenbank:
Struktur der Tabelle Wahlkreis_Stimmen (Tabelle links) und Ausschnitt aus der Tabelle Plz_Einwohner (Tabelle rechts)
Geografische Informationen
Vorbereitung
Wir installieren die PostGIS-Erweiterung in eine bestehende PostgreSQL-Installation, aktivieren die Erweiterung und importieren die geografischen Daten in die Datenbank:
Installation von PostGIS unter Ubuntu (im Terminal):
sudo apt install postgis
Aktivierung von PostGIS in der Datenbank (in der SQL-Konsole):
CREATE EXTENSION postgis;
Import der Umrisse
Nun ist der Import der Shapefiles in die Datenbank möglich. Dank der aktivierten Erweiterung “versteht” PostgreSQL nun geografische Daten und kann diese speichern, sonst käme es beim Import zu einer Fehlermeldung.
Import der PLZ-Gebiete (im Terminal):
shp2pgsql -s 4326 -I -G plz-5stellig.shp public.plz_geo > plz_geo_import.sql
psql -h localhost -p 5432 -d geo_db -U postgres -f plz_geo_import.sql
Die Umrisse der PLZ-Gebiete liegen nun in der Spalte “geog” mit dem geografischen Datentyp Multipolygon vor. “Multipolygon” bedeutet, dass ein Gebiet aus mehreren Teilgebieten bestehen kann.
Die Polygone setzen sich aus einzelnen Punkten zusammen. Die Koordinaten der einzelnen Punkte lassen sich in DBeaver als Text anzeigen:
Alternativ kann DBeaver die Umrisse auch direkt in einer Karte anzeigen:
Wir wiederholen nun den Vorgang für die Wahlkreise.
Import der Wahlkreis-Shapefiles (im Terminal):
shp2pgsql -s 4326 -I -G Geometrie_Wahlkreise_20DBT_VG250_geo.shp public.wahlkreis_geo > wahlkreis_geo_import.sql
psql -h localhost -p 5432 -d geo_db -U postgres -f wahlkreis_geo_import.sql
Visualisierung über QGIS
Die Open-Source-Software QGIS dient u.a. der Visualisierung von geografischen Informationen und ist hierbei deutlich mächtiger als DBeaver.
QGIS-Layer
In einem QGIS-Projekt können Verbindungen zu Datenbanken hergestellt und aus den dort enthaltenen geografischen Daten sogenannte “Layer” dem Projekt hinzugefügt werden.
Diese Layer enthalten alle (insb. geografischen) Daten, die von QGIS zur Verarbeitung und Visualisierung notwendig sind. Layer können direkt komplette Tabellen abbilden oder aus SQL-Abfragen auf diese Tabellen erzeugt werden. Verschiedene Layer können übereinander gelegt werden und miteinander kombiniert werden. Design und Beschriftungen der Umrisse eines Layers können dynamisch aus den Daten erfolgen.
Darstellung der Umrisse
Nachfolgend sieht man die Umrisse der Wahlkreise und PLZ-Gebiete als QGIS-Layer.
Hinweis: Zur unverzerrten Darstellung muss man das Koordinatenbezugssystem umstellen auf “EPSG:3857” (WGS 84 / Pseudo-Mercator).
Darstellung PLZ-Gebiete
Darstellung Wahlkreise
Beide Darstellungen übereinander gelegt
Zu beachtende Sonderfälle
Die am Anfang aufgestellte Behauptung, dass PLZ-Gebiete und Wahlkreise nicht direkt einander zugeordnet werden können, soll hier anhand eines Sonderfalles veranschaulicht werden.
So gehört das PLZ-Gebiet 82152 gleichermaßen zu den beiden Wahlkreisen 221 und 224.
Das PLZ-Gebiet 67468 gehört nicht nur zu zwei Wahlkreisen (208 und 209), sondern ist auch noch zweiteilig.
Das PLZ-Gebiet 55483 schafft es gar auf 6 Teilgebiete, die sich auf 2 Wahlkreise (200 und 201) verteilen.
Unsere nachfolgenden SQL-Abfragen müssen alle diese Spezialfälle abdecken.
Bearbeitung der Fragestellung
Da die Zuordnung der Einwohneranzahl über die PLZ erfolgt, müssen zuerst PLZ-Gebiete und Wahlkreise verknüpft werden. Das erfolgt über die Schnittmenge der beiden Gebiete. Im ersten Schritt wird jede Kombination der sich überschneidenden PLZ-Gebiete und Wahlkreise ermittelt:
- Ermittlung der Kombinationen der sich überschneidenden PLZ-Gebiete und Wahlkreise:
SELECT
plz.plz,
plz.gid AS plz_gid,
ST_AREA(plz.geog, TRUE) AS plz_fläche,
ST_AREA(wkr.geog, TRUE) AS wkr_fläche,
wkr.wkr_nr
FROM wahlkreis_geo wkr
JOIN plz_geo plz ON ST_INTERSECTS(wkr.geog, plz.geog)
Im nächsten Schritt wird ermittelt, zu wie viel Prozent sich das PLZ-Gebiet flächenmäßig in einem Wahlkreis befindet. Diese Hilfsgröße ermöglicht später den Dreisatz für die Verteilung der Einwohneranzahl der PLZ-Gebiete auf die Wahlkreise.
- Ermittlung der prozentualen Schnittmengen von Wahlkreis und PLZ-Gebiet:
SELECT
pwk.plz,
pwk.wkr_nr,
ST_AREA(ST_INTERSECTION(ST_Buffer(pg.geog, 0.0), ST_Buffer(wg.geog, 0.0)), TRUE) / plz_fläche AS schnittmenge_fläche_plz_prozentual
FROM plz_wahlkreis_kombinationen pwk
JOIN plz_geo pg ON pg.plz = pwk.plz
JOIN wahlkreis_geo wg ON wg.wkr_nr = pwk.wkr_nr
Hinweis: Der Buffer von 0 Größeneinheiten um die Umrisse löst ein sonst auftretendes Problem bei der Ermittlung der Schnittmengen.
Die Bevölkerungen der PLZ-Gebiete werden jetzt mithilfe der oben berechneten Hilfsgröße auf die Wahlkreise umgelegt.
- Ermittlung der Bevölkerung je Wahlkreis-PLZ-Schnittmenge:
SELECT
sm.wkr_nr,
sm.plz,
sm.schnittmenge_fläche_plz_prozentual,
sm.schnittmenge_fläche_plz_prozentual * pe.einwohner AS einwohner_wahlkreis
FROM plz_wahlkreis_schnittmengen sm
JOIN plz_einwohner pe ON sm.plz = pe.plz
Die Einwohnerzahlen der Schnittmengen werden nun pro Wahlkreis aufsummiert.
- Ermittlung der Einwohner je Wahlkreis:
SELECT wkr_nr, SUM(einwohner_wahlkreis) AS population
FROM einwohner_je_wkr_plz_schnittmenge
GROUP BY wkr_nr
Auch die nachfolgende Ermittlung der Wahlkreis-Flächen ist nur dank der geografischen Erweiterung möglich.
- Ermittlung der Fläche je Wahlkreis:
SELECT
wkr_nr,
ST_AREA(wg.geog, TRUE) AS wkr_fläche_qm,
ST_AREA(wg.geog, TRUE)/1000000 AS wkr_fläche_qkm
FROM wahlkreis_geo wg
Im letzten Schritt werden mithilfe der bisherigen Zwischenergebnisse und der Wahlergebnisse die Stimmenanteile der Parteien und die Bevölkerungsdichte je Wahlkreis ermittelt.
- Ermittlung Bevölkerungsdichte und Stimmen je Wahlkreis:
SELECT
e.wkr_nr,
wg.wkr_name,
e.population,
f.wkr_fläche_qkm,
e.population / f.wkr_fläche_qkm AS bevölkerungsdichte,
grüne.stimmen_anteil AS grüne,
unionsparteien.stimmen_anteil AS „union“
FROM einwohner_je_wahlkreis e
JOIN (
SELECT ws.“Prozent“ AS stimmen_anteil, ws.“Gebietsnummer“ AS wkr_nr
FROM wahlkreis_stimmen ws
WHERE ws.“Gebietsart“=’Wahlkreis‘ AND ws.“Stimme“ = 2
AND ws.“Gruppenname“ IN (‚GRÜNE‘)
) grüne ON grüne.wkr_nr = e.wkr_nr
JOIN (
SELECT ws.“Prozent“ AS stimmen_anteil, ws.“Gebietsnummer“ AS wkr_nr
FROM wahlkreis_stimmen ws
WHERE ws.“Gebietsart“=’Wahlkreis‘ AND ws.“Stimme“ = 2
AND ws.“Gruppenname“ IN (‚CDU‘,’CSU‘)
) unionsparteien ON unionsparteien.wkr_nr = e.wkr_nr
JOIN fläche_je_wahlkreis f ON e.wkr_nr = f.wkr_nr
JOIN wahlkreis_geo wg ON wg.wkr_nr = e.wkr_nr
Mit der Bevölkerungsdichte je Wahlkreis und den Stimmen aus den Wahlkreisen verfügen wir nun über alle Größen für eine Regressionsanalyse:
Regression
Die Regressionsanalyse auf Basis der Ergebnisse bestätigt die Hypothese. Die Grünen werden tendenziell in dicht besiedelten Wahlkreisen öfter gewählt als in dünn besiedelten Wahlkreisen. Bei den Unionsparteien ist der Trend umgekehrt.
Die Union
Die Grünen
Zwar ist die Bevölkerungsdichte bei weitem nicht der einzige Einflussfaktor und kann die Wahlergebnisse nur zum Teil erklären, ein gewisser Zusammenhang der beiden Größen lässt sich auf Basis der Datengrundlage allerdings durchaus ableiten.
Fazit
Mithilfe einer um GIS-Funktionalität erweiterten relationalen Datenbank konnten wir numerische Daten und geografische Daten verknüpfen und so neue Erkenntnisse erlangen. Die Datenverarbeitung bestand aus bekannten SQL-Befehlen, die lediglich um einige GIS-Funktionen ergänzt wurden. Der einmalige Aufwand zur Installation der GIS-Erweiterung war überschaubar, die Verbindung mit nicht-geografischen Daten simpel. Der Open-Source-GIS-Stack hat sich als praktikabel und zweckmäßig erwiesen.
Dieses erprobte Vorgehen bei der Aufbereitung und Analyse von Geo-Daten lässt sich für alle erdenklichen Arten von Visualisierungen oder statistischen Auswertungen, bis hin zu Machine Learning-Pipelines, übertragen und kann dazu genutzt werden, die in ihnen enthaltenen Informationen zielgerichtet auf vielfältige Fragestellungen und Probleme aus Wirtschaft und Wissenschaft anzuwenden.
