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:

Komentáře

Populární příspěvky z tohoto blogu

Barbora Junová: Podpora začínajícího podnikání zaměřeného na prodej výrobků a poskytování služeb

Petra Havlínová: Automatické zpracování podkladů pro vyhodnocení vybraných KPI

Kateřina Kolouchová & Lenka Tomešová: Vliv počasí na kriminalitu v New Yorku a Brně