Use PostGIS on open data to get stuff near other stuff

Heat maps are awesome.  You can tell great stories with them, like Axis Philly’s crime change map.  You have x amount of crimes in this neighborhood, y amount of crimes in that neighborhood, and if you have a shapefile of the city’s neighborhoods, you can do this with relative ease: 

axis-philly-crime-map-changes

But I think a lot developers working in open data stop there.  I think the neatest things are yet to be done outside of using shapefiles to define your separate areas.  Last year during a fight about an opening methadone clinic in Northeast Philly, someone was quoted saying there’s more crime around 7-Elevens than methadone dispensaries.  What a great challenge.  I want to play with this stuff so I started with crimes near Septa transit stops and heat map them.  So I threw both Philly crime data and septa data into PostGreSQL and used the PostGIS extension to pull out crime counts nearest each regional rail stop to get this:

septa rail stops heatmap

So for each station, with a latitude, longitude and a total count, I used LeafletJS and the plugin heatmap.js to set this up.  Nice and easy without the need for shapefiles or geoJson or whatever.

The project is hosted on GitHub but the SQL to get the data is, I think noteworthy:

SELECT row_to_json( ROW ) FROM (
   SELECT *, (
      SELECT COUNT(*)
      FROM incident
      WHERE
      ST_DWithin( ST_MakePoint( rail_stops.stop_lon,rail_stops.stop_lat ), incident.point, .0015 ) = 't'
      AND (
         incident.text_general_code LIKE '%Robbery%'
         OR incident.text_general_code LIKE '%Assault%'
         OR incident.text_general_code LIKE '%Rape%'
         OR incident.text_general_code LIKE '%Homicide%'
      )
   )
   AS COUNT
   FROM rail_stops
) ROW;

That SQL code right there asks PostGIS to pull selected crime incidents and count them around each regional rail location.  The juice of it is using the function ST_DWithin to create a buffered circle around the rail stop location, and asking the database to pull back all crimes of the desired type within that circle.

[link] Violent Crime Heat Map of Septa’s Regional Rail Stops

[link] Project page on Github – Crime counts near Septa regional rail stops

Leave a Reply

Your email address will not be published. Required fields are marked *