7 Harjoitus 6: Spatiaaliset relaatiot

Harjoituksen sisältö - Harjoitellaan PostGISin spatiaalisiin relaatioihin liittyvien funktioiden käyttöä.

Harjoituksen tavoite - Harjoituksen jälkeen opiskelija tuntee PostGISin spatiaalisen relaatioihin liittyvät funktiot ja osaa niiden avulla tehdä yksinkertaisia analyysejä.

7.0.1 Valmistautuminen

Avaa pgAdmin selaimeen ja kirjaudu sisään. Avaa Query Tool (Valitse trainingdatabase -> Ylhäältä Tools -> Query Tool).

7.0.2 Käytettäviä funktioita

Tässä harjoituksessa hyödynnetään ainakin näitä funktioita:

PostGIS-funktio	Toiminta
ST_Contains(geometry A, geometry B)	Palauttaa “TOSI”, jos A sisältää B:n
ST_Crosses(geometry A, geometry B)	Palauttaa “TOSI”, jos A leikkaa B:tä
ST_Disjoint(geometry A , geometry B)	Palauttaa “TOSI”, jos geometriat eivät leikkaa toisiaan
ST_Distance(geometry A, geometry B)	Palauttaa geometrioiden välisen minimietäisyyden
ST_DWithin(geometry A, geometry B, radius)	Palauttaa “TOSI”, jos A on lähempänä B:tä kuin annettua etäisyyttä
ST_Equals(geometry A, geometry B)	Palauttaa “TOSI”, jos A on sama kuin B
ST_Intersects(geometry A, geometry B)	Palauttaa “TOSI”, jos A leikkaa B:tä
ST_Overlaps(geometry A, geometry B)	Palauttaa “TOSI”, jos A ja B ovat päällekkäin, mutteivät kuitenkaan toistensa sisäpuolella
ST_Touches(geometry A, geometry B)	Palauttaa “TOSI”, jos A:n reuna koskettaa B:tä
ST_Within(geometry A, geometry B)	Palauttaa “TOSI”, jos A on B:n sisäpuolella

7.1 Harjoitus 6.1

Etsitään ne kunnat, joissa on lentokenttä:

SELECT
a.kunta_ni1
FROM 
nlsfi.hallintoalue a, nlsfi.lentokenttapiste b
WHERE
ST_Contains(a.wkb_geometry, b.wkb_geometry);

Huomaa kuinka kirjaimia a ja b käytetään kyselyssä aliaksina, eli sarakkeiden korvaavina niminä. Aliasten käyttö helpottaa varsinkin pitkien kyselyiden tulkitsemista ja kirjoittamista. Voit lukea lisää aliaksista esimerkiksi täältä: postgresql-alias.

Montako lentokenttää Suomessa on?

SELECT
...
FROM
...

-- Millä funktiolla voit laskea tietueiden määrän?
SELECT
funktio(id)
FROM
nlsfi.taulu;
-- Täydennä oikea taulu

SELECT
count(ogc_fid)
FROM
nlsfi.lentokenttapiste;

Jos lisäät SELECT-komentoon parametrin DISTINCT, saat selville kuinka monta lentokentällistä kuntaa Suomessa on.

SELECT

FROM
(SELECT DISTINCT

FROM

WHERE
ST_Contains(..., ...));

-- käytä sisäkkäistä kyselyä
-- sisäkkäiselle kyselyllä annetaan alias
SELECT
funktio(kunta_distinct)
FROM
(SELECT DISTINCT
a.kunta_ni1 as kunta_distinct
FROM
nlsfi.hallintoalue a, nlsfi.lentokenttapiste b
WHERE
sisältää(... , ... ));
-- Millä PostGIS- funktiolla voit tarkastaa onko geometria b geometrian a sisällä?

SELECT
count(kunta_distinct)
FROM
(SELECT DISTINCT
a.kunta_ni1 as kunta_distinct
FROM
nlsfi.hallintoalue a, nlsfi.lentokenttapiste b
WHERE
ST_Contains(a.wkb_geometry, b.wkb_geometry)) as DistincKunta;

7.2 Harjoitus 6.2

Missä Suomen kunnissa on kaksi lentokenttää?

SELECT ...

-- Vaihtoehto 1
-- Käytä CTE:tä
WITH apm AS (
SELECT DISTINCT
a.kunta_ni1, COUNT(...) OVER(PARTITION BY ...) AS CountAirports
FROM
nlsfi.hallintoalue a, nlsfi.lentokenttapiste b
WHERE
sisältää(a.wkb_geometry, b.wkb_geometry)
ORDER BY
CountAirports DESC
)

-- tarkastele tuloksia:

SELECT
kunta_ni1 as "Municipality", countairports AS "Count of Airports"
FROM
apm
WHERE
CountAirports = 2;

-- Vaihtoehto 2
SELECT 
a.kunta_ni1 as "Kunta", count(...) as "Lkm"
FROM
nlsfi.hallintoalue a, nlsfi.lentokenttapiste b
WHERE
sisältää(a.wkb_geometry, b.wkb_geometry)
GROUP BY
a.kunta_ni1
HAVING count(...) = ...;

-- täydennä tyhjät kohdat. Valitse oikea PostGIS- funktio

Perusteellinen ratkaisuvaihtoehto:

