Kaardid ja koordinaatite teisendus Excelis

Eelmisel nädalal sai kõigile huvilistele kättesaadavaks Exceli uus lisa – GeoFlow. Võimalus koostada ajalis-ruumilisi diagramme ja aruandeid.

Juba mõnda aega on meil laual Excel 2013, kus kaks vahvat kaardirakendust (mis tuleb lihtsalt üles leida) – Bing Maps rakendus Excelile ja PowerView aruanded. Mõlemad lubavad kuvada numbrilisi andmeid kaardil diagrammidena.

Kõik kolm eeldavad, et aruande lähteandmetes on kohainfo – kas siis leitava nimena või koordinaatidena. Selle nimeleidmisega on nii nagu on – päris iga küla ja talu ei ole bingi kaardilt leitav, või tuleb natuke vaeva näha, et kohanimi kujutada täpselt leitavaks. Koordinaatidega on aga veel keerukam. Eesti kaardirakendustes, registrites ja muudes andmeallikates on ristkoordinaadid (L-Est’92), eelpoolmainitud (ja ka paljud teised) kaardirakendused eeldavad aga geograafilisi koordinaate (pikkus ja laiuskraade). Nende kahe omavaheline sidumine ja teisendamine on aga hulk keerukat matemaatikat. Põhjus on kera ja tasapinna erinevuses.

Maakera on enamvähem ümmargune. Kaardid aga reeglina tasapinnalised. Maapeal on lihtsam mõõta kaugusi meetrites. Merel aga tuleb leida pikkus- ja laiuskraadid.

Ma ei hakka kogu seda kaardimajandamise teooriat ja praktikat ümber jutustama – liiga pikk jutt ja ega ma väga asjatundja ka pole. Külla aga leidsin ma võimaluse Exclis tavaliste valemitega kirja panna mõlematpidine teisendus – valemid on üsna keerukad ja ega ma ise ka päris hästi aru ei saa, kuidas nad toimivad. Aga vähemalt tulemuse nad annavad ja minu arvutustes jäi edasitagasi teisendamisel viga kuhugi alla paari sentimeetri.

Teisendamisvahendeid on ka teisi – kõik on aga sellised “teenusepõhised”. Kui sul on suur hulk analüüsiandmeid ja neid oleks vaja kaardile või registrile sobivasse vormi panna, siis on raske leida kedagi vabatahtlikku kopipastejat, kes andmeid näiteks veebilehele ja sealt tagasi viitsiks kopeerida. Seepärast on valem palju tõhusam abivahend.

Koostasin valemid Exceli tabelivalemitena (A1 viidete asemel veerunimed). Samad valemid on (võib-olla mõne pisimuudatusega) kasutatavad ka andmebaasipäringutes (SQL) ja PoverPivoti andmemudelites (DAX)

Hakatuseks pisut siiski mõned põhiasjad

Ristkoordinaatides on meil valitud ühikuks meeter, Eesti on paigutatud ühe kokkuleppelise 400×500 km ristküliku keskele. Selle ristküliku alumine serv jookseb umbes Riia kohal ja ülemine pisut põhjapool Helsingit. Vasak serv on umbes Liepaja ja Ahvenamaa joonel, parem ülemine nurk on enamvähem Peterburi ja Viiburi vahel, parem alumine nurk on Pihkva oblastis (lähim linnake on Opochka). Ristkoordinaadid on alt üles (põhja poole) vahemikus 6300000-6700000 (vahe siis 400km) ning vasakult paremale (ida suunas) vahemikus 300000-800000 (vahe 500km).

Polaarkoordinaadid (need geograafilised laius ja pikkuskraadid) antakse ette nurgana. Mitu kraadi ekvaatorist põhja (või lõuna) poole – laiuskraad ja mitu kraadi nullmeridiaanist ida (või lääne) poole. Laiuskraadid on vahemikus 0-90 kraadi (põhja või lõunalaiust). Pikkuskraadid on vahemikus 0-180 kraadi (kas siis ida või lääne poole).

