iTools: Building a System; Part 3- Connecting to the Data

In yesterday’s installment, I spoke of how we decided to store our data, what freeing the data means for us and for others, and how we found a simpler data solution that worked for the software developer (me) and the layman (John and many other people out there). Today, I want to talk about how we got this data to an interface that is useable for people to analyze, gather, and use.

The Google Docs spreadsheet that I discussed yesterday is great for editing, storing, and sharing. It is not good for summarizing, searching, and viewing in an organized manner. Think of a spreadsheet that has 50,000 rows: you want to see that data broken down by State, or maybe even zip code. Sure, you can write a function and put it in a column and use things like =SUM(B1:B250), but how many people out there can do that?

Today’s accountants certainly know how to use an Excel spreadsheet, a programmer could do this, and people that want to dedicate a few hours of studying can learn from a book or an online tutorial. It is not rocket science, true, but what if instead of forcing people to write difficult to remember functions, we simply had them click on a state on a map, and view all that data? What if we had them look at a bar chart to view the top values, and allow them to click on each bar to drill down further? What if, instead of a keyboard, it was point and click – or, as in the past couple years because of the iPhone and its new younger brother, the iPad — it was touch and swipe?

This is exactly what we wanted. But before we get into how we would display the data, we have to talk about how we are going to get the data out of the spreadsheet, freeing the data from the confines of a grid.

I like open-source, I think you are beginning to understand that. Yesterday, I spoke of sharing data. Now, we can extend this philosophy to sharing programming tools. It’s the same principles as with sharing data, but now, companies, rather than charging you for using their tools, are giving them away for free.

Why would they do this? The answer is simple growth and exposure. If a tool is only sold, then it is only purchased by the few developers implementing it. If it is shared, anyone with an enjoyment for programming can use and expand it. The tool now went from being able to only do five purposes, to doing twenty purposes, just by allowing others to help.

With that said, what did I end up using to display the data? Well, I wanted the project to be simple, so the pages are just html. I wanted the programming to be well adopted, run on many devices, I didn’t want to compile my code. Because of these factors, I chose JavaScript. If any of you use Gmail, you unknowingly are using a heavy amount of JavaScript with each email you send and label you apply. Because JavaScript code is running on your computer, there’s no need to go back to the server to get information — or not nearly as often. This means it’s fast. Fast is what we want.

The other thing JavaScript allows is AJAX, which stands for asynchronous javascript. This is what allows us to grab data for a particular state, and show it below the map, without having to refresh the entire page. Some of you may not remember life before ajax, but it was not pretty. For example, if you clicked on a state on the map, the whole page refreshed. Click another state? Another page load. A good example of AJAX usage is on Netflix’s website. When you click on a movie, a box will popup showing you all information about that movie. By using AJAX, they can first just bring back a little bit of data, until you ask for more. This makes things quick. Secondly, is adds a very intuitive nature to the site. Their interface is also beautiful and very cutting edge.

So, I have my HTML and JavaScript code ready, but since we’ve decided to use Google Docs, I need to figure out how to get at that data in a pretty map. Well, there’s a tool for that also, and you guessed it — it is free. Google has an API called the Google Visualization Query API, which is part of their Google Visualization suite of tools. This is basically more detailed JavaScript functions that enable the programmer to query data from a Google spreadsheet, the same way I would have queried data in a SQL Server database, and instead of paying a hefty sum for a SQL Server license, this is free.

Now I have a way to get at all the data.

Tomorrow, I will expanding on some details of the Query language and explain how we got the maps up and running by actually displaying them on the site, loading information into them, and then making them interactive by showing data in real-time.

Come back tomorrow, when I will talk about the front end map.

This post originally appeared on Grown In The City.