HTML5’s local SQL database & OpenLayers

I recently experimented with HTML5’s offline storage database capability to evaluate if it could be useful in a browser based mobile geo-application…

Applications for mobile devices often suffer from a lack of bandwidth and consistent network availability. Persistent offline client-side storage may be useful to get around these issues. Such technology could be especially relevant for geography field-trips etc, where data would be stored on the device then taken to a remote location away from reliable radio access.

Although offline databases using SQL are currently available in Webkit (Safari and iPhone Safari), Google Chrome and Palm’s WebOS. I’ve decided to concentrate this application on the iPhone. The HTML5 database storage capabilities are presently implemented as a wrapper to an underlying SQLite database layer.

Searching for features with EDINA’s Unlock Places gazetteer, my test web application uses an input form to ask for a place-name. It then composes and issues a GET request to the Unlock Places nameSearch API. This responds with a list of features in JSON containing various attributes such as name, lat/long and feature type, amongst other attributes.

An example of a single feature is shown below:

…

  {
    "type": "Feature",
    "id": "4574609",
    "bbox": [-3.1995506286621094, 55.949745178222656,
        -3.200449228286743, 55.950252532958984],
    "properties": {
      "name": "Edinburgh",
      "country": "United Kingdom",
      "countrycode": "GB",
      "featuretype": "seat of a first-order administrative division",
      "custodian": "GeoNames",
      "gazetteer": "GeoNames",
      "scale": "unknown",
      "centroid": "-3.2,55.95",
      "population": "435791",
      "footprint": "http://unlock.edina.ac.uk/ws/footprintLookup?format=json&identifier=4574609"
    }
  },

…

The features are also displayed on an OpenLayers map as pins/markers. I included the iPhone OpenLayers JavaScript from Whit (discussed in a previous blog), enabling the map to respond to iPhone gestures for zooming and panning etc.

From here, the user can select to store the retrieved features in the local database. The database is created using the new HTML5 window method openDatabase. This method returns a database object which I’m assigning to the variable unlockDB.

The user can then clear the page of search results, or close down the browser etc knowing that the persistent database has stored these features.

…

  var shortName = 'unlockDB';
  var version = '1.0';
  var displayName = 'Unlock mobile client database';
  var maxSize = 5000000; // in bytes

  var unlockDB = openDatabase(shortName, version, displayName, maxSize);

…

The openDatabase method takes 4 arguments:

1. Database name
2. Database version
3. Display name
4. Estimated size, in bytes of the data that will be stored in the database

My database name is unlockDB, the version is 1.0, the database display name is ‘Unlock mobile client database‘. The maximum database size is initially capped at 5MB. The database name parameter specifies the name you will use to reference the database in the code, and the display name is probably how the database will be displayed in some sort of database browser that might be bundled with HTML5 compatible browsers, although I couldn’t see Safari on the iPhone making use of it.

The database interface provides asynchronous access through a transaction paradigm:

…

  transaction.executeSQL( SQLStatement, SQLParameters,
    ResultsetCallback, ErrorCallback )

…

…like so:

…

  db.transaction(function(tx){
    tx.executeSql('SELECT * FROM MyTable
        WHERE CategoryField = ?',
    [ selectedCategory ],
    function (tx, rs) { displayMyResult(rs); },
    function (tx, err) { displayMyError(err); } );
  });

…

This example shows how to execute arbitary SQL statements, and so fetch data from the database. The functions displayMyResult(rs) and displayMyError(err) are just placeholders for whatever you want to do with the result once it arrives. I’m just traversing the list of features, inserting and retrieving them using INSERT / SELECT statements.

Here’s how it looks in action…

…

unlockDB.transaction(
  function (transaction) {
  var p = data[dataID];
  console.log("INSERTing: " + p.id + ", " + p.name + ", " + p.featuretype +
    ", " + p.country + ", " + p.custodian + ", " + p.lat + ", " + p.lon);
  transaction.executeSql("INSERT INTO unlockresults(id, name, featuretype,
    country, custodian, lat, lon) VALUES (?, ?, ?, ?, ?, ?, ?)",
    [p.id, p.name, p.featuretype, p.country, p.custodian, p.lat, p.lon]);
  }
);

…

So at any time in the future, the stored features and attributes can be retrieved and displayed back to the user. Note the use of the console logger – very useful for debugging!

Working locally with datasets and synchronizing with a server only when needed offers up exciting new ways of building web applications. Of course, in many cases local storage or session storage can suffice – and I wonder whether not having an explicit “offline” mode might cause problems. Others are concerned about the limited database size – the current default database size on iPhone is 5MB. However if your database grows beyond this limit, the user will be requested to allow or deny the size increase. However, it’s worth remembering that it’s really half the amount of data that you might expect, since every char needs two bytes in UTF-16.

Google’s mobile GMail client already makes use of a database to assist with performance over high-latency networks, but few others are making use of the technology, perhaps because of complexity, or the gaps in browser support. I think additional JavaScript libraries will soon emerge to help support working with databases though.

Integration with OpenLayers was fairly straight-forward in this example, and plan to evolve it in to a fully functional mobile client for the Unlock Places gazetteer. That’s pretty much it for the basics, I’m certainly impressed.

The demo application is available here – access currently only available to GeoNames source data.

http://unlock.edina.ac.uk/mobile.html

Advertisement