Język SQL w QGIS

1 Cel ćwiczenia

Celem ćwiczenia jest omówienie narzędzi selekcji atrybutowej z wykorzystaniem składni SQL.

2 Dane

W ćwiczeniu zostaną wykorzystane dane z folderu COUNTRIES:

• world_countries.gpkg - zawierające granice państw i terytoriów zależnych.
• wdi1990_2020.csv - zawierające podstawowe informacje dotyczące poszczególnych państw oraz terytoriów zależnych.

3 Przygotowanie danych

• Wczytać do QGIS dane world_countries.gpkg oraz wdi1990_2020.csv.
• Po wczytaniu pliku wdi1990_2020.csv trzeba upewnić się, że dane zostały wczytane jako dane liczbowe (zmiennoprzecinkowe lub całkowite).

• Do danych przestrzennych world_countries.gpkg dołączyć warstwę wdi1990_2020.csv.

3.1 Łączenie warstw

• Wybrać właściwości warstwy world_countries oraz opcję Joins (Złączenia).
• Klucz ID pozwalający na połączenie obu warstw to trzyliterowy kod obszaru (Code na warstwie wdi1990_2020 oraz ISO_A3 na warstwie world_countries.gpkg)
• Uwaga! Oba pliki zawierają kolumnę z nazwą terytorium (NAME, Name). Na etapie łączenia trzeba wskazać wszystkie kolumny w zbiorze wdi1990_2020 z wyjątkiem kolumny Name. Przy zduplikowanych nazwach kolumn nie będzie można zapisać pliku do geopaczki.

• Zapisać warstwę world_countries z dołączonymi danymi attrybutowymi jako world_countries_attr.gpkg, nazwa warstwy wdi.

4 Edytor SQL

• Database - DB Manager

• więcej: https://docs.qgis.org/3.34/en/docs/training_manual/databases/db_manager.html

• Z menu po lewej stronie (Providers), kliknąć prawym klawiszem myszy na Geopackage i wybrać New Connection. Ustaw połączenie do pliku world_countries_attr.gpkg

• Wybierz warstwę wdi

W oknie managera baz danych (DB Manager) po wskazaniu Database - SQL Window pojawi się okno edytora SQL

• Utworzenie zbioru danych zawierających tylko dane dla 2020 roku

Wklej w edytorze SQL poniższe polecenie, a następnie wybierz Execute. W wyniku wyświetlony zostanie podgląd tabeli atrybutów.

#| eval: false
SELECT Code, NAME_PL, Region, CONTINENT, TYPE, HDI_Code, INCOME2020, GDP2020, POP2020, RPOP2020, UPOP2020, LE2020, HDI2020, geom 
FROM wdi

Aby zapisać wykonaną selekcję do nowej warstwy zaznacz “Load as new layer”. Jako kolumnę z unikalnymi wartościami wybierz Code. Podaj nazwę warstwy wdi2020. Wybierz Load. Zwróć uwagę, że załadowana do QGIS warstwa ma tylko kolumny dla 2020 roku.

4.1 Selekcja danych na podstawie warunku

Wykorzystaj utworzoną warstwę wdi2020 do wybrania krajów spełniających określony warunek. Każdorazowo zapisz seleckję do nowej warstwy.

W lewym menu Providers wybierz Virtual Layers - Project layers i wskaż wdi2020. Następnie w edytorze SQL wpisz polecenie.

• Selekcja danych dla kontynentu Afryka.

#| eval: false
SELECT * 
FROM wdi2020 
WHERE CONTINENT = 'Africa'

#| eval: false
SELECT * 
FROM wdi2020 
WHERE CONTINENT = 'Africa' AND POP2020 > 50000000

#| eval: false
SELECT * 
FROM wdi2020 
WHERE CONTINENT = 'Africa' AND HDI_Code IN ('High', 'Very High')

#| eval: false
SELECT * 
FROM wdi2020 
WHERE Region LIKE '%Asia'

#| eval: false
SELECT * 
FROM wdi2020 
WHERE Region LIKE '%Asia%'

5 Okno SQL dla danej warstwy

6 Selekcja poprzez wyrażenie

Otworzyć okno selekcji poprzez wyrażenie oraz wpisać wyrażenie: HDI_Code IN (‘Low’, ‘Medium’)

7 Zadanie do samodzielnego wykonania

Jako rozwiązanie zadania należy oddać utworzoną geopaczkę wdi1990.gpkg oraz dokument tekstowy zawierający odpowiedzi na zadane pytania.

7.1 Zadanie 1

Wykorzystując edytor języka SQL:

Wczytaj do QGIS warstwę world_countries_attr.gpkg.

1. Utwórz zbiór danych zawierający kolumny Code, NAME_PL, Region, CONTINENT, TYPE, HDI_Code oraz dane dla 1990 roku. Zapisz utworzony zbiór danych jako wdi1990.gpkg
2. Wybierz kraje, których nazwa zaczyna się na P i zapisz je do osobnej warstwy w geopaczce wdi1990, nazwa warstwy: kraje_p.
3. Wybierz kraje z liczbą ludności między 50 - 100mln i zapisz je do osobnej warstwy w geopaczce wdi1990, nazwa warstwy: kraje_50_100mln.
4. Wybierz kraje położone w Europie oraz Azji i zapisz je do osobnej warstwy w geopaczce wdi1990, nazwa warstwy: kraje_europa_azja.

7.2 Zadanie 2

Wczytaj do QGIS warstwę world_countries_attr.gpkg.

Używając narzędzia selekcji poprzez wyrażenie (select by expression)

1. Wybierz kraje które w roku 1990 były sklasyfikowane jako Low income, a w roku 2020 jako Upper middle income. Jakie to kraje?
2. Wybierz kraje, w których w 1990 roku oczekiwana długość trwania życia przekraczała 70 lat. Ile jest takich obszarów?
3. Wybierz kraje, w których w 2020 roku oczekiwana długość trwania życia nie przekraczała 70 lat. Ile jest takich obszarów?