DC_Crash_Bot

DC Crash Bot has two goals:

1. Combine the city's open crash data with data on 311 and Vision Zero safety requests from residents to highlight the government's response (or lack thereof) to citizen concerns about the impact of traffic violence in their neighborhood
2. The open crashes dataset is only a start. We want to build on it by incorporating other sources, such as the Pulsepoint app for first responders, crash databases maintained by third parties, and Twitter, to get a fuller picture of the damage.

Why Start this project

DC's current Vision Zero program is failing, and the incompleteness of the open crash data is a known problem with serious consequences for city residents. https://usa.streetsblog.org/2020/09/29/why-your-city-doesnt-map-its-worst-car-crashes/

Resources

Data dictionary for the analysis_data schema in our database

The Google doc where we brainstorm analysis ideas

Diagram of our data flow and architecture (still a work in progress!)

Link to our map

TODO:

How to contribute

Our PostGIS database currently has Open Data DC datasets on 311/Vision Zero requests, crashes, crash details, census blocks, address points, and all roadway centerlines-related data. We also have a regular feed of DC Fire & EMS dispatch data for car crash calls. If you're interested in doing any reporting, analytics, or visualization with this data, all you need to do is 1) download pgAdmin; 2) message me for a login.

We need people with interests or skills in front-end, machine learning/NLP, data engineering, and geographic data. If that sounds like you, join us at an upcoming Code for DC Meetup!

Finding your way around

This repository contains multiple projects, which are run locally in different ways depending on their architecture. Check them out:

• /dcmap is the DC map front-end being worked on in #90
• /frontend is a map front-end, but isn’t actively being worked on at the moment.
• /data contains exports from our main PostgreSQL database — feel free to poke around.
• /notebooks contain different analysis projects in the form of Jupyter Notebooks.
• /scripts are miscellaneous data cleaning and collection python scripts.
• /SQL contain miscellaneous SQL queries against our main PostgreSQL database.

Sample query

--Which DC neighborhood has submitted the most Traffic Safety Assessment requests since 2015? 
CREATE TABLE Neighborhoods  AS (
SELECT 
    assessment_nbhd AS Neighborhood,
	ST_ConcaveHull(ST_Collect(d.geometry), 0.99) AS geometry
FROM source_data.address_points 
GROUP BY assessment_nbhd
)

CREATE TABLE all311_w_neighborhood AS (
SELECT b.Neighborhood, a.*
FROM source_data.all311 a
INNER JOIN Neighborhoods b ON ST_Intersects(b.geometry, a.geometry)
) 

SELECT Neighborhood, count(*) FROM all311_w_neighborhood
GROUP BY Neighborhood order by count(*) DESC