4.0.1 Valmistautuminen

Avaa pgAdmin selaimeen ja kirjaudu sisään. Avaa Query Tool (Valitse trainingdatabase -> Ylhäältä Tools -> Query Tool). Selaimesta kannattaa avata myös PostGISin funktioiden koottu dokumentaatiosivu: linkki

4.1 3.1: PostGISin perusfunktiot geometrioiden määrittelyyn

-ST_AsWKT ja muut mahdolliset geometrioiden muodot: – Käytetään ainakin lentokenttätasoa (tai myös liikennevaylat-tasoa tai puurekisteri) tehdään uusi geometria, johon geometria kopioidaan wkb_geometry-kentästä, mutta tallennetaan maantieteelliseen koordinaatistoon ja vaikka WKT-muodossa. (ei luoda indeksiä tässä)

• ST_Distance-funktio ja projisoidut sekä maantieteelliset koordinaatit – mistä saadaan PostGISsissä tietoa geometria-kentistä (sekä CRS:stä) –lentokenttä-aineisto ja distance funktion vertailu (tässä voisi myös käyttää jo explain analyzeä)

4.2 Harjoitus 3.2: Geometrioiden metatiedot

Tutki geometry_columns-taulua. Mitä tietoja eri tietokentät sisältävät?

SELECT *
FROM
geometry_columns;

SELECT
nimi_fi, geom
FROM kaupunginosajako;

SELECT
nimi_fi, ST_AsText(geom)
FROM kaupunginosajako;

4.3 Harjoitus 3.3: PostGISin perusfunktiot geometrioiden käsittelyyn

Testataan ensin Postgisin perusfunktioita, kuten ST_Centroid(), ST_Buffer() ja ST_Boundary(). Voit avata näiden dokumentaatiosivut edellä olevasta linkistä.

• Muodosta 5 metrin vyöhykkeet puurekisterin pisteiden ympärille:

SELECT ST_Buffer(geom, 5) FROM puurekisteri;
...

ST_Buffer() toimii myös negatiivisilla arvoilla:

SELECT ST_Buffer(geom, -100) FROM kaupunginosajako;
...

• Hae viheralueiden keskipisteet ylre_viheralueet-taulusta.

SELECT ST_Centroid(geom) FROM ylre_viheralueet;
...

• Jos alue ei ole konveksi, vaan enemmänkin “vääntynyt”, ST_Centroid()-funktion antama piste ei ole välttämättä alueen sisällä. Ovatko nyt kaikki keskipisteet itse viheralueen sisällä? Voit tutkia tätä funktiolla ST_PointOnSurface(), joka on eräänlainen yleistys keskipisteelle, mutta on aina alueen sisällä. Mikä on suurin etäisyys näiden kahden pisteen välillä? Entä suurin etäisyys centroidin ja itse viheralueen välillä?

SELECT
...

-- täydennä oikeat funktiot vinkkien perusteella.
-- täydennä funktioiden argumentit '...'- kohtiin
SELECT ST_Distance(<geom1>, <geom2>)
-- sorttausta jne.

-- Kahden eri tyyppisen pisteen välinen ero
SELECT 
fid, puiston_ni, round(ST_Distance(ST_PointOnSurface(geom), ST_Centroid(geom))::numeric, 2) as dist 
FROM ylre_viheralueet 
ORDER BY dist DESC
LIMIT 1

-- Centroidin ja itse geometrian ero
SELECT 
fid, puiston_ni, round(ST_Distance(geom, ST_Centroid(geom))::numeric, 2) as dist 
FROM ylre_viheralueet 
ORDER BY dist DESC
LIMIT 1

• Muodosta Helsingin kaupunginosajaon perusteella kaupungin rajat.

SELECT
...

-- täydennä oikeat funktiot vinkkien perusteella.
-- täydennä funktioiden argumentit '...'- kohtiin
SELECT ST_Boundary(<yhdistelmä kaupunginosista>)

SELECT
ST_Boundary(ST_Union(geom)) AS rajat
FROM kaupunginosajako; 

4.4 Harjoitus 3.4: Spatiaaliset relaatiot

SELECT
...

-- täydennä oikeat funktiot vinkkien perusteella.
-- täydennä funktioiden argumentit '...'- kohtiin
SELECT ...
FROM puurekisteri
JOIN liikennevaylat
ON <sopiva ST-funktio> 

SELECT DISTINCT p.fid, p.* FROM puurekisteri p 
JOIN liikennevaylat l 
ON ST_DWithin(p.geom, l.geom, 5)

SELECT
...

-- täydennä oikeat funktiot vinkkien perusteella.
-- täydennä funktioiden argumentit '...'- kohtiin
SELECT ...
FROM puurekisteri
JOIN liikennevaylat
ON <sopiva ST-funktio> 

SELECT a.geom FROM liikennevaylat a 
JOIN kaupunginosajako b
ON ST_Crosses(a.geom, b.geom)
-- Visuaalisena apuna voidaan siis myös käyttää seuraavaa:
--SELECT ST_Union(a.geom, ST_Boundary(b.geom)) FROM liikennevaylat a 
--JOIN kaupunginosajako b
--ON ST_Crosses(a.geom, b.geom)

SELECT
...

-- täydennä oikeat funktiot vinkkien perusteella.
-- täydennä funktioiden argumentit '...'- kohtiin
SELECT ...
FROM puurekisteri
LEFT JOIN puurekisteri
WHERE ...
AND <sopiva ST-funktio> 
...

SELECT p1.* FROM puurekisteri p1
LEFT JOIN puurekisteri p2
ON p1.fid <> p2.fid
AND ST_DWithin(p1.geom, p2.geom, 50);