Analyzing Violence in South Seattle

The corner of 37th & Oregon is about a 15 minute walk from my house in Columbia City. Last week it was a candidate for the ignominious title of most dangerous place in America. On July 9th, a person was fatally shot there:

5 days later on July 14th, a second person was fatally shot visiting the above shrine for the first victim. Now there are two shrines less than a block apart (and two police cars parked 24×7):

This is awful. No one wants people dying on any of Seattle’s streets and, in our neighborhood, there’s a perception that we’re sliding backwards in terms of safety. I wanted to dig into the data and see if there is anything to back up the perception that the neighborhood feels substantially more dangerous than before.

The City of Seattle maintains an Open Data portal and I’ve used it to get both crime data (in the form of police 911 calls) and neighborhood boundaries.

The data contains all reports of “shots fired” and I wanted to see if it’s up for 2017 in my neighborhood, Columbia City. We’re likely going to be ahead in July (data only for 1/2 the month) but YTD the numbers are not materially different:

Homicides, however, are up. July’s two murders are that spike below:

We had no murders in 2016. Moreover, 2017’s homicides are different than the one we had in 2015. That one was a shooting within a family. According to the police, the two this year are gang-related.

Moreover, drive-by shootings in Columbia City are way up – 11 YTD vs. 3 in 2016:

Equally concerning is that these shootings are more brazen in timing. These shootings are increasingly occurring during the day (and consequently increasing the likelihood of innocent bystanders getting hit).

For folks in Columbia City, the location of these shootings is also increasingly concerning. The map below shows 2017 YTD drive-by shootings in South Seattle (drive-by shootings in Seattle are exclusively a Central District/South Seattle phenomenon). The three dots below the words “Columbia City” represent three shootings on suburban streets near Columbia City’s main pedestrian thoroughfare:

These are the first shootings in the Angeline to Dawson corridor. There were none there in 2016:

Or 2015:

The goal for South Seattle is zero gang-related shootings in any of our neighborhoods. The data above suggests that gangs are getting more brazen in both the frequency, location and hours of their attacks. I look forward to seeing what the SPD is going to do to combat it and need to figure out what I can do to help make all of South Seattle safe.

Update: created a repo with some of the raw data plus how to do your own analysis.

Finding Nearby Things in QGIS (Tutorial)

If you get excited by maps and places, here’s a reasonably common problem:

  1. I’ve got a set of locations (e.g., landmarks)
  2. I’ve got another set of locations (e.g., points of interest) and those points have additional metadata (e.g., a star rating for each place)
  3. I’d like to find all the points of interest with at least a certain star rating that are within a fixed radius of my landmarks

I want to walk you through one way to solve this problem. (And you can find all the files in this Github repo).

Step 1: Get your data

I’m starting with two .csv files: base (my landmarks) and nearby (my points of interest). Each contains a name of a place, a lat/lon and, for nearby, a rating:






Step 2: Create your QGIS project

Open up QGIS and create a new project. I want to measure distance in meters and the locations I’m using are in the United States to I have set my default QGIS projection to EPSG 2163 – which is measured in meters. This is going to be critical: if you use a projection like EPSG 4269 (NAD 83) then you’ll end up doing all your work in degrees – which is meaningless.

Step 3: Import your .csv files as new layers

Use the command Layers >> Add Layer >> Add Delimited Text Layer. Use “Browse…” to locate the files and import similar to the image below:

After you import both files, you should have a (boring) map like the following:

Here’s a key thing to understand: your .csv files did not have a projection associated with them so QGIS adds the default of EPSG 4326 – which is in degrees, not meters. We need to fix that.

Step 4: Convert the layers to EPSG 2163

Right click on the name of each layer and select “Save As…”. Make sure you set the file name of the new layer and change the CRS to EPSG 2163. I also end the name of my files in the coordinate system (e.g., base_2163) so that I can remember it unprompted.

Step 5: Start a new QGIS project

I now start a new QGIS project so that I can be in the correct coordinate reference system (CRS) – EPSG 2163. Add both the new _2163 shapefiles by using the Layer >> Add Layer >> Add Vector Layer… command.

I added labels to each layer to understand what I’m looking at. You can show both the name and rating on nearby_2163 by using the expression concat( "location", ' | ', "rating"). Your map should look something like the following:

Step 6: Draw radius around landmarks

Use the command Vector >> Geoprocessing Tools >> Fixed Distance Buffer. Set your input layer as base_2163 (and verify that it says [EPSG:2163] next to it. Set your distance to a value in meters (I’m going to use 1,000 for a kilometer). Under “Buffer”, save this a new shapefile by clicking the … :

After you hit the “run” button and rearrange the layers on the map, you should see something like the following:

Step 7: Add landmark info to points of interest

This is where the magic happens. Go to Processing >> Toolbox and the click on SAGA (2.2.3) in the new right hand tab and open up “Vector Point Tools” and double click on “Add polygon attributes to points”.

Your “Points” is nearby_2163 and you want to add the nearby landmark: set “Polygons” to Buffer and select the “Attribute” location. Per earlier, save the new layer as a file:

Your map now has a new layer called “Results” (or similar). Right click on the layer and select “Open Attributes Table” and you should see that each point now has the corresponding nearby location attached:

Step 8: Visualize or export

If you want to see a subset of ratings (e.g., 10 or above), then just right click on “Results” and select Filter… Set the value of ratings to above 9 and you’ll see that one of our locations is now gone:

Here’s the map. For some reason, the names aren’t showing properly on the labels but you can see that only the places rated 10 are showing:

If you save the layer (right click) as an Excel spreadsheet, you’ll get the values in a form you can play with:


Mapping the City

I went to the Conflux Festival on Saturday morning and attended a talk by Matt Knutzen entitled Rebuilding the Historical City.  Matt’s a cartographer working at the NYPL and he was explaining a new tool they’ve built – and the Very Big Idea behind the tool.

The NYPL has over 60,000 maps of NYC in their Digital Gallery, but they’re simply digitized images.  They lack any actual mapping points: latlongs, etc. that can actually be use to project the map onto other maps.  As a result, they’ve decided to build a tool – the Map Rectifier – that allows anyone to convert an image of a historical map into an actual working map and share the results with the world.

The process is simple: you find an old map you want to convert into a working map.  You put it side by side with an OpenStreetMap map of New York.  You then click on a point in the old map and click on a corresponding point in OpenStreetMap.  Once you’ve done at least four points, you click “rectify” and the system warps the image of the old map to fit it onto the real map.  At this point, the old map is converted into a set of latitude and longitudes and can be used elsewhere (the system is also smart enough to tell you if you did a bad job).  I’d show you screen shots, but I can’t get a login to the system; it’s still in invite only mode 🙁

There’s some other cool functionality in the tool: it’s got the ability to crop maps (so you can skip parts of the map) and you can also trace out buildings and add data about them (e.g., it’s a public house, etc.).

This gets better and better because once you’ve converted the map into a set of KML coordinates and you can view it in Google Earth.  For example, here’s a projection of a 1924 aerial set of photos vs. what’s there today (a lot more farmland back then):

Long Island Aerial Images

Here’s another example, from the 20th Ward’s fire insurance map.  You can see what Madison Square looked like before the Garden and the Farley post office were built:

Madison Square with old fire insurance map projected on top

This technology is impressive as you can start to tell and visualize the history of the city.  Moreover, once the system launches, it’s going to be open to the public and anyone can rectify a map (that’s a sea change in how libraries work).  Also, kudos to the NYPL for making the entire system open source: you’ll be able to install the software on your own server and start rectifying your own maps.