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.

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/

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!)

TODO:

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!

--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