Zuzana Talašová, Veronika Doleželová: Analýza dopravních nehod v Brně v závislosti na počasí

Úvod:
Naše dvojice, téma projektu a získání dat

Naše dvojice existovala ještě předtím, než jsme se přihlásily do Czechitas. Obě pracujeme ve firmě 
FNZ, která vyvíjí investiční platformy pro finanční instituce a zároveň nabízí technickou podporu 
k oněm platformám. S SQL jsme se zběžně potkaly už v práci, ale především se jednalo 
o předepsané příkazy, kde jsme změnily pár hodnot, a hlubšího porozumění nebylo třeba.

Zároveň nás během Digitální akademie obě uhranulo PowerBI, které stále ještě považujeme 
za úžasný nástroj pro vizualizaci datové analýzy, a proto jsme si téma projektu vybíraly tak, 
abychom při něm využily jak SQL, tak PowerBI. Například Excelu jsme se plánovaly vyhnout, 
ale nakonec jsme přišly na to, že jelikož je to jeden z mála nástrojů, kde jsme si jisté v kramflecích, 
bez něj to nepůjde a doopravdy nám ušetřil spoustu nervů s vymýšlením složitějších technických 
postupů.

Též nás lákalo využití kódování v Pythonu, ale protože nás nakonec lákalo zkusit projekt udělat 
s použitím úplně všech nástrojů, ke kterým jsme v Digitální Akademii přišly do kontaktu, 
jelikož všechny nám přišly maximálně užitečné a zajímavé, rozhodly jsme se samy sebe zkrotit 
a jít do hloubky méně programů, než dělat všechno a nakonec neumět nic.

Na setkání s mentory jsme si myslely, že jdeme připravené: nejen s nápadem na projekt, ale také 
se záložním plánem, kdybychom přišly na to, že původní plán je podle mentorů nereálný.

Během večera jsme přišly i o záložní plán, jelikož naše představy nebyly realizovatelné v rámci
jednoho měsíčního projektu. Takže jsme byly bez plánu a bez dat. Zoufalé. Uprostřed večera Meet 
your Mentor, kde každý chtěl slyšet naše nápady na projekt, a my jsme vlastně byly bez nápadu.

Naštěstí nás v tom mentoři nenechali a sami nabídli data a spoustu nápadů, kterým směrem se 
vydat. S nápadem zkoumat vliv počasí na nějaký další aspekt se nás ujal Martin Zelený z RedHatu. 
A dokonce jsme se nakonec nemusely zaměřovat na počasí ve Spojených Státech (o čemž jsme 
se během večera dozvěděly, že je volně přístupné na internetu s detailními informacemi), 
ale data poskytla i meteorologická stanice v Tuřanech, a tak jsme měly ke svým datům hned 
bližší vztah.

S tímhle jsme detailně prošly brněnský web data.brno.cz a našly detailní soubor s informacemi 
o dopravních nehodách za celý rok 2017 na území města Brna.

Přístup k datům o nehodách byl snadný, avšak na první pohled nedával smysl, kontaktovaly 
jsme proto Brněnský magistrát, zda by nám byli schopni poradit. S příjemným překvapením jsme 
po týdnu obdržely odpověď. Legenda ke sloupcům se skrývala v jiném listu Excelu na 38. řádku 
excelové tabulky (z nějakého důvodu) a my jsme tak získaly klíč k rozkódování dat o dopravních 
nehodách. 

Cíl projektu

Účel projektu jsme viděly především v přispění k informovanosti veřejnosti a možnosti předvídat 
dopravní komplikace za daného počasí. Základní hypotézou bylo, že za špatného počasí 
(tj. při nadprůměrném úhrnu srážek, nízké teplotě, silném větru nebo přítomnosti sněhu) bude počet 
dopravních nehod vyšší a my jsme si vzaly za úkol nejen potvrzení/vyvrácení téhle hypotézy, ale též 
jsme chtěly najít korelaci mezi špatným počasím a detaily dopravních nehod: například druh a příčina 
nehody nebo druh vozidla.

Tímto jsme chtěly formulovat doporučení pro řidiče, že v některých případech je lepší zvolit jiný 
způsob dopravy nebo dbát zvýšené opatrnosti v některých dopravních situacích.

Úprava a čištění dat, vytvoření databáze a tabulek v SQL

V Excelu jsme upravily názvy sloupců, abychom mohly celý CSV soubor nahrát do Microsoft SQL 
Management Studia. Se samotným procesem nahrávání jsme se chvíli potýkaly, ale jelikož Google 
ví opravdu všechno, import dat byl nakonec úspěšný.

