In my last post, we imported data into a mongo database stored in MongoLab’s cloud. We used Cloud9 for the convenience of a ready-to-run environment for using the mongoimport utility and the mongo shell for testing a few queries.

In this post, we’ll discuss performing an post-import update on our stores collection that allows us to exploit mongo’s support for geospatial queries. This will support the use case in which we can find the stores that are closest to a particular location. For example, given a user’s current location, we want to find participating SNAP retailers within a desired range, such as three miles.

Because the update logic is a bit unwieldy to type interactively in the mongo shell, we’ll see how to use Cloud9 to save our update logic as a script that we can load and execute into the shell when we want to run it.

We’ll use the same technique to create a query that we can load into the shell as a function that we can execute with a few arguments so that we can easily run parameterized queries based on a location and desired range to find nearby stores.

Geospatial queries with mongo

Mongo provides a number of ways of supporting various types of geospatial queries. You can learn more about it starting here. For our use case, we can use mongo’s 2d indexes to find distances between points based on geometric projections to a flat Euclidean plane, which is suitable for relatively small distances. If our requirements were more stringent and involved greater distances, it would be better to use mongo’s 2dsphere support.

The data set that we imported from the USDA included longitude and latitude fields. We need to convert that data into a location field of a type expected by mongo to support indexing on geo data.

For each store document stored in our stores collection, we’ll add a new field that uses the store’s latitude and longitude formatted as coordinate pairs as follows:

{ loc: [ lng, lat ] }

With this specific coordinate format, it isn’t necessary but we will want to create a 2d index on these points to improve query performance, as discussed in the next section.

Geo support update script

Right-click on the snapdata folder in the Cloud9 Workspace Files pane and create a new file. Call it postimport.js. Double-click to open it in its own tab. Enter the following script:

print('updating snapdb for geo query support...');
db.stores.find().forEach(function(store) {
  db.stores.update(
    { _id: store._id },
    { $set: { loc: [store.longitude, store.latitude] }
  });
});

print('indexing...');
db.stores.ensureIndex({ "loc": "2d" });

You can ignore the little warning icons in Cloud9 that tell you you’re using an undeclared variable. We’ll load the script into the mongo shell where db is a valid object. You should see something similar to the following:

What the script does is apply an update function for every store document in the stores collection. After mongo is finished iterating through the entire collection, we then use the ensureIndex function to apply an index on GeoJSON points that we added.

Running the update script

In the Cloud9 Terminal, start the mongo shell; then load our newly created update script:

> load('postimport.js')

This will load and execute the script, as shown in the screenshot below. Be warned that this process will take around five minutes! There are different strategies we can pursue to make this a faster process. One would involve running our script on the server instead of from the shell in Cloud9 – but that would require administrative permission not available with the shared plan we’re using with MongoLab; other possibilities include transforming the data before it was imported, but for this blog series I wanted to keep the focus on things we can do to manipulate data within mongo.

Verify the update

In the mongo shell, run the following query to examine a store document:

> db.stores.find().pretty()

From the output, we can verify that our collection has indeed been updated

Creating a geo query

Now that the update script has run and added a new field to each store, our stores collection supports geospatial queries.

We can create another script to make it easy to query the stores collection with a function that accepts coordinates and a desired range. Create a new file called queries.js and add the following code:

// find stores within a target range (in miles)
function findStoresInRange(lat, lng, range, limit) {
    return db.runCommand({
        geoNear: 'stores',
        near: [lng, lat],          // longitude first
        spherical: true,
        distanceMultiplier: 3959,  // return results in miles
        maxDistance: range / 3950, // range in radians
        limit: limit || 100        // 100 is the default anyway
    });
}

This function allows us to easily make parameterized queries. From the mongo shell, we can load this function using load('queries.js').

At this point, you should see something like the following:

We need to supply a latitude and longitude as a reference point for the query. We could use the stores collection itself to find a store, get its location, then run our query to find all the other SNAP stores close to it.

But we can easily use Google’s geocoding service to give us the location coordinates for an address we submit.

Open another browser window and enter a query similar to this example, which uses the address of Hacker Dojo in Mountain View. Use + in place of spaces to separate the address components.

