Google Fusion Tables - create map of highway accidents

Google Fusion Tables - create map of highway accidents


Job Description


I manage a 99km stretch of a highway for which a list of traffic incidents is maintained and will be added to over the next 20 years.

(here is link to my inspiration: Note: this is what I would call the perfect example, my tool would not need to be so pretty and it would not be shared publicly.)

I'd like to turn this data into a some more useful. Specifically, I want to know where and the accidents are occuring and under what condition they are occurring.

For each accident, we know:
1) date
2) time
3) extent of accident: property damage/rockfall/collision/injuries/fatalies
4) location (GPS cordinates)
5) single vehicle or multiple vehicle
6) road conditions: dry/wet/snow (sometimes this informatoin is missing.)
8) whether emergency services responded
9) indicident report # (this is the unique identifier)

I think it would be useful to display this information in a map format using Google Fusion Tables***. Your product once established could be easily used be a reasonablly smart person who would only have to upload the new data and refresh the output. This person would rely on a one-page instruction sheet to perform the updates.

The user should be able to slice the output. For example, only show incidents involving multiple vehicles that occurred in section 1^^^, involving multiple vehicles on a Sunday between 9am on dry road. The user should be able see each incident (and the details concerning that incident) and (likely in a separate view) a intensity map.

Additional work that you could suggest: based on the information that we have, as listed above, pull/cross reference information from publicly available source. For example,
1) based on the GPS coordinates pull the publicly available weather data (e.g., daily high and low temperature, wind, rain, snow)from the nearest/most relevant Environment Canada weather station.
2) based on the GPS location and time, indicate weather it was daylight, dusk/dawn, or nighttime.

*** if you have a reasonable no-cost/easy-to-maintain alternative Google Fusion Tables, I am happy to hear of your suggestion. (The new Excel 2013 charting function perhaps?)

^^^^ Based on the GPS I'd like your excel sheet to assign the incident to one of ten sectoins of the highway (There may be more or less than 10 they are yet to be determined.)

Lastly, if you think I have miscategorized this Job, please suggest where it could be better placed.

Update - March 13: I am going to spend a few days cleaning up the source data just so it is clean when I give it to the person who is ultimately hired.
Upodate - April 8: Now trying to get some better GPS reference points to compare the incidents against.

Skills: gps