RYCHLE A ZBĚSILE PO BRNĚ

DATASETY

Veškerá zpracovávaná data pochází z veřejných zdrojů:

Dopravní nehody v Jihomoravském kraji v letech 2016-2018 (Policie České republiky)
Seznam měřených úseků v Brně (Městská policie Brno) - seznam míst, kde policisté měří překračování nejvyšší povolené rychlosti

IMPORT NEHOD

Data o dopravních nehodách v Brně v letech 2016-2018 jsme získaly ze stránek Policie České republiky. Šlo o čtyři soubory:

Číselník ve formátu xlsx
Tři soubory ve formátu csv, z nichž každý obsahuje údaje o dopravních nehodách v Jihomoravském kraji za jeden z uvedených roků

Číselník obsahuje legendu ke každému ze souborů ve formátu csv. Skládá se ze 2 listů: Seznam polí a Položky. V listu Seznam polí jsou kódy záhlaví tabulky o nehodách.

Význam kódů z hlavičky je v listu Položky. Ve stejném listu jsou i číselníky dimenzí.

Samotný obsah tabulky (data o nehodách) je v souborech ve formátu csv.

Vzhledem k několika málo atributům z celkového počtu (57), které z datasetu využíváme, jsme si s pomocí číselníku vyhledaly jejich význam. Pomocí funkce get_dimension(code) jsme si vytvořily slovník, do kterého jsme ukládaly jednotlivé číselníky.

Kromě hlavní tabulky s nehodami tedy vznikly dimenzionální tabulky: charakter, zavineni, alkohol, druh_vozidla a ta nejdůležitější dimenze hlavni_pricina. Jednotlivé příčiny, které zavinily nehodu, jsou rozdělené do kategorií, a proto je na tuto tabulku ještě navázaná tabulka kategorie_hlavni_pricina.

Každá nehoda má mj. atributy souradnice_x a souradnice_y. Tyto souřadnice jsou uvedené v souřadnicovém systému S-JTSK/Krovak. Pro další práci s prostorovými daty jsou vhodnější souřadnice v systému WGS84. Proto jsme tyto souřadnice transformovaly pomocí funkce krovak_to_wgs84(souradnice_x, souradnice_y), která k řešení využívá knihovnu shapely a nové hodnoty: souradnice_lat a souradnice_long jsme do tabulky přidaly jako nové atributy.

Abychom si při práci s daty mohly vyfiltrovat pouze brněnské nehody a také katastrální území a zároveň pro porovnávání míst nehod a úseků, vytvořily jsme si další funkce:

get_address(latitude, longitude) – funkce provede request API reverse.geocoder.api.here.com a vrátí slovník s informacemi o lokalitě (stát, kraj, město, katastrální území, ulice, číslo domu, PSČ). Záměrně vracíme celý slovník, kdyby se nám hodily ještě další informace o lokalitě.

Následující funkce vrací informace, které potřebujeme pro filtrování a porovnávání:

get_city_district(address) – vrátí katastrální území
get_city(address) – vrátí město
get_street(address) – vrátí ulici
get_house_number(address) – vrátí číslo domu

První skript z roku 2016 obsahoval kromě vkládání hodnot i vytvoření tabulek. Další skript směřující k automatizaci obsahoval pouze vkládání hodnot. Je tedy možné tento skript využít pro další vkládání dat v příštím roce a v dalších letech.

IMPORT MĚŘENÍ

Zaradovaly jsme se, když jsme na stránkách Městské policie Brno našly seznam měřených úseků.

Zdálo se, že jde o skvělý zdroj a zároveň příležitost vyzkoušet si web scraping. Připravily jsme si importovací skript a až v průběhu vylaďování skriptu jsme zjišťovaly, že v tabulce je mnoho chyb a skript na web scraping jednoduchoučké tabulky s 82 záznamy se natáhl na 300 řádků.

Atribut „Ulice“ byl pomocí regulárních výrazů rozdělen do více atributů, aby informace uvedená v každém z nich byla atomická (ulice, zacatek, konec, mapa). Názvy ulic by bylo vhodné vybírat ze seznamu brněnských ulic, aby se předešlo různým překlepům. Další prostor pro vyladění, aby se s daty dalo lépe pracovat, by bylo místa krajních bodů úseků definovaných názvy ulic, které se křižují, určit přesněji pomocí souřadnic těchto bodů.

