Božo - Orleški borec

Enostavnost geoprostorskih poizvedb v Postgisu

Za prikaz enostavnosti geoprostorskih poizvedb v Postgisu bomo uporabili en praktični primer. Ko sem vnesel v Postgis podatke o jamah iz katastra jam Jamarske Zveze Slovenije sem se lahko začel igrati s podatki. Npr. če želimo izvedeti število jam v vsaki občini Republike Slovenije in njihovo skupno dolžino, tako da naredimo nekakšno lestvico, je dovolj, da v bazo podatkov vnesemo še vektorsko datoteko v obliki shp z poligoni slovenskih občin, tako da lahko pogledamo koliko vhodov jam pade v vsak poligon. To naredimo s sledečim ukazom sql:

SELECT count(gis_schema.obcine.ob_ime) as stevilo, sum(gis_schema.katasterjzs.dolzina) as skupnadolzina, gis_schema.obcine.ob_ime
FROM gis_schema.katasterjzs, gis_schema.obcine
where contains(gis_schema.obcine.the_geom, gis_schema.katasterjzs.the_geom) group by gis_schema.obcine.ob_ime order by stevilo desc;

In dobimo sledeči rezultat:
Total query runtime: 128881 ms.
139 rows retrieved.
Rabilo je samo 129 sekund, ker v skupnem seštevku jam jih manjka 5 lahko še preverimo, da to so jame, ki padejo izven državne meje ali točno na mejo, glej spodnji seznam.

Prvi stolpec je število jam, drugi je skupna dolžina, tretji je ime občine:
786;82178;"BOVEC"
567;32516;"SEŽANA"
553;38066;"LOGATEC"
545;28926;"KOČEVJE"
462;43417;"HRPELJE-KOZINA"
449;22955;"ILIRSKA BISTRICA"
430;24048;"BOHINJ"
415;76536;"POSTOJNA"
383;39270;"CERKNICA"
332;27156;"LUČE"
283;12309;"ČRNOMELJ"
232;12196;"IDRIJA"
211;7250;"LOŠKA DOLINA"
198;11759;"KAMNIK"
183;12835;"NOVA GORICA"
177;6253;"NOVO MESTO"
174;10270;"RIBNICA"
173;5746;"DOLENJSKE TOPLICE"
172;10413;"DOBREPOLJE"
171;12478;"AJDOVŠČINA"
165;13474;"VRHNIKA"
155;5952;"ŽUŽEMBERK"
141;7024;"IVANČNA GORICA"
139;9589;"KOPER"
129;3908;"SEMIČ"
120;4765;"PIVKA"
108;39782;"TOLMIN"
107;24391;"DIVAČA"
103;4598;"KOMEN"
82;3639;"MIREN-KOSTANJEVICA"
74;6627;"GROSUPLJE"
70;4235;"RADOVLJICA"
64;5233;"KOBARID"
63;2606;"TREBNJE"
60;4464;"VIPAVA"
60;1918;"ŠOŠTANJ"
59;2039;"LOŠKI POTOK"
57;3035;"ŽELEZNIKI"
47;2560;"ŠKOFJA LOKA"
47;3343;"GORJE"
42;1310;"BREZOVICA"
39;1430;"IG"
39;4263;"ŽALEC"
38;2412;"BLED"
37;1493;"MIRNA PEČ"
36;1018;"KRANJ"
36;3024;"POLZELA"
35;2330;"SOLČAVA"
34;1461;"VELENJE"
34;3538;"KOSTANJEVICA NA KRKI"
34;2404;"VELIKE LAŠČE"
32;821;"LITIJA"
30;1275;"METLIKA"
30;1460;"MOZIRJE"
29;3688;"MISLINJA"
28;1249;"DOMŽALE"
27;1313;"OSILNICA"
27;3915;"KANAL"
25;1537;"CERKNO"
24;1602;"KOSTEL"
23;1329;"KRANJSKA GORA"
21;765;"DOBROVA-POLHOV GRADEC"
20;681;"BOROVNICA"
20;906;"KRŠKO"
19;1536;"NAZARJE"
19;618;"GORENJA VAS-POLJANE"
18;1714;"MORAVČE"
17;573;"MEDVODE"
17;801;"BRASLOVČE"
16;807;"SEVNICA"
16;354;"MENGEŠ"
15;644;"ZAGORJE OB SAVI"
15;1772;"NAKLO"
15;668;"ŠMARJEŠKE TOPLICE"
15;1232;"BREŽICE"
14;412;"LJUBLJANA"
14;740;"MAKOLE"
14;389;"HORJUL"
13;383;"LJUBNO"
13;247;"LAŠKO"
13;157;"PREBOLD"
12;418;"VRANSKO"
11;318;"MOKRONOG-TREBELNO"
11;245;"ŽIRI"
10;503;"RADLJE OB DRAVI"
10;284;"TRŽIČ"
10;608;"JEZERSKO"
9;177;"SLOVENJ GRADEC"
8;418;"REÈICA OB SAVINJI"
8;234;"GORNJI GRAD"
8;118;"CERKLJE NA GORENJSKEM"
8;185;"ČRNA NA KOROŠKEM"
8;117;"ŠMARTNO OB PAKI"
8;279;"KOZJE"
8;178;"BRDA"
8;501;"ŠENTJUR"
8;269;"VODICE"
7;236;"STRAŽA"
7;204;"SODRAŽICA"
6;93;"VOJNIK"
6;271;"BLOKE"
6;148;"TABOR"
6;103;"HRASTNIK"
5;140;"ŠENTJERNEJ"
5;138;"ŽIROVNICA"
5;58;"PREDDVOR"
4;58;"ŠKOCJAN"
4;117;"ŠTORE"
4;129;"ZREČE"
4;118;"RADEČE"
4;135;"LOG-DRAGOMER"
4;164;"ŠMARTNO PRI LITIJI"
4;131;"TRBOVLJE"
4;66;"IZOLA"
4;70;"ROGAŠKA SLATINA"
3;30;"CELJE"
3;51;"JESENICE"
3;251;"POLJÈANE"
3;43;"RAVNE NA KOROŠKEM"
2;37;"LENART"
2;46;"KUNGOTA"
2;25;"PREVALJE"
2;45;"LUKOVICA"
2;64;"DOBRNA"
2;44;"ŠENČUR"
2;16;"SLOVENSKE KONJICE"
2;17;"TRZIN"
2;58;"ŠKOFLJICA"
2;242;"PODČETRTEK"
1;20;"SLOVENSKA BISTRICA"
1;31;"BISTRICA OB SOTLI"
1;330;"ŠMARJE PRI JELŠAH"
1;75;"ROGATEC"
1;107;"DUPLEK"
1;16;"ŠENTRUPERT"
1;50;"CIRKULANE"
1;25;"DOBJE"
1;10;"MEŽICA"
1;10;"RAČE-FRAM"

