Celem laboratorium jest zapoznanie z działaniem przestrzennych baz danych na przykładzie systemu PostGIS.

UWAGA: Zwróć uwagę, która wersja PostGIS została wskazana przez prowadzącego i przygotuj odpowiednio bazę danych lub zaloguj się do uprzednio przygotowanej bazy.

Uwaga: laboratoria w roku akademickim 2016/2017 prowadzone są na serwerze mapserver, w oparciu o PostGIS w wersji 2.0 – proszę skorzystać z tej instrukcji.

Na serwerach borg i charon zainstalowany jest system PostGIS w wersji 1.5.3. Dokumentacja

Uwaga: instrukcja zakłada, że działamy na bazie domyślnej, o nazwie identycznej z nazwą użytkownika.

Baza danych, zanim można będzie jej użyć z PostGIS, należy ją odpowiednio przygotować, wykonując następujące kroki:

1. Upewnienie się, czy w bazie jest dostępny język plpgsql:

   $ createlang plpgsql

2. Dodanie z pliku typów przestrzennych:

   $ psql -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql

3. Import danych o układach odniesienia:

   $ psql -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql

4. Można również dodać komentarze - forma samodokumentacji elementów bazy:

   psql -f /usr/share/postgresql/9.1/contrib/postgis_comments.sql

Na serwerze mapserver.kis.agh.edu.pl zainstalowany jest PostGIS w wersji 2.0. Dane do logowania zostaną podane przez prowadzącego na zajęciach, baza skojarzona z kontem jest posiada już obsługę PostGIS (tzn. zostało już na niej wykonane CREATE EXTENSION).

UWAGA: Każda osoba powinna utworzyć schemat w bazie dla siebie, np. jan_kowalski i wszystkie operacje wykonywać w tym schemacie!

CREATE SCHEMA jan_kowalski;

