Aggregation von Daten auf unterschiedlichen Rasterebenen

Sa 24 März 2018

Filed under Blog

Tags Postgres

Kürzlich habe ich mit den „Regionalstatistische Rastereinheiten“ der Statistik Austria gearbeitet[1]. Wir haben mit den Ebenen 250, 500 und 1000 Metern gearbeitet. Und mit diesen Daten musste ein Datensatz verschnitten werden, der personenbezogene Daten beinhaltet hat. Wenn die Anzahl der Personen unter einer bestimmten Größe (<5) auf unterster Ebene (250 Meter) lag, dann mussten man es auf die nächste höhere Ebene (500 Meter) legen und überprüfen, ob dort die Kriterien zutrafen. Falls nicht, dann auf den 1000 Meter Raster. Jetzt lagen sowohl die Daten, als auch die Rasterdaten in einer Postgres-Datenbank vor. Also was liegt dann näher als sich diese Dinge auch in der Datenbank berechnen zu lassen.

Der Einfachheit nehmen wir folgende Tabelle her:

Create table raster.personen (
raster_250_id character varying,
raster_500_id character varying,
raster_1000_id character varying,
geschlecht character(1),
altersklasse character(6),
personen int
);

Jetzt noch ein paar Testdaten:

insert into raster.personen values
('250mN263325E457650', '500mN26330E45765', '1kmN2633E4576', 'w', '20-30', 14),
('250mN263325E457650', '500mN26330E45765', '1kmN2633E4576', 'm', '40-50', 1),
('250mN263300E457650', '500mN26330E45765', '1kmN2633E4576', 'm', '40-50', 5),
('250mN263325E457650', '500mN26330E45765', '1kmN2633E4576', 'w', '40-50', 1),
('250mN263300E457650', '500mN26330E45765', '1kmN2633E4576', 'w', '40-50', 2);

Für die Berechnung selbst, habe ich der Einfachheit halber jeweils einen Hash verwendet, der alle relevanten Spalten umfasst. Jetzt legen wir für jede Ebene noch einen Hash an:

alter table raster.personen add column hash_250 text, add column hash_500 text, add column hash_1000 text;

update raster.personen set hash_250 = encode(digest(raster_1000_id || raster_500_id || raster_250_id || geschlecht || altersklasse, 'sha256'), 'hex'),  hash_500 = encode(digest(raster_1000_id || raster_500_id || geschlecht || altersklasse, 'sha256'), 'hex'),  hash_1000 = encode(digest(raster_1000_id || geschlecht || altersklasse, 'sha256'), 'hex');

Jetzt müssen wir noch überlegen, wie wir die Daten aggregieren müssen. Die Idee ist, dass man auf der untersten Eben (250 Meter) anfängt zu überprüfen, welche Daten unter der minimalen Anzahl an Personen (5) liegt. Danach die diese Daten auf der Ebene 500 Meter überprüft und gegebenenfalls dann auf die höchsten Ebene (1000 Meter) legt. Danach muss man auf der 500 Meter Ebene überprüfen, welche der möglichen Daten, nicht auf die 1000 Meter Ebene gehoben wurden und am Ende noch auf dem 250 Meter Raster überprüfen, welche Daten noch übrig sind. Das ganze wird in einer SQL-Abfrage mit mehreren CTEs[2] überprüft. Der erste Teil holt sich alle Daten, die auf der 250er Ebene die Kriterien nicht erfüllen:

with nicht_250 as (
select hash_500, hash_1000 from raster.personen where personen < 5
group by 1,2
)

Die nächste CTE aggregiert dann die Daten für die 500er-Ebene

vielleicht_500 as (
select a.hash_500, a.hash_1000, sum(a.personen) as personen from raster.personen as a, nicht_250 as b
where a.hash_500=b.hash_500 group by 1,2
)

Im nächsten Schritt werden die Datensätze ermittelt, die auch auf der 500er Ebene zu wenig Personen haben

nicht_500 as (
select hash_1000 from vielleicht_500 where personen < 5
group by 1
)

Diese Daten werden dann auf der 1000er Ebene aggregiert

schon_1000 as (
select a.hash_1000, sum(a.personen) as personen from raster.personen as a, nicht_500 as b
where a.hash_1000=b.hash_1000 group by 1
)

Und im nächsten Schritt geht es wieder auf die 500er Ebene um die Daten für diese Ebene zu ermitteln. Dafür müssen wir alle potenziellen Datensätze eliminieren, die schon auf der 1000er Ebene in Verwendung sind

exclude_1000 as (
select a.hash_1000, a.hash_500 from vielleicht_500 as a
left join schon_1000 as b on (a.hash_1000=b.hash_1000)
where a.personen >= 5 and b.hash_1000 is null
)

Und danach können wir alle Datensätze für die 500er-Ebene ermitteln

schon_500 as (
select a.hash_500, a.hash_1000, sum(a.personen) as personen from
raster.personen as a, exclude_1000 as b
where a.hash_500 = b.hash_500
group by 1,2
)

Im letzten Schritt ermitteln wir noch alle Datensätze für den 250 Meter Raster

schon_250 as (
select hash_250, a.personen from raster.personen as a
left join schon_1000 as b on (a.hash_1000=b.hash_1000)
left join schon_500 as c on (a.hash_500=c.hash_500)
where b.hash_1000 is null and c.hash_500 is null
)

Und zum Abschluss das komplette Statement:

with nicht_250 as (
select hash_500, hash_1000 from raster.personen where personen < 5
group by 1,2
),
vielleicht_500 as (
select a.hash_500, a.hash_1000, sum(a.personen) as personen from raster.personen as a, nicht_250 as b
where a.hash_500=b.hash_500 group by 1,2
),
nicht_500 as (
select hash_1000 from vielleicht_500 where personen < 5 group by 1 ), schon_1000 as ( select a.hash_1000, sum(a.personen) as personen from raster.personen as a, nicht_500 as b where a.hash_1000=b.hash_1000 group by 1 ), exclude_1000 as ( select a.hash_1000, a.hash_500 from vielleicht_500 as a left join schon_1000 as b on (a.hash_1000=b.hash_1000) where a.personen >= 5 and b.hash_1000 is null
),
schon_500 as (
select a.hash_500, a.hash_1000, sum(a.personen) as personen from
raster.personen as a, exclude_1000 as b
where a.hash_500 = b.hash_500
group by 1,2
),
schon_250 as (
select hash_250, a.personen from raster.personen as a
left join schon_1000 as b on (a.hash_1000=b.hash_1000)
left join schon_500 as c on (a.hash_500=c.hash_500)
where b.hash_1000 is null and c.hash_500 is null
)
select hash_250 as id, '250'::text as ebene, personen from schon_250
UNION
select hash_500 as id, '500'::text as ebene, sum(personen) as personen from schon_500 group by 1,2
UNION
select hash_1000 as id, '1000'::text as ebene, sum(personen) as personen from schon_1000 group by 1,2;

Jetzt könnte man beispielsweise noch einen View anlegen, der die einzelnen Zeilen über den Hash wieder mit den "Roh-Daten" verknüpft und so die einzelnen Merkmale wieder abrufen kann.

[1] http://data.statistik.gv.at/web/meta.jsp?dataset=OGDEXT_RASTER_1

[2] https://www.postgresql.org/docs/current/static/queries-with.html


Comments


PataGonia © jad Powered by Pelican and Twitter Bootstrap. Icons by Font Awesome and Font Awesome More