Ker v skupnem seštevku manjka 5 jam naredimo en outer left join, da ugotovimo, če tiste ki manjkajo so res jame ki padejo izven državne meje ali točno na mejo in torej koordinate vhoda ne padejo v nobeno občino, query izgleda takole:
SELECT gis_schema.katasterjzs.kat_st, gis_schema.katasterjzs.ime, gis_schema.obcine.ob_ime
FROM gis_schema.katasterjzs
LEFT OUTER JOIN gis_schema.obcine
on contains(gis_schema.obcine.the_geom, gis_schema.katasterjzs.the_geom) order by gis_schema.obcine.ob_ime;

To pa so tiste jame katerih koordinate vhoda ne padejo v nobeno občino:
3706;"Jama vrh Golega hriba";""
7222;"Su 1 (Surinka)";""
851;"Pečenevka";""
1793;"Jama pod Krogom";""
4546;"Velko zjulo";""

Nalaganje podatkov v bazo Postgis

Najbolj enostaven način za nalaganje podatkov v podatkovno bazo Postgis je z uvozom datotek shp.
To lahko naredimo z ukazom shp2pgsql s katerim ustvarimo datoteko v jeziku sql za prenos v bazo podatkov preko ukaza psql, kot vedno to opravimo kar v ukazni vrstici.

Če imamo naprimer eno datoteko shp z našimi waypointi v projekciji EPSG:3787 (D48 za Slovenijo),
Sql datoteko z ukazi sql za vnos podatkov ustvarimo takole:


