postgresql paikkatietomies

Monikulmiossa reikä, eikä! – reikien poisto PostGIS-kannassa

Monikulmioiden sisällä olevat reiät aiheuttavat silloin tällöin ongelmia erilaisissa paikkatietoanalyyseissa. Ajoittain tuleekin tarve saada eliminoitua monikulmion sisällä olevat reiät ilman käsin tehtävää naputtelua (ns. automaattinen reikien poisto). Yksittäiset reiät on monesti helpompi poistaa käsin, mutta mikäli poistettavia reikiä on satoja, on jonkinlaisen automaation rakentaminen jo hyvinkin mielekästä. Automaation rakentamiselle on perusteet myös silloin, kun reikien poistoa ajetaan osana jotakin paikkatietokohteiden massakäsittelyä. Loistavat työkalut paikkatietokohteiden automaattiseen käsittelyyn tarjoaa PostGIS-tietokanta, jonka spatiaalifunktioiden toiminnallisuuksia yhdistelemällä pystyy rakentamaan käyttökelpoisen ratkaisun reikien poistamiseksi. PostGIS-tietokanta on avoimen lähdekoodin relaatiotietokanta, jonka paikkatieto-ominaisuudet ovat aivan omaa luokkaansa.

Reikien eliminointi PostGIS:ssä funktioiden toiminnallisuuksia yhdistäen

Oma esimerkkini on seurausta tilanteesta, jossa olen ensin sulauttanut yhteen topologialtaan epäeheitä geometrioita ST_Union-spatiaalifunktiota hyödyntäen, ja huomannut lopputuloksen sisältävän n kappaletta pienikokosia ja vaikeasti käsin poistettavia reikiä. Siispä pgAdmin auki ja tuumasta toimeen. Tässä eräs ratkaisu reikien poistamiseksi.

SELECT
ST_Collect
 (
  ST_MakePolygon(reiallinen_geometria.ulkopiiri_geom)
 ) AS geom 
FROM 
 (
  SELECT ST_ExteriorRing
   (
    (
     ST_Dump(geom)
    ).geom
   ) AS ulkopiiri_geom 
  FROM taulu_jossa_reiallinen_geometria
 ) AS reiallinen_geometria
;

Yllä esitetty ratkaisu perustuu siihen, että ensiksi tunnistetaan monikulmion sisällä olevat reiät. Lopullista reiätöntä geometriaa luotaessa nämä tunnistetut reiät ohitetaan, jolloin lopputuloksena on reiätön monikulmio. Spatiaali SQL:n näkökulmasta ratkaisussa hyödynnetään useita eri PostGIS:n funktioita, joista ST_Collect-funktiota hyödynnetään eri geometriatyyppien keräämiseksi yhdeksi geometriaksi, ja ST_MakePolygon-funktiota siten, että sen avulla muodostetaan ST_ExteriorRing-funktion palauttamasta viivageometriasta monikulmio. ST_ExteriorRing-funktiota käytetään puolestaan hyödyksi erotettaessa monikulmion sisällä olevat reiät keräämällä reiän sisältävän monikulmion ulkoraja eli piiri viivageometriaksi. ST_Dump-funktion avulla ohitetaan reikien piirit.

 

Spatiaali SQL:n näkökulmasta ratkaisussa hyödynnetään useita eri PostGIS:n funktioita.

 

Lopputulos ja jatkokehitysidea

Testasin kyselyä muutamilla rei’ällisillä monikulmiolla / PostGIS-tauluilla, joissa paljon on paljon rei’ällisiä geometrioita. Jokaisella kerralla reikien poisto näyttäisi onnistuneen, joten menetelmä näyttäisi toimivan tilanteessa, jossa kaikki taulussa olevat geometriat halutaan yhdistää yhdeksi moniosaiseksi monikulmioksi. Uskallan suositella menetelmää pienellä varauksella myös muille, mikäli rei’älliset monikulmiot piinaavat. Sain jo heti päähäni jatkokehitysidean, että jalostan em. kyselyn itsenäiseksi PostgreSQL-funktioksi. Tällöin menetelmää voisi hyödyntää kätevästi osana muita PostgreSQL-kannassa ajettavia kyselyjä vaikkapa seuraavalla tavalla:

