(Special “If You’re Going To San Francisco” edition)
In part 1 of this series, we looked at how Esri Maps For Cognos – EM4C – allows us to embed a map from an Esri map server inside a Reoprt Studio report. But the map is pretty useless if it doesn’t allow us to connect to our data and perform some kind of analysis that can’t be done with a regular list report, or with some kind of graph.
From a mapping perspective there are a couple of concepts that we need to keep in mind if we are going to bind business data to a map: one is the idea of a point, the other the idea of a shape.
We’ll start with a point. A point is a lat/long value on a map: it is (strictly speaking) an entity with no area. It could be a point that represents a store location, a home address, whatever you like. The important thing to keep in mind is that even if a store (or your house) occupies area, from a mapping/point perspective it is simply a point on the map.
So what kind of data can we plot using points? Crime data is one example – a police call is typically to a particular address. If we can plot these locations on a map, by type, we might gain insights into what kinds of crimes are being reported not just by location, but by location relatively to each other – what kinds of crimes cluster together, geographically.
Crime data for San Francisco for March, 2012 is available on the web, and this data set comes with both category of crime and lat/long of the police report. This makes the data set ideal for plotting on a map.
First, I set up a quick Framework Manager model that retrieves the data from my database. Then, we need a query in Report Studio that retrieves the data:
Note that we have a Category, Description, and X and Y values representing Longitude and Latitude respectively.
I add a map placeholder (as we did in Part 1) and then save the report. (I could, of course, add any additional report items, queries etc to the report that I wish.) I then open the map placeholder in Esri Maps Designer, add a base map, and then add a new layer: the special Cognos X Y Layer. I rename it Crime_Locations:
A wizard enables me to select the query associated with the Crime_Locations layer, which will display points:
Note the inclusion of a Unique Field – this is the IncidentNum from the original data.
Further configuration allows me to then assign the Lat/Long from the data set, and identify each point by the Category of crime.
I now have a set of symbols – coloured squares – that correspond with the categories of my data. When I view my report, I can see the location of each crime, by colour-coded type, at each location it was reported at:
Even at this zoom level I can draw some conclusions about what areas have more crime – the north-east seems to have more reports that the south-east, for example. But by selection of specific crimes, and zooming in, interesting patterns begin to emerge.
The orange squares represent drug-related charges. The green and purple squares are assault and robbery charges respectively. The drug-related charges are more concentrated in one relatively small area, while the assault and robbery charges seem more spread out – but with a concentration of them in the area the drug charges are also being laid.
If we zoom in even closer, we can see that certain streets and corners have more calls than others in close proximity – that the crimes seem to cluster together:
But zooming out again, we see an interesting outlier – a rash of drug charges along one street, with what appears to be relatively few assaults or robberies:
Zooming in we see that this activity is almost completely confined to a 7-block stretch of Haight St., with virtually no activity in the surrounding area, and few robberies or assaults:
This kind of spatial relationship is extremely hard to discern from a list or chart, even a chart that breaks events like police calls down by geographic category of some kind. But using mapping, with a simple zoom we can go from an overall view of patterns of activity to a much higher degree of detail that begins to tell some kind of story, or at least warrant further investigation.
But wait, there’s more…
By hovering over an individual square, I can get additional category information from my underlying data, assuming I have included it in my query. In this case there is a sub-category of the call:
By adjusting the query I can re-categorize my data to yield results by, for example, day of the week, or sub-category. For example, here we can contrast Possession of Marijuana (green) with Possession of Base/Rock Cocaine (pink):
Marijuana possession seems more diffuse, although concentrated in a few areas. The cocaine charges are much more concentrated.
In our next entry in this series, we’ll take a look at allocating data to shapes, to colour-code areas to represent different levels of activity.