Czy zastanawialiście się kiedyś, w jaki sposób korzystać z wektorowych źródeł danych takich jak Państwowy Rejestr Granic, czy TERYT, we współpracy z bazą danych PostGIS, a jednocześnie efektywnie aktualizować taki zbiór? Pewną podpowiedzią może być skorzystanie z Foreign Data Wrappers. To rozszerzenie standardu SQL/MED, które pozwala na dołączanie zbiorów danych zewnętrznych do serwera bazy danych PostgreSQL, nie w postaci importu tabeli, a jedynie interfejsu pomiędzy usługami.

Instalacja

A więc od czego zacząć? Od instalacji rozszerzenia PostgreSQL OGR-FDW https://github.com/pramsey/pgsql-ogr-fdw z repozytorium twórcy, lub z pakietów instalacyjnych odpowiednich dla każdego systemu operacyjnego:

 

Kolejny krok to instalacja rozszerzenia bezpośrednio w bazie danych. Oczywiście jeśli chcecie korzystać z danych przestrzennych, to aktywne rozszerzenie PostGIS jest oczywistą oczywistością. Wywołajmy polecenie:

CREATE EXTENSION ogr_fdw;
Obca tabela

Następny ruch to skorzystanie z dyrektyw FDW w PostgreSQL i wywołanie dwóch zapytań: CREATE SERVER  oraz CREATE FOREIGN TABLE

Ich podstawowa składnia jest następująca:

CREATE SERVER myserver FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (datasource 'xx', format 'xx' );

CREATE FOREIGN TABLE nazwa_tabeli (
nazwa_kolumny typ,
nazwa_kolumny2 typ
OPTIONS (layer 'ogr_layer_name');

Warto zaznaczyć że nie musimy wskazać wszystkich pól atrybutowych z zbioru OGR, a tylko te które są nam potrzebne (oczywiście jeśli przewidujemy również zapisywanie do zbioru, warto jednak wskazać wszystkie pola). Parametry dodatkowe wymagane w naszym przypadku możemy ustalić przy pomocy dodatkowego narzędzia ogr_fdw_info. W standardowej instalacji Ubuntu znajduje się ono w /usr/lib/postgresql/x.x/bin. W pierwszej kolejności możemy ustalić, które formaty danych będą dostępne.

mechanik@en75-01:~/$ ogr_fdw_info -f
Supported Formats:
-> "OGR_GRASS" (readonly)
-> "PCIDSK" (read/write)
-> "netCDF" (read/write)
(...)

Możliwe jest również sprawdzenie które zbiory danych widziane będą przez ogr_fdw we wskazanej ścieżce.
mechanik@en75-01:~/$ ogr_fdw_info -s ~/dev/gis/prg
Layers:
województwa
Państwo
gminy
obreby_ewidencyjne
jednostki_ewidencyjne
powiaty

Najważniejszą funkcjonalnością ogr_fdw_info jest możliwość przygotowania definicji schematu tabeli zewnętrznej.

mechanik@en75-01:~/$ ogr_fdw_info -s ~/dev/gis/prg -l powiaty

CREATE SERVER myserver
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource '/home/mechanik/dev/gis/prg',
format 'ESRI Shapefile' );

CREATE FOREIGN TABLE powiaty (
fid bigint,
geom Geometry(Polygon),
iip_przest varchar,
iip_identy varchar,
iip_wersja varchar,
jpt_sjr_ko varchar,
jpt_kod_je varchar,
jpt_nazwa_ varchar,
jpt_nazw01 varchar,
jpt_organ_ varchar,
jpt_orga01 varchar,
jpt_jor_id real,
wazny_od date,
wazny_do date,
jpt_wazna_ varchar,
wersja_od date,
wersja_do date,
jpt_powier real,
jpt_kj_iip varchar,
jpt_kj_i01 varchar,
jpt_kj_i02 varchar,
jpt_kod_01 varchar,
id_bufora_ real,
id_bufor01 real,
id_technic real,
jpt_opis varchar,
jpt_sps_ko varchar,
gra_ids varchar,
status_obi varchar,
opis_bledu varchar,
typ_bledu varchar
) SERVER myserver
OPTIONS (layer 'powiaty');