Jelikož hlavní faktová tabulka obsahovala pouze kódy pro hodnoty v jednotlivých sloupcích, vytvořily 
jsme číselníky pro rozkódování hodnot během pozdější analýzy. Pro vytvoření číselníků jsme se 
rozhodly využít znalosti SQL a vytvořily jsme je pomocí příkazů jako je tento:

CREATE TABLE [dbo].[C_zavineni_nehody](
    [ID] [varchar](50) NULL,
    [Vysvetlivka] [varchar](50) NULL,
    [Vysvetlivka2] [varchar](50) NULL
)

INSERT [dbo].C_zavineni_nehody ([id], [vysvetlivka], [vysvetlivka2]) VALUES (1, N'řidičem', N'motorového vozidla')
GO
INSERT [dbo].C_zavineni_nehody ([id], [vysvetlivka], [vysvetlivka2]) VALUES (2, N'řidičem', N'nemotorového vozidla')
GO
INSERT [dbo].C_zavineni_nehody ([id], [vysvetlivka], [vysvetlivka2]) VALUES (3, N'chodcem','')
GO
INSERT [dbo].C_zavineni_nehody ([id], [vysvetlivka], [vysvetlivka2]) VALUES (4, N'lesní zvěří, domácím zvířectvem','')
GO
INSERT [dbo].C_zavineni_nehody ([id], [vysvetlivka], [vysvetlivka2]) VALUES (5, N'jiným účastníkem silničního provozu','')
GO
INSERT [dbo].C_zavineni_nehody ([id], [vysvetlivka], [vysvetlivka2]) VALUES (6, N'závadou komunikace','')
GO
INSERT [dbo].C_zavineni_nehody ([id], [vysvetlivka], [vysvetlivka2]) VALUES (7, N'technickou závadou vozidla','')
GO
INSERT [dbo].C_zavineni_nehody ([id], [vysvetlivka], [vysvetlivka2]) VALUES (0, N'jiné zavinění','')
GO

Všechny SQL dotazy jsou pak nahrané zde: https://github.com/zuzanaDA/sql-pocasi-nehody

Tabulky v databázi výsledně byly tyhle (“C_...” označuje číselník, "P_..." tabulky počasí a data_nehody
je faktová tabulka):

P_vyska_snehu

P_uhrn_srazek

P_teplota

P_rychlost_vetru

data_nehody

C_zpusob_vyprosteni_osob_z_vozidla

C_zavineni_nehody

C_vozidlo_po_nehode

C_vnejsi_ovlivneni_ridice

C_viditelnost

C_unik_provoznich_prepravnich_hmot

C_stav_ridice

C_stav_povrchu_vozovky

C_stav_komunikace

C_stav_chodce

C_smyk

C_smerove_pomery

C_smer_jizdy_nebo_postaveni_vozidla

C_situovani_nehody

C_situace_v_miste_nehody

C_rozhledove_pomery

C_povetrnostni_podminky

C_poskytnuti_prni_pomoci

C_pohlavi_osoby

C_nasledky_na_zivotech_a_zdravi_chodcu

C_misto_dopravni_nehody

C_lokalita_nehody

C_kategorie_ridice

C_kategorie_chodce

C_chovani_chodce

C_charakteristika_vozidla

C_charakter_nehody

C_hlavni_priciny_nehody

C_druh_vozidla

C_druh_srazky_jedoucich_vozidel

C_druh_pozemni_komunikace

C_druh_povrchu_vozovky

C_druh_pevne_prekazky

C_druh_nehody

C_druh_krizujici_komunikace

C_deleni_komunikace

C_alkohol_u_vinika_nehody_pritomen

A tady je náš první krok k samotné analýze:

---nejvyssi pocet nehod ---
select count (identifikacni_cislo) as pocet_nehod, casove_udaje_o_dopravni_nehode
from dbo.data_nehody
group by casove_udaje_o_dopravni_nehode
order by pocet_nehod desc 

pocet_nehod	casove_udaje_o_dopravni_nehode
40	2017-01-31 00:00:00.000
39	2017-06-23 00:00:00.000
37	2017-10-09 00:00:00.000
37	2017-05-18 00:00:00.000

K vytvoření tabulek s údaji o počasí jsme původně měly v plánu též využít SQL, ale po půl hodině 
přepisování příkazu k unpivotování tabulky (každá z tabulek obsahovala sloupce rok, měsíc a 
teplotu pro každý den zvlášť) jsme zavzpomínaly na PowerBI, kde se stejná funkce udělala pomocí
tří kliků. Sloupce na řádky jsme tedy rozdělily v PowerBI a výsledné tabulky naimportovaly zpět do 
SQL studia.

