incubator-cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jacob Rhoden <jacob.rho...@me.com>
Subject Efficient IP address location lookup
Date Sat, 16 Nov 2013 00:36:23 GMT
Hi Guys,

It occurs to me that someone may have done this before and be willing to share, or may just
be interested in helping work out it.

Assuming a database table where the partition key is the first component of a users IPv4 address,
i.e. (ip=100.0.0.1, part=100) and the remaining three parts of the IP address become a 24bit
integer.

create table location(
    part int,
    start bigint,
    end bigint,
    country text,
    city text,
    primary key (part, start, end));

// range 100.0.0.0 - 100.0.0.10
insert into location (part, start, end, country, city) values(100,0,10,'AU','Melbourne’);

// range 100.0.0.11 - 100.0.0.200
insert into location (part, start, end, country, city) values(100,11,200,'US','New York’);

// range 100.0.0.201-100.0.0.255
insert into location (part, start, end, country, city) values(100,201,255,'UK','London');

What is the appropriate way to then query this? While the following is possible:

select * from location where part=100 and start<=30

What I need to do, is this, which seems not allowed. What is the correct way to query this?

select * from location where part=100 and start<=30 and end>=30

Or perhaps I’m going about this all wrong? Thanks!
Mime
View raw message