$ shp2pgsql -I -s 3787 waypoints3787.shp gis_schema.tocke > tocke.sql
Shapefile type: Point
Postgis type: POINT[2]

Datoteka tocke.sql bo vsebovala ukaz CREATE TABLE s katerim bo ustvarjena nova tabela v podatkovni bazi in vrsta ukazov INSERT za vnos podatkov.

Uporabili smo dve opciji za program shp2 shp2pgsql:
-I zato da bo ustvarjen index GiST index na koloni geometry
-s zato da PostGIS razume v kateri projekciji so koordinate, t.i. srid , v našem primeru projekcija EPSG:3787 (Gauss Kruger D48)

Datoteko bomo potem izvedli preko psql takole, kot uporabnik gis:

$ psql -d gisdb -h localhost -U gis -f tocke.sql

dean@refolo:~$ psql -d gisdb -h localhost -U gis -f tocke.sql

Postgis bo izvedel ukaze in odgovoril takole:

BEGIN
psql:tocke.sql:21: NOTICE: CREATE TABLE will create implicit sequence "tocke_gid_seq" for serial column "tocke.gid"
psql:tocke.sql:21: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tocke_pkey" for table "tocke"
CREATE TABLE
addgeometrycolumn
--------------------------------------------------------
gis_schema.tocke.the_geom SRID:3787 TYPE:POINT DIMS:2
(1 row)

INSERT 0 1
INSERT 0 1
....

INSERT 0 1
CREATE INDEX
COMMIT


Seveda lahko ukaze sql za ustvariti novo tabelo in vnesti podatke v Postgis napišemo tudi sami.
Dovolj je da spišemo programček, ki bere podatke za vnos, (največkrat bomo imeli podatke v obliki csv datoteke) in spiše ustrezne ukaze sql.
Tako tehniko sem naprimer uporabil za vnos podatkov vhodov jam iz katastra Jamarske Zveze Slovenije, kjer sem imel na razpolago datoteko Excel, ki sem jo pretvoril v csv in y enostavnim programčkom v jeziku Free Pascal sem preko parserja csv prebral polja in ustvaril ukaze za insert.

Še bolj enostavno pa je, da podatke v datoteki csv vnesemo v Postgis z ukazom COPY, na tej povezavi dobite natančno razlago postopka.

Uporaba geoprostorskih informacijskih sistemov

Universitat de Girona je pripravila zelo učinkovit video za predstavitev pomembnosti geoprostorskih informacij in sistemov GIS

Nastavitev vlog in uporabnika v Postgis-u

Na splošno velja varnostno pravilo da ni primerno opravljati vnos GIS podatkov v podatkovno bazo PostGIS database kot uporabnik (in z vlogo) postgres, ki bi morala biti uporabljen samo za administrativno delo.
Zato bomo ustvarili novo vlogo in uporabnika GIS za upravljanje s podatki v bazi podatkov PostGIS. Lahko celo ustvarite več uporabnikov z različnimi dovoljenji (SELECT, INSERT, UPDATE, DELETE na različnih tabelah), tako da ustvarite bolj varno okolje, vse je odvisno od vaših potreb pri uporabi GIS-a.

Povežemo se na postgres (kot uporabnik postgres ): psql
in vnesemo v ukazno vrstico naslednji ukaz da ustvarimo vlogo skupine ki jo bomo imenovali gisgroup (lahko izberete manj dovoljenj če to rabite iz varnostnih razlogov):

CREATE ROLE gisgroup NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE;

natipkate še tole zato da ustvarite vlogo za login, imenovali jo bomo gis (lahko izberete katerokoli ime in geslo):

CREATE ROLE gis LOGIN PASSWORD 'mojegeslo' NOINHERIT;

vključimo sedaj uporabnika gis v skupino gisgroup :

GRANT gisgroup TO gis;

DODELJEVANJE DOVOLJENJ:

Dodeliti moramo dovoljenja za tabele našega postgistemplate (lastnik tabel geometry_columns in spatial_ref_sys bo postal uporabnik gis):

najprej se odklopimo iz prejšnje povezave (vtipkamo \q), in se povežemo na bazo podatkov postgistemplate kot uporabnik postgres:

psql -d postgistemplate

dodelimo dovoljenja:

ALTER TABLE geometry_columns OWNER TO gis;
ALTER TABLE spatial_ref_sys OWNER TO gis;