WITH apm AS (
SELECT DISTINCT
a.kunta_ni1, COUNT(a.kunta_ni1) OVER(PARTITION BY a.kunta_ni1) AS CountAirports
FROM
nlsfi.hallintoalue a, nlsfi.lentokenttapiste b
WHERE
ST_Contains(a.wkb_geometry, b.wkb_geometry)
ORDER BY
CountAirports DESC
)

SELECT
kunta_ni1 as "Municipality", countairports AS "Count of Airports"
FROM
apm
WHERE
CountAirports = 2;

Pelkistetympi ratkaisuvaihtoehto:

SELECT 
a.kunta_ni1 as "Kunta", count(*) as "Lkm"
FROM
nlsfi.hallintoalue a, nlsfi.lentokenttapiste b
WHERE
ST_Contains(a.wkb_geometry, b.wkb_geometry)
GROUP BY
a.kunta_ni1
HAVING count(*) = 2;

Suorita ao. SQL-lause. Mitä sillä saadaan selville?

SELECT
ST_ASText(a.wkb_geometry), a.ogc_fid
FROM
nlsfi.lentokenttapiste a, nlsfi.lentokenttapiste b
WHERE
ST_Equals(a.wkb_geometry,b.wkb_geometry) AND a.ogc_fid <> b.ogc_fid;

SQL- kysely palauttaa ne tietueet, joiden geometria on identtinen, mutta id eri.

7.3 Harjoitus 6.3

Etsitään ne kunnat, joissa ei ole rautatietä:

SELECT 
b.kunta_ni1
FROM
(SELECT
 ST_union(wkb_geometry) as wkb_geometry
 FROM
 nlsfi.rautatieviiva) as a, nlsfi.hallintoalue b
WHERE
ST_Disjoint(a.wkb_geometry, b.wkb_geometry);

7.4 Harjoitus 6.4

Etsitään kolme lähintä lentokenttää.

K Nearest Neighbours -menetelmällä (KNN) voidaan hakea kolme lähimpänä jonkin kunnan keskustaa sijaitsevaa lentokenttää.

WITH forssa AS
(SELECT
 wkb_geometry
 FROM
 nlsfi.hallintoalue
 WHERE
 kunta_ni1 = 'Forssa')  

SELECT
*, round(ST_Distance(forssa.wkb_geometry, a.wkb_geometry)/1000) as "km"
FROM 
nlsfi.lentokenttapiste a, forssa
ORDER BY
forssa.wkb_geometry <-> a.wkb_geometry
LIMIT 3;

Sama ongelma voidaan ratkaista myös ilman KNN-algoritmia:

SELECT
*, round(ST_Distance(wkb_geometry,(
    SELECT ST_Centroid(wkb_geometry)
    FROM
    nlsfi.hallintoalue
    WHERE
    kunta_ni1 ='Forssa'))/1000) as etaisyys
FROM
nlsfi.lentokenttapiste 
ORDER by
etaisyys
LIMIT 3;

Miksi saadut tulokset poikkeavat toisistaan?

7.5 Harjoitus 6.5

Mitkä ovat Kuopion naapurikunnat?

SELECT
b.kunta_ni1
FROM
(SELECT
 kunta_ni1, wkb_geometry
 FROM
 nlsfi.hallintoalue
 WHERE
 kunta_ni1 = 'Kuopio') a, nlsfi.hallintoalue b
WHERE
ST_Touches(a.wkb_geometry, b.wkb_geometry);

7.6 Harjoitus 6.6

Etsitään ne tieviivat, jotka leikkaavat kuntarajoja:

SELECT
a.tienumero, a.wkb_geometry
FROM
nlsfi.tieviiva a, nlsfi.hallintoalue b
WHERE
ST_Crosses(a.wkb_geometry, b.wkb_geometry);

Tulosten visualisoimiseksi, voit muodostaa uuden skeeman (tmp). Voit luoda uuden taulun, johon viet tuloksen. Visualisointiin voit käyttää esimerkiksi QGIS-ohjelmistoa. Voit muodostaa tuloksesta myös näkymän (view), mutta muista kuitenkin lisätä mukaan yksilöivä tunnus (id) sekä myös DISTINCT, jotta yksilöivät tunnukset pysyvät yksilöivinä.

CREATE SCHEMA IF NOT EXISTS tmp;

DROP TABLE IF EXISTS tmp.crossroads;

CREATE TABLE tmp.crossroads AS
(
    SELECT
    a.tienumero, a.wkb_geometry
    FROM
    nlsfi.tieviiva a, nlsfi.hallintoalue b
    WHERE
    ST_Crosses(a.wkb_geometry, b.wkb_geometry)
);

DROP VIEW IF EXISTS tmp.view_crossroads;

CREATE VIEW tmp.view_crossroads AS
(
    SELECT DISTINCT
    a.tienumero, a.wkb_geometry, a.ogc_fid
    FROM
    nlsfi.tieviiva a, nlsfi.hallintoalue b
    WHERE
    ST_Crosses(a.wkb_geometry, b.wkb_geometry)
);

Kumman luominen oli nopeampaa: taulun vai näkymän?

Entä käyttö QGISissä? Miksi?

7.7 Harjoitus 6.7

Lasketaan minimietäisyydet lentoasemilta lähimmälle rautatielle:

SELECT
a.ogc_fid, MIN(ST_Distance(a.wkb_geometry, b.wkb_geometry)) as "dist"
FROM
nlsfi.lentokenttapiste a, nlsfi.rautatieviiva b
GROUP BY
a.ogc_fid 
ORDER BY
dist;