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:

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.