How to perform multi-dimensional queries in CouchDB/Cloudant

Question

I'm a beginner with CouchDB/Cloudant and I would like some expert advice on the most appropriate method of performing multidimensional queries.

Example... My documents are like this

{
  _id: 79f14b64c57461584b152123e3924516,
  lat: -71.05204477,
  lng: 42.36674199,
  time: 1531500769,
  tileX: 5,
  tileY: 10,
  lod: 7,
  val1: 200.1,
  val2: 101.5,
  val3: 50
}

lat, lng, and time are the query parameters and they will be queried as ranges. For example fetch all the documents that have

 lat_startkey = -70 & lat_endkey = -72  AND
 lng_startkey = 50 & lng_endkey = 40  AND
 time_startkey = 1531500769 & time_endkey = 1530500000

I will also query using time as a range, and tileX, tileY, lod as exact values

For example
tileX = 5 AND
tileY = 10 AND
lod = 7 AND
time_startkey = 1531500769 & time_endkey = 1530500000

I've been reading about Views (map reduce), and I guess for the first type of query I could create a View each for time, lat, lng. My client could then perform 3 separate range queries, one against each View, and then in the client perform an intersection (inner join) of the resulting document id's. However this is obviously moving some of the processing outside of CouchDB, and I was hoping I could do this all within CouchDB itself.

I have also just found that CouchSearch (json/lucene), and n1ql exist... would these be of any help?

Answer 1

You should be able to use the N1QL query language for queries like this with no problems. N1QL is only available for Couchbase, not the CouchDB project that Couchbase grew out of.

For example, if I understand your first query there, you could write it like this in N1QL:

SELECT * 
FROM datapoints 
WHERE lat BETWEEN -72 AND -70 AND
      lng BETWEEN 40 AND 50  AND
      time BETWEEN 1531500769 AND 1530500000

To run such a query efficiently, you'll need an index, like this:

CREATE INDEX lat_long_time_idx ON datapoints(lat, lng, time)

You can find out more about N1QL here: https://query-tutorial.couchbase.com/tutorial/#1

Answer 2

Sadly CouchDB is extremely poor at handling these sorts of multi-dimensional queries. You can have views on any of the axes but there is no easy way to retrieve the intersection, as you describe.

However an extension was written in the early days of that project to handle GeoSpatial queries (lat, long) called GeoCouch and that extension has been included in the Cloudant platform that you seem to be using. That means that you can do direct queries on the lat/long combination, just not the time axis using the GeoJSON format: https://console.bluemix.net/docs/services/Cloudant/api/cloudant-geo.html#cloudant-nosql-db-geospatial

However Cloudant also has another query system - Query: https://console.bluemix.net/docs/services/Cloudant/api/cloudant_query.html#query

Under this system you can build an arbitary index over your documents and then query for documents having certain criteria. For example this query selector will find documents with years in the range 1900-1903:

{
"selector": {
    "year": {
        "$gte": 1900
    },
    "year": {
        "$lte": 1903
    }
},

So it looks to me as if you could index the three values you care about (Lat, Long and Time) and build a 3 axis query in Cloudant. I have not tried that myself however.