Julkaistu 23.3.2020

Tökkiikö SQL:n opiskelu? Mikä apuun?

SQL on ohjelmointikieli, josta on paljon hyötyä paikkatietoasiantuntijalle. On raastava pohtia, kuinka paljon aikaa on hukannut tiettyjen paikkatietotehtävien toimittamiseen GIS Desktop -työkaluilla sen sijaan, että olisi osannut toimittaa kyseiset paikkatietotehtävät SQL-komennoilla. Tietokantaohjelmien tapa esittää kyselyjen ajamiseen kulunut aika, usein vain millisekunneissa, ei vähennä tätä tuskaa. Tai ehkä raastavinta on se, että ei aikanaan ryhtynyt  opiskelemaan SQL:ää, vaan jäi kamppailemaan kysymyksen “Opinkohan minä ohjelmoimaan?” kanssa.

Kuvitellaan, että emme jääneet tuskailemaan tämän kysymyksen kanssa ja pääsimme opiskelemaan SQL:ää ripeästi. Minua auttoi huomattavasti opittuani, että SQL-komentoja kirjoittaessa on tärkeä aloittaa kysymyksellä: mitä haluan nähdä tuloksena syntyvässä taulussa? Kuulostaa lähtökohtaisesti loogiselta, mutta syvennytään vielä pohtimaan ajatusta: siis minkälaisen tulostaulun haluan nähdä SQL-kyselyn ajamisen tuloksena?

Jos vaikka haluaisimme selvittää, miten Yhdysvallat halkovan tienumero 20 (eng. “US Route 20”) jakautuu eri osavaltioiden välille pituudeltaan, voisimme aloittaa hahmottelemalla mielessämme, minkänäköisen taulun haluamme nähdä kyselyn lopputuloksena. 

SQL
Esimerkin lähtöaineistot visualisoituna QGISissa.

Emme halua vain tietoa osavaltion nimestä ja tien pituudesta osavaltion sisällä, vaan haluamme myös nähdä osavaltiot halkovat tiegeometriat.  Näin tauluun tulee sarakkeet osavaltio, tie_pituus ja geom (paikkatiedon sisältävä sarake). Kuvittelemme mielessämme taulun rakenteen: 

osavaltiotie_pituusgeom
x…x…x…

On tärkeä pohtia, miltä taulumme näyttää kyselymme tuloksena, sillä SQL on niin sanotusti deklaratiivinen ohjelmointikieli, jota hyödynnettäessä on tärkeä miettiä vastauksia kysymyksiin “Mitä…?” sen sijaan että vastattaisiin kysymykseen “Miten…?”. Tässä mielessä muodostammekin SQL-kyselymme tapaan: SELECT <jotain> FROM <jostain>, ottamatta kantaa siihen, miten se tieto sieltä tietokannasta haetaan. Näin ollen voidaan todeta, että teemme “deklaraation” siitä, mitä haluamme tulostaulusssa nähdä.  

Jos palaamme takaisin käytännön esimerkkiimme, ideanamme on laskea teiden osavaltiokohtaiset etäisyydet ja yhdistää osavaltion sisäiset tiesegmentit toisiinsa. SQL-kyselyn voisi toimittaa esimerkiksi näin:

SELECT s.name AS osavaltio,
    ROUND(SUM(ST_Length(ST_Intersection(r.geom::geography, s.geom::geography)))::numeric, 1) AS tie_pituus,
    ST_Collect(ST_Intersection(r.geom, s.geom)) AS geom
    FROM roads AS r, states AS s
    WHERE ST_Intersects(r.geom, s.geom) and number = '20' and class ='Federal'
    GROUP BY osavaltio
    ORDER BY tie_pituus desc;

Kyselyn ajaminen vei PostGISiltani 328 millisekunttia. Pohja-aineistona meillä oli käytössä Natural Earthilta Yhdysvaltain päätiestö ja osavaltiot US Census Bureaulta. Tuloksena saamme: 

SQL

Ja karttanäkymältään:

SQL
Karttanäkymä avoimen lähdekoodiin perustuvassa tietokantojen hallintatyökalussa DBeaverissa

Huomaamme myös, kuinka kunkin osavaltion osalta syntyi yksi tiegeometria usean tiesegmentin sijaan. 

SQL

Voimme tutkailla myös SQL-kyselyn sisältöä ja siten pyrkiä hahmottamaan kyselyn logiikkaa:

SQL
  1. ST_Length-funktio laskee viivan pituuden, kun taas sen sisälle asetettu ST_intersection-funktio ristiinleikkaa teiden geometriat (r.geom) osavaltioiden monikulmiogeometrioilla (s.geom).
  2. ST_Collect-funktio kerää nimensä mukaisesti geometrioita yhdeksi paikkatietokohteeksi ja sen sisälle asetettu ST_Intersection ristiinleikkaa geometriat toisillansa edeltävän kohdan tapaan. Tämän kohdan tuloksena syntyy osavaltiokohtaiset tiegeometriat. 
  3. WHERE-ehdon sisälle on asetettu ehdot, joiden avulla filtteroidaan lähtöaineistoja. ST_Intersects-funktio  antaa tuloksena totuusarvon sen mukaan, ristiinleikkaavatko paikkatietogeometriat toisensa. 
  4. GROUP BY -lauseke ryhmittää tulokset, tässä tapauksessa osavaltion mukaan.

Tärkeintä on hahmottaa SQL-kyselyiden logiikka ja muistaa, että alkutaipaleen jälkeen, perusasiat ymmärrettyä, kyselyiden tuottaminen on huomattavasti helpompaa. Mentyäsi eteenpäin opiskeluissa voit lukea myös kollegani Topin erinomaisen artikkelin PostGISin käytöstä.

Siinä päästään asiaan huomattavasti syvemmälle ja saadaan oiva annos konkreettisia oppeja spatiaalisen SQL:n ja siten PostGISin käyttöön.  

Me Gispossa teemme jatkuvasti töitä tietokantojen ja erityisesti PostGISin kanssa. Välillä työn alla on tietomallinnus paikkatietoaineistojen tuottamiseen, mittavien paikkatietoaineistojen analysointi ja ydintietovarantojen tallentaminen ja ylläpito PostGIS-tietokannaksi. Meille on aina ilo keskustella PostGISin käytöstä eri toimintaympäristöissä ja eri paikkatieto-ongelmien ratkomiseen. Jos kiinnostut tästä tematiikasta, olethan yhteyksissä meihin.

Lopuksi, PostGIS-koulutuksiemme kautta pääsee pitkälle myös SQL:än opiskelun suhteen eli pysy myös kärryillä koulutuskalenteristamme tai pyydä tarjous asiakaskohtaisesta koulutuksesta.

Profiilikuva

Santtu Pyykkönen

Santtu Pyykkönen on aluetieteen HM, jota kiinnostaa erityisesti paikkatieto kaikkine ulottuvuuksineen, avoimen lähdekoodin ohjelmistot ja yhteisöt sekä kaupunkikehitys. Harrastuksina mm. juokseminen ja lukeminen.