Průměrná denní teplota vzduchu ve °C
stanice: B2BTUR01
rok	měsíc	1.	2.	3.	4.	5.
2017	01	-7,1	-5,5	-1,0	1,2	-4,1
2017	02	-1,5	-0,3	1,2	1,3	0,9
2017	03	4,9	6,4	6,4	12,8	9,1
2017	04	16,4	17,5	13,7	13,2	12,0
2017	05	12,6	11,1	12,4	14,0	13,7
2017	06	20,6	21,1	22,1	20,7	18,9
2017	07	19,7	19,6	19,3	20,1	23,1
2017	08	28,6	27,2	27,5	25,1	25,8
2017	09	16,4	14,5	13,7	15,4	17,3
2017	10	11,7	12,4	9,4	11,0	14,4
2017	11	5,6	10,0	6,2	7,5	8,3
2017	12	-3,3	-3,0	-0,2	1,1	3,0

Tvorba datového modelu a dosažené výstupy

 

V PowerBI jsme vytvořily i naši první verzi datového modelu a v nastalé chvíli jsme byly natolik 
okouzlené tímto programem, že jsme se rozhodly využít k analýze primárně PowerBI a SQL použít 
až jako kontrolu výsledků.

Náš datový model se během projektu rozrostl až do podoby níže:

Jako první krok jsme začaly pátrat po anomáliích v nehodách. Ze začátku jsme musely překonat 
spoustu nezdarů a minimálních výsledků, než jsem se dostaly k prezentovatelným datům. 
V některých chvílích jsme dostaly strach, že naše hypotéza, že počasí má vliv na dopravní nehody, 
bude nakonec neplatná a my nebudeme mít co prezentovat.

Tady máme zatím na ukázku pár dat bez vlivu počasí:

Den s nejvyšším počtem nehod byl 31/01/2017:

Rok	Mesic	Den	Pocet nehod	Den v tydnu
2017	Leden	31	40	2
2017	Cerven	23	39	5
2017	Kvetem	18	37	4
2017	Rijen	9	37	1
2017	Listopad	20	37	1
2017	Kveten	22	36	1
2017	Listopad	27	36	1
2017	Prosinec	21	36	4

Z tabulek o počasí jsme vybrali 4, které podle nás nejvíce ovlivní řidiče.

Nejvíce sněhu:

Rok	Mesic	Den	Vyska snehu	Pocet nehod
2017	Unor	2	22	18
2017	Unor	3	20	19
2017	Unor	1	19	26
2017	Unor	4	15	7
2017	Leden	12	10	25

Nejvíce srážek:

Rok	Mesic	Den	Uhrn srazek	Pocet nehod
2017	Cervenec	20	22	21
2017	Leden	31	16,2	40
2017	Cervenec	24	15,1	21
2017	Duben	17	14,1	19
2017	Rijen	3	13,7	32

Nejsilnější vítr:

Rok	Mesic	Den	Rychlost vetru	Pocet nehod
2017	Rijen	29	12	21
2017	Duben	19	9,7	16
2017	Rijen	28	9	13
2017	Unor	9	8	16
2017	Unor	24	8	27

Nejnižší teplota:

Rok	Mesic	Den	Prumerna teplota	Pocet nehod
2017	Leden	7	-10,9	17
2017	Leden	19	-10	18
2017	Leden	10	-9,9	18
2017	Leden	8	-9,1	17
2017	Leden	20	-8,8	32

Tyto hodnoty nesplňovaly zcela naše očekávání a proto jsme přidali další parametr – SMYK

Začali jsme se dostávat k zajímavým datům. Tento graf znázorňuje poměr nehod se smykem a 
bez smyku ve dny, kdy byla sněhová pokrývka:

31/01/2017 se událo 16 nehod beze smyku a 15 nehod se smykem

Avšak nejvíce sněhu napadlo až o 2 dny později – 22 cm sněhu 2/2/2017

20/07/2017 – 22 mm vody / „pouze“ 21 nehod – v dešti asi řídit převážně umíme :)

Ale – den s druhým nejvyšším úhrnem srážek (tentokrát sněhových, protože teplota byla pod nulou) 
připadá hned na 31/01/2017: 40 dopravních nehod.

Datum	Uhrn srazek	SMYK	Pocet nehod
2017-07-20	22	0	11
2017-07-20	22	1	5
2017-01-31	16,2	0	16
2017-01-31	16,2	1	15
2017-07-24	15,1	0	16
2017-07-24	15,1	1	1
2017-04-17	14,1	0	13
2017-04-17	14,1	1	2
2017-10-03	13,7	0	25
2017-10-03	13,7	1	3

