Chris Essig

Walkthroughs, tips and tricks from a data journalist in eastern Iowa

Archive for the ‘Google Documents’ Category

Using hexagon maps to show areas with high levels of reported crime

leave a comment »

Screenshot: Violent crime in WaterlooCrime maps are a big hit at news organizations these days. And with good reason: Good crime maps can tell your readers a whole lot about crime not only in their city but their backyard.

For the last year and a half, we’ve been doing just that. By collecting data every week from our local police departments, we’ve been able to provide our community a great, constantly updated resource on crime in Waterloo and Cedar Falls. And we’ve even dispelled some myths on where crime happens along the way.

The process, which I’ve blogged about in the past, has evolved since the project was first introduced. But basically it goes like this: I download PDFs of crime reports from the Waterloo and Cedar Falls police departments every week. Those PDFs are scraped using a Python script and exported into an CSV. The script pulls out reports of the most serious crimes and disregards minor reports like assistance calls.

The CSV is then uploaded to Google spreadsheets as a back up. We also use this website to get latitude, longitude coordinates from the addresses in the Google spreadsheet. Finally, the spreadsheet is converted into JSON using Mr. Data Converter and loaded onto our FTP server. We use Leaflet to map the JSON files.

We’ve broken the map into months and cities, meaning we have a map and a corresponding JSON file for each month for both Waterloo and Cedar Falls. We also have JSON files with all of the year’s crime reports for both Waterloo and Cedar Falls.

The resulting maps are great for readers looking for detailed information on crime in their neighborhood. It also provides plenty of filters so readers can see what areas have more shootings, assaults, burglaries, etc.

But it largely fails to give a good overview of what areas of the cities had the most reported crimes in 2013 for two reasons: 1) Because of the sheer number of crime reports (3,684 reports in 2013 for Waterloo alone) and the presence of multiple reports coming from the same address, it’s nearly impossible to simply look at the 2013 maps and see what areas of the cities had the most reports, and; 2) The JSON files containing all of the year’s reports runs well on modern browsers but runs more slowly outdated browsers (I’m looking at you, Internet Explorer), which aren’t equipped to iterate over JSON files with thousands of items.

As a result, I decided to create separate choropleth maps that serve one purpose: To show what areas of the cities had the most reported crimes. The cities would be divided into sections and areas with more crimes would be darker on the map, while the areas with fewer crimes would be lighter. Dividing the city into sections would also mean the resulting JSON file would have dozens of items instead of thousands. The map would serve a simple purpose and load quickly.

What would the areas of the cities actually look like? I’ve been fascinated with hex maps since I saw this gorgeous map from the L.A. Times on 9/11 response times. The map not only looked amazing but did an incredibly effective job displaying the data and telling the story. While we want our map to look nice, telling  its story and serving our journalistic purpose will always be our No. 1 goal. Fortunately, hex maps do both.

And even more fortunately, the process of creating hex maps got a whole lot easier when data journalist Kevin Schaul introduced a command line tool called binify that turns dot density shapefiles into shapefiles with hexagon shapes. Basically the map is cut into hexagon shapes, and all the points inside each hexagon are counted. Then each hexagon is given a corresponding count value for the number points that fall in between its borders.

I used binify to convert our 2013 crime maps into hex maps. As I mentioned earlier, we started out with CSVs with all the crimes. My workflow for creating the hex maps and then converting them into JSON files for Leaflet is in a gist below. It utilizes the fantastic command line tool og2ogr, which is used to convert map files, and binify.

# We start with a CSV called "crime_master_wloo_violent.csv" that has violent crime reports in Waterloo.
# Included in csv are columns for latitude ("lat") and longitude ("long"):
https://github.com/csessig86/crime_map2013/blob/master/csv/crime_master_wloo_violent.csv

# Command to turn CSV into DBF with the same name
ogr2ogr -f "ESRI Shapefile" crime_master_wloo_violent.dbf crime_master_wloo_violent.csv

# Create a file called "crime_master_wloo_violent.vrt"
# Change "SrcLayer" to the name of the source
# Change OGRVRTLayer name to "wloo_violent"
# Add name of lat, long columns to "GeometryField"
<OGRVRTDataSource>
  <OGRVRTLayer name="wloo_violent">
    <SrcDataSource relativeToVRT="1">./</SrcDataSource>
    <SrcLayer>crime_master_wloo_violent</SrcLayer>
    <GeometryType>wkbPoint</GeometryType>
    <LayerSRS>WGS84</LayerSRS>
    <GeometryField encoding="PointFromColumns" x="long" y="lat"/>
  </OGRVRTLayer>
</OGRVRTDataSource>

# Create "violent" folder to stash shp file
mkdir violent