SELECT poista_reiat(geom) AS geom
Reikien poisto PostGIS-kannassa
Vasemmalla kuvassa on monikulmio, jossa on reikä (punainen geometria). Oikealla kuvassa on sama monikulmio (sininen geometria), jonka geometria “on käsitelty” reiät poistavalla SQL-kyselyllä.

Kiitos Laurille artikkelissa esitellyn menetelmän testaamisesta ja parannusehdotuksesta (ks. kommentit). Mikäli tavoitteena on ratkaisu, jossa lopputulokseksi halutaan taulu, jossa on useita eri id:n omaavia monikulmioita, kannattaa Laurin esittämää menetelmää koeponnistaa.

Olisiko sinulla tarvetta monikulmioiden reiät poistavalle PostgreSQL-funktiolle, vai oletko jo luonut itse sellaisen? 

2 KOMMENTTIA
  • Lauri
    Vastaa

    Hei,
    Kiitos uudesta blogista!

    Pieni kommentti tähän reikienpoistoSQL:ään.
    SQL kyllä toimii, mutta perustelusi ei mielestäni osu aivan oikeaan.

    Pelkästään ST_MakePolygon(ST_ExteriorRing(geom)) pitäisi toimia, jos kyseessä olisi pelkkiä yksi-osaisia polygoneja. Ilmeisesti sinulla polygonit ovat MultiPolygoneja, eli koostuvat useasta osata, jolloin ST_ExteriorRing ei toimi. ST_Dump:a jakaa multipolygonit tavallisiksi polygoneiksi (sisältää edelleen reikiä). Tämän jälkeen yllä kuvattu toimii. ST_Collect tekee reiättömille polygoneille käänteisen operaation kuin st_dump, eli kasaa niistä taas multipolygoneja. Paitsi nyt kyselysi palauttaa ainoastaan yhden moniosaisen multipolygonin, jos taulussa on useita rivejä. Kyselyn perään pitäisi lisätä group by id (tai joku muu yksilöivä id.).

    Eli kysely tulisi muotoon:
    SELECT
    id,
    st_collect(reiattomat.geom) AS geom
    FROM
    (SELECT
    id,
    ST_MakePolygon(
    ST_ExteriorRing(
    (ST_Dump(geom)).geom
    )
    ) as geom
    FROM holes
    ) as reiattomat
    GROUP BY id;

    1. Paikkatietomies
      Vastaa

      Hei Lauri, ja kiitos arvokkaasta kommentista.

      Kappas vaan, kyselyni tosiaan kerää kaikki taulussa olevat monikulmiot yhden monesta osasta koostuvan monikulmion osasiksi (MultiPolygon) ilman tuota esittämääsi GROUP BY -määrittelyä. Täytyy lisätä se tuonne artikkelissa esitettyyn kyselyyn. En testaillessani näemmä tajunnut vilkaista ominaisuustietotaulua sen tarkemmin, vaan katsoin ainoastaan visuaalisesti, että reiät ovat poistuneet. Omassa käyttötapauksessani yksi yhtenäinen monikulmio ilman reikiä (yksi geometria taulussa) oli riittävä/toivottava lopputulos, joten en sen tarkemmin osannut edes tarkastella lopputulosta.

      Ja tosiaan kuten uumoilitkin, oli allekirjoittaneen rei’ällisessä aineistossa juurikin MultiPolygoneja, jolloin pelkkä ST_ExteriorRing ei suostunut toimimaan. Lähinkin liikkeelle siitä ajatuksesta, että MultiPolygon pitää räjäyttää Single Part -polygoneiksi, poistaa niistä rei’ät ja kerätä Single Partit jälleen Multi Part polygoneiksi.

      Minulla on tässä muutama PostGIS-kirjoitus hautumassa, joten toivottavasti sinulla olisi jatkossakin aikaa käydä tiirailemassa kyselyjäni ja esittämässä parannusehdotuksia 🙂

Vastaa

Sähköpostiosoitettasi ei julkaista. Pakolliset kentät on merkitty *