Crime Stats

15 Jan

In the open data world, crime statistics are an important data set. But, I have had some recent thoughts about crime statistics which I would like to share.

Where do crime stats come from?

When I worked in that field, we had 3 systems in which we could pull from – CAD, ARS and RMS. The systems worked as follows:

  • CAD – are the calls for service. They were the most current. But also, the crime type and information is the most inaccurate.
  • ARS – These are reports that have been approved by an officer and a supervisor(SGT). They are probably 1 to 2 weeks out from the data of occurrence but are more accurate than CAD.
  • RMS – These are reports that have been approved by records employees and are the final, authoritative source of information. These are possible a month old but are the most accurate.

As you can see, as time increases (we get further away from the data of occurrence) the accuracy of the data associated with the incident increases. Do you know which source the data set you are using comes from?

The City of Albuquerque publishes crime incidents. According to the metadata document:

This dataset contains the block location, case number description and date of calls for service received by APD that have been entered into the case management system and approved by a supervisor.

This leads me to believe that the data would be from ARS. Meaning it is a few weeks behind, but fairly accurate. Looking at the data, the most recent incident is January 8th. Today is the 15th, so it’s a week behind. About right for ARS. Having this metadata means I can be confident in what I am measuring. Good Job Albuquerque!

Which System Should we Query?

If you want to see what is happening now, you need to query CAD. But these data are only good for getting an idea about where incidents are occurring, not what types of incidents.

If you want to look at a month of crime, you should use ARS.

And for any longer historical crime analysis, RMS is the way to go.

But Wait, I have an Issue with ALL Crime Data

First, most crime data only lists the highest charge. One crime. If I shoot someone during a drug deal, this is a murder/homicide. No mention of distribution. If you are studying drug crimes, this will not show up. That is a problem.

We had a case in Albuquerque recently where an individual stole a vehicle that was running. They probably would charge him with unlawful taking of a motor vehicle (not auto theft). But there was a child inside the car. Now it’s a kidnapping. The report would pick the highest charge, kidnapping. But the real criminal intent was auto theft/unlawful taking. As much as I want to see that individual locked away for what he did, the crime statistic does not properly reflect the crime – it actually neglects the primary offense. And this can now be exacerbated by my next issue with crime data.

Lastly, here is a scenario: a person calls 911 because of a shooting, an officer is dispatched, an arrest is made, an attempted murder report is filed and it makes it to RMS – the official record. Everything is good. But the case goes to trial – or it doesn’t because of a plea deal – and the individual is charged/pleas and found guilty of aggravated assault. The court is the official record. A crime of attempted murder never occurred at the location, on the date that the report states, an aggravated assault did. What if the person was found not guilty? A murder never occurred. But the police say it did? Is that libel?

I know this may seem nitpicky, but given the unbelievable number of plea deals and reduced charges, how accurate are our police reports – probably more so than the final case disposition but that is the final truth. Crime stats are not crimes, they are charges if we don’t use the final disposition.

I think this is a new area for crime research. A department reports to the FBI UCR based on RMS, but those charges may not actually be what the courts decided. I would love to see the difference between charges in RMS and final disposition. Maps comparing crimes with final disposition should show much lower levels of crime and far fewer felonies.

Just something to think about.




Creating a Tile Server in Go and Consuming in Leaflet

16 Nov

I have used TileMill to create tiles and use them as a custom basemap in Fulcrum app and I loved it. I wanted to be able to do this in my web maps with Leaflet. After a short search, I found the answer.

What I Wanted

There are tile servers already made for you, such as TileStache, but I wanted to run a small, simple server on windows and I couldn’t modify IIS, or install Apache. How can I do this simply, without infrastructure?

What I Knew and Didn’t

I know a tile server splits data up in to images and serves them based on zoom level and row and column.

I know TileMill is awesome and will export MBTiles for me.

I have no idea what an MBTiles file looks inside.

Github to the Resuce

I found a PHP tile server by Bryan McBride on Github. It was the perfect example. From the code, it was clear that MBTiles are just SQLite DBs. What?!?! That is brilliant and makes using them simple. You just need to query for zoom level, row and column – as Bryan did n his code.

I installed WAMP because it is the easiest way to get Apache and PHP on my machine to test the PHP Server with my own tiles. It worked with no problems. So I know I can generate tiles and server them. Now I need to create a solution that did not require Apache, PHP, or changes to IIS.

My Solution

I chose to work with Go. It would create a simple .exe I could run on any machine.