Kraadide ülesmärkimiseks on mitu erinevat süsteemi:

  • 56,80046 – see on 56 koma veel natuke kraadi (koma asemele kasutatakse enamasti punkti)
  • 56°48’1.641″ – see on 56 kraadi, 48 minutit ja 1 koma veel pisut sekundit (komakohana kasutatakse reeglina punkti)
  • 0,991354973 – see on peaaegu 1 radiaan. Kõikvõimalikud funktsioonid tahavad nurkasid saada just sellisel moel

Inimene kasutab enamasti seda kraadides märkimist, arvuti tahab arvutamiseks seda radiaanides arvu. Kõik kolm eelpooltoodud kuju on täpselt sama nurk

Segaduse vältimiseks lisatakse nurgale üks suunatähis:

  • 20°43’36.148″E 56°48’1.641″N – 20 kraadi ja natuke veel idapikkust ja 56 kraadi millegagi põhjalaiust

Mõnel pool märgitakse ka tähtede asemel pluss-miinus (pluss on põhja ja ida suunas, miinus on lõuna ja lääne suunas)

Meie neli ristküliku nurka on:

  • 20°43’36.148″E 56°48’1.641″N – see on see Liepaja juures, vasak alumine nurk (6300000    ja 300000)
  • 28°54’18.279″E 56°44’44.758″N – see on seal Pihkva oblastis, parem alumine nurk (6300000 ja 800000)
  • 29°26’1.813″E 60°19’33.148″N – see on seal Peterburi kandis, parem ülemine (6700000 ja 800000)
  • 20°22’22.929″E 60°23’11.147″N – ning see on vasakul üleval Ahvenamaa kandis (6700000 ja 300000)

Läheme nüüd selle teisendamise juurde. Tunnistan, valemid on keerukad, aga ka keerukaid valemeid saab kopida-pasteda. Kui kasutada tabelivalemeid, siis täidetakse nendega kohe terve veerg.

Tegin näidiseks kaks tabelit – LestGeo – ristkoordinaadid geograafilisteks ja GeoLest – vastupidiseks teisenduseks. Minu tabelis on katsetuseks mõned punktid algul ristkoordinaatidest geograafiliseteks ja siis vastupidi. Lisasin ka väikese kontrolltabeli, mis näitab selle edasitagasi teisenduse viga. Nagu eelnevalt mainisin – suurim viga oli 2cm suurusjärgus.

Tehtud tabelid panen ka üles oma “veebisaiti” – www.sarv.ee/ftp/henn/Excel – nimega Koordinaatide teisendus. Eks siis igaüks vaata, kas ja kuidas neid kasutada.

Nüüd väike seletus.

Esimene tabel LestGeo

  • Esimesed kaks veergu on ristkoordinaadid nimedega [North] ja [East]
  • Järgmine veerg [Pik] on pikkuskraad – arvutatud kogu selle mängu kõige lihtsama valemiga:
    • [Pik] =DEGREES(ATAN(([East]-500000)/(4020205+6375000-[North]))/0,85417585805)+24
  • Laiuskraadi arvutamiseks on lisatud üks vahemuutuja [Lvm] – ilma selleta oleks valem LIIGA keeruline (kuigi saaks)
    • [Lvm] =PI()/2-2*ATAN(POWER(SQRT(POWER([East]-500000;2)+POWER(4020205+6375000-[North];2))/11473298,03838;1,17071910962562))
  • Laiuskraadi enda saab siis valemiga:
    • [Lai] =DEGREES([Lvm]+0,003356551*SIN(2*[Lvm])+0,000006571*SIN(4*[Lvm])+0,000000017*SIN(6*[Lvm]))
  • Ülejäänud on juba vormistuslikud valemid
  • PikRound ja LaiRound on ümmardused tuhandik sekundi täpsuseks. Kes tahab võib seda ümmardust kohendada
    • [PikRound] =IF(Ümardus;ROUND([Pik]*3600000;0)/3600000;[Pik])
    • [LaiRound] =IF(Ümardus;ROUND([Lai]*3600000;0)/3600000;[Lai])
  • Veerud Pikkus ja Laius on teisendus kraadikujul esitamiseks
    • [Pikkus] =INT([PikRound])&”°”&INT(MOD([PikRound]*60;60))&”‘”&INT(MOD([PikRound]*3600;60))&”.”&MID(([PikRound]-FLOOR.PRECISE([PikRound];1/3600))*3600+0,0000000001;3;3)&””””
    • [Laius] =INT([LaiRound])&”°”&INT(MOD([LaiRound]*60;60))&”‘”&INT(MOD([LaiRound]*3600;60))&”.”&MID(([LaiRound]-FLOOR.PRECISE([LaiRound];1/3600))*3600+0,0000000001;3;3)&””””
  • Viimased kaks veergu on sidumiseks GoogleMapi ja BingMapiga. Esimene sobib Googlist, teine nii Googlist kui Bingist otsimiseks:
    • [KoosGoogle] =[@Pikkus]&”E “&[@Laius]&”N”
    • [KoosBing] =SUBSTITUTE([Lai];”,”;”.”)&”, “&SUBSTITUTE([Pik];”,”;”.”)

