Monday, June 30, 2008

Skinny Love Primavera Sound Festival 2008

A friend I was with recorded this video of Bon Iver at the Primavera Sound Festival in Barcelona. We were sitting in the third row. It was my favorite show of the entire weekend. If you have an opportunity to see them, it's an amazing musical experience--I strongly recommend it. (Plus, it doesn't hurt that Bon Iver is a Wisconsinite, support your local midwest artists!).

Sunday, June 15, 2008

Querying Big Datasets with MySql and Java

I received this error when calling the executeQuery() method on a PreparedStatement object. You'll note that within the JDBC MySql code, there is a method invoked called "readAllResults." It appears that the default behavior of the executeQuery method is to read all data records into memory before returning! This simply will not do when dealing with large datasets.

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
at com.mysql.jdbc.MysqlIO.nextRow(
at com.mysql.jdbc.MysqlIO.readAllResults(
at com.mysql.jdbc.PreparedStatement.executeQuery(

The solution was non-intuitive but an easy find with Google. From MySQL JDBC Memory Usage on Large ResultSet:

If you are working with ResultSets that have a large number of rows or large values, and can not allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

To enable this functionality, you need to create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,

The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this any result sets created with the statement will be retrieved row-by-row.

There are a few caveats, which are described in the following links:

Friday, June 06, 2008

Bicing Usage Survey Now Live!

As part of my ongoing research in "green computing" I have been working on a project at Telefónica Research on improving the Bicing system. As a first step in this direction, we have created a Bicing usage survey. Bicing is a shared biking system in Barcelona, Spain (think of it like a shared car system ala ZipCar). Citizens pay 24 Euros a year for the ability to use bikes parked around the city. There are a few really clever ideas that Bicing employs.

Bicing Overview
For the 24 Euro a year payment, you can ride for 30 minutes for free. After that, each 30 minutes costs 30 cents up to 2 hours. Bicing is meant to be used for commuting--thus, if you surpass your 2 hour limit, monetary penalties begin to emerge (3 euros / hour). Bicing applies service penalties (reduced access to Bicing) if you exceed the two hour limit on more than three occasions. Note, however, that you can bike everywhere in Barcelona easily within an hour so two hours is more than enough time. If you keep the bike out for 24 hours, you pay 150 Euros (the cost of the bike). Bicing is meant to provide an alternative form of transit in the city (e.g., to reduce congestion). As a result, it is not a service for tourists, only for residents of Barcelona (and surrounding areas). The kiosks are in two languages: Spanish and Catalan (Catalan is the regional language here).

IMG_4134 (768x1024) IMG_4199 (768x1024)

Figure 1. (left) A Bicing kiosk. (right) A close-up of the Bicing LCD screen. Note that only two languages are used: Spanish and Catalan.

Checking-in / Checking-out Bikes
The Bicing system relies heavily on technology to control and enforce usage. The Bicing cards are RFID. To check out a bike, one simply swipes their Bicing card at the Bicing kiosk. The kiosk contains an LCD flat screen that tells the user which bike was just unlocked for him/her. If the Bicing station contains no usable bikes (e.g., either it's empty or the bikes at the station do not work), the Kiosk informs the user about other stations close by. To return a bike, the user simply drops the bike at another Bicing station and the bike is auto-locked back into the grid. Note that since this system is so digitally oriented, it's very amenable to being studied (e.g., usage data comes nearly for free--kind of like real-world click through data). Bicing users can log into the Bicing website and see a chronological log of their usage. Note that this data only shows temporal activity and does not include data on which Bicing stations are used. The aggregate data of which Bicing stations are full/available is available online as well (see below).

IMG_4151 (1024x768)_safeIMG_4153 (768x1024)

Figure 2. (left) The Bicing RFID card. (right) A user scanning his RFID card across the Bicing kiosk.

Figure 3. A bike is automatically unlocked after the Bicing user scans his/her RFID card. The bike is automatically locked back into the station once the two prods on the underside of the handle bars are placed into the appropriate nodes.

Real-Time Bicing Information
The Bicing website uses the Google Maps API to create a near real-time geo-spatial visualization of bike usage (so you can check online if a Bicing station near home or work has bikes available). Here's the official website map and here's a slightly better version created by a Bicing user. Bicing Map

Figure 4. The Bicing map displaying stations with bikes available (in green) and not available (in red).

Bicing Bike Design
The bikes themselves are made of custom parts that do not interoperate with normal bikes (thus, thieves have little incentive to steal them). They are also quite unique in shape and coloring. Two self-powered lights are used (one headlamp in the front and one red light in the back) for safety. A three-speed gear shifter and a bell chime are located in opposing positions on the handlebars. Also, a handy basket is located on the front handlebars for carrying groceries, bags, or other things.IMG_4127 (1024x768)_cropped Figure 5. Two Bicing bikes locked up to a Bicing station.

Figure 6. Bicing baskets being put to good use (photo credit: Dave Chiu).

Some Bicing Problems
One problem that the Bicing system faces is that of load balancing. Often times the bikes become disproportionately distributed in the city. This is probably due to two factors: (1) Barcelona, as a city, is built on a hill. Thus, we speculate that many Bicers choose to bicycle downhill but not uphill (e.g., they commute on Bicing in only one direction). (2) The resident and commercial density of the city is not evenly balanced (i.e., many Barcelona citizens commute in the same directions). As a result, Bicing uses trucks to move bikes around the city. We spoke to a Bicing truck driver last week. He told us that the city has multiple trucks, each driver is assigned to a region of Barcelona and is informed via mobile phone on where to pick-up/drop-off bikes. The problem of load balancing is significant as it is a major deterrent to usage. Many Bicing users complain about going to a Bicing station to pick up a bike and finding it empty or arriving at a Bicing station to drop off a bike and finding it full.

Another issue which many Bicers are quick to point out is that although the Bicing service runs 365 days a year, it is not open 24 hours a day / 7 days a week. From Sunday through Thursday: the service is open all day except for between 12AM and 5AM, at which time you can only return bicycles (you cannot check them out). On Fridays and Saturdays: The service operates 24 hours. I would imagine that this constraint is based on some agreement with the city and has to do with safety.

IMG_4185 (1024x768) IMG_4187 (1024x768)

Figure 7. (left) A Bicing truck. (right) The inside of the Bicing truck.

IMG_4182 (1024x768) IMG_4184 (1024x768) IMG_4122 (768x1024) IMG_4193 (768x1024)

Figure 8. (top) A Bicing truck driver moving bikes into an empty station. (bottom) The before and after.

The Bicing Usage Survey
We created an advertisement to promote our survey in three different languages: English, Spanish and Catalan.

Spanish - Llamada a todos los usuarios de bicing!
Somos un pequeño grupo científico de Telefónica Investigación y Desarrollo trabajando en informatica ecológica y sostenible. En uno de nuestros proyectos, estamos estudiando el sistema Bicing en Barcelona. Nuestro objetivo es aplicar tecnicas de inteligencia artificial e informatica avanzada para mejorar el sistema Bicing y vuestra experiencia con el. Por ejemplo, uno de nuestros objetivos es conseguir que las bicicletas estén distribuidas de una forma más eficiente. Este es un proyecto nuevo y queremos involucrar a la parte más importante del sistema Bicing: sus usuarios!. Por ello, hemos creado una encuesta online para recoger vuestra opinión y experiencia de uso con Bicing. La encuesta solo os llevara entre 5 y 10 minutos y los datos son totalmente anónimos.

Contamos con vuestra colaboración.

Català - Crida a tots els usuaris de bicing!
Som un petit grup científic de Telefònica Recerca i Desenvolupament treballant en computació ecològica i sostenible. En un dels nostres projectes estem estudiant el sistema Bicing de Barcelona. El nostre objectiu es aplicar conceptes de intel•ligencia artificial i recerca en informàtica per millorar el sistema Bicing. Per eixample, un dels nostres objectius es que les bicicletes estiguin distribuïdes d'una forma més eficient. Com que aquest projecte està començant volem involucrar la part més important del sistema Bicing: els seus usuaris! Doncs, hem creat una enquesta online per recollir l'opinió de l’ús (positiva i negativa) de Bicing. Trigareu entre 5 i 10 minuts a realitzar l'enquesta, les dades son totalment anònimes.

Esperem la vostra col•laboració

English - Call to All Bicing Users!
We are a small scientific group at Telefónica I+D working on "green computing" research. In one of our projects, we are studying Barcelona’s Bicing system. Our goal is to apply advanced concepts in computer science to improve the Bicing experience, so that, for example the bicycles are better distributed in the city. This project is just beginning and we wanted to involve the most important piece of the Bicing system: its users! Therefore, we have created an online survey to acquire feedback about your experience (positive and negative) with Bicing. The survey will only take about 5-10 minutes to fill out and is completely anonymous.

We look forward to your feedback!