Efficient CouchDB time-range queries

CouchDB is a modern key/value-store that has only limited query capabilities (esp. when compared to a typical relational database management system). All queries are run against materialized views that take only one parameter. This means that it is very easy to query for documents that were created after a certain point in time, or for ones that were created before, it's not possible to query for documents that have been created between 2 points in time in a single query. The client application has to issue 2 separate queries and calculate the intersection of a (potentially very large) set of documents itself.

Taggings:

Submitted by David Markvica on Wed, 11/28/2012 - 19:24
1 answer

Use GeoCouch as a secondary index

There exists a plugin for CouchDB called GeoCouch, which can be used to issue very basic geospatial queries against the database. With this plugin you can formulate queries based on locations and bounding boxes. For example: "give me all the restaurants in a certain (rectangular) area".
This is possible because GeoCouch uses a different indexing system than CouchDB internally.

Using this plugin we can represent the time interval we're interested in as a 1 dimensional bounding box around the document start dates.

view definition:

function(doc)
{
if(doc.begin && doc.end)
{
//start and end time as UNIX timestamps (seconds, not milliseconds)
var begin = Math.round(new Date(doc.begin).getTime()/1000);
var end = Math.round(new Date(doc.end).getTime()/1000);
emit(
{
type: "Point",
bbox : [0,start,0,end]
}, null
);
}
}

query:

http://localhost:5984/entries/_design/entries/_spatial/entriesByPeriod?bbox=0,1280998800,0,128100060

Submitted by David Markvica on Wed, 11/28/2012 - 19:31

Taggings: