Excel – data (nejen) na mapách Google

Google mapy – to není jako práce s buzolou. Hodí se základní znalosti HTML, CSS, PHP a hlavně javascriptu. A také musíme říct Excelu – zoznámte sa prosím. Jdeme na to.

Co Google mapy umí?

A) Statický obrázek mapy
V praxi to funguje jednoduše. Stačí předat Googlu dotaz v podobě parametrizované webové adresy a jako výsledek vám bude vrácen statický obrázek mapy.

B) Dynamická mapa
Dynamické mapy dovolují uživateli mapy nejen zobrazovat, ale i ovládat (posun, změna měřítka, vyhledávání, Stret View), barevně upravovat a doplňovat (vlastní elementy na mapě nebo i celé vrstvy).

Marker (značka, špendlík, praporek)

  • implicitní ikona, které je možné přiřadit barvu a umí třeba „hopsat“ na daných souřadnicích mapy
  • lze ho nahradit vlastní (průhlednou) ikonou (formát PNG)
  • může obsahovat jednoduchou textovou informaci po najetí myškou (stejné chování, jaké má atribut title v HTML)

InfoWindow (bublina)

  • kontejner, jehož obsah je možné stylovat (CSS), bohužel tvar a odsazení okrajů od obsahu nelze řídit
  • obsah je čistě textový nebo ve formě HTML
  • zpravidla je jeho zobrazení navázané na událost markeru (běžně se vykresluje po klepnutí)
  • hodí se pro zobrazování hodnot
Google mapy - marker, infowindow

Google mapy – marker, infowindow

ukázka online

InfoBox (box)

  • doplněk ve formě externího (komprimovaného) javascriptu
  • užití podobné jako u InfoView
  • je možné jej kompletně stylovat (CSS)

Kruh

  • tvar kruhu s nastavitelnou průhledností a velikostí v daném místě mapy
  • upotřebení stejné jako má bublinový graf v Excelu

Polygon

  • ke zvýraznění plochy (regionu) na mapě
  • intenzita barvy může vyjadřovat míru výskytu (mapy výskytu klíšťat apod.), v Excelu lze tohoto dosáhnout jen programovým obarvováním tvaru

Ostatní

Jak propojit Google mapy s Excelem?

Jednoduše. Děje se tak prostřednictvím ActiveX prvku prohlížeče s názvem Web Browser a programovým užitím jeho metody Navigate. Je to prvek zastaralý, nečekejte žádné zázraky.

Prvek Web Browser

Prvek Web Browser

Statický obrázek mapy

Jde vlastně „pouze“ o to sestavit parametrizovanou webovou adresu, kterou Googlu předáme. I tak to není procházka růžovým sadem. Popis se nachází na stránce Static Maps API V2 Developer Guide. Dozvíme se zde mimo jiné, že pro volné užití nesmíme překročit určitý „traffic“. V praxi se jedná o tisíce vygenerovaných zobrazení (max. 50 zobrazení/min./IP adresa, 1000 zobrazení/den/IP adresa), takže se není čeho bát. Free varianta má také omezenou velikost obrázku (max. 1280 x 1280 px při scale = 2, viz odkaz). Místo, které chceme zadat, můžeme vyjádřit buď s pomocí zeměpisných souřadnic (stupně severní šířky a východní délky), nebo zkusit vyhledávání. V první variantě potřebujeme koordináty pro česká města. Ty naštěstí není těžké dohledat. Podotýkám, že při převodu na desetinné číslo dělíme minuty 60 a vteřiny číslem 3600.

Zeměpisné souřadnice českých měst

Zeměpisné souřadnice českých měst

Chceme-li místo na mapě definovat s pomocí předané adresy, narazíme na problém. Webová adresa nesmí obsahovat některé znaky (typicky mezeru). Ty musíme před vložením překódovat (URL encoding) do UTF-8. Je otázka, jestli totéž dělat i pro české znaky obsahující diakritiku. Osobně doporučuji i jejich zařazení ke zpracování. Pro Visual Basic najdeme na internetu několik příkladů funkce pro dané účely, většinou ale hodně laxních. Nejlepší je sestavit si vlastní párovací tabulku a z ní programově znaky nahrazovat.

