Przepis na PostGIS

Podstawą działania QAZP2 jest przestrzenna baza danych. Informacje o śladach działalności człowieka, a także o prowadzonych badaniach archeologicznych łączy się z przestrzenią po przez dodanie do nich współrzędnych geograficznych. W przypadku archeologii jest to kluczowa informacja, gdyż umożliwia analizę danych w kontekście krajobrazu i środowiska, które prawdopodobnie miały niebagatelny wpływ na decyzje podejmowane przez dawnych osadników.

Zanim przejdę do omówienia schematu bazy danych, do którego dostosowany jest QAZP2, w kilku zdaniach opiszę przygotowywanie systemu PostgreSQL do pracy z bazami przestrzennymi. Nie zamierzam szczegółowo omawiać poszczególnych poleceń - lepiej ode mnie robi to bogata dokumentacja. Zgodnie z tytułem - to ma być przepis, który szybko pozwoli upiec ciastko ;). Będę pisał z perspektywy użytkownika Linuksa, a dokładnie Debiana i zakładam, że czytelnicy posiadają podstawową wiedzę na temat posługiwania się tym systemem.

1. Instalacja i konfiguracja PostgreSQL

# apt-get install postgresql postgis

To polecenie chyba nie wymaga komentarza. Po kilku albo kilkunastu minutach oczekiwania (w zależności od prędkości połączenia) aktualna wersja oprogramowania - czyli system PostgreSQL i jego rozszerzenie Postgis powinno znaleźć się na dysku i zostać zainstalowane i uruchomione.

# cd /etc/postgresql/{wersja}/main/
# cp pg_hba.conf pg_hba.bak
# echo "local all all password" >> /etc/postgresql/{wersja}/main/pg_hba.conf
# /etc/init.d/postgresql restart

W domyślnej konfiguracji z bazą może się połączyć użytkownik root albo specjalnie tworzony do tego celu postgresql. A więc przed nawiązaniem połączenia z bazą i wykonaniem polecenia SELECT, UPDATE, CREATE itp. należy zalogować się jako root albo postgresql (ten drugi ma zdefiniowane domyślne hasło postgresql). Osobiście wolę inne podejście, w którym użytkownika i hasło podaje się w chwili łączenia z bazą danych. Ma ono dodatkowe uzasadnienie: w sytuacji, w której będziemy próbowali się połączyć w QGIS z bazą jako inny użytkownik, na przykład milosz, w domyślnej konfiguracji Postgre odrzuci nasze rządanie. Dlatego do pliku pg_hba.conf dodajemy następujący wiersz local all all password (trzecia linia). Żeby zabezpieczyć się przed uszkodzeniem tego pliku, wcześniej (druga linia) należy wykonać jego kopię zapasową. Ta operacja wymaga zrestartowania systemu, co czyni polecenie w lini czwartej.

To jest najprostsza konfiguracja, jaką można sobie wyobrazić, ale w zupełności wystarcza do lokalnego testowania i korzystania z systemu PostgreSQL.

# createuser -U postgresql -W -d -P tester

Na koniec tworzymy nowego użytkownika o nazwie tester, który będzie uprawniony do tworzenia nowych baz danych (przełącznik -d). Przełącznik -P oznacza, że w chwili tworzenia użytkownika zostaniemy poproszeni o nadanie mu hasła. Przełączniki -U i -W są standardowe dla każdego polecenia PostgreSQL. Pierwszym wskazujemy tego użytkownika, jako który łączymy się do bazy w celu utworzenia nowego - w tym przypadku tym użytkownikiem jest wspominany już postgresql; użycie drugiego spowoduje, że przed dodaniem nowego, trzeba będzie podać hasło użytkownika postgresql.

2. Tworzenie bazy PostGIS

# createdb -O tester -h localhost -p 5432 -U tester -W azp2
# createlang plpgsql -U tester -W azp2

Kiedy nowy użytkownik jest już dodany, czas na utworzenie bazy danych. W pierwszym poleceniu tworzymy nową bazę o nazwie azp2, której właścicielem będzie tester (przełącznik -O). Po wykonaniu drugiej komendy w bazie azp2 będzie można tworzyć procedury w języku PL/SQL. Przełączniki -W i -U mają takie samo znaczenie, jak przy dodawaniu nowego użytkownika.

# cd /usr/share/postgresql/{wersja_postgre}/contrib/postgis-{wersja_postgis}
# psql -f postgis.sql -U tester -W azp2
# psql -f spatial_ref_sys.sql -U tester -W azp2

Na koniec do bazy azp2 trzeba dodać procedury, których używamy np. do dodawania współrzędnych, wyszukiwania na ich podstawie, itd., które są zaimplementowane w języku PL/SQL, o którym pisałem powyżej. Polecenia, które dodają wspomniane procedury znajdują się w katalogu utworzonym w chwili instalacji Postgis. Przechodzimy do niego w pierwszej lini, a następnie wywołujemy narzędzie psql podając jako parametr plik postgis.sql, który zawiera definicje procedur SQL. Do pracy z przestrzenną bazą danych przydatne będzie także dodanie definicji systemów odniesienia, które są używane do konwertowania współrzędnych. To także robimy za pomocą polecenia psql podając jako parametr plik spatial_ref_sys.sql.

I tyle. Baza przestrzenna jest gotowa do pracy. Od tej chwili można już tworzyć tabele ze współrzędnymi geograficznymi. Ale o tym innym razem.

Kilka uwag o Python DBAPI

