In 2014, the Bicimad public bicycle service became operational. Like many other people in Madrid who suffer from daily traffic jams in Madrid, we thought it was good news to have other public transport alternatives. But we were worried about one issue: safety. Would there be more accidents? Would this information be transparently transmitted to citizens?
|Figure 1: Open Data Portal of the Madrid City Council.|
- Double Collision: Traffic accident between two vehicles in motion.
- Multiple collision: Traffic accident between more than two vehicles in motion.
- Impact with stationary object: Accident between a vehicle in motion with a driver and an immobile object that occupies the road or area away from it, whether a parked vehicle, tree, street lamp, etc.
- Atropello: Accident between a vehicle and a pedestrian occupying the roadway or passing through sidewalks, shelters, walkways or areas of public roads not intended for the movement of vehicles.
- Tipping: Accident suffered by a vehicle with more than two wheels and that for some reason its tires lose contact with the road being supported on one side or on the roof.
- Motorcycle crash: Accident suffered by a motorcycle, which at some point loses its balance, due to the driver or due to the circumstances of the road.
- Moped fall: Accident suffered by a moped, which at a certain moment loses its balance, due to the driver or the circumstances of the road.
- Bicycle fall: Accident suffered by a bicycle, which at a certain moment loses its balance, due to the driver or the circumstances of the road.
|Figure 2: File downloaded in Excel format.|
|Figure 3, We create a table|
|Figure 4: Appearance of the table.|
|Figure 5: Insert row of totals.|
- Tomorrow: from 7 to 12
- Afternoon: from 13 to 20
- Night: from 21 to 6
|Figure 6: Use Search-Replace function.|
|Figure 7: Use of the nested SI function.|
|Figure 8: Create dynamic table.|
|Figure 9: We choose fields.|
|Figure 10: Incidents per month.|
- What are the most frequent accidents?
- When do they occur? Are there more at sometimes of the year than others? More in certain time slots than others?
- Where are they happening? Are they more frequent in some districts than in others? Are there “black spots” of accident accumulation?
- Type of accident (Rows)
- District (Filter)
- Hourly section (Columns)
- Number of incidents (values)
|Figure 11: Accidents by time zone and district.|
- District (Rows)
- Months (Filter)
- Number of incidents (values)
|Figure 12, Incidents by district|
|Figure 13: We add segmentation by Section Schedule|
|Figure 14, Configuration of columns for “buttons”|
|Figure 15: Segmentation buttons on the table|
|Figure 16: Report connections.|
|Figure 17: We insert a dynamic graph|
|Figure 18, type of incident / tomorrow.|
|Figure 19: Dynamic graph type of incident / night|
|Figure 20, Example of panel|
- Which accident is the most frequent?
- At what times do the most accidents occur?
- In which district do more accidents occur? Is there a specific street?
- Does the number of accidents vary according to the season of the year?
|Figure 21, Number of accidents per district|
|Figure 23: Incidents occurred on Calle Alcalá.|