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.