GeoIP 索引

库片段

GeoIP 数据上的空间索引，以加快提取速度

与 PostgreSQL 兼容

任何版本

编写语言

SQL

依赖于

无

如果您使用的是 PostgreSQL + ip2location（或 MaxMind），在 ip2location 表上创建以下空间索引可以显著提高性能。

在 ip_from/ip_to 属性上创建空间索引

-- 取自 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));

需要将 IP 转换为 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'))
   );

inet_to_bigint 函数来自同一页面

-- 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;

由 bricklen 添加

另请参阅

• 通过 PLPython 使用 GeoIP