Cassandra order by a mathematical expression

Question

I have a column family with following specifications:

CREATE COLUMNFAMILY places (
  name text,    
  category text,
  latitude double,
  longitude double,
PRIMARY KEY (name, category));

What I need, is to search the places and order by distance formula on the basis of latitude and longitudes that is I will provide a set of latitude and longitudes and get results sorted by the shortest distance from those points. This is the query I want to run:

SELECT name, distance(latitude, longitude, 37.8074907, -122.4074504) as distance FROM places ORDER BY distance;

I have written a UDF named distance that can calculate the distance between two sets of latitudes and longitudes. Now I know that Cassandra does not allow order by a non-clustering key but there is no way I can save all the distances and then cluster them.

I am new to Cassandra so if there is any way to change the design of the table somehow or do something with CQL I am open to it.

Posco Grubb Posco Grubb · Accepted Answer · 2018-06-28T03:15:15

As you mentioned, sorting in Cassandra is based on clustering key; that is, order is part of the definition of the table. Your ordering of SELECT results must follow the table design.

Your best bet is to load the SELECT results into memory and sort the data in memory on-demand. (Without more specific information on when and how often you do this operation, it's impossible to give better advice.)

Cassandra order by a mathematical expression

1 Answers