Idea: Put only range end points in the table with primary key (part, remainder)
insert into location (part, remainder, city) values (100,10,Sydney) // 100.0.0.1-100.0.0.10 is Sydney insert into location (part, remainder, city) values (100,50,Melbourne) // 100.0.0.11-100.0.0.5 is Melb then look up (100.0.0.30) as select * from location where part=100 and remainder >= 30 limit 1 For nonused ranges just put in an empty city or some other known value :) /Janne On Nov 16, 2013, at 04:51 , Jacob Rhoden <jacob.rho...@me.com> wrote: > > On 16 Nov 2013, at 1:47 pm, Jon Haddad <j...@jonhaddad.com> wrote: >> Instead of determining your table first, you should figure out what you want >> to ask Cassandra. > > Thanks Jon, Perhaps I should have been more clear. I need to efficiently look > up the location of an IP address. > >> On Nov 15, 2013, at 4:36 PM, Jacob Rhoden <jacob.rho...@me.com> wrote: >> >>> 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! >>