http://maps.googleapis.com/maps/api/geocode/json?sensor=false&address=599+fairchild+dr,mountain+view,ca

Look for the geometry property in the results and note the latitude and longitude values.

Running our query

With our queries script loaded into the shell (described in the last section), we can now execute a query like the one shown below:

> q = findStoresInRange(37.4028344, -122.0496017, 3, 100)

Stores will be printed in the shell, but since we saved the result in a variable, we can take examine it in pieces. To display how many results were returned, enter:

> q.results.length

The results are sorted in distance order in units of miles (since our function provided a distanceMultiplier, which multiplies radians by the radius of the earth in miles to give us unit-friendly results).

We can take a look at individual results, such as the first one, like this:

> q.results[0]

or the last:

> q.results[64]

Next steps

In the past few posts, we covered the process of importing csv data into a mongo database. To save the trouble of setting up our own environment, we leveraged two excellent cloud services: MongoLab for hosting the database, and Cloud9 for providing a development environment that allowed us to interactively work with SNAP data.

In this post, we discussed how we tuned the data to support queries that allow us to locate stores within a desired range of a specific location, and we took a look at what those queries look like.

We now have a solid foundation upon which to build an API to help citizens find SNAP stores near to them.

In the next few posts, we’ll discuss:

• Building an API layer to respons to various REST requests for data using Node
• Building a mobile-first web app to provide a user interface that consumes the API using Node, Express, EJS templates for Node, and Bootstrap
• Moving the API and app into production and setting up an automated job to harvest updated SNAP data on a daily basis
• The open source projects on GitHub that provide a working SNAP API and app; a bit about the architecture and design philosophy; and details how to fork them and keep your fork current, contribute changes, and submit and follow issues.

In my last post, we set up accounts with MongoLab and Cloud9 to prepare to import SNAP data. In this post, we’ll walk through the process of downloading and extracting the data, importing it into our mongo database, and performing a few simple queries.

Recap

At this point, we have a newly created, empty database hosted in MongoLab. Our database user and connection information has been saved in our notes in Cloud9. Your actual connection will be different than the one shown in the following screenshots.

MongoLab database connection and user

Cloud9 notes

Accessing mongo

Let’s confirm that we can access our database. In Cloud9, maximize the Terminal window and then start the mongo shell with the connection string for the database that we previously saved. Remember your connection string will be different.

mongo ds053198.mongolab.com:53198/snapdb -u apiuser -p snapdb

If everything is correct, you will get a welcome message and prompt.

Enter ctrl-c to exit the shell for now.

Using aliases to make connecting easier

To make it easy to access the mongo shell with the same connection string each time, you can enter the following at the terminal prompt to create aliases for the mongo and mongoimport commands. The aliases will be appended to your Cloud9 workspace .bashrc file. The final command will reload .bashrc into the current environment so you don’t have to log out and back in for the aliases to take effect.

$ echo 'alias mongo="mongo ds053198.mongolab.com:53198/snapdb -u apiuser -p snapdb"' >> ~/.bashrc
$ echo 'alias mongoimport="mongoimport -h ds053198.mongolab.com:53198 -d snapdb -u apiuser -p snapdb"' >> ~/.bashrc
$ . ~/.bashrc

Note that the mongoimport doesn’t work with the same url connection string format you can use for mongo, which has the database name appended after the last slash. With mongoimport the host portion used with -h is separate from the database name you specify with the -d option.

Confirm you can log into snapdb at MongoLab by entering mongo at the command line, as shown below:

Download SNAP Data

Now that we’re set up with mongo, let’s download the current SNAP data file using curl:

$ curl -O www.snapretailerlocator.com/export/Nationwide.zip

Unzip the downloaded file to extract the compressed CSV file:

$ unzip -o Nationwide.zip

Don’t try to open the extracted CSV file into the Cloud9 editor – it’s too big! You can examine the top of the file to get a feel for what’s in it using the head command:

$ head store_locations_2013_10_29.csv

By default, you’ll get a dump of the first ten lines of the file. You can see the first line is a header with field names for the data.

