Define functions, downloads files

Clean data and prepare for geocoding

Here is a snapshot of the data as it was received

When preparing the car crash (“accidents”) data, we generate a factor variable of the seriousness (“gravité”) of the crash, from least serious to most serious.

To convert the municipality codes to names, I created a tab-separated file from this tablea on the provincial government website.

The dataset contains multiple variables related to the crash location, but it doesnt include the latitude and longitude of the crash. We will need to create a string variable (I called it “location”) that will be passed to the google maps API so that it can return us a latitude and a longitude.

The geographical variables are as follow. They are never all filled.

• “NO_CIVIQ_ACCDN” , the street civic number
  
• “SFX_NO_CIVIQ_ACCDN”, a suffix to the street number
  
• “RUE_ACCDN”, the road name
  
• “CD_MUNCP”, the city code. Here is a dictionnary to convert city code to name.
  
• “NO_ROUTE” is the road number where the accident happened (numbered roads are typically highways). This seems to be used as a alternative to the road name RUE_ACCDN.
  
• “CD_PNT_CDRNL_ROUTE” is the direction (North, South, East, West (Ouest) ) travelled on the road/highway.
  
• “BORNE_KM_ACCDN” is the milestone number (used on highways and northern gravel roads)

They also use landmarks (road crossings, etc..) to help locate the accident:

• TP_REPRR_ACCDN is the type of landmark.
  
• 1 means the intersection of two roads,
  
• 2 " means “other landmark”
  

• 0 means the type is not specified.

• “ACCDN_PRES_DE” is the landmark that the type refers to. It can be the road that intersects the road named under “RUE_ACCDN”, a bridge, a school name, etc.
  
• “NB_METRE_DIST_ACCD” is the distance in meters between the landmark and the accident.
  

• “CD_PNT_CDRNL_REPRR” is the direction (North,South, East, Ouest) from the landmark to the accident.

Since most crashes involving pedestrians and bikes are located in the cities, the data typically contains the street civic number + street name or the name of the two streets at the road crossing. I didnt try to geocode the more complicated cases involving the milestone number.

Before creating the string that would be passed to the google maps API, I first had to replace a lot of abbreviations using regular expressions. For example, “BD” is actually “Boulevard” and “ST” usually stands for “Saint”. The regular expression tool of choice was which is the boundary of a word.

Geocoding using ggmap

I used the ggmappackage to geocode the car crashes through the google maps API. The free version of the API is limited to 2 500 calls per day, so I had to get a premium API key. This project didnt cost me anything since I had some google credit from I dont know when.

11639 crashes involving bikes occurred in province de Québec between 2011 and 2016.

I didnt attempt to geocode 213 of them because I wasnt able to generate a satisfying “location” string. These crashes are listed in the appendix.

The API couldnt return a latitude/longitude for 392 of the 11426 that I tried to geocode. These are also listed in the appendix.

Crashes by year

Crashes by month of the year

Crashes by time of the day

Crashes by weather conditions

Crashes by administrative area

Crashes by city (top 10)

Crashes by seriousness

Map of deadly crashes

This map shows all the 66deadly crashes involving bikesin province de Québec between 2011-2016 that were successfully geocoded. If multiple crashes occurred at the same spot then we will only see the most recent one.

Heatmap

The following heatmap allows us to easily dangerous areas even if the crases didnt occur at the exact coordinnates.

MarkerCluster

This last map shows clusters of accidents. If you zoom to the maximum, you will be able to see the details of all crashes that occurrend at the intersection of 3e avenue and 4e rue in Québec city, but also the crash that occurred right next to it at “410 3e avenue”.