I needed a SQLite library. I chose go-sqlite3. I grabbed my go to webserver code and started trying to connect to the MBTiles I created. From Bryan’s code, I know there is a table called tiles with columns for zoom_level, tile_column, tile_row and tile_data. Is that all? Are there other tables? And what are the data types in these columns, because Go will make me specify them (there are ways around this).

I googled MBTiles Spec and there it was, on Github posted by Mapbox.  Now I know there is a metadata table as well and all the columns in each table and their types. I started by querying the metadata table just to verify my database connection worked.

Connection to metadata table

Connection to metadata table

Once I got a response, I went to work on the tiles table. I need to connect, query and return a PNG with the data.

func Tiles(w http.ResponseWriter, r *http.Request) {
w.Header().Set(“Access-Control-Allow-Origin”, “*”)
w.Header().Set(“Content-Type”, “image/png”)
vars := mux.Vars(r)
z := vars[“z”]
x := vars[“x”]
y := vars[“y”]
db, _ := sql.Open(“sqlite3”, “./tiles.mbtiles”)
rows, _ := db.Query(“SELECT * FROM tiles WHERE zoom_level = ? AND tile_column = ? AND tile_row = ?”, z, x, y)

for rows.Next() {

var zoom_level int32
var tile_column int32
var tile_row int32
var tile_data []byte
rows.Scan(&zoom_level, &tile_column, &tile_row, &tile_data) //tile_data blob)



The above code is the handler function for the route:

Route{ “Tiles”, “GET”, “/{db}/{z}/{x}/{y}”, Tiles,}

I set two headers, one that allows cross origin and another that specifies I am returning an image.

I then grab the variables from the route. I only grab z,x and y. I have {db} in the route, but I am hard coding this in the map for now. In the future, by passing it, I can use one route to grab different tiles.

The query passes parameters using ? and then specifying a variable for each ?.

Lastly, I loop, scan and write out the results. the great part is I read bytes in tile_data and w.Write wants bytes. No conversion of types needed.

I now have a tile server. The complete code is on Github.

Connecting to server and getting back an image (tile)

Connecting to server and getting back an image (tile)

Connect to the Server from Leaflet

Connecting from leaflet is as easy as creating your standard map, then adding an L.tilelayer:

var mbTiles = new L.tileLayer(‘http://localhost:8080/tiles/{z}/{x}/{y}’, {
tms: true,
opacity: 0.7

The URL to the server is our Route: /{db}/{z}/{x}/{y}. I hard coded the {db} so you will see in the URL it already has tiles and starts at {z}.

You can now watch the network traffic in the developer tools of your browser and see the requests for tiles at the different zoom levels. Using tiles loads my data seconds faster then when I bring it in as layers.

Download the code, run go build and then drop the server in a folder with an mbtiles file named tiles and you are ready to go.

If you want a PHP Tile server option, Bryan pointed me to this one.

Graph Databases II: Routing

18 Aug

I wrote a previous post on graph databases that did little more than add some nodes and a relationship. The post required me to install neo4j and python py2neo. It was simple, but it was a start. I have moved on to other things since then, most notably Go. But I have found myself coming back in a round about way. I recently wrote a plugin for Open Trail Data in Leaflet. Trails are a network but the data is really just lines. I thought if we add nodes at the starting points, intersections and eds of the trails, we could use them for routing. I set off to work.

The Data

The first step was grabbing a simple network. I used the Open Trail Data dummy data. I was going to draw nodes then import them in to a graph database, but I decided to shelve that for later – when I know I can route. The data is drawn in the image below.

Open Trail Data

Open Trail Data

The Graph

Next, I needed to create the graph database. I started with all the endpoints of the lines and then added nodes to the intersections. The code below shows how I created two nodes and a path between them.

one = Node(“trail”,id=”1″,junction=”1″)
oneint = Node(“trail”,id=”1″,junction=”1,2,3,4″)
p=Path(one, Rel(“connects”),oneint)

The end result was a graph database as seen in the image below.

Finished Graph Database

Finished Graph Database


Now it was time to route. I do not have weights or costs on the edges. I just wanted to see if I could get from one point to another. I chose node 3 to node 6. They are both on the left. I like it because there are two possibly ways to go. To route, I used the following query:

results=graph.cypher.execute(“START beginning=node(22),end=node(32) MATCH p = shortestPath(beginning-[*..5000]-end) RETURN p”)

The nodes are 22 and 32 because that it the internal ID assigned by the database. My result is a path:

(:trail {id:”3″,junction:”3″})-[:connects]->(:trail {id:”1″,junction:”1,2,3,4″})-[:connects]->(:trail {id:”1″,junction:”1,7″})-
[:connects]->(:trail {id:”7″,junction:”7,6,8,9,12″})-[:connects]->(:trail {id:”6″,junction:”6″})

This tells me the database is going from node 3 to the intersection of 1,2,3,4 to the intersection of 1,7 to the intersection of 7,6,8,9,12 to the end node 6.

You can see the nodes by using

resultPath = results[0][0]
for n in solved:
   print n

Now I know all the nodes required to get from one point to another. If I added (lat,long) values to each node, I could have drawn the path on a map.

I will put this in my pocket for later. I am sure it will come up again and I will be better suited to solve a problem. In the meantime, I think I will work on figuring out how to get a standard data format in to the Database – shapefile or geojson?

Bypass Cognos Forms

29 Jul

The City of Albuquerque has a Cognos report that allows you to see employee earnings. You can view the report on their transparency website or you can download a copy on the open data site. I do not want to go through a Cognos front page every time I need the data and I do not want to check when the last time they exported the xls or xml version to the open data site. I want to grab a fresh copy – I really want a service. Since they do not have one, we are stuck using Cognos every time to get the live data. Luckily, we can script it.

Cognos has several JavaScript functions cognosLaunch(), cognosLaunchWithTarget() and cognosLaunchInWindow(). These functions take different parameters and then call cognosLaunchArgArray(). Where do you get the JavaScipt library from? The City of Albuquerque – or anyone who has Cognos installed. The file is located at:

You can link to this file in your HTML

Now, you just need to know how to format the inputs properly. You can find all the information you need by running the report on the transparency site first. When the report finishes, view the source. You will see all the variables highlighted in the image below:

Cognos Report Source reveals all the parameters.

Cognos Report Source reveals all the parameters.

Now, format the function, passing the correct data. For cognosLaunch(), you will have the function below:

“/content/folder[@name=’Transparency’]/report[@name=’Transparency Report – Graded employees’]”,”ui.action”,”run”,”run.prompt”,

Put this in an HTML file in the <script> section and you will launch a window and download the CSV automatically. I have put a file on GitHub. There is another example which includes HTML and a JS file. The CABQ.js file formats the function for you. In this file, you could pass optional search parameters. I will leave that part up to you – I like grabbing all the data.

You can pas different outputFormats as well – CSV, HTML, PDF, singleXLS, XHTML, XLWA, and XML. Lastly, the City does not allow ajax calls from cross domain, so you may need to have the CORS extension installed in chrome. You can get it from

How would I use this in production? I think I would run a simple Python(cherrypy) or Go server that hosts the HTML and JS. Then I would write my application to call the site. I know where the download will go, so I could parse it when done. Then I could either return it to the server or do something with it on my machine.

ESRI REST Geocoding Service in Go

9 Jul

The first thing I wanted to do with Go was to connect to a REST Endpoint. This is an easy task, something I do a lot, and it provides some great possibilities for larger applications. The full code is on GitHub Code, but I will walk through it section by section below.

First, declare the package and import the needed libraries.

package main

import (

We us fmt to print out the result to the console, net/http to connect to the ESRI service, io/util for reading the data the page returns and encoding/json to read the results -which are returned as json.

Next, we need to define the structure of our results.

type geocoderesult struct{
Candidates []candidates
type candidates struct{
Address string
Location struct{
X float64
Y float64

The GeoJSON result from the geocoding service will have a bunch of data and the results are stored in an array named candidates. You need a struct that will grab that result set. The struct geocoderesult has a Candidate []candidates. We need to define candidates. The second struct defines the candidate as contianing an Address and a Location. Location is also a struct that contains and x and y value. The structs match the JSON response.

Now, we create our main function to run the program.

func main(){

response, err := http.Get(“http://server/Geocode/findAddressCandidates?Street=100+lomas&f=json&#8221;)
if err != nil {
fmt.Printf(“%s”, err)



The function connects to the service and passes the parameters street and f. These are part of the ESRI REST API and I have explained them in other posts. The result of http.Get returns a response and an error. If there is an error, we Printf it. In the else statement, we can print the results.

defer response.Body.Close()
c, _ := ioutil.ReadAll(response.Body)
var data geocoderesult


We close the Body when we are done with it – defer allows us to keep the close close to where we open it. Next, we read the body of the response. In Go, there are a lot of functions that return more than one value (a value and an error). If you do not want to use one of the values returned, you need to use an underscore. Otherwise, if you declare it with a name and don’t use it later, the application will not run.

The next line declares data as a new empty geocoderesult. We then unmarshal(decode) the json response – c – to data. Notice the ampersand before data? Go uses pointers. The ampersand says that we want to put the unmarhaled json data in to the location of data in memory. Basically, replace the current value of data with the json.

Lastly, we can grab the first result returned from the service and print it. Indexes start at 0. Data contains a slice of Candidates so we can index on it using data.candidates[x]. Then candidates has fields for address and location.

Now you can build the application and run it. To make it more useful, you could have it read a textfile of addresses and write out a new file with their locations. Or, reverse geocode a series of points. Then you could build the binary and run it all the time.

In my next post, I will show you how to wrap this code in to a library so we can call it from another program as a function ourlibrary.geocode().

Go Library for ESRI REST

9 Jul

In my last post, I showed how to consume an ESRI REST geocoding service using Go. In this post, I will consume a feature service and query it. This post will take it one step further. We will put the code in a library so that anyone can use it in their applications. The full code is on GitHub.

Usually, the first line is package main, but we will change it to the name for our new package. then we can import the same libraries as in our geocoding sample.

package esrirest

import (


The structs that we will create will be different as well. In the geoocoding example we wanted candidates. When running a query, we want features.

type query struct{
Features []features

ESRI GeoJSON uses Attributes instead of properties, so we will create a map of type interface for the attributes in the feature. This allows us to grab any data types without having to hardcode them as we did in the geocoding example (address, location {x,y}).

type features struct{
Attributes map[string]interface{}

In case we need it later – I ever get around to adding more functionality – I will put the variable data outside the function.

var data query

Now, instead of writing a main() function, we will write a function Query – an not include a main in this program because it is a library, and not meant to be executed stand alone. The Query functino will take a string (URL with parameters) that points to the ESRI service. It returns a slice of features. The rest of the code looks exactly like the geocoding example.

func Query(s string) []features {

response, err := http.Get(s)
if err != nil {
fmt.Printf(“%s”, err)

} else {
defer response.Body.Close()
c, _ := ioutil.ReadAll(response.Body)

return data.Features

The one difference is that the function returns the slice of features – data.Features. You now have a functioning library that you can use in any program.

Use the Library

I will now show you how to use the library.

Run the command:

go get

This will grab the plugin and install it locally for you. Now write your application.

Declare the package to be main and import the needed libraries. In this case we only need fmt and our new library that we created, downloaded and installed.

package main

import (


If you look in your pkg directory, you will see a folder with your OS type (windows_amd64) then, PaulCrickard, Go and a file esrirest.a. That is the path you use for the import.

Create your main function and now just call the function in our library passing the URL.

func main(){
//Pass query parameters in URL. MUST HAVE —–> f=json
d :=esrirest.Query(“*&f=json&#8221;)

All of the data is now in d. We can now access features and attributes.


Or, we can iterate through all the features and print the titles.

for i:=0; i<len(d); i++{

What if you do not know what the attribute names (keys) are? You can iterate through and grab them.

for key, value := range d[0].Attributes{


This will print out the key and value for the first feature. Throw this inside your for loop above to go through each feature.

You can grab just the keys by using:

for key, _ := range d[0].Attributes{


Or print the values:

for key, _ := range d[0].Attributes{


Now you know how to crate a library, how to install it and how to use it in another application.


The Go Language

9 Jul

I am primarily a Python and JavaScript developer  (I throw in an occasional C# application which is usually for creating REST endpoints on the server side). But recently, I have started looking more seriously at Go.  The Go language is not replacing JavaScript for me, but it could replace a big chunk of my Python and C# code.

I see two big advantages in using Go – besides being fast and the whole concurrency brouhaha:

  1. Cross platform
  2. No dependencies.

Before you yell that Python is cross platform too, let me explain. The same Go code runs on Windows, MAC and Linux. So does Python, right? Well, Go code compiles to binaries for each of the operating systems. This is a big deal for me and one of the reasons I like C#. I can just deploy the .exe and it runs. No command line. No .bat file to pretend it is an .exe. This brings me to the second advantage.

Everything you need to run a Go application is in the binary. C# is great, but I can’t count the number of times I deployed an application and the user had an old version of .NET and it didn’t work. With Python it’s even worse. My development machine has every third party library I need, but getting this setup on every machine I want to deploy to is a nightmare (Docker looks like it could help with this).

There are other things I like about Go – godoc, gofmt, goget. There are also things I don’t like (I am not proficient with)  – pointers.

In later posts, I will share my Go code. For now, here is the obligatory Hello World application.

package main

import “fmt”

func main() {
fmt.Println(“Hello, World”)