Import SNAP Data

Now we can import the data into our mongo database using the mongoimport utility, which can import data in either json, csv, tsv formats.

The header field names are not in an object-friendly format, so we’ll provide mongoimport with the actual field names we’d like to use when it stores the data. We’ll store the field names in a variable that we’ll use with the mongoimport command so that the command itself doesn’t look so long.

$ FIELDS="storeName,longitude,latitude,address1,address2,city,state,zip5,zip4,county"

Now we’ll start the import process. This will take a couple of minutes.

$ mongoimport --type csv --fields $FIELDS --collection stores --file store_locations_2013_10_29.csv

Note this requires setting up the mongoimport that we discussed earlier. If you didn’t do that, you will also need to supply -h, -d, -u, and -p arguments for host, database, username, and password respectively.

If all is going well, you will see output as shown below:

When mongoimport is finished, it will report the number of objects it imported, as shown below. For the most recent data, that’s 249,961 stores.

Querying mongo

Now that we’ve imported data, we can go back into the mongo shell and take a look at it. Again, assuming you set up the mongo alias discussed earlier, simply enter mongo at the terminal prompt.

Enter show collections in the shell; you should see the newly created stores collection in the output. Enter db.stores.stats() to see the metadata associated with the stores collection. Your results should be similar to this:

Listing stores

Enter db.stores.find().limit(5).pretty() to list the first 5 stores with nice formatting.

Removing the header

You probably notice that the first document in the collection isn’t a valid store – it’s the first line of csv file that we imported, which consisted of a header of field names.

There is an option for mongoimport that allows us to specify that the first line is a header, but unfortunately mongoimport will then use those field names instead of the ones we supplied.

We also could have processed the csv file to remove the first line before importing, but because it’s a big file, I chose not to perform any type of operation that might have involved creating a temporary file or copy. For example, the following would have worked, but would have resulted in a copy of the file:

tail =n +2 store_locations_2013-10-29.csv >> output.csv

An easy way to remove this document is remove it after it has been imported. We can remove it with a simple query like this:

> db.stores.remove({ storeName: 'Store_Name' })

And confirm that it’s gone:

Finding stores

Let’s find stores in Chicago:

> db.stores.find({ city: 'Chicago' }).pretty()

This will result in a list with a lot of results. So many, in fact, that they will not be returned all at once. The result of find is a cursor that can be iterated, and the shell will prompt you to type “it” if you wish to iterate to see more results, as shown here:

Just how many results are there? You can find out in the shell either before you submit the find query:

> db.stores.count({ city: 'Chicago' })

or by getting the count for the cursor that find returned:

> db.stores.find({ city: 'Chicago' }).count()

Either method returns the same answer, which for the data I used was 2,402 stores.

If I had wanted to save the cursor in a variable, then I could have done something like this (which will still output results):

> chicago_stores = db.stores.find({ city: 'Chicago' })
...
> chicago_stores.count()
2402

Next steps

We now have data and we can perform queries, such as find the stores within a city or zip code, and with a bit of effort and math (see using the Havesine formula), we can compute distances to stores to find those within a desired range of a specific location.

However, mongo has great support for fast geo queries that we can exploit as long as we provide location data in a specific format. In our next post, we cover the post-import updates we perform to take advantage of this facility.

In my last post, I discussed SNAP data. In this post, we will cover the process of harvesting USDA SNAP CSV data. We will store data in MongoDB and augment it to support fast geo queries. These geo queries will be used to locate nearby SNAP stores from a particular location.

We will exploit two Cloud-hosted services to reduce the amount of effort it takes to create an environment for working with the data. For our purposes, the free plan of both services will be adequate.

• Cloud9 is a Cloud-hosted IDE. We will use the shell environment it provides to perform the tasks necessary to download SNAP data and import it into a mongo database.

• MongoLab provides a Cloud-hosted MongoDB service. We will use it to import and serve SNAP data to support our queries.

Cloud9

Go to Cloud9 and sign up using your GitHub account.

Create a new workspace

Name your new workspace