# Convert "vrt" file into shp and put it in our "violent" folder
ogr2ogr -f "ESRI Shapefile" violent crime_master_wloo_property.vrt

# Binify the "wloo_violent" shp into a shp called "wloo_violent_binify" with three options
# The first sets the number of hexagons across to 45
# The second sets the custom extent to a box around Waterloo
# Custom extent is basically the extent of the area that the overlay of hexagons will cover
# The third tells binify to ignore hexagons that have zero points
binify wloo_violent.shp wloo_violent_binify.shp -n 45 -E -92.431873 -92.260867 42.4097259 42.558403 --exclude-empty

# Convert binified shp into JSON file called "crime_data_review_violent"
ogr2ogr -f "GeoJSON" crime_data_review_violent.json wloo_violent_binify.shp

# Add this variable to JSON file
var crime_data_review_violent

# Here's the final files:
https://github.com/csessig86/crime_map2013/tree/master/shps/wloo_shps

# And our JSON file:
https://github.com/csessig86/crime_map2013/blob/master/JSON/crime_data_review_violent.json

# And the Javascript file that is used with Leaflet to run the map.
# Lines 113 through 291 are the ones related to pulling the data from the JSON files,
# coloring them, setting the legend and setting a mouseover for each hexagon
https://github.com/csessig86/crime_map2013/blob/master/js/script_map_review.js

# And the map online:
http://wcfcourier.com/app/crime_map2013/index_wloo.php

The above shows the process I took for creating our hex map of violent crimes in Waterloo. I duplicated this process three more times to get four maps in all. One showed property crimes in Waterloo, while the other two show violent and property crime in Cedar Falls.

* Note: For more information on converting shapefiles with ogr2ogr, check out this blog post from Ben Balter. Also, this cheat sheet of ogr2ogr command line tools was invaluable as well.

Another great resource is this blog post on minifying GeoJSON files from Bjørn Sandvik. While the post deals with GeoJSON files specifically, the process works with JSON files as well. Minifying my JSON files made them about 50-150 KB smaller. While it’s not a drastic difference, every little bit counts.

To get choropleth maps up and running in Leaflet, check out this tutorial. You can also check out my Javascript code for our crime maps.

One last note: The map is responsive. Check it out.

Advertisements

Written by csessig

January 11, 2014 at 4:42 pm

How we used a Google spreadsheet to power our election app

with one comment

Election 2013 app screen shot

Last Tuesday was election night in the Cedar Valley. While we didn’t have a huge number of contested races on the ballot, we did have a mayor’s race in Waterloo and Waverly, Iowa, as well as city council races in several area towns.

As NPR’s Jeremy Bowers proudly proclaimed, election nights are exciting times for news nerds. This election, we decided to do a little bit of experimenting.

Before the election, we posted biographical information for all the candidates running in a contested race in Waterloo, Cedar Falls and Waverly. We also promoted other races in smaller towns. This gave our readers a good overview of the races on the ballot and information on the candidates. And best of all, it was all in one place.

Three reporters were responsible for getting the biographical information for each candidate and entering it online. We used a Google spreadsheet to store the information. This allowed the reporters to enter the information online themselves. I could then go into the spreadsheet, edit the text and make sure it was formatted correctly. We then used Tabletop.js to import the data into our app and Handlebars.js to template it.

The basic setup for the app is available on my Github page. This is a very similar setup mentioned in my last blog post.

Before the app went live, I exported all the data in the Google spreadsheet into JSON format using a method mentioned here. I did this for two reasons: 1) It spend up the load time of the app because browsers didn’t have to connect to the spreadsheet, download the data, format the data into JSON (which is what Tabletop.js does with the data in this app) and then display it online using templates rendered in Handlebars.js. Instead, it’s already downloaded, formatted into JSON and ready to be templated. And: 2) Tabletop.js has a bug that may cause some readers not to see any of the data at all. I wanted to avoid this problem.

The night of the election, we wanted to update the election results live. And we wanted to use the same app to display the results. Fortunately, the process was easy to do: I added new columns in our spreadsheet for vote totals and precincts reported. The new data was then pulled into our app and displayed with simple bar charts.

To display the results live, we had to ditch the exported JSON data and use the Google spreadsheet to power the app. This allowed us to update the spreadsheet and have the results display live on our website. We had one reporter at the county courthouse who punched in numbers for our Waterloo and Cedar Falls races. We had another reporter in Waverly who punched in results for races in that town. And we had another reporter in the newsroom who was monitoring the races in rural towns and giving me updated election results to enter into the spreadsheet.

