Analyzing South Seattle Violence with Open Data
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
map_images
seattle_neighborhoods-32148
LICENSE
README.md
seattle_violence_map.qgs

README.md

Analyzing South Seattle Violence via Open Data

I live in Columbia City in South Seattle and wanted to understand the recent perceived uptick in violence. Full analysis in this blog post.

This repo is set of data to help anyone get started if they want to repeat the analysis or try it for their neighborhood. Below I've listed some SQL I used to analyze data in Postgres with PostGIS; dumped output from it into QGIS or this Google Sheet.

/* Import police data */
/* Source: https://data.seattle.gov/Public-Safety/Seattle-Police-Department-911-Incident-Response/3k2p-39jp/data */
CREATE TABLE police_calls
(
	event_clearance_description varchar,
	event_clearance_subgroup varchar,
	event_clearance_group varchar,
	event_clearance_data timestamp,
	hundred_block_location varchar,
	initial_type_description varchar,
	initial_type_subgroup varchar,
	initial_type_group varchar,
    latitude double precision,
    longitude double precision
);

/* Manually update headers; manually filter/reorder columns at data.seattle.gov */
COPY police_calls FROM '/Users/lindsayrgwatt/Downloads/Seattle_Police_Department_911_Incident_Response.csv' DELIMITER ',' CSV HEADER;

/* UPDATE police calls data to correct spatial reference projection */
/* Northern Washington spatial reference: https://epsg.io/32148 */
ALTER TABLE police_calls ADD COLUMN geom geometry(POINT,4326);
UPDATE police_calls SET geom = ST_SetSRID(ST_MakePoint(longitude,latitude),4326);
ALTER TABLE police_calls
 ALTER COLUMN geom TYPE geometry(Point,32148) 
  USING ST_Transform(geom,32148);

CREATE INDEX idx_police_geom ON police_calls USING GIST(geom);

/* Import neighborhod boundaries */
/* https://data.seattle.gov/dataset/Neighborhoods/2mbt-aqqx */

shp2pgsql -s SRID ~/Desktop/seattle_neighborhoods_32148.shp neighborhoods | psql -h localhost -d lindsayrgwatt -U lindsayrgwatt;

/* http://postgis.net/2013/08/30/tip_ST_Set_or_Transform/ */
/* Use QGIS to convert to 32148 and save as shapefile */
/* Set & convert to 32148 */
ALTER TABLE neighborhoods
 ALTER COLUMN geom TYPE geometry(MultiPolygon,32148) 
  USING ST_SetSRID(geom,32148);

/* Get counts of different offences */
SELECT
	event_clearance_description,
	COUNT (event_clearance_description),
	s_hood,
	l_hood
FROM
	police_calls
JOIN
	neighborhoods
ON ST_Contains(police_calls.geom, neighborhoods.geom)
WHERE
	event_clearance_description IN (
		'DRIVE BY SHOOTING (NO INJURIES)',
		'HOMICIDE',
		'PERSON WITH A GUN',
	)
GROUP BY
	event_clearance_description
ORDER BY
	event_clearance_description
;

/* Homicide and gun data */
SELECT
	police_calls.*,
	Extract(month from police_calls.event_clearance_date) AS month,
	Extract(year from police_calls.event_clearance_date) AS year,
	s_hood,
	l_hood
FROM
	neighborhoods
JOIN
	police_calls
ON ST_Contains(neighborhoods.geom, police_calls.geom)
WHERE
	police_calls.event_clearance_description IN (
		'DRIVE BY SHOOTING (NO INJURIES)',
		'HOMICIDE',
		'PERSON WITH A GUN'
	)
ORDER BY
	police_calls.event_clearance_date DESC
;

/* Shots reported */
SELECT
	police_calls.*,
	Extract(month from police_calls.event_clearance_date) AS month,
	Extract(year from police_calls.event_clearance_date) AS year,
	s_hood,
	l_hood
FROM
	neighborhoods
JOIN
	police_calls
ON ST_Contains(neighborhoods.geom, police_calls.geom)
WHERE
	police_calls.initial_type_description LIKE '%SHOT%'
ORDER BY
	police_calls.event_clearance_date DESC
;

/* Gang-related */
SELECT
	police_calls.*,
	Extract(month from police_calls.event_clearance_date) AS month,
	Extract(year from police_calls.event_clearance_date) AS year,
	s_hood,
	l_hood
FROM
	neighborhoods
JOIN
	police_calls
ON ST_Contains(neighborhoods.geom, police_calls.geom)
WHERE
	police_calls.event_clearance_description IN (
		'DISTURBANCE, GANG RELATED',
		'ASSAULTS, GANG RELATED',
		'ASSAULTS, GANG RELATED '
	)
;