Call it “snapdb” and select the “Custom” option (which gives you a barebones workspace with just a README.md file). Cloud9 will take a moment while it provisions your new cloud-hosted development workspace.

Open your new workspace

Click the “Start Editing” button. Your workspace will open in a new window (or tab) and initialize.

Prepare your workspace

Right-click on README.md and choose “Delete” from the context menu to get rid of it.

Right-click on the snapdata workspace folder and create a new file. Call it “notes.md”. Double-click to open the file. We’ll use the file to save notes about our mongo database that we’ll create in the next step.

MongoLab

Create a new database

Go to MongoLab and sign up for an account. Once you’re signed in, create a new database.

Create a new database - configuration options

Choose a free sandbox database on Amazon; name it “snapdb”.

Open the database page

Add a database user

To access the database from a client, we need to create a database user. The database user credentials will be used when connecting to snapdb. Click the link that says “Click here”.

Set database user credentials

Choose “apiuser” for the username and “snapdb” for the password.

Save the database information

You’re done with database creation. Save the connection information and database credentials in your Cloud9 notes.

Next steps

We now have everything in place to get SNAP data and loaded into a mongo database. In our next post, we’ll walk through the steps of downloading, importing, transforming, and querying SNAP data interactively from the Cloud9 shell.

In my last post, I introduced SNAPfinder, a mobile-first web app, along with a separate framework and REST API, for helping low-income citizens find nearby participating SNAP stores (SNAP was formerly known as the “food stamps” program).

In this series, we will do a deep dive on the technology stack and tools involved, which, depending on the specific portion of the stack, included Node, MongoDB, Express and EJS templates, Twitter Bootstrap, and Digital Ocean.

What is SNAP?

SNAP stands for the Supplemental Nutrition Assistance Program mandated by the Federal Government and supervised by states to help millions of individuals and families who need financial assistance to buy food. Formerly known as the Food Stamp Program, SNAP provides an economic benefit as well as well as serving to eliminate hunger.

Today, instead of food stamps, eligible recipients are issued Electronic Benefit Transfer (EBT) cards. These cards can be used to purchase food and beverage items authorized by the USDA’s SNAP program at retailers that accept EBT transactions.

About SNAP data

The USDA Food and Nutrition Service maintains and publishes a list of retailers (vendors) across the country that welcome SNAP EBT customers. Data is stored in files in Comma Separated Value (CSV) format, which typically can be read by spreadsheet programs, such as Microsoft Excel. The file is updated regularly and can be downloaded from the following URL:

https://www.snapretailerlocator.com/export/Nationwide.zip

Resource Format

The URL retrieves a zip file that stores a single CSV file with a .csv extension. The name of the file represents the publish date; for example:

store_locations_2013_11_13.csv

When we import the data into mongo, we will map the field names to more JSON friendly names as shown in the following table.

Next steps

In the next post, we’ll leverage two Cloud-host sevices for working with SNAP data. We’ll use Cloud9 to work with an editor and shell that we can use for downloading, importing, and querying mongo data, and we’ll use MongoLab for storing the data that we harvest from the USDA.

We’ll also discuss how we update the data as part of the import process to provide the fields necessary to support mongo geo queries. This will make it easy to answer queries, such as “find the stores within range of a particular location.”

Back in May I blogged about brainstorming with Gwynne Kostin, Director of Digital Services Innovation Center at the GSA about kicking off an open source project to support the Digital Government Strategy.

Gwynne introduced me to Gray Brooks, Sr. API Strategist at the GSA Digital Services Innovation Center, and together the three of us got together on a number of weekly Google Hangouts to bounce our ideas off each other until we had a strategy in place for what we wanted to accomplish around Gwynne’s idea to help out low income citizens. These were our goals:

• To make a positive contribution to society by leveraging the Government’s commitment to Open Data
• To demonstrate a successful model of Open Source collaboration between a Federal Agency and the private sector development community
• To provide a well-documented example of leveraging a technology stack based on Node and MongoDB as a particularly effective way of building high-performance, scalable, cloud-hosted REST APIs for broad consumption.