The workflow worked great. While most news outlets were waiting for the county websites to update with election results, we were able to display the results right away. Unfortunately, the Black Hawk County website never wound up working on election, making our app the only place readers could go to get election results.

Our effort paid off: The app received about 11,500 pageviews, with about 10,000 of those pageviews coming the night of election (about 7,000 pageviews) and the following day. The app was more popular than any single story on our website for the month of October and November.

Reporters at the party headquarters said many of the candidates first tuned into the local television station to get election results but quickly went to our webpage when they realized we were the only ones with updated results. In fact, Waterloo’s mayor found out he won his race by looking at our website. Here’s a photo of him checking out our website on election night.

Which leads me to my last point: You’ll notice how our mayor is checking out the results on a smartphone. Our app (like all of our apps) was responsively designed, which means it looks great on mobile phones. It’s critical that news producers make sure their apps work on mobile. It’s pretty much mandatory. Because as our mayor shows, people love checking the news on their phones.

Written by csessig

November 10, 2013 at 10:19 pm

Creating responsive maps with Leaflet, Google Docs

with 9 comments

wloo_history_teaserNote: This is cross-posted from Lee’s data journalism blog, which you can read over there by clicking here.

Quick: Name five people you know who don’t have a smartphone.

Stumped? Yeah, me too. The fact is more and more people have smartphones and are using them to keep up with the world.

What does that mean for news app developers? We need to be especially conscience of the mobile platform and make sure everything we build for the web is compatible on the smallest of screens.

One great way to do this is creating apps through responsive design. The idea behind responsive design is creating one web page for all users, as opposed to making separate pages for desktop and mobile computers.

Then we simply add, rearrange, subtract or tweak features on a web page based on the size of the browser the user has when they are viewing the app.

Maps can be difficult to manage on mobile platforms, especially when you add in legends, info boxes, etc. But they are not impossible. Fortunately Leaflet, an alternative to Google maps, is designed to work especially well on mobile platforms.

In this example, we will be loading data into a Google spreadsheet and using Leaflet to map the data on a responsive map.

1. Learn a little bit of Leaflet

Before we start, it would probably be best if you familiarize yourself with Leaflet. Fortunately, their website has some wonderful walk-throughs. I’d recommend going through this one before going anything further.

2. Grab the code

Now, go to my Github page and download the template.

The Readme file includes instructions on how to set up a basic map using Google spreadsheets and Tabletop.js, which is a wonderful tool that allows us to do all kinds of things with data in a Google spreadsheet, including map it using Leaflet.

3. Edit your index.html page

After you have followed the instructions on my Github page, you should have a map ready to go.

All you have to do is go into the index.html page and edit the title of the map, as well as add your own information into the “sidebar_content” div. Also make sure you add your name to the credits because you deserve credit for this awesome map you are putting together.

4. How does it work?

Now open up your map in a browser. If you rearrange the browser window size, you’ll notice that the map rearranges its size. The other components on the page also automatically readjust.

Some of this is done with the Bootstrap web framework, which was designed with responsive designing in mind.

I’ve also added my own CSS. One easy thing I’ve done with elements on the page is declare their widths and heights using percentages instead of pixels. This ensures that the components will automatically be adjusted regardless of the screen size.

Take a look at our css/styles.css file to get an idea of what I’m talking about: 

/* Body */
body {
    padding-left: 0px;
    padding-right: 0px;
  margin: 0;
    height: 100%;
}

html {
	height: 100%;
}


/* Map */
#map {
	position: absolute;
	float: left;
	top: 1%;
	height: 98%;
	width: 100%;
	z-index: 1;
}

The map’s height is 98 percent and width is 100 percent, ensuring it changes its size when the browser changes its size. If we set it to 600 pixels wide, the map would stay 600 pixels wide, even when the browser was adjusted.

– You’ll notice some other changes. For instance, if you have a wide screen, the map’s sidebar will be on the right side of the screen. We did this by using absolute positioning to place the sidebar and its content on the page:

/* Sidebar */
#sidebar {
  position: absolute;
	top: 2%;
	right: 1%;
	height: 96%;
	width: 30%;
	z-index: 2;
	border: 1px solid #999;
	padding-left: 1%;
	padding-right: 1%;
	background-color: #FFFFFF;
    background-color:rgba(255,255,255,0.9);
}

#sidebar h3 {
	line-height: 30px;
}

#sidebar_content {
	float: left;
	width: 30%;
	height: 70%;
	position: fixed;
	overflow: auto;
	padding-top: 5px;
}

The sidebar’s “right” position is set to 1 percent. This ensures that the sidebar will appear only 1 percent from the right side of the page. Additionally, its “top” position is set to 2 percent. This, effectively, pushes it to the top right corner of the screen.