Tabulka pro "encoding" do UTF-8

Tabulka pro „encoding“ do UTF-8

Obrázek níže ukazuje zkompletování adresy a možnosti parametrizace. Finální webová adresa (ukázka online) je užita v metodě Navigate prvku Web Browser na základě události Worksheet_Change.

Google mapy - statický obrázek

Google mapy – statický obrázek

Pozn. Je tu jedna otravná věc, se kterou si zatím nevím rady. Při otevírání sešitu a prvním načítání obsahu do prvku Web Browser Excel zobrazí dialogové okno pro uložení obrázku staticmap.png coby výsledek dotazu. Jedná se pravděpodobně o idiotské chování v rámci zabezpečení. Dialog jednoduše zavřete tlačítkem Storno a v prvku Web Browser klepněte na Obnovit stránku.

Dynamická mapa

Webové rozhraní map Googlu pracuje také s parametry ve webové adrese. Jednoduchým příkladem budiž odkaz https://www.google.com/maps/@49.8291547,15.4883802,8z. Parametrem jsou zde zeměpisné souřadnice středu mapy a 8z říká, jaké je její měřítko (při normálním rozlišení monitorů zkoušejte 7z a 8z).

Nyní opustíme uživatelský pohled na mapy a budeme se věnovat programátorské podpoře popsané na stránce Google Maps JavaScript API v3. Tady už půjde o skutečné provázání dat Excelu s markery a InfoWindows (InfoBoxes). Prvku Web Browser tentokrát nepředáme parametrizovanou webovou adresu, jako v případě statického obrázku, ale předpřipravenou HTML šablonu doplněnou o data z Excelu v proměnné (pole polí). Šablona je ve formátu ANSI (jednoduše řečeno s kódováním češtiny Windows-1250) a nechá se s ní bez problémů pracovat pod VBA. A přestože to není nezbytně nutné, dal jsem si práci s tím, aby byl výsledný soubor v UTF-8 bez BOM.

Obrázek níže dokumentuje skutečný příklad nastylované mapy, stylované InfoBoxy, vlastní tlačítko Domů pro návrat do výchozího zobrazení a dodané ikony pro markery (třešinkou na dortu je hopsající praporek u extra hodnoty – maxima).

Google mapy - markery a infoboxy

Google mapy – markery a infoboxy

Pro vycentrování mapy se hodí souřadnice obce Číhošť u Ledče nad Sázavou, kterou ne všichni uznávají za geografický střed ČR. Mezi ovládacími prvky jsem ponechal roletku pro výběr typu mapy (silniční, satelitní, hybridní, s možností skrýt popisky). Naopak jsem zakázal změnu měřítka kolečkem myši a Street View.

Další snímek pochází ze stejného sešitu, pouze šablona a tedy forma předaných dat a jejich zobrazení je jiné (bublinový graf, ukázka online).

Google mapy - markery a kruhy

Google mapy – markery a kruhy

Poslední ilustrace už tolik společného s Excelem nemá.

Google mapy - polygon

Google mapy – polygon

Excel do zvýraznění regionu (Libereckého kraje, ukázka online) na mapě zasahuje pouze minimálně – předává barvu výplně polygonu (sytost určuje míru hodnoty, intenzitu, četnost výskytu aj., viz také teplotní barevné škály). Pracnost spočívá v něčem naprosto jiném a nezáživném – sběru dat. Geografická data pro hranice krajů a okresů ČR se shánějí docela těžko. Pomineme-li placené služby, pak něco málo najdeme na stránkách jednotlivých krajů a dále na stránkách ČVUT. Hned na úvod říkám, že data z tohoto zdroje jsem odzkoušel a nejsou dvakrát aktuální a přesná. Souřadnice přitom sosáme ze souborů SHP. Asi nejlepším pomocníkem vám bude open source QGIS. Ten mimo jiné zvládá i převod koordinátů, např. z „Křovákova“ S-JTSK na WGS 84 (běžná zem. šířka a délka). Získaný polygon se souřadnicemi je nutné ještě textově upravit pro potřeby map Google, v mém případě procedurou VBA. U Libereckého kraje jsem dospěl k cca 700 hraničním bodům. Jejich textové vyjádření představuje datový objem 33 kB. A to je pouze jeden kraj. Představte si jít do úrovně okresů. Pravděpodobně by byla vhodnější forma dat KML nebo JSON, jež Google také umí. Proč ale nesáhnout po jiném nástroji – geomapě.

