Best way to join on a range?

Question

I think this may be a common problem that may not have an answer for every tool. Right now we are trying to use amazons Redshift. The only problem we have now is we are trying to do a look up of zip code for an IP address. The table we have that connects IP to city is a range by IP converted to an integer.

Example:

Start IP | End IP  | City

| 123123 | 123129 | Rancho Cucamonga|

I have tried the obvious inner join on intip >= startip and intip < endip.

Does anyone know a good way to do this?

Encarnacion

Answer 1

Beginning with PostgreSQL 9.2 you could use one of the new range types,int4range or int8range.

CREATE TABLE city (
  city_id serial PRIMARY KEY 
 ,ip_range int4range
 ,city text
 ,zip  text
);

Then your query could simply be:

SELECT c.zip
FROM   city_ip 
WHERE  $intip <@ i.ip_range;

<@ .. "element is contained by"

To make this fast for a big table use a GiST index:

CREATE INDEX city_ip_range_idx ON city USING gist (ip_range);

But I doubt Amazon Redshift is up to date. We had other people with problems recently:
Using sql function generate_series() in redshift

Audwen

Redshift isn't actually PostgreSQL, it is ParAccel, a fork of Pg 8.1 with various column store features etc. – Deutsch

Answer 2

Try using between, listing the table with the target value second:

select *
from table1 t1
join table2 t2
  on t2.ip between t1.startip and t1.endip

And make sure there's an index on table2.ip.

It should perform pretty well.

Mecham

Answer 3

Assuming the range is contained within TableA, and the ID is in TableB, the following query should work with SQL

SELECT TableA.*, TableB.*
FROM TableA JOIN TableB 
ON TableA.StartIP <= TableB.ID AND TableB.ID <= TableA.EndIP

Metacarpus