How to perform a query with multiple greater/less than condition in CQL/cassandra?

Question

I am writing a map app. Once I got the bounds of map I have to query the DB for points. The structure:

CREATE TABLE map.items (
    id timeuuid,
    type int,
    lat double,
    lng double,
    rank int
)

and I would like to query like this:

select * from map.items 
where type=1 
and (lat > 30 and lat < 35)
and (lng > 100 and lng < 110)
order by rank desc
limit 10;

Newbie to cassandra, please help or provide references. Thank you!

Jim Meyer Jim Meyer · Accepted Answer · 2015-07-25T12:04:33

CQL can only apply a range query on one clustering key, so you won't be able to do that directly in CQL.

A couple possible approaches:

Pair Cassandra with Apache Spark. Read the table into an RDD and apply a filter operation to keep only rows that match the ranges you are looking for. Then do a sort operation by rank. Then use collect() to gather and output the top ten results.
Or in pure Cassandra, partition your data by latitude. For example one partition might contain data points for all latitudes >= 30 and < 31, and so on. Your client would then do multiple queries of each latitude partition needed (i.e. 30, 31, 32, 33, and 34) and use a range query on longitude (using longitude as a clustering column). As results are returned, keep the highest ten ranked rows and discard the others.