Ustvarimo nov schema za naše podatke GIS (bi ne smeli ustvariti podatkov GIS v public schema):

CREATE SCHEMA gis_schema AUTHORIZATION gis;

odklopimo se od povezave (\q)



Sedaj lahko ustvarimo bazo podatkov kamor bomo naložili podatke (imenovali jo bomo gisdb), z uporabo ukaza createdb, iz modela postgistemplate ki smo ga pravkar ustvarili:
kot uporabnik postgres vnesemo ukaz
$ createdb -T postgistemplate -O gis gisdb

Priprava modela za Postgis

Za prvo ureditev Postgisa po nastvitvi, mi je veliko pomagal dober članek, ki ga je napisal in objavil na spletu Paolo Corti:
http://www.paolocorti.net/2008/01/30/installing-postgis-on-ubuntu/
v katerem med drugim opiše pripravo modela, ki ga lahko potem uporabljamo za pripravo baz podatkov in si s tem prihranimo precej dela za nadaljne nastavitve novih baz podatkov,
Nastavitev poteka kar preko ukazne vrstice.

Za pripravo modela se povežemo kot uporabnik postgres:

sudo su postgres

Pripravimo bazo podatkov, ki bo služila kot model, imenujemo jo npr. postgistemplate:

createdb postgistemplate

Ji dodamo možnost za upravljanje preko proceduralnega jezika PL/pgSQL za rdbms PostgreSQL, ki ga potrebuje PostGIS, v ukazno vrstico vnesemo:

createlang plpgsql postgistemplate

preko odjemalca psql izvršimo naslednja dva ukaza:

psql -d postgistemplate -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql -d postgistemplate -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql

Naš template je pripravljen, dodano je bilo veliko funkcij in sta bili ustvarjeni dve tabeli - geometry_columns and spatial_ref_sys.
Lahko sedaj poskusimo model postgistemplate, ki smo ga pravkar ustvarili:
psql -d postgistemplate -c "SELECT postgis_full_version();"



postgis_full_version
-------------------------------------------------------------------------------------------------------
POSTGIS="1.5.1" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.6" USE_STATS


Tabela spatial_ref_sys vsebuje podatke o projekcijah, ki jih lahko uporabljamo v bazi podatkov za vnos geografskih objektov.
Spatial_ref_sys je bila ustvarjena s sledečim ukazom sql (ko je bila ustvarjena baza podatkov z ukazom createdb postgistemplate):
CREATE TABLE spatial_ref_sys
(
srid integer NOT NULL,
auth_name character varying(256),
auth_srid integer,
srtext character varying(2048),
proj4text character varying(2048),
CONSTRAINT spatial_ref_sys_pkey PRIMARY KEY (srid)
)
WITH (
OIDS=FALSE
);

V stolpcu srid so identifikacijske številke projekcij (EPSG), medtem ko je v stolpcu proj4text definicija projekcije kot jo uporabljamo v knjižnici proj4.
Leta 2008 je Geodetska uprava registrala pri skrbniku zbirke EPSG parametra koordinatnih sistemov SI-D48 (pod številko CODE 3787) in SI-D96 (pod številko CODE 3794) tako da jih sedaj dobimo tudi v tej tabeli.
Če pa bi v tej tabeli ne dobili parametrov koordinatnega sistema ki ga rabimo, jih lahko vnesemo z ukazom sql s programom psql vedno kot uporabnik postgres. Naprimer če bi hoteli ročno vnesti parametre D48 z 7 parametri za Primorsko in Notranjsko (operacija seveda ni potrebna ker sedaj imamo parametre za EPSG:3787), se najprej povežemo na bazo podatkov:

postgres@veciaruga:/home/dean$ psql -d postgistemplate
psql (8.4.4)

in potem vnesemo projekcijo D48 s 7 parametri za proj4, ji damo npr. identifikacijsko številko 111111 ker je še neobstoječa in si jo tako z lahkoto zapomnimo:

postgistemplate=# INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) VALUES (111111, 'DEAN', 111111, '', '+proj=tmerc +lat_0=0 +lon_0=15 +k=0.9999 +x_0=500000 +y_0=-5000000 +ellps=bessel +units=m +towgs84=355.803451,274.321338,462.96856,9.087389,6.490416,-14.502467,20.888271 +no_defs -f %.3f');

