Update to my 911 Viewer

Way back in January of this year I blogged about a little experiment I did parsing local traffic-related incidents in my home town. A local police department had posted their data in HTML and I used a combination of YQL and ColdFusion to parse it. This was done via a simple scheduled task. A second task turned street addresses into longitude and latitude pairs. Finally I made use of cfmap to display the results. All in all, I think it was pretty cool. The results matched with what I would have assumed were the busiest streets. But yesterday I discovered something cool. Apparently I left the process on and it ran for six months. For all that time ColdFusion was hitting and scraping the data, cleaning it and importing it into my database. I did a quick SQL to count the number of rows and found I had a bit over 8.5K. I whipped up a quick report template and I thought I’d share the results. Again - this is traffic incident data for Lafayette, LA. A larger city would probably have more. Anyway, first I began with a simple table of general stats:

Next I displayed how many incidents happened per day. No big surprises here - Sunday is the safest day to drive.

The per hour chart is also as you expect - a sharp rise as soon as the afternoon rush hour appears.

Now for the big pie chart. This one displays the amount of incidents per type. My favorite is “Hazardous Situation”:

Next I reported on unique street addresses. I didn’t imagine I’d see a lot on any particular address (street, yes, actual address, no), but I was pretty surprised:

What’s awesome is if you map that top address you see it is right between a school and an insurance company:

Awesome. I then wrote a quick SQL statement that tried to get just the street. It isn’t perfect, but this is what I used:

select mid(address, locate(‘ ‘, address)+1, length(address)) as thestreet, count( mid(address, locate(‘ ‘, address)+1, length(address))) as total
from data
group by mid(address, locate(‘ ‘, address)+1, length(address))
order by count(mid(address, locate(‘ ‘, address)+1, length(address))) desc
limit 0,10

And here is the result:

Finally, for the heck of it, I used SQL to find the average longitude and latitude of all 8600+ incidents to map out the most dangerous spot in Lafayette. Before anyone says it - yeah - I know that’s not really accurate, but it was fun as heck.

All of this was fun - but really unnecessary. Back in January when I first built this, I contacted the police department via their web site to ask about an XML export, but I never heard back.

Edit Thanks to Jason Fisher for the idea and Andrew Powell for the help. I uploaded my data to SpatialKey and created a few heat maps. Their service is amazing. The following thumbnails link to larger images (very large, so click with caution on a mobile device). I just spent a few minutes on this so I’m sure the tool could be used better than what I did, but I’m incredibly impressed by the product.

Raymond Camden's Picture

About Raymond Camden

Raymond is a developer advocate for Extend by Auth0. He focuses on serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support.

Lafayette, LA https://www.raymondcamden.com