Nowa wersja QAZP2 jest już dostępna bezpośrednio albo z repozytorium wtyczek. Działa już edytor faktów kulturowych, to znaczy do każdego stanowiska można przyporządkować określenia jego przynależności chronologiczno-kulturowej.

Wymagania

Jednym z ważniejszych założeń, których staram się trzymać rozwijając program jest jego "bezobsługowość", czyli uwolnienie użytkownika od obowiązku dbania o zależności w postaci dodatkowych modułów, spoza biblioteki standardowej i w tym sterowników do bazy danych. Obecnie QAZP2 umożliwia korzystanie z bazy SQLite albo PostgreSQL, z tej prostej przyczyny, że tylko spośród tych dwóch można wybierać w QGIS-ie. Moduł do obsługi jest dostępny w bibliotece standardowej Pythona, natomiast instalator QuantumGIS, gdy jest uruchamiany pod kontrolą MS Windows, domyślnie dodaje sterownik psycopg do łączenia się z bazą PostgreSQL. W przypadku użytkowników Linuksa problem jest trochę bardziej skomplikowany, ale w takiej sytuacji zakładam, że potrafią oni korzystać z menadżera pakietów i zainstalować wymagane biblioteki.

Wszystko to wydaje się trochę zagmatwane, więc w skrócie sytuację można opisać następująco: dwa rodzaje bazy danych SQLite/Spatialite i PostgreSQL/Postgis i sterowniki sqlite3 oraz psycopg, które implementują wzorzec opisany w PEP 249.

Pobieranie informacji o połączeniu

Pierwszy z problemów, które w związku z tym trzeba było rozwiązać to określenie, z jakiej bazy korzysta użytkownik. Jak już wspominałem QAZP ma być "bezobsługowe", czyli między innymi automatycznie rozwiązywać problemy konfiguracji, np. takie jak wybór bazy, z której pobrać dane. W obecnej wersji obsługiwane są trzy warstwy wektorowe: Miejsca, czyli zbiór punktów charakterystycznych na mapie; Trasy - drogi pokonane w trakcie badań archeologicznych; Stanowiska - ślady dawnego osadnictwa. Każda z nich w QGIS jest reprezentowana przez obiekt klasy QgsVectorLayer. Z tego kolei można uzyskać informacje o źródle pochodzenia warstwy, którym może być plik SHP, usługa WFS, czy właśnie relacyjna baza danych. Wywołując metodę dataProvider() uzyskuje się obiekt klasy QgsVectorDataProvider, którego metoda dataSourceUri() zwraca ciąg reprezentujący połączenie. Może to być ścieżka do pliku, albo lokalizacja bazy z parametrami - nazwą użytkownika, hasłem, itp. Żeby uniknąć ręcznego parsowania takiej informacji można wykorzystać klasę QgsDataSourceURI, której metody host(), port(), database() i inne pozwalają pobrać potrzebne informacje do nawiązania połączenia z bazą danych. To może się wydawać skomplikowane, ale w rzeczywistości jest dość banalne i całą operację łączenia można załatwić w kilku liniach kodu, tak jak to jest zrobione w metodzie zrodla.getPolaczenie2(). Jak zwykle w takich przypadkach większość czasu zajmuje znalezienie rozwiązania problemu, a nie jego implementacja ;).

Parametry poleceń SQL

Wspomniana metoda w zależności od nazwy bazy łączy się z wykorzystaniem modułu sqlite3 albo psycopg. I tutaj z mojej perspektywy rozpoczyna się najgorsze - otóż instrukja PEP 249 dopuszcza kilka różnych sposobów podawania parametrów polecenia sql. W przypadku sqlite przykładowe zapytanie mogłoby mieć postać (1) select * from tabela where a=? and b=? albo (2) select * from tabela where a=:pa and b=:pb gdy posługujemy się parametrami nazwanymi. Natomiast używając psycopg polecenie (1) trzeba zapisać jako select * from tabela where a=%s, a (2) w postaci select * from tabela where a=%(pa)s and b=%(pb)s. Każdy z tych formatów jest poprawny z uwagi na PEP 249, ale z jakiegoś powodu autorzy sqlite3 zdecydowali się na jeden, a psycopg na drugi. Są dwa rozwiązania tego problemu:

1. Każdorazowe badanie rodzaju bazy i na tej podstawie konstruowanie poleceń z odpowiednio zaznaczonymi parametrami.
2. Skonstruowanie adaptera, który by "opakował" oryginalne połączenie sqlite3 albo psycopg i umożliwiał jednorodny sposób wykonywania poleceń.

Zdecydowałem się na drugą opcję i napisałem dwie klasy - Polaczenie i Polecenie. Pierwsza opakowuje połączenie sqlite3 albo psycopg i w metodzie prep(sql), znając rodzaj bazy dostosowuje polecenie SQL do jej wymagań. Jako format wejściowy zdecydowałem się na ten używany w sqlite3, gdyż wydaje mi się bardziej czytelny. Jeżeli połączenie dotyczy bazy PostgreSQL, to za pomocą wyrażenia regularnego :([0-9a-zA-Z_]+) i metody re.subn parametry są zamieniane na format z psycopg.

Nie jest komfortowym rozwiązaniem, gdy do napisania uniwersalnego kodu trzeba tworzyć dodatkowe klasy tak jak w powyższym przypadku. Choćby z tego powodu bardziej podoba mi się rozwiązanie JDBC albo choćby z Go. Tu można postawić zarzut, że przecież istnieje coś takiego jak ORM i cały mój wywód nie ma sensu. Postaram się na niego odpowiedzieć wkrótce.