/* Homicide and gun data in Columbia City */
SELECT
	police_calls.event_clearance_description,
	Extract(year from police_calls.event_clearance_date) AS year,
	Extract(month from police_calls.event_clearance_date) AS month,
	COUNT(police_calls.event_clearance_description)
FROM
	neighborhoods
JOIN
	police_calls
ON ST_Contains(neighborhoods.geom, police_calls.geom)
WHERE
	police_calls.event_clearance_description IN (
		'DRIVE BY SHOOTING (NO INJURIES)'/*,
		'HOMICIDE',
		'PERSON WITH A GUN'*/
	) AND
	s_hood = 'Columbia City'
GROUP BY
	police_calls.event_clearance_description,
	month,
	year
ORDER BY
	police_calls.event_clearance_description,
	year DESC,
	month DESC
;

/* Shots reported in Columbia City */
SELECT
	Extract(year from police_calls.event_clearance_date) AS year,
	Extract(month from police_calls.event_clearance_date) AS month,
	COUNT(event_clearance_description)
FROM
	neighborhoods
JOIN
	police_calls
ON ST_Contains(neighborhoods.geom, police_calls.geom)
WHERE
	police_calls.initial_type_description LIKE '%SHOT%' AND
	s_hood = 'Columbia City'
GROUP BY
	month,
	year
ORDER BY
	year DESC,
	month DESC
;

/* Gang-related */
SELECT
	Extract(year from police_calls.event_clearance_date) AS year,
	Extract(month from police_calls.event_clearance_date) AS month,
	COUNT(event_clearance_description)
FROM
	neighborhoods
JOIN
	police_calls
ON ST_Contains(neighborhoods.geom, police_calls.geom)
WHERE
	police_calls.event_clearance_description IN (
		'DISTURBANCE, GANG RELATED',
		'ASSAULTS, GANG RELATED',
		'ASSAULTS, GANG RELATED '
	) AND
	s_hood = 'Columbia City'
GROUP BY
	month,
	year
ORDER BY
	year DESC,
	month DESC
;

/* Assaults with a firearm */
SELECT
	Extract(year from police_calls.event_clearance_date) AS year,
	Extract(month from police_calls.event_clearance_date) AS month,
	COUNT(event_clearance_description)
FROM
	neighborhoods
JOIN
	police_calls
ON ST_Contains(neighborhoods.geom, police_calls.geom)
WHERE
	police_calls.event_clearance_description IN (
		'ASSAULTS, FIREARM INVOLVED'
	) AND
	s_hood = 'Columbia City'
GROUP BY
	month,
	year
ORDER BY
	year DESC,
	month DESC
;

/* Gun calls */
SELECT
	police_calls.event_clearance_description,
	police_calls.event_clearance_subgroup
FROM
	police_calls
WHERE
	police_calls.event_clearance_subgroup = 'GUN CALLS'
GROUP BY
	police_calls.event_clearance_description,
	police_calls.event_clearance_subgroup
ORDER BY
	police_calls.event_clearance_description
;

/* Drive-by Shootings by Hour of Day in Columbia City */
SELECT
	Extract(year from police_calls.event_clearance_date) AS year,
	CASE Extract(hour from police_calls.event_clearance_date)
		WHEN 23 THEN 'NIGHT'
		WHEN 22 THEN 'NIGHT'
		WHEN 21 THEN 'DAY'
		WHEN 20 THEN 'DAY'
		WHEN 19 THEN 'DAY'
		WHEN 18 THEN 'DAY'
		WHEN 17 THEN 'DAY'
		WHEN 16 THEN 'DAY'
		WHEN 15 THEN 'DAY'
		WHEN 14 THEN 'DAY'
		WHEN 13 THEN 'DAY'
		WHEN 12 THEN 'DAY'
		WHEN 10 THEN 'DAY'
		WHEN 9 THEN 'DAY'
		WHEN 8 THEN 'DAY'
		WHEN 7 THEN 'DAY'
		WHEN 6 THEN 'DAY'
		WHEN 5 THEN 'NIGHT'
		WHEN 4 THEN 'NIGHT'
		WHEN 3 THEN 'NIGHT'
		WHEN 2 THEN 'NIGHT'
		WHEN 1 THEN 'NIGHT'
		WHEN 0 THEN 'NIGHT'
	END AS time_of_day,
	COUNT(police_calls.event_clearance_description)
FROM
	neighborhoods
JOIN
	police_calls
ON ST_Contains(neighborhoods.geom, police_calls.geom)
WHERE
	police_calls.event_clearance_description IN (
		'DRIVE BY SHOOTING (NO INJURIES)'
	) AND
	s_hood = 'Columbia City'
GROUP BY
	time_of_day,
	year
ORDER BY
	year DESC,
	time_of_day
;