Google mapy - geomapa

Google mapy – geomapa

ukázka online

Tip netip: Společnost ARCDATA PRAHA poskytuje datové podklady pro mapy zdarma, nicméně k jejich prohlížení budete potřebovat přinejmenším program ArcView, který je dnes součástí ArcGIS for Desktop. Po registraci si objemný balíček v 60denní trial verzi můžete stáhnout. Na stránkách ARCDATA k němu možná najdete i lokalizaci. Nezkoušel jsem. Nevím tedy ani, čeho je a není možné s trial verzí dosáhnout.

Poznámky
Pro práci s mapami Google potřebujeme samozřejmě připojení k internetu. Použitelnost prvku Web Browser lehce skřípe, ale svou úlohu splní. Excel kdysi dávno (naposledy v Excelu 2000?) uměl zobrazovat data na mapách a to včetně ČR do úrovně okresů. Pak přišel placený MapPoint a konec nativní podpory. Mapy (Microsoft Bing Maps) se objevují až zase v Excelu 2013 a jeho PowerView (zjednodušeně nadstavba kontingenčních tabulek a náhrada doplňku PowerPivot, vyžaduje min. 2 GB RAM). Kromě toho snad ještě určité možnosti skýtá placený SharePoint. Pro většinu uživatelů je každopádně vizualizace dat na mapách pořádný oříšek. V Excelu je možné pouze simulovat mapy bublinovým grafem nebo obarvováním tvarů mapy.

Bublinový graf
Bublinový graf očekává trojice hodnot. Ty udávají souřadnice středu kruhu S[x, y] a hodnotu pro velikost (plochu) kruhu. Za souřadnice přitom dosazujeme zeměpisné koordináty transformované do použitého výřezu mapy (dán levým dolním a pravým horním rohem mapy, která slouží zároveň jako výplň zobrazované oblasti grafu. V grafu pochopitelně skrýváme mřížku, osy a vlastně vše s výjimkou popisků dat, kdy ve štítcích necháme zobrazit velikost bublin. Snímek níže neobsahuje ani popisky (hodnoty se každopádně objeví po najetí myškou nad datovou řadu).

Excel - bublinový graf

Excel – bublinový graf

Simulace mapy intenzity
Otázkou číslo jedna je samozřejmě způsob, jak dostat mapu ČR v křivkách do Excelu. Zvolil jsem import z Corel Draw. Excel (a Microsoft Office jako celek) ovšem formát CDR nikdy pořádně nepodporoval a jediné, co jakžtakž zvládá, je EMF, resp. WMF. Vše je relativně v pohodě, tedy dokud se nepokusíte převést vložené křivky na kresbu Office (typicky při pokusu Skupina / Oddělit). V tu chvíli se nezřídka křivky deformují a přijdete o přesnost, která je pro doléhání hranic polygonů nezbytná. Připravte se na to, že vám bude někdy až do pláče. Pokud se vám to povede, obarvování na základě události Worksheet_Change už bude proti tomu hračka.

Excel - mapa intenzity

Excel – mapa intenzity

Tento článek byl jakýmsi průvodcem mapami Google. Ani tak z pohledu know-how, jako spíš z důvodu pracnosti si tentokrát dovolím nezveřejnit konkrétní sešity a šablony Excelu s příklady, obslužný VBA kód, databanku zeměpisných souřadnic českých měst, vektorovou mapu v Excelu, nebo třeba tabulku pro URL encoding. V případě, že je dokážete ocenit, dejte vědět.

Odkazy:
Google Maps API Tutorial

Klikni a stahuj!