Z atributu „Limit“ byla pomocí regulárního výrazu vyextrahována pouze číselná informace bez jednotky (km/h), aby se se vzniklým číslem dalo dále pracovat, kdyby to bylo potřeba.

Kvůli poslednímu atributu „Důvod měření“ jsme pozměnily datový model a vytvořily jsme pro tento atribut dekompoziční tabulku a číselník.

Aby bylo možné měřené úseky zobrazit v mapě, bylo potřeba krajním bodům úseků přiřadit souřadnice. K těmto účelům jsme si vytvořily funkci get_crossroads_wgs84_coordinates(street_1, street_2) s argumenty názvů ulic křižovatky, která provede request API geocoder.api.here.com a vrátí WGS84 souřadnice tohoto místa.

Dalším krokem k úspěšnému vykreslení úseků bylo potřeba ke každému úseku kromě krajních bodů přiřadit ještě body, které jsou mezi krajními body. Pomocí vytvořeného algoritmu jsou získané body od sebe vzdálené maximálně 2 metry. K tomuto účelu jsme vytvořily funkci get_route_points(start_lat, start_long, end_lat, end_long), která provede request API dev.virtualearth.net a vrátí pole s tuply (dvěma dvojicemi souřadnic začátku a konce úseku). Čím byl rovnější úsek mezi jednotlivými body, tím vzdálenější byly tyto body od sebe. Abychom měly body úseků vzdálené od sebe maximálně 2 metry, došlo uvnitř této funkce k volání funkce get_distance(start_lat, start_long, end_lat, end_long), která u každého přidávaného bodu ověřila, zda je vzdálenost menší než 2 m. V případě, že to bylo potřeba, úsečka mezi těmito dvěma body se proložila dalšími body (dynamicky podle vzdálenosti). Toto obstarala funkce interleave_abscissa(start, end, distance).

Pro body úseků jsme si v databázi vytvořily další tabulku mereni_usek, do které jsme vložily jednotlivé body úseků s vazbou na jednotlivé úseky.

PŘÍPRAVA PODKLADŮ PRO MAPU

Myslely jsme si, že máme vyhráno. Máme připravená veškerá data a už zbývá jen jejich vizualizace s pomocí Power BI. Zjistily jsme však, že Power BI nedokáže ani zobrazovat úseky (v mapách zobrazuje pouze body) a stejně tak sloučení dvou vrstev map, které jsme potřebovaly pro vizualizaci překryvů měřených úseků a nehodovosti, standardní cestou pomocí Power BI není možné.

To nás ovšem neodradilo. Hledaly jsme, hodně jsme se ptaly a nakonec jsme řešení našly. Dokonce jsme vyřešily oba tyto problémy najednou, když jsme objevily mapový vizuál Mapbox, který umožňuje neomezeně vrstvit mapy na sebe a skýtá velké množství funkcinonalit vedoucích ke customizování mapy. Zároveň je možné nastavit zobrazení úseků (linií).

Pro vrstvu s měřenými úseky jsme využily data z tabulky mereni_usek. Data pro vykreslení polygonů katastrálních území jsme získaly z webových stránek ArcGIS Hub. Naimportovaly jsme si data o polygonech (katastralni_uzemi_id, latituda a longituda).

Bylo však ještě potřeba tomuto vizuálu data předpřipravit ve vhodné podobě: ve specifickém formátu geojson. Standardní geojson se Mapboxu nepozdával, a proto jsme si připravily dva skripty, které správný formát geojsonu upravily: useky_to_geojson a katastralni_uzemi_to_geojson.

DATOVÝ MODEL

V zobrazeném datovém modelu jsou všechna dosud naimportovaná data. Navíc jsou tu dvě analytické tabulky: nehodovost_a_mereni a efektivita_mereni. O těchto tabulkách se dozvíte více na další stránce věnující se analýze dat pomocí Pythonu.

VYTVOŘENÍ DATOVÉHO SKLADU

Vzhledem k tomu, že datový sklad je vhodnější strukturou pro provádění analýz než vytvořený transakční datový model, pro práci v Power BI jsme datový model do datového skladu transformovaly. Tento krok jsme realizovaly pomocí skriptu v SQL.

POMOCNÉ FUNKCE

Všechny funkce, které jsme si vytvořily v Pythonu, jsme uložily do pomocného modulu util.py.