Currently, the USDA Food and Nutrition Service administers SNAP. If a person wants to find retail stores participating in SNAP, the USDA hosts a flash-based webpage at www.snapretailerlocator.com. We wanted to leverage the same USDA data, and architecturally, we wanted to provide a reference implementation that demonstrated a clear separation of responsibilities between:

• an open source framework for dealing with SNAP data
• a REST API that exposes SNAP functionality to be consumed by app developers
• a mobile-first web app that uses the API to provide a snappy (pun intended) UI

Today I’m pleased to announce SNAPfinder, available on the web and on your mobile phone at the following link:

https://snapfinder.org/

In terms of actual development time, it took just under two weeks for two contributors (myself and Tenny Susanto, Sr. Software Engineer at Coupons.com) to get this out the door with three separate open source projects hosted on GitHub:

• snapfinder - a mobile-first web app that uses the API (Node, Express, EJS, Bootstrap 3)
• snapfinder-api - REST API for querying SNAP data (Node, Express)
• snapfinder-lib - provides underlying framework for importing and querying SNAP data (Node, MongoDB)

The project includes an import job that harvests USDA data daily and imports retail stores in the Mongo database. Mongo provides special support for geo queries, which along with its support for JavaScript makes it very well-suited for pairing with Node for an extremely responsive and scalable API. The web app itself leverages also leverages Node and Express along with EJS templates, as well as Bootstrap 3 for the mobile-first UI support.

Over the next week, I plan to take a deeper dive into the technology and how we built the solution. In the meantime, I welcome you to check out the site and the various GitHub projects. I’m certain the USDA would welcome more contributors as we flesh out the analytics, enhance the UI and add a few more features. And, of course, all involved are certainly delighted to see Open Data project participation.

Last month I blogged about the Digital Government Strategy in the age of GitHub. Inspired by our meetings with Lisa Schlosser (Deputy Federal CIO) and Haley Van Dyck (Senior Advisor to the US CIO), I wrote an API and co-developed an app for bringing Federal challenges to the iPhone.

My intention was to turn over the projects to the GSA, and last week I was able to meet up with Gwynne Kostin (Director of Digital Services Innovation Center in the Office of Citizen Services & Innovative Technologies) and Sheila Campbell (Director, Center for Excellence in Digital Government), along with other members of the team (Karen Trebon, Jacob Parcell, and Tammi Marcoullier), who took time out of a hectic launch day for some great discussion on fostering a development community around Federal open data.

We ended up brainstorming on a project that Gwynne wants to kick off as a model of open source collaboration between the GSA and open source contributors in the private sector from start to finish, concept through live deployment.

The project concept is to make it easy for citizens to find:

• Free or low cost health clinics
• Section 8 housing
• Stores that accept SNAP (“food stamp”) cards

The project will exemplify a GitHub-oriented workflow around source control, wiki, issue tracking, continuous integration, and automated deployment. It will also serve as a recipe for others in leveraging various Amazon Cloud PaaS providers for hosting the API and data and for performing analytics.

To get the project off the ground, I created a repo on GitHub (now here). We’ll begin fleshing out the concept and adding issues for work that needs to get done. If you’re interested in contributing, go ahead and start watching the repo now for issue notifications.

“Big data is (bovine excrement)” — or so says Harper Reed, former CTO for the Obama for America campaign, speaking today at the opening session of the CeBIT trade show in Sydney.

He makes a valid point — the term itself no longer means exactly what it once did. As Reed explains, storing vast amounts of data back in 2007 “was literally hard.” As far as he’s concerned, big data should just be called data; storage is not the issue — it’s the analysis that provides answers to questions that matters.

And he’s right. In the first place, most organizations are not actually storing the amount of data that the term “big data” brings to mind. And for those who are — and for those who do anticipate the need to scale — there are rich solutions today that were difficult or prohibitively expensive to implement five years ago.

Nevertheless, “Big Data” will continue to be relevant as a way to emphasize the difference between traditional data processing techniques and applications and current perspectives on the scale of data that can be captured, analyzed and visualized; the explosion of mobile devices and machine-generated sensor data; and analytics tools and techniques for providing actionable insights in real time.

