6 Harjoitus 5: Paikkatietokohteiden geometria

Harjoituksen sisältö - Harjoituksessa tutustutaan PostGISin tapaan käsitellä geometrioita.

Harjoituksen tavoite - Harjoituksen jälkeen opiskelija tuntee PostGIS-geometrioiden käsittelyn perusteet.

6.0.1 Valmistautuminen

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

6.1 Harjoitus 5.1

Luodaan aluksi tietokantaan uusi taulu, johon muodostetaan erilaisia geometrioita:

DROP TABLE IF EXISTS geom_test;

CREATE TABLE IF NOT EXISTS geom_test
(
    gid   serial PRIMARY KEY,
    name  varchar(50),
    geom  geometry(geometry, 3067)
);

Lisää sen jälkeen tauluun yksittäinen piste seuraavalla komennolla:

INSERT INTO
geom_test(name, geom)
VALUES
('Single point', 'SRID=3067; POINT(0 0)');

Voit tarkastella taulun tietoja SQL-kyselyllä tai QGIS-ohjelmiston avulla:

SELECT
name, ST_AsText(geom), ST_GeometryType(geom)
FROM
geom_test;

6.2 Harjoitus 5.2

Lisää vielä tauluun viiva ja monikulmio.

Voit käyttää joko WKT-formaattia (LINESTRING(0 1, 2 3) ja POLYGON((4 5, 6 7, 8 9, 10 11, 4 5)), tai PostGIS:n funktioita ST_MakeLine, ST_MakePolygon ja ST_MakeEnvelope.

Tarkista PostGISin ohjekirjasta funktioiden oikeat muodot: 8.4 Geometry Constructors.

WKT- formaatti:

INSERT INTO

VALUES

-- viivageometria
INSERT INTO
geom_test(name, geom)
VALUES
(..., '...; ...'); -- täytä arvot. Ota mallia ylhäällä olevasta piste- esimerkistä.

-- polygonigeometria (monikulmio, alue)
INSERT INTO
geom_test(name, geom)
VALUES
(..., '...; ...'); -- täytä arvot. Ota mallia ylhäällä olevasta piste- esimerkistä.

INSERT INTO
geom_test(name, geom)
VALUES
('Single linestring', 'SRID=3067; LINESTRING(0 1, 2 3)');

INSERT INTO
geom_test(name,geom)
VALUES
('Single polygon', 'SRID=3067; POLYGON((4 5, 6 7, 8 9, 10 11, 4 5))');

PostGIS- funktiolla:

INSERT INTO

VALUES

-- Tarkista dokumentaatiosta, miten funktioita käytetään ja täydennä arvot.
INSERT INTO
geom_test(name,geom)
VALUES
('Simple polygon using ST_MakeEnvelope', ST_MakeEnvelope());

INSERT INTO
geom_test(name,geom)
VALUES
('Single point using ST_MakePoint', ST_SetSRID(ST_MakePoint(),3067));

INSERT INTO
geom_test(name,geom)
VALUES
('Single linestring using ST_MakeLine',
 ST_SetSRID(ST_MakeLine(ST_MakePoint(),ST_MakePoint()),3067));

INSERT INTO
geom_test(name,geom)
VALUES
('Simple polygon using ST_MakeEnvelope', ST_MakeEnvelope(20,20,40,40,3067));

INSERT INTO
geom_test(name,geom)
VALUES
('Single point using ST_MakePoint', ST_SetSRID(ST_MakePoint(100,200),3067));

INSERT INTO
geom_test(name,geom)
VALUES
('Single linestring using ST_MakeLine',
 ST_SetSRID(ST_MakeLine(ST_MakePoint(1,1),ST_MakePoint(50,50)),3067));

6.2.1 Pinta-alat

Geometrioihin liittyviä ominaisuuksia voidaan käyttää erilaisilla SQL-funktioilla. Seuraavalla hakulausekkeella haetaan Suomen pinta-alaltaan 10 pienintä kuntaa:

SELECT 
kunta_ni1 as "Nimi", ST_Area(wkb_geometry) as "Pinta-ala"
FROM 
nlsfi.hallintoalue
ORDER BY
ST_Area(wkb_geometry) asc
LIMIT 10;

Pinta-alat esitetään käytetyn koordinaattijärjestelmän yksiköissä, tässä neliömetreinä. Jos haluat pinta-alan hehtaareina, jaa alue 10 000:lla (100 m x 100 m).

6.3 Harjoitus 5.3

Mikä on Uudenmaan maakunnan viidenneksi pienimmän kunnan pinta-ala neliökilometreinä?

SELECT
...
FROM
...
WHERE
...
ORDER BY
...
LIMIT ...
OFFSET ...;

-- täydennä sarakkeet
-- round() funktio pyöristää arvon. Parametri '2' tarkoittaa, että luku
-- palautuu kahden desimaaliluvun tarkkuudella
SELECT
kunta_nimi_fi, maakunta
round((pinta_ala_funktio(geometria)/ ... ::numeric, 2) as "Pinta-ala (km2)"
-- selvitä, millä PostGIS- funktiolla voidaan laskea polygonin pinta-ala.
-- funktio palauttaa arvon koordinaattijärjestelmän yksiköissä, 
-- tässä tapauksessa neliömetreinä. Yksikkö tulee muuntaa neliökilometreiksi
-- jakolaskulla. Täydennä oikea arvo.
FROM
nlsfi.hallintoalue
WHERE
maakunta = 'Uusimaa'
ORDER BY
pinta_ala_funktio(geometria) ... -- järjestä nousevaan järjestykseen
LIMIT 1
OFFSET ...;
-- OFFSET- avainsana siirtää palautuvan tulosjoukon alkamaan tietueesta x.
-- Millä arvolla saat viidenneksi pienimmän kunnan?

SELECT
kunta_ni1 as "Kunta", maaku_ni1 As "Maakunta",
round((ST_Area(wkb_geometry)/(1000*1000))::numeric,2) as "Pinta-ala (km2)"
FROM
nlsfi.hallintoalue
WHERE
maaku_ni1 = 'Uusimaa'
ORDER BY
ST_Area(wkb_geometry) asc
LIMIT 1
OFFSET 4;

6.3.1 Topologia

Voimme tutkia paikkatietoaineistojen geometrioiden topologiaa monella eri tavalla, esimerkiksi luettelo suomalaisista kunnista, joissa on reikä:

SELECT
kunta_ni1 as "Nimi"
FROM
nlsfi.hallintoalue
WHERE 
ST_NumInteriorRings(ST_GeometryN(wkb_geometry,1)) > 0;

6.3.2 Pituus

Paikkatietoaineistojen viivojen pituuksia voi tutkia ST_Length-funktiolla, harjoitusaineistossa olevien rautateiden yhteispituus:

SELECT
sum(ST_Length(wkb_geometry))/1000 as "Rautateiden yhteenlaskettu pituus"
FROM
nlsfi.rautatieviiva;

6.4 Harjoitus 5.4

Mikä on suomalaisen tien nro 66 kokonaispituus?

SELECT
...
FROM
...
WHERE
...;

-- Millä PostGIS- funktiolla saat laskettua pituuden?
-- käytä SUM- funktiota laskemaan tietueiden kokonaispituus
SELECT
round(SUM(pituus_funktio(wkb_geometry))::numeric,2) as "Reitin 66 kokonaispituus (m)"
FROM
nlsfi.tieviiva
WHERE
...; -- valitse tietueet tienumeron mukaan

SELECT
round(SUM(ST_Length(wkb_geometry))::numeric,2) as "Reitin 66 kokonaispituus (m)"
FROM
nlsfi.tieviiva
WHERE
tienumero = 66;

6.4.1 Esitysmuodot

Aikaisemmin katsoimme jo geometrioita tekstimuotoisena. PostGIS sisältää myös muita esitystapoja:

SELECT
ST_asGeoJSON(wkb_geometry)
FROM
nlsfi.hallintoalue
WHERE
kunta_ni1 = 'Ilomantsi';

Kokeile eri tiedostomuotoja:

GML
SVG
KML
X3D
EWKB, EWKT

6.4.2 Eri tieluokkien pituudet

Käytössä olevan aineiston tieluokittainen kokonaispituus:

SELECT
tieluokka as "Tieluokka", sum(st_length(wkb_geometry))/1000 as "Kokonaispituus"
FROM 
nlsfi.tieviiva
GROUP BY
tieluokka
ORDER BY
"Kokonaispituus" desc;

6.4.3 Tietotyyppien muutokset

PostgreSQL sisältää kaksi tapaa tietotyyppien muutoksiin (casting):

cast(varchar_col AS int)     -- SQL yhteensopiva
varchar_col::int        -- PostgreSQLn perinteinen tapa

SELECT CAST('POINT(0 0)' as geometry);
SELECT 'POINT(0 0)'::geometry;

6.5 Harjoitus 5.5

Muodosta ensimmäisen harjoituksen lentokenttiä kuvaavasta tiedosta uusi paikkatietotaso, jossa lentokenttien sijainti ilmoitetaan PostGIS:n geometria-tyyppinä.

Alla näkyvästä SQL-komennosta kannattaa ottaa vähän vinkkiä (myös sen suhteen, että kannattaa tarkastella vain pientä otosta lentokenttä-aineistosta):

SELECT name, longitude, latitude, 'POINT(' || longitude || ' '|| latitude || ')' as WKT_uudet
FROM airports
ORDER BY name
LIMIT 10;

DROP TABLE IF EXISTS airports_geom

CREATE TABLE airports_geom AS
(
    SELECT
    ...
    FROM
    ...
);

SELECT
...
FROM
...
ORDER BY
...
LIMIT ...;

-- Millä PostGIS- funktiolla voit määrittää koordinaattijärjestelmän?
DROP TABLE IF EXISTS airports_geom;

CREATE TABLE airports_geom AS
(
    SELECT
    *, aseta_koordinaattijärjestelmä(CAST('...' as geometry),4326) as geometry
    FROM
    airports
);
-- täydennä funktio CAST() oikein. Katso ylhäältä mallia.

-- tarkastele uutta taulua:

SELECT
name, longitude, latitude, geom
FROM
airports_geom
ORDER BY
name
LIMIT 10;


DROP TABLE IF EXISTS airports_geom;

CREATE TABLE airports_geom AS
(
    SELECT
    *, ST_SetSRID(CAST('POINT(' || longitude || ' '|| latitude || ')' as geometry),4326) as geometry
    FROM
    airports
);


-- tarkastele uutta taulua:

SELECT
name, longitude, latitude, geom
FROM
airports_geom
ORDER BY
name
LIMIT 10;

Harkitse geometrian esitystapaa: geometry vai geography?