I think you’ll want to model your table similar to how an R-Tree [1] / Quad
tree [2] works. Let’s suppose you had a 10x10 meter land area and you wanted
to put stuff in there. In order to find “all the things in point x,y”, you
could break your land area into a grid. A partition would contain all the
items that are in that grid space. In my simple example, I’d have 100
partitions.
For example:
// space is a simple "x.y" text field
CREATE TABLE geospatial (
space text,
item text,
primary key (space, item)
);
insert into geospatial (space, item) values ('1.1', 'hat');
insert into geospatial (space, item) values ('1.1', 'bird');
insert into geospatial (space, item) values ('6.4', 'dog’);
This example is pretty trivial, and doesn’t take into account hot partitions.
That’s where the process of subdividing a space occurs when it reaches a
certain size.
[1] https://en.wikipedia.org/wiki/R-tree <https://en.wikipedia.org/wiki/R-tree>
[2] https://en.wikipedia.org/wiki/Quadtree
<https://en.wikipedia.org/wiki/Quadtree>
> On May 5, 2017, at 12:54 PM, Nitan Kainth <[email protected]> wrote:
>
> Make metadata as partition key and x,y as part of partition key i.e. Primary
> key. It should work
>
> Sent from my iPhone
>
>> On May 5, 2017, at 2:40 PM, Lydia <[email protected]> wrote:
>>
>> Hi all,
>>
>> I am new to Apache Cassandra and I would like to get some advice on how to
>> tackle a table creation / indexing in a sophisticated way.
>>
>> My aim is to store x- and y-coordinates, accompanied by some columns with
>> meta information (m1, ... ,m5). There will be around 100,000,000 rows
>> overall. Some rows might have the same (x,y) pairs but always distinct meta
>> information.
>>
>> In the end I want to do a rather simple range query in the form of e.g. (0
>> >= x <= 1) AND (0 >= y <= 1).
>>
>> What would be the best choice of variables to set as primary key, partition
>> key. Or should I use a index? And if so on what column(s)?
>>
>> Thanks in advance!
>> Best regards,
>> Lydia
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [email protected]
>> For additional commands, e-mail: [email protected]
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
