Kõik sõltub sellest, millisel otstarbel on plaanis neid andmeid kasutada.
Kui tegemist on taustainfoga, mille alusel ei tehta täpseid otsinguid ja mida võibolla trükitakse kirjadele/arvetele, siis piisab veerust aadress. Tõstaksin riigi "sulgude ette" nii, et tabelis on aadressiandmete jaoks kaks veergu: aadress ja riigi_kood. riigi_kood on välisvõtme veerg, mis viitab klassifikaatori tabelile Riik. Tabelis Riik olevad andmed oleksid määratud rahvusvahelise klassifikaatoriga: https://www.iban.com/country-codes
Kui soov on teha aadressi alusel täpseid otsinguid, siis tuleb aadress "lahti lõhkuda" ning tükid erinevatesse tabelitesse/veergudesse salvestada. See pole triviaalne ülesanne. Näiteks Eestis on kasutusel kaheksa-tasemeline aadressiandmete süsteem (https://geoportaal.maaamet.ee/est/Andmed-ja-kaardid/Aadressiandmed-p112.html).
O'Reilly digitaalse platvormi kaudu saab lugeda raamatut Blaha, M., 2010. Patterns of Data Modeling.
Kuidas kasutada O'Reilly digitaalset platvormi?
Peatükis 10 käsitletakse andmete modelleerimise arhetüüpe ning üks nendest on "Location" (jaotis 10.12). Eesti keele seletava sõnaraamatu kohaselt on arhetüüp "inimkonna kollektiivsest alateadvusest lähtuv püsistruktuur, mis teadvuses avaldub universaalse motiivi v. kujundina". Muinasjuttudele mõeldes on arhetüübiks näiteks noorim vend, kes lõpuks tapab lohe ning saab tasuks printsessi tähelepanu ja pool kunigriiki.
Koordinaatide hoidmisel on mõtet, kui tahate näiteks asukohti kaardil kujutada. Andmebaasis koordinaatide ja koha-aadresside hoidmine ei välista teineteist. Nii Eesti aadressiandmete süsteem kui "Location" arhetüüp näevad ette ka ruumiandmete hoidmist koordinaatidena.
Mis puudutab sobivaid veerge ja tüüpe, siis see sõltub kasutatavast koordinaatide süsteemist ja andmebaasisüsteemist.
Näide: https://twcc.fr/
PostgreSQL ja Oracle on mõlemad objekt-relatsioonilised süsteemid ning lubavad defineerida uusi tüüpe - miks mitte ka geograafiliste koordinaatide jaoks. PostgreSQLis on mõned geomeetrilised tüübid/operaatorid süsteemi-defineeritud neid saab koordinaatide esitamiseks kasutada: https://stackoverflow.com/questions/1023229/spatial-data-in-postgresql Täpsemalt saate esitada ruumilise asukoha Point tüüpi väärtusena. Tavaks on esitada koordinaadid laiuskraad/pikkuskraad järjekorras, st Point tüüpi väärtuses võiks esimene koordinaat (arv) tähistada laiuskraadi ja teine pikkuskraadi. Lisaks on vaja CHECK kitsendusi. Laiuskraad peab olema vahemikus -90 ja 90 (otspunktid kaasa arvatud) ja pikkuskraad peab olema vahemikus -180 ja 180 (otspunktid kaasa arvatud).
Piirangud, et pikkuskraad ei tohi olla 0 ja laiuskraad ei tohi olla, on ebaõiged.
Täpsema veateate huvides soovitan koordinaatide lubatud väärtuste vahemiku kontrollimiseks kahte eraldi kitsendust.CREATE TABLE Asukoht (
asukoht_id SERIAL,
koordinaat point NOT NULL,
on_aktiivne BOOLEAN NOT NULL DEFAULT TRUE,
CONSTRAINT pk_asukoht PRIMARY KEY (asukoht_id));ALTER TABLE Asukoht ADD CONSTRAINT chk_asukoht_koordinaat_laiuskraad CHECK (koordinaat[0] BETWEEN -90 AND 90);ALTER TABLE Asukoht ADD CONSTRAINT chk_asukoht_koordinaat_pikkuskraad CHECK (koordinaat[1] BETWEEN -180 AND 180);INSERT INTO Asukoht (koordinaat) VALUES (point(41.40338, 2.17403));INSERT INTO Asukoht (koordinaat) VALUES (point(91.40338, 2.17403));
/*ERROR: new row for relation "asukoht" violates check constraint "chk_asukoht_koordinaat_laiuskraad"
DETAIL: Failing row contains (2, (91.40338,2.17403)).*/
Probleem tekib siis, kui soovin lisada tabelile kitsenduse, et aktiivsete asukohtade koordinaadid peavad olema unikaalsed. Sellise kitsenduse realiseerimine ebaõnnestub.CREATE UNIQUE INDEX ak_asukoht_aktiivne ON Asukoht (koordinaat) WHERE on_aktiivne=TRUE;/*ERROR: data type point has no default operator class for access method "btree"HINT: You must specify an operator class for the index or define a default operator class for the data type.*/ALTER TABLE Asukoht ADD CONSTRAINT ak_asukoht_aktiivneEXCLUDE (koordinaat WITH =) WHERE (on_aktiivne=TRUE);/*ERROR: data type point has no default operator class for access method "btree"HINT: You must specify an operator class for the index or define a default operator class for the data type.*/
Võimalik lahendus oleks arvutatava veeru lisamine, kus oleks POINT tüüpi väärtuse tekstiliseks väärtuseks teisendamise tulemus.ALTER TABLE Asukoht ADD COLUMN koordinaat_tekst text GENERATED ALWAYS AS (CAST(koordinaat AS text)) STORED NOT NULL;
Kitsenduse realiseerimine õnnestub.CREATE UNIQUE INDEX ak_asukoht_aktiivne ON Asukoht (koordinaat_tekst) WHERE on_aktiivne=TRUE;
Rea lisamine ebaõnnestub.INSERT INTO Asukoht (koordinaat) VALUES (point(41.40338, 2.17403));/*ERROR: duplicate key value violates unique constraint "ak_asukoht_aktiivne"DETAIL: Key (koordinaat_tekst)=((41.40338,2.17403)) already exists.*/
Rea lisamine õnnestub.INSERT INTO Asukoht (koordinaat, on_aktiivne) VALUES (point(41.40338, 2.17403), FALSE);
Eelneva lahenduse probleem on, et koordinaadid salvestatakse kahekordselt.
Selle asemel võiks seega kasutada lahendust, kus hoitakse laius- ja pikkuskraade kümnendmurruna. Siis kasutaksin:
laiuskraad: DECIMAL(8,6) - PostgreSQL või NUMBER(8,6) - Oracle.
pikkuskraad: DECIMAL(9,6) - PostgreSQL või NUMBER(9,6) - Oracle.
Tabeli struktuuri parema loetavuse huvides võiksid olla laiuskraadi ja pikkuskraadi veerud kõrvuti ning laiuskraadi veerg eespool.
Selliste veergude põhjal saab ka jõustada eelnimetatud kitsenduse.
Kui peaksin hoidma kraade, minuteid ja sekundeid, siis võtaks iga selle komponendi jaoks kasutusele täisarvu tüüpi veeru.
Nii PostgreSQLi kui Oracle jaoks on olemas laiendused geograafiliste andmetega töötamiseks, kuid nendega ma kahjuks tuttav ei ole. PostgreSQL: PostGis (http://postgis.net/) Oracle: Oracle Spatial and Graph (https://docs.oracle.com/database/121/SPATL/toc.htm)
Hinda postitust:
Keskmine hinne : Pole veel hinnanguid!