Open Data mit Postgres und Postgis (Teil 3)
Posted on Do 16 Jänner 2025 in Blog
Im ersten und zweiten Teil wurden die Bevölkerungsdaten auf Rasterebene den Gemeinden zugeordnet.
Im dritten und letzten Teil der Serie werden die Daten des Breitbandatlas mit den in den ersten beiden Teilen erstellten Bevölkerungsdaten verknüpft.
Inhalt:
Verknüpfung mit den Daten des Breitbandatlas
In einem letzten Schritt sollen die Daten noch mit den Informationen aus dem Breitbandatlas verknüpft werden.
Dafür wird zuerst überprüft, wie die Daten im Breitbandatlas aussehen.
select id, count(*)
from data_gv_at.breitbandatlas b
group by 1
having count(*) > 1
order by 2 desc
Dadurch sieht man, es kann pro Rastereinheit durchaus mehrere Einträge geben. Daher sollte in einem ersten Schritt die maximale Download-Geschwindigkeit pro Rastereinheit berechnet werden.
select
id
, max(download) as download
from data_gv_at.breitbandatlas
group by id
In einem nächsten Schritt werden die einzelnen Teile zusammengeführt und Gruppen nach Geschwindigkeit gebildet und die Anzahl der Bewohner:innen für die einzelnen Klassen berechnet.
with select_matches as (
select
rr.name as grid_name
, rr.id as grid_id
, gag.reg_code
, rr.value
from dwh_oesterreich.gemeinden_aktueller_gebietsstand gag,
dwh_inspire.at_bev_rastereinheiten_2024 rr
where
rr.geom @ st_transform(gag.geom, 3035) and st_within(rr.geom, st_transform(gag.geom, 3035))
),
order_cells as (
select
rr.name as grid_name
, rr.id as grid_id
, gag.reg_code
, rr.value
, row_number() over (partition by rr.name order by st_area(st_difference(rr.geom, st_transform(gag.geom, 3035))) / st_area(rr.geom) desc) as grid_order
from dwh_oesterreich.gemeinden_aktueller_gebietsstand gag,
dwh_inspire.at_bev_rastereinheiten_2024 rr
where st_intersects(rr.geom, st_transform(gag.geom, 3035))
),
union_cells as (
select grid_name, grid_id, reg_code, value as bev from select_matches
union
select grid_name, grid_id, reg_code, value as bev from order_cells where grid_order = 1
),
select_max_speed_per_rastereinheit as (
select
id
, max(download) as download
from data_gv_at.breitbandatlas
group by id
)
select
reg_code
, sum(bev) filter (where download < 10) as kleiner_10
, sum(bev) filter (where download >= 10 and download < 50) as kleiner_50
, sum(bev) filter (where download >= 50 and download < 100) as kleiner_100
, sum(bev) filter (where download >= 100 and download < 1000) as kleiner_1000
, sum(bev) filter (where download >= 1000) as groesser_gleich_1000
, sum(bev) as bevoelkerung
from union_cells as a
left join select_max_speed_per_rastereinheit b on (a.grid_id = b.id )
group by 1
Hier hat man mehrere Gruppen genutzt, um festzustellen, wie viele Personen pro Gemeinde in die jeweiligen Kategorien fallen.
Der left join
wird deshalb benötigt, da es Rastereinheiten gibt, wo zwar Menschen leben, es aber laut Breitbandatlas keinen Festnetzanschluss gibt.
Fazit
Wie man hoffentlich nach dieser Blogserie sieht, bieten Datensätze, die als Open Data zur Verfügung gestellt werden, durchaus sehr spannende Möglichkeiten. Es gibt aber auch Herausforderungen. Diese kann ma auch gut mittels Postgres samt der Postgis-Erweiterung bewältigen.
Eine Analyse ist oftmals nur dann sinnvoll, wenn man die Ergebnisse gut visualisieren und damit transportieren kann. Daher habe ich auch den Resultaten mittels QGIS auch noch eine Karte gemacht.
Die Idee zu dieser Datenaufbereitung ist mir durch den ÖROK-Atlas gekommen. Allerdings war ich mehr an der Bevölkerung, als an den Haushalten interessiert und daher habe ich mir gedacht, man kann das ja gleich für eine Blogserie nutzen ;-)