There are 90 different maps on the Global Adaptation Index (GaIn), a data browser we built with the Global Adaptation Institute featuring 15 years of data showing countries vulnerability and adaptability to climate change. These maps are powered by a SQLite database, which is fed data from CouchDB using our new couch-sqlite library. We were able to release this openly thanks to support from the Global Adaptation Institute. This new library creates a bridge between two very different databases — CouchDB and SQLite.

Dynamic maps, like those on the Global Adaptation Index, merge dynamic application data (in this case, global climate change readiness, and vulnerability) with static geodata (a world map). To power these maps we needed a geo-aware method to actually merge and query these two types of data sources. Previously we’ve explored PostGIS and Spatialite, and recently looked into GeoCouch. However for this project we didn’t need the complexity of PostGIS or Spatialite. With GeoCouch it’s unclear how we could handle data merges between the dynamic and static data and how well GeoJSON could work as a transport format for detailed geographies.

Luckily SQLite, which Spatialite is built on, includes basic geo-spatial querying as part of its R Tree module, making it a viable solution in this scenario. While basic, this capability is just what we needed to generate our maps, and as a relational database it proved very straight forward to merge our two data sources.

How this works

The general basis for our solution is that the site’s canonical datastore is a CouchDB database, and the maps are generated based on geodata in SQLite databases. Once a SQLite database is subscribed to a CouchDB database, functionality is then provided by our new couch-sqlite library — creating interactive maps. Here is a quick video demonstrating how this works.

Two details that make this possible

There are two technical details about the database technologies used here that make this possible.

1. CouchDB has a changes API that provides a way to query databases for records that have changed. As may be expected, you can use this feature to request a set of changes since a certain point, but you can also establish long-lived connections where changes are provided in near realtime. The couch-sqlite library provides a generic way to establish one-off and long-lived connections, which then write changes from CouchDB down into a SQLite database.

2. SQLite’s [ATTACH DATABASE command](http://www.sqlite.org/lang_attach.html) allows a single query to be run against two JOINed databases that live in separate files. Using this capability, we're able to keep geographic entity definitions in one file and cleanly distinguish that data the application is managing.

How this works with mapping software

One added benefit of this approach was that it allowed us to better integrate TileMill, our map design studio, into our workflow. We designed GaIn’s maps in TileMill using the Carto design language, and then took the project from Tilemill and added it to the GaIn project. Because TillMill is composed of a set of individual projects that handle various bits of map creation — like processing Carto style sheets, localizing data, and rendering actual tiles — we were able to reuse those components cleanly.

We’re very happy with the results of linking CouchDB and SQLite together like this to power our maps. Using SQLite as a specialized geo-index takes advantages of strengths from both SQLite and CouchDB, and using that combination to power dynamic maps designed in TileMill is a nice way to work.

A big thank you to the Global Adaptation Institute for funding the open sourcing of the couch-sqlite library. You can check out the project over on couch-sqlite’s GitHub page.