GeoIP index

Library Snippets

Spatial index on GeoIP data to speed up fetches

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing

If your are using PostgreSQL + ip2location (or MaxMind), creating the following spatial index on the ip2location table results in a huge performance increase.

Create a spatial index on the ip_from/ip_to attributes:

-- Taken from http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Fast_interval_.28of_time_or_ip_addresses.29_searching_with_spatial_indexes

CREATE INDEX ip2location_range_gist ON ip2location USING gist ((box(point(ip_from,ip_from),point(ip_to,ip_to))) box_ops);

analyze verbose ip2location;

explain analyze
select *
from ip2location
where box(point(ip_from,ip_from),point(ip_to,ip_to)) @> box(point (3512069689,3512069689), point(3512069689,3512069689));

Query version where the IP needs to be converted to bigint:

explain analyze
select *
from ip2location
where box(point(ip_from,ip_from),point(ip_to,ip_to))
   @> box(
          point(inet_to_bigint('209.85.238.57'),inet_to_bigint('209.85.238.57')),
          point(inet_to_bigint('209.85.238.57'),inet_to_bigint('209.85.238.57'))
   );

The inet_to_bigint functions come from that same page:

-- from http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Bigint_to_ip
drop function if exists bigint_to_inet(bigint);
create function bigint_to_inet(bigint) returns inet as $$
select (($1>>24&255)||'.'||($1>>16&255)||'.'||($1>>8&255)||'.'||($1>>0&255))::inet
$$ language sql strict immutable;
grant execute on function bigint_to_inet(bigint) to public;

drop function if exists inet_to_bigint(inet);
CREATE OR REPLACE FUNCTION inet_to_bigint(inet) RETURNS bigint AS $$
SELECT $1 - inet '0.0.0.0'
$$ LANGUAGE SQL strict immutable;
grant execute on function inet_to_bigint(inet) to public;

added by bricklen

See also

• GeoIP via PLPython