If you haven’t yet heard about the Digital Government Strategy, you might be a bit surprised by what you learn, and you will certainly be impressed. Of course, the federal government’s use of information technology and data has a long history, as does its investment in research into technological advances that ultimately become a part of the fabric of our daily lives (thank you, DARPA).

The reason why you will be impressed with the Digital Government Strategy, however, is the extensive and comprehensive commitment the federal government has made to embrace the elements of successful Silicon Valley startup culture and open, social, collaborative development to deliver information and services to the citizens of this country — anywhere, anytime, on any platform or device.

Our team paid a visit to Lisa Schlosser, Deputy Federal CIO, back in January, when I learned about how the federal mobility roadmap evolved into the broader digital government strategy. As I learned about her office’s efforts to promote improving citizen access to government data, I naively suggested that her office should consider promoting the use of GitHub as a means of fostering an open source development community to create the APIs and apps that could get data out to the public. Lisa graciously smiled at me and said I really needed to meet Haley Van Dyck, a policy analyst on her team.

A few weeks later, I spent an hour on the phone with Haley. Frankly, I was blown away by how clear the federal vision is and how articulately Haley was advocating and championing ideas that sound normal for startups, open source developers, and members of the GitHub revolution — yet not at all what you expect to hear coming from the government. And, most impressive of all, this wasn’t just talk — the Digital Government Strategy was published not quite one year ago, and there are already success stories.

For example, take a look at RFP-EZ. Aside from the clean, Twitter Bootstrap-styled interface, the most amazing part is the “Fork this site on Github!” link at the bottom of the page. Think and digest on that for just a moment.

Then take a look at the beta site for MyUSA and note the Developers link at the bottom of the page. Yet another aspect of the Digital Government Strategy is to ensure that agency sites provide a developer link.

Well, you can’t spend an hour on the phone with someone like Haley and not be inspired by her contagious enthusiasm to be a part of history and make a contribution to your country. She pointed me out to Data.gov, where I spent some time looking at raw datasets that have been published by various agencies. At her suggestion and with her encouragement, I focused on consuming a federal data source, providing a REST API that could be easily consumed by a mobile device, and open sourcing the project on GitHub.

At Data.gov, I found a link to federal challenges data and the current web portal at Challenge.gov. This is actually a fascinating site to browse because there are actual cash rewards for submitting a winning entry to challenges posted by various agencies through Challenge.gov. I liked the idea, so I decided as a proof of concept to harvest the existing RSS feed, and make these challenges available in an iPhone app.

The result of two weekends and a few evenings of effort resulted in an actual complete app and is shown in the following screenshot.

Challenge.gov iPhone app

The app is an open source project on GitHub. It provides similar functionality to the existing website. The app uses a REST API written in Node.js, hosted by Modulus.io in the Amazon cloud.

The API is a separate GitHub project, which harvests the RSS feed hourly and stores the data in a MongoDB database hosted by MongoLab, also in the Amazon cloud. The hourly harvest job is kicked off using a cloud-based service at CRON.io (a project I recently joined). The rest of the API serves to respond to queries for data (for example, the JSON equivalent of the source RSS feed).

Because there is already an existing Challenges.gov site and this weekend project was more about providing a proof of concept that just happened to turn out to be a complete working app, the project and license will be transferred to the GSA, the agency responsible for the site. However, it is really interesting to note that the government is actually encouraging people in the private sector to feel free to commercialize any of their products that consume open data.

Haley pointed me toward a major success story, iTriage. In fact, the company did so well with its mobile app and site, they have since been acquired by Aetna. The mobile healthtech market is projected to quadruple to $400 million by 2016, so it makes sense that the government’s strategy is to not only increase efficiency within federal agencies and improve citizen access to information, but to stimulate successful commerce as well based on digital innovation.

I would encourage civic-minded developers and entrepreneurs to take a look at the Digital Government Strategy, become a part of a new and growing developer community, and become aware of opportunities to contribute to your country — and pursue financial rewards stemming from this historic opportunity. And if you’re looking for strategic guidance or a partner to work with to pursue cloud and mobile development, feel free to get in touch.