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