odgovor ki potrjuje da je operacija izvršena:
INSERT 0 1

če se želite prepričati, da je v tabeli spatial_ref_sys res prisotna nova projekcija, kar izvedete v programu psql ukaz sql:
postgistemplate=# select * from spatial_ref_sys where srid=111111; in dobite tole:


srid | auth_name | auth_srid | srtext | proj4text
--------+-----------+-----------+--------+-------------------------------------------------------------------------------------------------------
111111 | DEAN | 111111 | | +proj=tmerc +lat_0=0 +lon_0=15 +k=0.9999 +x_0=500000 +y_0=-5000000 +ellps=bessel +units=m
+towgs84=355.803451,274.321338,462.96856,9.087389,6.490416,-14.502467,20.888271 +no_defs -f %.3f

za izhod iz programa psql vpišemo ukaz:

postgistemplate-# \q
postgres@veciaruga:/home/dean$

Sedaj ko je model postgistemplate pripravljen ga lahko uporabimo za pripravo naših baz podatkov, tako da bodo vsakič vsebovale vse tabele in projekcije in ne bo treba vsakič ponavljati vseh prejšnjih ukazov ko bomo želeli ustvariti novo bazo podatkov.

Nastavitev prostorske baze podatkov PostGIS na sistemu Ubuntu Linux

Prostorska baza podatkov je baza podatkov, ki omogoča uporabo posebnih podatkovnih tipov za geometrične objekte in omogoča shranjevanje geometričnih podatkov (po navadi geografske narave) v navadnih tabelah baze podatkov. Podpira posebne funkcije in indekse za manipulacijo in poizvedbo preko jezika SQL (Structured Query Language). Torej ni samo način za shranjevanje prostorskih podatkov, omogoča njihovo analizo in pri tem nima omejitev, ki jih ima grafična ponazoritev prostorskih podatkov na topografski karti, predvsem pri analizi podatkov v več dimenzijah. V jamarskem društvu JOSPDTrst uporabljamo prostorsko bazo podatkov PostGIS za shranjevanje podatkov o novih vhodih jam in za obdelavo geografskih podatkov jamarskega katastra.

Postgis je razširitev odprtokodnega relacijskega sistema za upravljanje baz podatkov Postgresql, ki ga je treba namestiti preden namestimo postgis.
Ubuntu Linux vsebuje v svojem uradnem skladišču paketov le stabilne različice programov. Če si želimo, kot v našem primeru, nastaviti zadnjo različico, je na voljo posebno skladišče UbuntuGIS:https://wiki.ubuntu.com/UbuntuGIS
Nastavitev ubuntugis-unstable je zelo enostavna, kar iz upravne vrstice vnesemo ukaza:

sudo add-apt-repository ppa:ubuntugis/ubuntugis-unstable

sudo apt-get update

Potem pa lahko nastavimo zadnjo različico programov Quantum GIS (bo nastavljena avtomatično zadnja različica knjižnice libgeos in proj4 ki jih uporablja tudi postgis):

sudo apt-get install qgis
(bo nastavljena tudi libgeos 3.2.2 in proj4)

Namestimo sedaj postgresql in pgadmin3, nato pa lahko nastavimo še postgis. Ne navajam podrobnosti za nastavitev, saj je uporaba apt-get ali programa synaptic dovolj enostavna, raje si bomo ogledali v naslednjem članku podrobno nastavitev Postgisa.

Reka večne noči

Vsakič ko grem na sprehod v Park Škocjanske jame se ustavim na razgledni točki nad Veliko Dolino, ogromno udornico pod vasico Škocjan in poslušam bučanje Reke v slapu, ki se nahaja 165 metrov globoko na dnu udornice. Tokrat je bil vodotok precejšen, slap je ustvaril pravo meglico na dnu udornice.
In takoj pomislim na še nerešeno uganko podzemnega toka Reke. Čeprav vsako leto raziskovalci in moderni jamarski delavci priskrbijo za dodatek temu mozajiku, zgodba še zdaleč ni končana, prav to ji daje poseben čar...

GrottenarBeiter and the river of the night from Alessandro Beltrame on Vimeo.