Collecting data through a custom web form
To start we set up a Google Spreadsheet with the fields of information we wanted to collect. We enabled the “Publish to the web” setting on our workbook so its data will be available to us through Google’s API. Then we enabled a web form for the spreadsheet. This provides us a way to enter data into the spreadsheet without granting access to modify it.
Since the default Google form is not so great looking, we built a new web form in a simple HTMl page. To spruce it up we used Twitter Bootstrap, which provides some nice default styles for form elements. Here is how clean our new form looks.
The location of the spill is the most important information we want to collect, so we spent some time making that easy to enter. As you start to type the name of an LGA, the equivalent of a county in Nigeria, you will see an auto-completed list of matching names. When you select one, we save the coordinates of the LGA’s center with your report. Optionally, you can click or touch the inline map to set a more precise location for the spill.
We override the normal HTML submit and use JQuery AJAX to post the data entered in our custom form back to Google’s form. As long as there is not authentication or validation on the Google form, Google’s servers will accept the posted data and store it in the spreadsheet.
Working with the data in Google Spreadsheets
Now that the form submissions are in a spreadsheet, there are a few interesting things you can do with them. First, you can make charts that summarize the responses. These charts are dynamic and will update with new submissions. You can embed the charts on your web page too for dynamic visualization of the responses.
Next, you can use pivot tables for aggregating data. For instance, you could group all of these submissions by LGA to get a count of the total projects in each LGA.
If you want a review-and-approve process for form submissions before they are visualized, you can disable the “republish automatically” setting in the “publish to web” feature, so updates to the spreadsheet will not automatically be made public. Now you can review new submissions, make any necessary changes, and then manually click the “republish now” button to make the new reports publicly available.
Finally, all of the data in the spreadsheet is available through the Google Spreadsheet API, which is what we’ll use to map the data on our own website.
Mapping data from the spreadsheet
To visualize the submissions, we set up another simple web page and used MapBox.js to set up a custom MapBox Streets base map with some colors we chose to match our site. Using the Google Docs extension for MapBox.js, we requested the submissions from the spreadsheet when the page is loaded. Google provides the data in a JSON feed, which the MapBox.js extension formats into GeoJSON. We can easily add that to the map as a layer of markers with custom colors or icons, using MapBox.js’s markers interface.
Now we have a map with custom colors and markers for the locations we’re crowdsourcing on our custom form. As new reports come in, they automatically show up when the map is refreshed. If you wanted to, you could add a simple JS setInterval function to automatically refresh the map every so often.
Making it scale
In our testing, the process of submitting points to the Google form and getting them on the map is instantaneous. We tested loading 25,000 points in a continuous burst through the form and saw no performance problems. Google Spreadsheets can scale to 400,000 cells, so the number of columns per record will impact how many reports you can accept.
Modern browsers will have no trouble showing 1,000 or so points on a map. Older versions of Internet Explorer will choke on a few hundred points. If you’re planning to map a lot of points, try using a pivot table to group your points by something like a state or county. Then you can use count of points in the marker’s tooltip description. If you want to draw circles scaled by the number of points instead of simple pin markers, check out Clustr.
We haven’t had a chance to try this workflow in a popular production application, but Google’s API is generally fast and we’re quite confident the MapBox side of things will scale without any trouble. So give it a shot and let us know what you learn!