Zarządzanie Danymi Przestrzennymi

Ćwiczenie 1 - PostgreSQL

Linia poleceń - tworzenie nowego użytkownika, schematu i bazy danych

Na początek, w celu przypomnienia podstawych informacji i sposobów poruszania się w środowisku PostgreSQL, stworzymy przy wykorzystaniu terminala: nowego użytkownika, schemat i prostą bazę danych.

Aby utworzyć nowego użytkownika bazy danych należy najpierw zalogować się przy pomocy domyślnego administratora czyli postgres. Najprościej zrobić to zmieniająć w terminalu użytkownika unixowego tzn.

su postgres

Następnie należy się zalogować do lini poleceń poprzez:

psql

Domyślnie użytkownik logowany jest od bazy danych o tej samej nazwie co jego login. Można to zmienić przy pomocy parametry -d.

Jeśli jest to świeża instalacja należy najpierw zmienić hasło użytkownika Ubuntu:

sudo passwd postgres

a następnie, z lini poleceń psql, hasło użytkownika bazy:

ALTER USER postgres WITH PASSWORD 'twoje hasło'

Użytkownik postgres winien być zarezerwowany do funkcji administracyjnych. Dlatego też należy stworzyć nowego, z dostępem do konkretnej bazy. Niezbędny kod to:

CREATE USER nazwa_użytkownika WITH PASSWORD 'hasło'

Kolej na bazę danych:

CREATE DATABASE nazwa_bazy

oraz uprawnienia

GRANT ALL PRIVILEGES ON DATABASE baza_danych TO nazwa_uzytkownika

ZADANIE 1.1: Utwórz użytkownika zdp oraz bazę danych i schemat o tej samej nazwie. Przydziel odpowiednie uprawnienia a następnie zaloguj się do tak utworzonego środowiska.

Wprowadzanie danych do bazy

Jednym z prostszych i efektywnych sposobów wprowadzenia danych do tabeli w bazie danych jest plik CSV. Podczas zajęć za przykład posłużą dane z portalu Geonames. Na początek należy pobrać dane dotyczące Polski i Niemisc klikając w poniższy odnośnik- http://earthisflat.net/download/dydaktyka/miasta.tar.gz

Kod SQL tworzący tabelę ma następującą składnie:

CREATE TABLE nazwa_tabeli (nazwa_pola1 typ_pola1, nazwa_pola2 typ_pola2) 

Przy tworzeniu tabeli, należy dobrze rozpoznać strukturę danych wejściowych i prawidłowo określić typy danych

Za import danych z pliku odpowiada poleceni COPY w języku SQL oraz jego odpowiednik w psql, czyli \copy. Druga z nich jest mniej efektywna i bardziej obciążająca serwer, jednak nie wymaga w odróżnieniu od COPY uprawnień administracyjnych (superuser). Jej składnia to:

\copy tabela_docelowa from 'ścieżka_dostępu' with null as ''

Polecenie domyślnie przyjmuje znak tabulatora jako delimiter. Użycie with null as ' ', jest konieczne jeśli plik zawiera puste pola, gdyż \copy spodziewa się \N

.

ZADANIE 1.1: Na podstawie danych w portalu Geonames przygotuj odpowiednią strukturę tabeli, zawierająca wszystkie kolumny z właściwie przyporządkowanymi typami. Utwórz tabele dla Polski i Niemiec.

Podstawowe zapytania i operacje na danych

Podstawowe zapytanie do bazy danych to SELECT:

SELECT nazwy_kolumn FROM nazwa_tabeli WHERE warunek

oraz INSERT:

INSERT INTO nazwa_tabeli (nazwy_kolumn) VALUES (wartości)

lub:

INSERT INTO nazwa_tabeli (nazwy_kolumn) SELECT ...

ZADANIE 1.3:
1. Utwórz tabelę zawierającą miasta, zarówno niemieckie jak i polskie, o liczbie mieszkańców powyżej 10000.
2. Wyeksportuj uzyskaną tabelę do pliku csv, posortowanego według liczby mieszkańców.
3. Policz miasta niemieckie powyżej 100k mieszkańców.

Autor: Michał Rzeszewski, Instytut Geokologii i Geoinformacji, UAM Poznań, mail: mrzeszewski@gmail.com
Creative Commons License
Ten utwór jest dostępny na licencji Creative Commons Uznanie autorstwa-Użycie niekomercyjne-Na tych samych warunkach 2.5 Polska.