Když jsme se ale snažily o podrobnější analýzu dat nehod v závislosti na počasí, dospěly jsme 
k závěru, že nakonec přece jen bude lepší a pohodlnější vytvořit si pomocí SELECT příkazu
jednotlivé tabulky k analýze a až následně je vizualizovat pomocí PowerBI.

Jelikož datový model byl příliš obsáhlý, zaměřily jsme se pouze na následující aspekty počasí, 
které podle nás mohly ukazovat největší korelaci s daty z dopravních nehod: Průměrná denní teplota, 
úhrn srážek, přítomnost sněhové pokrývky a síla větru.

Následující aspekty počasí jsme tedy kvůli rozsahu práce nepokryly: Tlak vzduchu, vlhkost vzduchu 
a počet hodin slunečního svitu.

U rychlosti větru jsme se zaměřily na druh vozidla, jelikož jsme měly teorii, že by silný vítr mohl 
ovlivňovat cyklisty a motocyklisty a případně i vozidla svou boční plochou velká jako třeba kamiony.

Po poradě s mentorem jsme se rozhodly srovnat průměrný denní počet nehod s horním a dolním 
decilem tabulky síly větru a zjistit tak, jestli se počet nehod pro různé druhy vozidel liší v těchto 
okrajových hodnotách síly větru.

--prumerny pocet nehod podle druhu vozidla --
select DV.Vysvetlivka, count(identifikacni_cislo) as CountOf, 

count(identifikacni_cislo)/365.00 as PrumerneZaDen
from dbo.data_nehody N
inner join C_druh_vozidla DV on DV.ID = N.druh_vozidla
group by DV.Vysvetlivka
order by PrumerneZaDen desc

--horni decil s vetrem --
select Vysvetlivka, count(identifikacni_cislo) as CountOf,
count(identifikacni_cislo)/
cast((select count(datum) from P_rychlost_vetru

where rychlost_vetru > 5.2) as float)
as PrumerneZaDen
from dbo.data_nehody N
inner join P_rychlost_vetru TOPVitr 
on TOPVitr.datum = N.casove_udaje_o_dopravni_nehode
inner join C_druh_vozidla DV
on DV.ID = N.druh_vozidla
where rychlost_vetru > 5.2
group by Vysvetlivka
order by PrumerneZaDen desc

K hodnotě 5,2 m/s jsme dospěly pomocí příkazu

select top 36 * from P_rychlost_vetru  
order by Rychlost_vetru desc

pro zobrazení horního decilu tabulky podle rychlosti větru. Spodní hranice decilu ukazovala 
hodnotu 5,3 m/s.

Jelikož ale v roce bylo více dní, kdy rychlost větru byla 5,3 m/s, přišlo nám nepatřičné vybrat jen den, 
který náhodně ukázalo SQL Studio (protože v takové dny se mohl stát různý počet různých 
dopravních nehod).

Vzaly jsme proto v potaz všechny dny s rychlostí větru obsaženou v horním decilu. 
Abychom následně získaly denní průměrný počet nehod, vydělily jsme součet nehod během těchto 
dní počtem dní, kdy byl vítr rychlejší než 5,2.

Ačkoliv tedy v ukázkách SELECT příkazů zde jsou příkazy definované jako decil, nejedná se o decil 
v pravém slova smyslu, nýbrž o množinu hodnot obsažených v horním decilu.

Stejný postup jsme zachovaly pro dolní decil a následně i pro úhrn srážek.

--dolni decil s vetrem --
select Vysvetlivka, count(identifikacni_cislo) as CountOf,
count(identifikacni_cislo)/
cast((select count(datum) from P_rychlost_vetru

where rychlost_vetru < 2.1) as float)
as PrumerneZaDen
from dbo.data_nehody N
inner join P_rychlost_vetru TOPVitr 
on TOPVitr.datum = N.casove_udaje_o_dopravni_nehode
inner join C_druh_vozidla DV
on DV.ID = N.druh_vozidla
where rychlost_vetru < 2.1
group by Vysvetlivka
order by PrumerneZaDen desc

Vzniklé tabulky jsme importovaly do PowerBI, kde jsme došly k výsledkům níže:

Cyklisté si zjevně z větru nic nedělají, nebo na silnice nevyjedou, protože ačkoliv je průměrný počet 
nehod při silnějším větru větší, hodnoty jsou stále hluboko pod průměrem. Tahle hypotéza se tedy 
nepotvrdila.

Mírně nadprůměrný byl počet nehod motocyklů, ale nedostatečně k tomu, abychom mohly považovat 
hypotézu za potvrzenou.

