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!