We also used percentages to declare widths, heights and padding lengths for the sidebar.

– You’ll also notice when your browser is reduced drastically, the content of the sidebar disappears off the page. Instead, we have just the title of the sidebar at the top of the page. This is done with CSS media queries:

/* Styles from mobile devices */
@media (max-width: 625px) {
  
  #sidebar_content {
  	display: none;
	}

  /* Sidebar */
  #sidebar {
		position: relative;
		margin-top: 0%;
		float: left;
		left: 0%;
		right: 0%;
		top: 0%;
		padding-left: 2%;
		padding-right: 2%;
		height: 35px;
		width: 96.5%;
	}

}

Basically what the above code is saying is: If the browser is 625 pixels wide or smaller, apply the following CSS styles. These styles would therefore apply to almost all mobile phones. So what you are saying to the browser is: If this is a mobile browser, apply these styles to the elements on the page.

The first thing we do is hide the “sidebar_content” div, which is within our main “sidebar” div. Besides the “sidebar_content” div, we also have a div within the “sidebar” div called “sidebar_header” for our title. The template sets the title to “Tabletop to Leaflet” initially, although you should change that to match your project.

We hide the “sidebar_content” div with the property “display: none.” Hiding it ensures that the only thing left in our “sidebar” div is the title. Then the sidebar is pushed to the top left corner of the page using absolute positioning.

So what do we do with that information we have hidden? We put it in another div using some Javascript. Then we toggle that div from hidden to visible using a button with the class “toggle_description.” This toggle feature is enabled using jQuery.

From our js/script.js file:

// Toggle for 'About this map' and X buttons
// Only visible on mobile
isVisibleDescription = false;
// Grab header, then content of sidebar
sidebarHeader = $('#sidebar_header').html();
sidebarContent = $('#sidebar_content').html();
// Then grab credit information
creditsContent = $('#credits_content').html();
$('.toggle_description').click(function() {
  if (isVisibleDescription === false) {
		$('#description_box_cover').show();
		// Add Sidebar header into our description box
		// And 'Scroll to read more...' text on wide mobile screen
		$('#description_box_header').html(sidebarHeader + '<div id="scroll_more"><strong>Scroll to read more...</strong></div>');
		// Add the rest of our sidebar content, credit information
		$('#description_box_content').html(sidebarContent + '<br />' + 'Credits:' + creditsContent);
		$('#description_box').show();
		isVisibleDescription = true;
	} else {
		$('#description_box').hide();
		$('#description_box_cover').hide();
		isVisibleDescription = false;
	}
});

The above code first grabs the information from the “sidebar_content” div, then places it in the “description_box” div. It also sets our toggle function, which is activated when the user clicks on the button with the class “toggle_description.”

– The “description_box” div is also styled similarly to the “sidebar” div. The big difference is the “description_box” div is hidden by default because we only want it shown if we are on a mobile phone. The button with the class “toggle_description” is also hidden by default.

From our css/styles.css file:

/* 'About this map' button, description box */
/* Mobile only */
.toggle_description {
  display: none;
	z-index: 8;
	position: relative;
	float: right;
    right: 0%;
    top: 0%;
}

#description_box_cover {
	display: none;
	z-index: 10;
	position: absolute;
	top: 0%;
	width: 100%;
	height: 100%;
    background-color: #444444;
    background-color:rgba(44,44,44,0.9);
}

#description_box {
	position: absolute;
	display: none;
	z-index: 11;
	width: 92%;
	height: 93%;
	padding-top: 1%;
	padding-left: 1%;
    padding-right: 1%;
    left: 2.5%;
    top: 2.5%;
    border: 1px solid #999;
    background-color: #FFFFFF;
    background-color:rgba(255,255,255,0.9);
}

#description_box h3 {
	padding-bottom: 0px;
	line-height: 15px;
}

Now we do the opposite with the “toggle_description” when compared to what we did with the “sidebar_content” div.

With the “sidebar_content” div, we had it shown by default then hidden on mobile phones using CSS media queries. With the button, we hide it by default and then show it on mobile phones using a CSS property of “display: inline.”

From our css/styles.css file:

/* Styles from mobile devices */
@media (max-width: 625px) {

	.toggle_description {
		display: inline;
	}

}

As a noted above, when someone clicks on that button, jQuery toggles between hidden and shown on the button with the class “toggle_description”. It is hidden by default, so it is shown when the user first clicks  it. Then when the user clicks the blue X button (which also has the class of “toggle description”), the box disappears.

A similar philosophy is in place to hide and show the credits box.

That should give you a good idea of what is happening with this map. Feel free to fork the repo and create your own awesome maps.

Have any questions? Don’t hesitate to leave a comment.