Překvapivá byla data nákladních automobilů, kdy počet nehod na den byl nižší ve dnech s vyšší 
rychlostí větru:

Úhrn srážek na rozdíl od rychlosti větru sliboval o něco zajímavější výsledky, už jen podle denního 
počtu nehod při úhrnu srážek nad 4 a 10 mm.

U osobních automobilů nebyl rozdíl až tolik markantní:

A u cyklistů dokonce počet nehod za den za deště klesl (pro potvrzení hypotézy, že cyklisté 
za deště nevyjíždí, a tudíž jsou méně častěji účastníky nehod, bychom potřebovaly další soubor dat):

Nicméně u nákladních automobilů už se hypotéza potvrzuje:

A traktory překvapily i nás. Celkový počet nehod s traktory byl sice jen 24, ale podstatná část 
z nich se stala v nejdeštivějších dnech:

Co se týká druhu dopravní nehody, ve dnech s větším úhrnem srážek mají motoristé větší 
pravděpodobnost nárazu do pevné překážky:

Zatímco srážka s lesní zvěří je znatelně nižší:

Zajímavý výsledek ukazuje i srovnání úhrnu srážek s druhem nehody “havárie”. 
Ovšem jaká je definice havárie, to se nám zjistit nepovedlo.

Příčina nehody v souvislosti s úhrnem srážek též slibuje zajímavé výsledky.

Ačkoliv potvrzení toho, že řidiči za deště nezvládají řízení, až tolik zajímavé asi nebude:

Podle grafů níže řidiči nepřizpůsobují řízení ztíženým povětrnostním podmínkám a jezdí bezohledně 
a agresivně:

A největší problém při větším úhrnu srážek je couvání a otáčení:

Pro řidiče alespoň mluví fakt, že při vyšším úhrnu srážek se věnují řízení více než ve slunečných 
dnech:

A také to, že při vyšším úhrnu srážek je řidičům méněkrát detekován alkohol:

Ať už je to s dopravními nehodami za špatného počasí, jakkoliv, nehody v těchto dnech mají 
průměrně vyšší hmotnou škodu, vyšší podíl ztráty na životech a vyšší počet zraněných:

 

Zhodnocení dosažených výsledků

Ačkoliv jsme v projektu počítaly s teorií, že i silný vítr bude mít vliv průběh nehod, tahle konkrétní 
domněnka se nepotvrdila a výzkum jsme soustředily především na dny se sněhovou pokrývkou a 
dešťovými srážkami.

Jinak ale na výše uvedených grafech můžeme vidět, že nejen se v určitém měřítku potvrdila základní 
hypotéza, že za nepříznivého počasí se zvýší počet dopravních nehod v Brně, ale též můžeme ukázat 
vliv počasí na jednotlivé aspekty těchto nehod.

Neomylně jsme tak ukázaly korelaci sněhové pokrývky a procenta dopravních nehod s přítomností 
smyku.

Dále jsme ukázaly, že druh vozidla je v dopravních nehodách při nadprůměrném úhrnu srážek 
relevantní a že nákladní automobily jsou účastníky nehody podstatně častěji. Překvapivým faktem 
byl vliv úhrnu srážek na nehody traktorů, kdy za deštivých dní počet nehod narostl o 100 % oproti 
průměrnému počtu nehod za den během roku.

Nejmarkantnější rozdíly v příčinách nehod za deště vs. při nulových srážkách se ukázaly být ve stylu 
jízdy: Při nadprůměrném úhrnu srážek řidiči znatelně častěji nepřizpůsobili rychlost vozidla 
okolnostem, stoupl počet jízd klasifikovaných jako agresivní a bezohledná a řidiči celkově častěji 
nezvládli řízení vozidla. Jako zajímavost bychom uvedly nárůst nehod způsobených otáčením nebo 
couváním, kdy ve dnech s vyšším úhrnem srážek vzrostl denní průměr takových nehod téměř třikrát.

Závěr 

Na základě těchto poznatků bychom rády doporučily řidičům nejen opatrnou jízdu v nepříznivém 
počasí, ale především se za stěrači svých aut prosím chovejte ohleduplně a trpělivě s ohledem 
na ostatní řidiče.

Navíc, při pohledu na celkové hmotné škody a počty zraněných při dopravních nehodách v deštivých 
dnech je doporučeníhodné se rozmyslet, jestli přece jen v takový den raději nezvolit městskou 
hromadnou dopravu.

Zdroje:

https://data.brno.cz/dataset/?id=dopravni-nehody

http://portal.chmi.cz/historicka-data/pocasi/denni-data#

https://www.sqlservercentral.com

https://www.w3schools.com

https://stackoverflow.com