Upewnijmy się jeszcze co do uprawnień dostępu do plików (w Ubuntu konieczne jest dostosowanie uprawnień w taki sposób aby użytkownik postgres miał prawa odczytu, oraz zapisu). Tak przygotowane zapytania powinniśmy wywołać z poziomu klienta psql (pgadmin, etc.).

Nasza tabela jest już gotowa do wykorzystania. Teraz łyżka dziegciu. Każde zapytanie z poziomu PostgreSQL powoduje otworzenie połączenia do zbioru zewnętrznego i jego pełne skanowanie. Z punktu widzenia wydajności jest to zabójcze. Ale na to istnieje wspaniały sposób – widok zmaterializowany. Przy okazji możemy ograniczyć ilość kolumn atrybutowych, czy dokonać innych zmian (np. formatowanie ciągów).

CREATE MATERIALIZED VIEW opengov.powiaty AS
SELECT fid, ST_Buffer(geom, 0) AS geometry, jpt_sjr_ko, jpt_kod_je, jpt_nazwa_ AS nazwa
FROM ogr.powiaty;

Query returned successfully: 380 rows affected, 3.7 secs execution time.

Zwrócćie uwagę na czas „importu” danych do tabeli. W normalnych warunkach import Shapefile przy pomocy QGIS trwa kilkadziesiąt sekund.
Zdecydowanie należy przed rozpoczęciem dalszej pracy utworzyć indeks przestrzenny.

Potem wystarczy tylko wywołać

REFRESH MATERIALIZED VIEW schema.tabela;

aby po aktualizacji zbiorów zewnętrznych (np. po pobraniu z serwera GUGiK) były one dostępne.

Kilka trików

Powiecie zapewne że dość pracochłonne rozwiązanie, ale już odpowiadam, istnieje możliwość zaimportowania wszystkich zbiorów ze wskazanej ścieżki (serwera fdw). W tym celu wywołujemy IMPORT FOREIGN SCHEMA z ogr_all, wskazujemy docelową schemę danych.

IMPORT FOREIGN SCHEMA ogr_all
FROM SERVER myserver
INTO ogr;

I już! 😉

Jeszcze jedna uwaga. W trakcie tworzenia serwera FDW możemy podać inne dodatkowe parametry OGR, tak jak przy pracy z linii poleceń, np. wskazać kodowanie znaków w pliku źródłowym:
CREATE SERVER myserver_latin1
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource '/gis/prg',
format 'ESRI Shapefile',
open_options 'ENCODING=CP1250' );

Na koniec taka ciekawostka. OGR potrafi czytać bardzo wiele różnych formatów danych. Potrafi je również przetwarzać w locie. Zobaczcie, nawet WFS możemy podłączyć do naszej bazy PostgreSQL.

$ ogr_fdw_info -s WFS:http://geodezja.powiatoleski.pl:8080/cgi-bin/olesno?SERVICE=WFS -l budynki

CREATE SERVER myserver
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource 'WFS:http://geodezja.powiatoleski.pl:8080/cgi-bin/olesno?SERVICE=WFS',
format 'WFS' );

CREATE FOREIGN TABLE budynki (
fid bigint,
msgeometry Geometry(Geometry,2177),
gml_id varchar,
funkc_uz varchar,
id_budynku varchar,
kat varchar,
l_kond_n varchar,
numer_bud varchar,
pow varchar,
x varchar,
y varchar,
zrodlo varchar
) SERVER myserver
OPTIONS (layer 'budynki');

Życzę wielu ciekawych pomysłów na wykorzystanie tej funkcjonalności. Pochwalcie się swoimi osiągnięciami, ciekawymi zastosowaniami.

Materiały dodatkowe:

dokumentacja OGR FDW – Paul Ramsey

Foreign Data Wrappers na wiki Postgresql

 


tomasznycz

Geograf, kartograf, góral z wyboru