Cenną umiejętnością jest poszukiwanie rozwiązania problemu na różne sposoby. Dziś padło „pytanie z sali”  o rozwiązanie dość prostego problemu związanego z agregowaniem danych. Posłuchajmy najpierw krótkiej opowieści.

Problem

 

Posiadam tabelę w QGIS o dla przykładu następującej treści:

gmina   |  atrybut
-----------|-------------
A           | zzz
B           | zzz
C           | xxx
D           | yyy
E           | zzz

chciałbym stworzyć warstwę wirtualną (bez geometrii), która wylistowałaby wszystkie atrybuty jakie mają gminy, zliczyła ile jest gmin, które posiadają dany atrybut i co najważniejsze ponieważ tego nie umiem zrobić wylistowałaby mi w jednej kolumnie wszystkie gminy, które posiadają dany atrybut:

atrybut     | count(gmina)       |   gmina
------------ |------------------------|-----------
xxx           | 1                          | C
yyy           | 1                          | D
zzz           | 3                          | A, B, E

wykorzystuje następujące zapytanie:
select atrybut, count(gmina), gmina from tabela_z_danymi group by atrybut;

niestety nie potrafię wylistować w jednej kolumnie wszystkich gmin posiadających dany atrybut. Wylistowaniu podlega tylko pierwsza gmina spełniająca warunek.

Rozwiązanie

Na ratunek w takiej sytuacji przychodzi funkcja agregująca string_agg(atrybut, separator) w PostgreSQL.

Jej użycie w wyżej wymienionym problemie jest następujące:

select atrybut, count(gmina), string_agg(gmina, ', ') from tabela_z_danymi group by atrybut;

Zobaczmy jej działanie na przykładzie z państwowego rejestru nazw geograficznych.

SELECT woj, rodzaj_obi, count(rodzaj_obi) AS ilosc, string_agg(naz_glowna, ',') FROM gus.prng GROUP BY woj, rodzaj_obi;

Funkcja agregująca string_agg

Funkcja agregująca string_agg

W przypadku gdy nie posiadamy aktywnej bazy PostgreSQL w której możemy taką operację wykonać, spróbujmy skorzystać z możliwości jakie daje nam QGIS i jego warstwy wirtualne. Po wczytaniu warstwy wektorowej (np. shapefile), otwieramy menu Bazy Danych -> Zarządzanie Bazami. Następnie w lewej zakładce otwieramy  Virtual Layers -> QGIS Layers -> nasza_tabela_z_danymi. Teraz menedżer zarządzania bazami będzie zachowywał się tak, jakby nasza tabela była częścią bazy SQLite i pozwoli nam na wykorzystanie składni tego języka.

Funkcja agregująca ciągi znakowe ma tu składnię group_concat(atrybut, separator).
W takim razie spróbujmy zrealizować zapytanie funkcjonalnie podobne do tego wykonanego w PostgreSQL:

SELECT woj,count(naz_glowna), rodzaj_obi, group_concat(naz_glowna)
FROM prng GROUP BY woj, rodzaj_obi;

Funkcja group_concat

Funkcja group_concat

Operacja odwrotna

Czasami zdarza się nam konieczność wykonania operacji odwrotnej. Przejścia od ciągu znakowego o zmiennej długości, rozdzielonego separatorami do wielu wartości atrybutowych. Zaprezentuję wam to na przykładzie adresu leśnego wykorzystywanego w SILP PGL LP.

Ma on formę XX-YY-Z-AA-BBB-CCC gdzie kolejne ciągi alfanumeryczne oznaczają dyrekcję regionalną, nadleśnictwo, obręb, leśnictwo, oddział, wydzielenie, etc. Niestety nie długość tego pola może być zmienna, więc wymaga użycia wyrażeń regularnych (reg_expów), które nie należą do łatwych, lub funkcji konwertujących łańcuch znakowy na tablicę atrybutową (typ array).

Na początek przykład rozwiązania z PostgreSQL. Składnia jest analogiczna: string_to_array(atrybut, separator). Dodatkowo jednak przy typach tablicowych korzystamy ze wskaźnika na element tablicy [X],  A więc zapytanie o łączną powierzchnię oddziałów leśnych w dyrekcjach regionalnych możemy wykonać w taki sposób:

SELECT (string_to_array(adr_for, '-' , ' '))[1] AS dyrekcja, sum(st_area(geometry)) AS powierzchnia FROM bdl.g_compartment GROUP BY dyrekcja;

Funkcja string_to_array

Funkcja string_to_array

 

Inna metoda, to skorzystanie z silnika wyrażeń QGIS, bez użycia silnika bazodanowego. Warto wtedy skorzystać z funkcji tablicowych (array) takich jak string_to_array (składnia identyczna jak w postgresql) oraz array_get. Ta druga funkcja jest odpowiedzialna z wybór obiektu z tablicy. Co istotne, funkcje i składnia PostgreSQL zakładają że indeks w tablicy liczymy od 1, zaś w QGIS liczenie rozpoczynamy od zera. Spróbujmy zwizualizować numer dyrekcji z pola adr_for:

array_get(string_to_array( "adr_for" ,'-',''),0)

Pobieranie elementu tablicy

Pobieranie elementu tablicy

Chciałbym abyście ten krótki artykuł potraktowali jako zachętę do poznania typu tablicowego oraz metod pracy z nim. Dzięki temu wiele operacji będzie dużo łatwiejszych do wykonania.

Z zainteresowaniem czekam na kolejne pytania z sali 😉