Funkcje PostGIS zainstalowane są w schemacie domyślnym public. Ponieważ pracują Państwo w odrębnych, osobistych schematach, polecamy ustawienie zmiennej search_path tak aby zawierała zarówno Państwa własny schemat, jak i schemat public (najlepiej w tej kolejności, aby uniknąć przypadkowego odwołania do tabeli która może już istnieć w schemacie public.

Narzędzie shp2pgsql wykorzystywane w późniejszych ćwiczeniach posiada opcje umieszczające nazwę schematu bezpośrednio w generowanym kodzie SQL.

W wersjach wcześniejszych niż 2.0, tabele z kolumnami przestrzenne tworzymy w dwóch krokach:

1. Utworzenie tabeli ze wszystkimi atrybutami nie-przestrzennymi.
2. Dodanie kolumn przestrzennych przy pomocy funckji AddGeometryColumn:

   SELECT AddGeometryColumn(tabela, nazwa_atrybutu, srid, typ, wymiary)

lub gdy używamy innego schematu niż public:

SELECT AddGeometryColumn(schemat, tabela, nazwa_atrybutu, srid, typ, wymiary)

gdzie:

• tabela - nazwa tabeli
• nazwa_atrybutu - nazwa tworzonej kolumny
• srid - identyfikator układu odniesienia (np. 4326 dla WGS-84 znanego m.in. z GPS - stopnie) lub -1 dla nieokreślonego SRID, podawane jako liczba
• typ - przechowywany typ, abstrakcyjny np. GEOMETRY lub określone np. POLYGON lub LINESTRING, podawane jako ciąg znaków.
• wymiary - liczba wymiarów, 2 lub 3

Przykład:

SELECT addgeometrycolumn('sebastian_ernst', 'test', 'geom', 4326, 'point', 2);

W ćwiczeniu korzystaj z układu odniesienia (SRID) EPSG:4326, czyli popularnego m.in. z odbiorników GPS układu opartego o stopnie.

Utwórz tabelę miasta przechowującą co najmniej nazwę miasta oraz współrzędne punktu, stanowiącego ustalony środek miasta. Dodaj, na podstawie danych z Wikipedii (link geohack np. na stronie o Krakowie), współrzędne co najmniej dwóch polskich miast.

Uwaga: Zwróć uwagę, że zazwyczaj współrzędne podawane są w kolejności (szerokość długość), natomiast w WKT obowiązuje (długość szerokość). Kod WKT reprezentujący „środek” Krakowa w układzie WGS-84 będzie więc wyglądał tak:

POINT(19.938333 50.061389)

Napisz zapytanie, które obliczy odległość między tymi miastami. W jakich jednostkach jest wynik?

Aby uzyskać wynik w metrach, należy zmienić układ współrzędnych na taki, który da w miarę dokładny dla obszaru Polski wynik wyrażony w metrach. Korzystając z wyszukiwarki na stronie http://spatialreference.org spróbuj jakieś lokalne układy dla Polski i oblicz jeszcze raz odległość, ale tym razem rzutując oba punkty (wewnątrz funkcji ST_Distance) na wybrany układ odniesienia, korzystając z funkcji ST_Transform.

Powtórz ćwiczenie dla kilku innych wybranych SRID.

W poprzednim zadaniu mówiliśmy o „w miarę dokładnym” układzie odniesienia dla Polski, gdyż dla typów GEOMETRY odległość obliczana jest na płaszczyźnie na którą rzutowana jest powierzchnia kuli ziemskiej. Oczywistym jest więc, że nie da się znaleźć układu „globalnie dokładnego”. Stąd potrzeba korzystania z lokalnych układów współrzędnych.

PostGIS udostępnia również typ GEOGRAPHY, który operuje na danych w układzie WGS-84, ale obliczenia wykonywane są na geoidzie reprezentującej kształt kuli ziemskiej. Wadą jest to, że wiele funkcji PostGIS nie jest dostępnych dla typu GEOGRAPHY.

Powtórz ćwiczenie 1, tworząc analogiczną tabelę miasta2, ale tym razem korzystaj z typu GEOGRAPHY. Pamiętaj, że teraz nie ma potrzeby rzutowania układów współrzędnych.

Porównaj wynik z wynikami ćwiczenia 1.

W archiwum krakow.tar.bz2 znajdują się elementy mapy Krakowa pochodzące z serwisu OpenStreetMap. Dane zapisane są w formacie ESRI shapefile i są zapisane w układzie współrzędnych EPSG:3785 (tzw. Mercator).

Narzędzie shp2pgsql pozwala na wygenerowanie z plików SHP kodu SQL tworzącego bazę danych. Wygeneruj dla plików SHP pliki SQL (pamiętając o użyciu parametru -s tak, aby kod miał zgodne z rzeczywistością SRID, np.:

$ shp2pgsql -s 3785 admin.shp > admin.sql

Następnie zaimportuj pliki SQL do bazy.

Tabele zawierają:

• admin - granice administracyjne Krakowa (multipolygon)
• amenities - elementy typu POI (point)
• roads - drogi (multilinestring)

Do każdej z tabel dodaj nową kolumnę typu GEOGRAPHY (ALTER TABLE) a następnie, przy pomocy polecenia UPDATE wypełnij ją odpowiednio przeliczonymi danymi z kolumny geom.

Oblicz obszar Krakowa w km².

Skorzystaj z kolumny z geometrią oraz z tej z geografią. Porównaj wyniki między sobą oraz z wartością z Wikipedii.

Korzystając z kolumn geograficznych oblicz łączną długość dróg zawierających się w granicach administracyjnych Krakowa.

Oblicz również łączną długość autostrad zapisanych w bazie danych (niekoniecznie w granicach Krakowa).

Sprawdź, ile obiektów należących do poszczególnych kategorii (tabela amenities) znajduje się w promieniu 2 km od bieżącej lokalizacji.

Tabelę wynikową uporządkuj w kolejności malejącej wg. liczby punktów danego typu.

Wyświetl listę 10 najdłuższych ulic w Krakowie.