Kogu tabel mõnede kontrollpunktidega on siis umbes selline:

North East Pik Lvm Lai PikRound LaiRound Pikkus Laius KoosGoogle KoosBing
6540115

500000

24

1,026777

59

24

59

24°0’0.000″ 59°0’0.000″ 24°0’0.000″E 59°0’0.000″N 59.0000001345021, 24
6540117

500100

24,00174

1,026777

59,00002

24,00174

59,00002

24°0’6.264″ 59°0’0.059″ 24°0’6.264″E 59°0’0.059″N 59.0000163768951, 24.0017399671282
6540544

557470,2

25

1,026777

59

25

59

25°0’0.000″ 59°0’0.000″ 25°0’0.000″E 59°0’0.000″N 59.0000001345021, 24.9999999999415
6387062

653237

26,56321

1,002261

57,59956

26,56321

57,59956

26°33’47.559″ 57°35’58.424″ 26°33’47.559″E 57°35’58.424″N 57.5995621225819, 26.5632107320944
6419843

647076

26,48052

1,007446

57,89575

26,48052

57,89575

26°28’49.866″ 57°53’44.700″ 26°28’49.866″E 57°53’44.700″N 57.8957500370986, 26.4805183004908
6458323

658345

26,69646

1,01342

58,23708

26,69646

58,23708

26°41’47.240″ 58°14’13.493″ 26°41’47.240″E 58°14’13.493″N 58.2370812881714, 26.6964555842604
6462151

648773

26,53608

1,01408

58,27478

26,53608

58,27478

26°32’9.880″ 58°16’29.194″ 26°32’9.880″E 58°16’29.194″N 58.2747759845022, 26.5360776428389
6300000

300000

20,72671

0,988276

56,80046

20,72671

56,80046

20°43’36.148″ 56°48’1.641″ 20°43’36.148″E 56°48’1.641″N 56.8004559657799, 20.7267078817767
6300000

800000

28,90508

0,987319

56,74577

28,90508

56,74577

28°54’18.279″ 56°44’44.758″ 28°54’18.279″E 56°44’44.758″N 56.7457659806527, 28.9050774349304
6700000

800000

29,43384

1,049993

60,32587

29,43384

60,32587

29°26’1.813″ 60°19’33.148″ 29°26’1.813″E 60°19’33.148″N 60.3258745054734, 29.4338368664975
6700000

300000

20,37304

1,051054

60,38643

20,37304

60,38643

20°22’22.929″ 60°23’11.147″ 20°22’22.929″E 60°23’11.147″N 60.3864297768834, 20.3730358932703

Viimased neli punkti on Eesti aluskaardi nurgad

Proovisin saadud punkte nii GoogleMapis kui BingMapis ja kuvasin nad ka BingMap rwakenduses Excelis:

Esialgu jätan pooleli. Homme lisan ka teise poole – kuidas Geograafilised koordinaadid “meie” koordinaatideks teisendada. Peale seda panen ka näidised veebi üles nagu lubatud.

This entry was posted in Excel, Funktsioonid, Kaardirakendused, Koordinaatide teisendus. Bookmark the permalink.

Lisa kommentaar

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Muuda )

Twitter picture

You are commenting using your Twitter account. Log Out / Muuda )

Facebook photo

You are commenting using your Facebook account. Log Out / Muuda )

Google+ photo

You are commenting using your Google+ account. Log Out / Muuda )

Connecting to %s